Need a form to relieve a production log

[payton_fulton]

Greetings Everyone,



I need to create a form in access that has a single textbox. When the text box is in focus I need to scan a order # barcode and have it look within a table for that specific barcode # that was scanned and place the current date within a completed field. Any Ideas and Thanks in advance for any responses.

[TG_W]

Does the scanner send the barcode information to a location in the database?

[payton_fulton]

No. What I just need to have happen is when the barcode is scanned in to the text box, I need the current date entered into a completed field for that matching barcodes line within a specific table. For example if I scan the following barcode: 20076100-000, it looks for that number in a table and places 5/10/12 in the completed column on that orders line. Hope this helps and thanks in advance for any responses.

[TG_W]

Where does the scanner send the information? Is it to the text box? I am trying to figure out where the scanned information is stored (even if temporarily) to proceed

[payton_fulton]

The scanner hooked up via USB, would send the barcode to the textbox. The goal would be to scan one barcode after the other repeatidly without have to press any buttons.

[TG_W]

Ok. Try setting the code in the Text Box using the On Change event. Test the code below to see if that will work for you.

Docmd.RunSQL ("UPDATE YourTable " & _
     "SET YourTable.Completed = Date() " & _
     "WHERE YourTable.Barcode = [Forms]![YourFormName]![TextBoxName]"

Hope that helps.

[payton_fulton]

Below is my current code, Table name is "SOXL", and the barcode field name is "Shoporder", the field completion name is "Tube Done" Form Name is "Form1", and the textbox is Text0, when the barcode is scanned the code pops up with the Private Sub Text0_Change() highlighted in yellow. Any ideas and thanks again for your help



Private Sub Text0_Change()

Docmd.RunSQL ("UPDATE SOXL " & _
"SET SOXL.Tube Done = Date() " & _
"WHERE SOXL.Shoporder = [Forms]![Form1]![Text0]"

[TG_W]

Remove the space between Tube and Done in your table and the code. Using spaces and hyphens in field names is bad practice and can lead to many problems. You can delete the space or use an underscore _

[payton_fulton]

I changed my code to the following, I still get the Private Sub Text0_Change() highlighted in yellow

Private Sub Text0_Change()
Docmd.RunSQL ("UPDATE SOXL " & _
"SET SOXL.TubeDone = Date() " & _
"WHERE SOXL.Shoporder = [Forms]![Form1]![Text0]"
End Sub

[TG_W]

I'm sorry. Close the parenthesis - [Text0]")

That should do it.

[payton_fulton]

I don't get the error now, but the date is not appearing in the table "SOXL", could it be a number formatting issue between the form and the table? If so what format would be correct for a barcode like 200759258-000

Thanks in advance

[Epidural]

I believe it would have to be text as it contains the non-numerical "-".

[payton_fulton]

Does the textbox need to be bound to the table?

[TG_W]

You don't have to bind the table. I am trying to recreate the scenario now.

[TG_W]

Ok, after playing with the code some, try I got it to work as below. I hope this will do the trick for you. If not, I am testing a few other things, as well.

Docmd.RunSQL ("UPDATE SOXL " & _
"SET TubeDone = Date() " & _
"WHERE Shoporder = [Forms]![Form1]![Text0]")