Add letters to sequential numbers

[bassplayer79]

Hello everyone, this is my first post. I had a little programming background, but as of recently my boss is teaching me how to create Access and VB applications. Its been quite a learning experience!!


Ok heres my issue. I created an application that generates tags for stock. I start the first number with 1111, when a button is pushed it creates a new tag with 1112 and so on. I need to add WA to the beginning of the numbers. I created a function for this, and here is the code:

Public Function Generate_Number_Click()
'Create Washer Serial Number
Dim lngsnum As Long

lngsnum = DMax("[WAS]", "table1")

lngsnum = lngsnum + 1

wastxt = "WA" & lngsnum

End Function

It clearly did now like when I added "WA", as I keep getting a mismatch error. I was hoping it would be that easy, but I guess not.

[RuralGuy]

"WA" is a string value and you cannot put it in a numerical field! Is "wastxt" a control on your form? To what type of field is it bound?

[bassplayer79]

"WA" is a string value and you cannot put it in a numerical field! Is "wastxt" a control on your form? To what type of field is it bound?

wastxt is the name of the text box i want to but the text (WA1111) in. From there it is dumped into a database along with other information to tie everything together

[RuralGuy]

Does the wastxt control have a ControlSource? If so, what is it? If it is a Field in the RecordSource of the form, what type of Field is it?

[bassplayer79]

Does the wastxt control have a ControlSource? If so, what is it? If it is a Field in the RecordSource of the form, what type of Field is it?

Yes it does have a control source, it is bound to the "WAS" field in the table1 table. The field type is text, long.

[June7]

Access 2010 doesn't have a 'long' text field. There are Text and Memo. I think 2013 changed those to Short and Long Text.

[bassplayer79]

I want to make sure I didn't mislead you. If I remove "WA" &, everything works beautifully.

[bassplayer79]

Access 2010 doesn't have a 'long' text field. There are Text and Memo. I think 2013 changed those to Short and Long Text.

Your right, its just text

[RuralGuy]

I strongly suspect your "WAS" field is numeric and not Text. Try doing this and see what you get:
wastxt = "WA9999" '-- & lngsnum

[bassplayer79]

I strongly suspect your "WAS" field is numeric and not Text. Try doing this and see what you get:
wastxt = "WA9999" '-- & lngsnum

Heres a snapshot. It says text, but maybe Im missing something. I think you're missing something from your code, if I put that in, '-- & lngsnum would be commented out.

[RuralGuy]

Or even better would be:
Me!WAS = "WA9999"
...which will bypass the control and write directly to the Field in the table.

[RuralGuy]

That was the point, to just write text to the control. Thanks for the picture. It sure looks like a Text field to me. Now that we know it is a Text field you may wish to review the [ lngsnum = DMax("[WAS]", "table1") ] line since ASCII does not sort like numbers do.

[bassplayer79]

That was the point, to just write text to the control. Thanks for the picture. It sure looks like a Text field to me. Now that we know it is a Text field you may wish to review the [ lngsnum = DMax("[WAS]", "table1") ] line since ASCII does not sort like numbers do.

Do you have an suggestions, I was under the impression I could reuse this code for this purpose, but apparently not.

[RuralGuy]

Let's solve the "mismatch error" first. Any chance you could post the db? Removing any sensitive data of course. Compact and Repair and then ZIP it before posting. If not, we can still take shots at it.

[bassplayer79]

No I can post it, nothing sensitive, still in dev stage.