Table settings

[Nick T]

Hi,

I have a table which has product records in it.
In one of the columns i have a barcode number such as 05015622200123.
I need this number to remain as is, however i am currently loosing the '0'
off the beginning of the number.

Any suggestions as to how i can sort this???

Many thanks

 

[John W. Vinson]

Hi,

I have a table which has product records in it.
In one of the columns i have a barcode number such as 05015622200123.
I need this number to remain as is, however i am currently loosing the '0'
off the beginning of the number.

Any suggestions as to how i can sort this???

Many thanks

Store it in a Text datatype field rather than any sort of Number. You'll never
be doing arithmatic with it; number fields will not only lose leading zeros
but will also have limited precision.

 

[Nick T]

Hi John,

Thanks for the tip. Iv tried this, and changed my other related tables to
reflect the data as text, however the text box on my form for which the
barcode is inputted into has the following code attached in its 'After
Update' event. Because i changed the format to text, i keep getting an
expression error and visual basic is highlighting the '.FindFirst "[Barcode]
= " &Me.Barcode part of the code. Any suggestions?? Thanks:

Private Sub Barcode_AfterUpdate()
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("Product List", dbOpenDynaset)
With rst
.FindFirst "[Barcode] = " & Me.Barcode

If .NoMatch Then
MsgBox "Barcode " & Me.Barcode & " Not Found in Product List -
See Supervisor!"
Else
Me.Product = !Product
Me.Outer = !Outer
Me.Shelflife = !Shelflife
End If

.Close
End With
Set rst = Nothing

End Sub

 

[John W. Vinson]

Because i changed the format to text, i keep getting an
expression error and visual basic is highlighting the '.FindFirst "[Barcode]
= " &Me.Barcode part of the code. Any suggestions?? Thanks:

A Text field needs syntactically required quotemarks around the search
parameter: try

.FindFirst "[Barcode] = '" & Me.Barcode & "'"

For clarity (don't do it this way) that's

.FindFirst "[Barcode] = ' " & Me.Barcode & " ' "

The result will be that FindFirst will use a search parameter such as

[Barcode] = '05015622200123'

which will work correctly.

If you have not done so already, put an Index on the barcode field in table
design; make it a unique Index (or a Primary Key) if there should only be one
record in the table for a given barcode value.

 

[Len]

Nick,

I think the new text version of the bar code needs to have quotes around it
in the code. Try adding single quotes as follows:

Change
.FindFirst "[Barcode] = " & Me.Barcode
to
.FindFirst "[Barcode] = '" & Me.Barcode & "'"
(note the single quote after the equal sign and between the double quotes at
the end.

Change
MsgBox "Barcode " & Me.Barcode & " Not Found in Product List -
to
MsgBox "Barcode '" & Me.Barcode & "' Not Found in Product List -
(note the single quotes inserted before and after the double quotes adjacent
to the ampersands).

I'm crossing my fingers but this always seems to work for me!

Len




--
"All those who suffer in the world do so because of a desire for their own
happiness. All those happy in the world are so because of their desire for
the happiness of others." --Shantidiva

Hi John,

Thanks for the tip. Iv tried this, and changed my other related tables to
reflect the data as text, however the text box on my form for which the
barcode is inputted into has the following code attached in its 'After
Update' event. Because i changed the format to text, i keep getting an
expression error and visual basic is highlighting the '.FindFirst "[Barcode]
= " &Me.Barcode part of the code. Any suggestions?? Thanks:

Private Sub Barcode_AfterUpdate()
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("Product List", dbOpenDynaset)
With rst
.FindFirst "[Barcode] = " & Me.Barcode

If .NoMatch Then
MsgBox "Barcode " & Me.Barcode & " Not Found in Product List -
See Supervisor!"
Else
Me.Product = !Product
Me.Outer = !Outer
Me.Shelflife = !Shelflife
End If

.Close
End With
Set rst = Nothing

End Sub

Store it in a Text datatype field rather than any sort of Number. You'll never
be doing arithmatic with it; number fields will not only lose leading zeros
but will also have limited precision.

 

[Nick T]

Genius!!
Thanks for your help!

Because i changed the format to text, i keep getting an
expression error and visual basic is highlighting the '.FindFirst "[Barcode]
= " &Me.Barcode part of the code. Any suggestions?? Thanks:

A Text field needs syntactically required quotemarks around the search
parameter: try

.FindFirst "[Barcode] = '" & Me.Barcode & "'"

For clarity (don't do it this way) that's

.FindFirst "[Barcode] = ' " & Me.Barcode & " ' "

The result will be that FindFirst will use a search parameter such as

[Barcode] = '05015622200123'

which will work correctly.

If you have not done so already, put an Index on the barcode field in table
design; make it a unique Index (or a Primary Key) if there should only be one
record in the table for a given barcode value.