A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Seperate ID Prefix and ID Number



 
 
Thread Tools Display Modes
  #1  
Old March 12th, 2009, 04:35 PM posted to microsoft.public.access.tablesdbdesign
Barry A&P[_2_]
external usenet poster
 
Posts: 119
Default Seperate ID Prefix and ID Number

I am playing with a barcode reader in my database. and from what i can figure
out standard procedure is to set focus to a control then read the scanner.
i am wondering if anybody has any great ideas to have the barcode itself tell
access what to do. here is what i am hoping..

On my report that i use to print barcodes i can concatenate a prefix to each
Barcode type ie; "*LNID" & [locationid] & "*" = Location Barcode, PNID =
PartNumbr, SNID = SerialNumber Ect..

Then with your help of course i would like some code that is somehow linked
to a unbound Control [barcodeScan]
Set the extracted 4 digit prefix from the barcode as CodePrefix
Set remaining digits as CodeNumber

If Code prefix = "SNID" open form "Serialnumbers" find record serialnumber
= Codenumber. (call lookup combos created by the wizard on other forms??)
Else if Codeprefix = "PNID" Open Form "PartNumbers" Find record partnumber =
codenumber.
Else If there is no prefix assume it is a part number and open form part
numbers.. Can i Do this?? because most barcodes from manufactures would not
have my prefix... (worse case is i could have an additional control with a
list of prefixes that would have to be selected prior to scanning a
Non-Prefixed barcode then concatenate the controls prior to running this
code??)

I would only have 4 or 5 prefixes so they may just be referred in code. or
would it not be too complicated to have prefixes in a table with another
field that would open a certain form so the possibility of 20 prefixes could
be "pre Planned"

any help on this code or a complete new direction would be appreciated..

Thanks
Barry
  #2  
Old March 14th, 2009, 09:02 AM posted to microsoft.public.access.tablesdbdesign
Jack Cannon
external usenet poster
 
Posts: 151
Default Seperate ID Prefix and ID Number

Barry,

You certainly must have the focus set to the proper control otherwise you
would not know where the scanner is sending the data.

I would suggest that you set the scanner to automatically send a LF or CR
character after the scan. This will produce an immediate return after the
data is scanned which will negate the need to depress the Tab or Enter key on
the keyboard in order to continue.

Something like this should accomplish your objective.

Private Sub barcodeScan_AfterUpdate()
On Error GoTo Err_barcodeScan_AfterUpdate

Select Case UCase(Left(Me.barcodeScan, 4))
Case Is = "SNID"
DoCmd.OpenForm "Serialnumbers", acNormal, , "[serialnumber]=" & _
Right(Me.barcodeScan, Len(Me.barcodeScan) - 4)
Case Is = "PNID"
DoCmd.OpenForm "PartNumbers", acNormal, , "[partnumber]=" & _
Right(Me.barcodeScan, Len(Me.barcodeScan) - 4)
Case Is = "XXXX"
' Do something similar
Case Is = "YYYY"
' Do something else
Case Is = "ZZZZ"
' Continue as long as you like
Case Else
DoCmd.OpenForm "PartNumbers", acNormal, , "[partnumber]=" & _
Me.barcodeScan
End Select

Exit_barcodeScan_AfterUpdate:
Exit Sub

Err_barcodeScan_AfterUpdate:
MsgBox "Procedure is barcodeScan_AfterUpdate" & vbNewLine & _
Err.Number & vbNewLine & Err.Description
Resume Exit_barcodeScan_AfterUpdate

End Sub

Jack Cannon


"Barry A&P" wrote:

I am playing with a barcode reader in my database. and from what i can figure
out standard procedure is to set focus to a control then read the scanner.
i am wondering if anybody has any great ideas to have the barcode itself tell
access what to do. here is what i am hoping..

On my report that i use to print barcodes i can concatenate a prefix to each
Barcode type ie; "*LNID" & [locationid] & "*" = Location Barcode, PNID =
PartNumbr, SNID = SerialNumber Ect..

Then with your help of course i would like some code that is somehow linked
to a unbound Control [barcodeScan]
Set the extracted 4 digit prefix from the barcode as CodePrefix
Set remaining digits as CodeNumber

If Code prefix = "SNID" open form "Serialnumbers" find record serialnumber
= Codenumber. (call lookup combos created by the wizard on other forms??)
Else if Codeprefix = "PNID" Open Form "PartNumbers" Find record partnumber =
codenumber.
Else If there is no prefix assume it is a part number and open form part
numbers.. Can i Do this?? because most barcodes from manufactures would not
have my prefix... (worse case is i could have an additional control with a
list of prefixes that would have to be selected prior to scanning a
Non-Prefixed barcode then concatenate the controls prior to running this
code??)

I would only have 4 or 5 prefixes so they may just be referred in code. or
would it not be too complicated to have prefixes in a table with another
field that would open a certain form so the possibility of 20 prefixes could
be "pre Planned"

any help on this code or a complete new direction would be appreciated..

Thanks
Barry

  #3  
Old March 17th, 2009, 05:08 AM posted to microsoft.public.access.tablesdbdesign
Barry A&P[_2_]
external usenet poster
 
Posts: 119
Default Seperate ID Prefix and ID Number

Jack
Exactly what i was looking for..
Thank You

"Jack Cannon" wrote:

Barry,

You certainly must have the focus set to the proper control otherwise you
would not know where the scanner is sending the data.

I would suggest that you set the scanner to automatically send a LF or CR
character after the scan. This will produce an immediate return after the
data is scanned which will negate the need to depress the Tab or Enter key on
the keyboard in order to continue.

Something like this should accomplish your objective.

Private Sub barcodeScan_AfterUpdate()
On Error GoTo Err_barcodeScan_AfterUpdate

Select Case UCase(Left(Me.barcodeScan, 4))
Case Is = "SNID"
DoCmd.OpenForm "Serialnumbers", acNormal, , "[serialnumber]=" & _
Right(Me.barcodeScan, Len(Me.barcodeScan) - 4)
Case Is = "PNID"
DoCmd.OpenForm "PartNumbers", acNormal, , "[partnumber]=" & _
Right(Me.barcodeScan, Len(Me.barcodeScan) - 4)
Case Is = "XXXX"
' Do something similar
Case Is = "YYYY"
' Do something else
Case Is = "ZZZZ"
' Continue as long as you like
Case Else
DoCmd.OpenForm "PartNumbers", acNormal, , "[partnumber]=" & _
Me.barcodeScan
End Select

Exit_barcodeScan_AfterUpdate:
Exit Sub

Err_barcodeScan_AfterUpdate:
MsgBox "Procedure is barcodeScan_AfterUpdate" & vbNewLine & _
Err.Number & vbNewLine & Err.Description
Resume Exit_barcodeScan_AfterUpdate

End Sub

Jack Cannon


"Barry A&P" wrote:

I am playing with a barcode reader in my database. and from what i can figure
out standard procedure is to set focus to a control then read the scanner.
i am wondering if anybody has any great ideas to have the barcode itself tell
access what to do. here is what i am hoping..

On my report that i use to print barcodes i can concatenate a prefix to each
Barcode type ie; "*LNID" & [locationid] & "*" = Location Barcode, PNID =
PartNumbr, SNID = SerialNumber Ect..

Then with your help of course i would like some code that is somehow linked
to a unbound Control [barcodeScan]
Set the extracted 4 digit prefix from the barcode as CodePrefix
Set remaining digits as CodeNumber

If Code prefix = "SNID" open form "Serialnumbers" find record serialnumber
= Codenumber. (call lookup combos created by the wizard on other forms??)
Else if Codeprefix = "PNID" Open Form "PartNumbers" Find record partnumber =
codenumber.
Else If there is no prefix assume it is a part number and open form part
numbers.. Can i Do this?? because most barcodes from manufactures would not
have my prefix... (worse case is i could have an additional control with a
list of prefixes that would have to be selected prior to scanning a
Non-Prefixed barcode then concatenate the controls prior to running this
code??)

I would only have 4 or 5 prefixes so they may just be referred in code. or
would it not be too complicated to have prefixes in a table with another
field that would open a certain form so the possibility of 20 prefixes could
be "pre Planned"

any help on this code or a complete new direction would be appreciated..

Thanks
Barry

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:32 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.