Using SQL with an unbound Text Box

[Max2413]

Good evening,

I am working on my inventory tracking system that keeps a record of items removed from the inventory by scanning a bar code with a scanner that automatically sends a carriage return after a scan (hits enter). So I am using the "on Enter" event to write my code.

I would like to simply use the SQL "INSERT INTO" statement. I am usinf an unbound text box as input. Here is what I have so far:

Private Sub scanTextBox_Enter()
    Dim StrSQL As String
    Dim scannedCode As String
    
    scannedCode = scanTextBox.Value
    
    StrSQL = "INSERT INTO [Current Sales] ([Purchase Date], [Purchase Time], [Product Code]) VALUES (Date(), Time(), scannedText)"
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL (StrSQL)
    scanTextBox.Value = ""
    DoCmd.SetWarnings True
    
    
End Sub

Any advice?

[RuralGuy]

This line:

StrSQL = "INSERT INTO [Current Sales] ([Purchase Date], [Purchase Time], [Product Code]) VALUES (Date(), Time(), scannedText)"


...has at least one problem. What do you think is in scannedText?

[Max2413]

I started with the txtBox.Text This is returns the formatted string in the box while it has focus right?

So I started with that, and I was having issues so I tried the .value. That of course is wrong because it returns the default property. Which should be an empty string?

So with using the .text call I still have a problem with getting through a whole cycle.

I can enter text, hit enter, and it highlights the text but dosnt write the new record, so i go to design view, and back to form view, click the text box and it prompts for a parameter value.

Thank you for the not just correcting my code, but questioning it, and please continue to do so.

[John_G]

I think you may have misunderstood what the "On enter" event is - it has nothing to do with the user pressing "Enter". The "On enter" fires as soon as the cursor enters the field (or the user clicks on it)

I think you want to use the "After Update" event instead.

[Max2413]

Wow, how dumb. Strange that I built a version 1 of this program and used the macro builder and it still runs completly through the on enter event... weird. You are right though. I will move things around!

[RuralGuy]

Let us know if it is still an issue.