Reducing SQL code 2

[tag141]

Sorry, me again. Another one of my projects. This time for organising and controling stock. It uses a VB6 front form and access as the database. There are more than 50 items in the db and I would like to reduce the amount of code in the app.

If the user enters 'stockitem1' into the textbox as a barcode, it runs an SQL.

If the user enters 'stockitem2' into the textbox as a barcode, it runs an SQL etc.

Each SQL performs exactly the same function. I was hoping to just have one line of SQL code and somehow have the app recognise the barcode number in the textbox.

If txtSearch = "123456" Then
Set rs = conn.Execute("UPDATE tblStock SET kitdate = #" & Format(Now(), "dd/mmm/yy") & "#, quantity = quantity + 5 WHERE [reagent] = '" & txtSearch.Text & "'")

So instead of having 50 lines and SQLs each with a different txtSearch number I could have something in place of the 123456 so it knows which item it is.

Sorry if it's difficult to understand but it's difficult to explain.

TIA

 

[johnwm]

I'm not in front of my dev machine at present so I can't check this, but it will give you a start.

Sub Text1_change
If Val(Text1.Text) < {your lowest first digit} _
Or Val(Text1.Text) > {Your highest value} Then
Msgbox "Re-scan barcode"
End If
If Len(Text1.Text) = {whatever your length of barcode is} Then
'do your database stuff here 
End If
End Sub

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller