DLookUp for textbox control source to pull from another query

[figment222]

Hello, I have been all over the web and keep seeing the same examples on how to do this, but for some reason, I can't get it work for me.

I have a form called "frm_Metals_JobDetails" it is bound to a query called: qry_MetalsJobsActive

There is one textbox on the form that I want pull data from another query called: qry_MetalsJobs_ItemStats. The field I want is called: ItemsCompleted

I made sure that qry_MetalsJobs_ItemStats has a field called: JobNum, which is the one that will have a matching value to a textbox on the form called: "TRC #"

Everybody says to use the DLookUp function in the control source for my textbox. Everybody pretty much uses the same format, so I don't know what I'm doing wrong. Here's what I have so far, via the expression builder:

=DLookUp("ItemsCompleted","qry_MetalsJobs_ItemStats","JobNum=" & [Forms]![frm_Metals_JobDetails]![TRC #])

I've also tried it with or without brackets. Any ideas?

 

[apr pillai]

Remove the # symbol from the Textbox name, modify the Textbox reference in the Dlookup() function to reflect the name change. Normally this symbol (#) is used in expressions to enter a date constant like: #04/15/2018#.

 

[ranman256]

You don't need Dlookup.
if you use a combo box,put the field needed in an extra column,then fill the textbox with it.
cboBox bound to column 1 ,ClientID
column 2 is client name, so fill the textbox with it.
NOTE: in VB, columns start with zero,so actual column 2 is .column(1)

sub cboBox_afterUpdate()
   TxtBoxName= cboBox.column(1)
end sub

If you want to use DLOOKUP,
and the last param is a string, you must surround it with quotes,,,,,

=dlookup("field","query","[name]=' " & me.txtBox & " ' ")

 

[figment222]

Remove the # symbol from the Textbox name, modify the Textbox reference in the Dlookup() function to reflect the name change. Normally this symbol (#) is used in expressions to enter a date constant like: #04/15/2018#.

@ apr pillai... thank you for the suggestion. That is a good point, but I don't think that would be a problem unless I didn't surround [TRC #] with brackets, right?

 

[figment222]

You don't need Dlookup.
if you use a combo box,put the field needed in an extra column,then fill the textbox with it.
cboBox bound to column 1 ,ClientID
column 2 is client name, so fill the textbox with it.
NOTE: in VB, columns start with zero,so actual column 2 is .column(1)

sub cboBox_afterUpdate()
   TxtBoxName= cboBox.column(1)
end sub

If you want to use DLOOKUP,
and the last param is a string, you must surround it with quotes,,,,,

=dlookup("field","query","[name]=' " & me.txtBox & " ' ")

I didn't quite understand your first suggestion with the combobox, but the 2nd part about surrounding the text string with apostrophes turned out to be the problem. It is indeed a text string I was pulling and when I put them in as follows into the expression builder, it worked like a charm. Thank you:

=DLookUp("ItemsCompleted","qry_MetalsJobs_ItemStats","JobNum='" & [Forms]![frm_Metals_JobDetails]![TRC #] & "'")