VBA, DLookup issue

[jtm013]

Hello,



I am making a DB to track tooling.
The tools are already identified by an ID such as TH-101 or YZ-223.
Subsequently I set up the table with an autonumbered field called "RecordId" as the PK.

However, users need to be able to lookup the tools by the toolID. This ID is stored in a field called "ToolID" which is a text field.
I am trying to open the tool's detail page to the selected record and I'm not sure where I am going wrong. Any and all assistance is appreciated. I have listed the code below. The idea is that when the user clicks the 'find' button an inputbox will request the Tool ID and then that will be fed to the query which the dlookup can then use to select the appropriate record.

Private Sub btnFindTool_Click()
Dim RecordID As String
Dim boxofsox As String

boxofsox = InputBox("Enter ID")
RecordID = DLookup("recordID", "qryToolID_RecordIDConverter", "[ToolID]=" & boxofsox)

End Sub

The referenced query code is:

SELECT tblDataPrime.recordID, tblDataPrime.toolIDFROM tblDataPrime
WHERE (((tblDataPrime.toolID)=[Enter Tool ID]));

[June7]

Text field parameters require apostrophe delimiters.

RecordID = DLookup("recordID", "qryToolID_RecordIDConverter", "[ToolID]='" & boxofsox &"'")

InputBoxes are hard to validate user input. Better would be to have input to a control such as a combobox. Combobox could have columns with the RecordID and the ToolID so both are directly available on the form. No need for DLookup.

[jtm013]

Text field parameters require apostrophe delimiters.

RecordID = DLookup("recordID", "qryToolID_RecordIDConverter", "[ToolID]='" & boxofsox &"'")

InputBoxes are hard to validate user input. Better would be to have input to a control such as a combobox. Combobox could have columns with the RecordID and the ToolID so both are directly available on the form. No need for DLookup.

June 7 -

A) I like the combobox idea but there is somewhere between 5-10,000 tools, so it would be a bear to look through.
B) Thanks for the apostrophe info
C) I'm a dumb dumb and forgot to include a piece of code- This is supposed to be part of a "find record" button on the dashboard. So the onclick is actually: (I have adjusted it based on your apostrophe recommendation)

Private Sub btnFindTool_Click()Dim recordID As String
Dim boxofsox As String


boxofsox = InputBox("Enter ID")
recordID = DLookup("recordID", "qryToolID_RecordIDConverter", "[ToolID]='" & boxofsox & "'")




DoCmd.OpenForm("frmToolDetail",,, "[RecordID]=" & recordID &)


End Sub

However, currently the OpenForm section is throwing an Compile Error "Expected Expression".
I'm sure I am missing something simple, but I'm just not seeing it.

Thanks in advance!

[June7]

Don't use parentheses for the OpenForm command. Parenthesis usually mean a function. This could be a single line of code.

DoCmd.OpenForm "frmToolDetail", , , "[RecordID]=" & DLookup("recordID", "qryToolID_RecordIDConverter", "[ToolID]='" & InputBox("Enter ID") & "'")

Form will still open even if user input is not valid, causing much frustration.

Can type into combobox. AutoExpand property will match to list items. Can set LimitToList property as Yes. This will assure user input is valid. Review http://allenbrowne.com/ser-32.html

[jtm013]

I input your code- but now receive an error message.

"Run-time error '2471':
"The expression you entered as a query parameter produced this error: '[Enter Tool ID]'"

[June7]

Eliminate the WHERE clause from the query.

I never use dynamic parameters in query.

[jtm013]

Awesome!!! Thank you for the assist!