You can also use the format property of the textbox on a form to display the text then have it disappear when the box gets focus.
In the format property use: @;"Type Your Name Here"
Like all things in Access there are multiple ways to "skin a cat".
You can change the control to a combobox and change the columns to hide the ID and show the name.
You can use DLookup to get the name based on the ID.
You can change your forms recordsource query to join the tables and get the...
Where are you putting this code? The Form_Current of which form? Your popup?
If so, that is why there is an error. This should be on the Form_Current of your tool list.
This works for me in your stripped database:
Dim rcrdst As DAO.Recordset
Dim varTN As Variant
varTN = InputBox("Enter a Tool Number or Part Number.")
Set rcrdst = CurrentDb.OpenRecordset("SELECT * FROM ToolingNotes WHERE TN_ToolNumber = '" & varTN & "' AND TN_Critical = TRUE", dbOpenDynaset...
I also notice you make a reference to TN_ToolNumber, but this is not a field in your table.
It looks like you need [Tool Number]. Be sure to use the brackets since your field name has a space in it.
Since your ToolNumber is text you need single quotes around it.
Like so:
Private Sub Form_Current()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim varTN As Variant
varTN = InputBox("Enter a Tool Number or Part Number.")
Set rs = CurrentDb.OpenRecordset("SELECT * FROM ToolingNotes WHERE...
A recordset is just that, a set of records. You specify what records with the SELECT statement just like a form. The recordset lives in memory while the code is running.
CurrentDB is just a reference to the connection.
So the code is saying in the database that I'm in, open a set of records...
The way I would go about this would probably be something like this:
Bind the popup form to the notes table and the textbox to the message.
Then this code would go in the OnCurrent event of your tooling page:
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblNotes WHERE...
This would probably be best in the after update of your textbox since most scanners move fields after they scan. Then you would simply check for the string and if it doesn't start with "EN" then set the field to "" and set focus.
You don't have to break out the date in delimiters. Date() is a function that does not require being broken out.
So from the original string all that is necessary is to put quotes around the SQL string.
Docmd.RunSQL "UPDATE tblInstruments SET tblInstruments.ModDate = Date();"