Dlookup from an SQL Query to an ubound textbox (1 Viewer)

murray83

Games Collector
Local time
Today, 09:47
Joined
Mar 31, 2017
Messages
728
hi all

i have a sql query which pulls locations and the check string for each location but for quick check when people need to know the check string have put the query to combo box so this displays all the locations from the query

Then next to this i have placed a text box which i have placed the following in so far but it just screams #Name? at me

could someone much smarter than me point out what i have done wrong, ta

Code:
=DLookUp([Check_String],[Q_Check_Strings],[cmbLocation].[Value]=[Q_Check_Strings].[LOCATION_ID])
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:47
Joined
May 7, 2009
Messages
19,169
what are Check_String, and Q_Check_Strings? are they textboxes?

usually dlookup parameters are enclosed in double Quotes ("). eg:

=DLookUp("[Check_String]","[Q_Check_Strings]","
[LOCATION_ID]=" & [cmbLocation].[Value])
 
Last edited:

murray83

Games Collector
Local time
Today, 09:47
Joined
Mar 31, 2017
Messages
728
what are Check_String, and Q_Check_Strings? are they textboxes?

usually dlookup parameters are enclosed in double Quotes ("). eg:

=DLookUp("[Check_String]","[Q_Check_Strings]",[LOCATION_ID]=" & [cmbLocation].[Value])


Check_String is the item to check in the Query & Q_Check_Strings is the name of said query
 

murray83

Games Collector
Local time
Today, 09:47
Joined
Mar 31, 2017
Messages
728
this is the sql code which is running the query

Code:
SELECT 

Location_Id , Check_String 

FROM Location  

WHERE Check_String IS NOT NULL

ORDER BY Location_Id
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:47
Joined
May 7, 2009
Messages
19,169
enclosed them in quotes as on the sample, remove the brackets, if necessary from the quoted parameters.

Code:
=DLookUp("Check_String","Q_Check_Strings","LOCATION_ID=" & [cmbLocation].[Value])
 

murray83

Games Collector
Local time
Today, 09:47
Joined
Mar 31, 2017
Messages
728
how about if i put it on the after update event of the combobox would it look something like this

Code:
Private Sub cmbLocation_AfterUpdate()
txtChkString.Value = DLookup("Check_String", "Q_Check_Strings", "LOCATION_ID=" & [cmbLocation].[Value])
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:47
Joined
May 7, 2009
Messages
19,169
yes, but, txtChkString should not be Bound or does not have an Expression as it's Control Source, eg:

=[Some Expression].
 

murray83

Games Collector
Local time
Today, 09:47
Joined
Mar 31, 2017
Messages
728
ok almost working but sates now a data type mismatch

this i believe is down to looking for number or such or me using the wrong quotation marks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:47
Joined
May 7, 2009
Messages
19,169
What are thr Column Name Of the combo. You put its column number (minus-1).
If the it is in column 1:

"LOCATION_ID=" & [cmbLocation].Column(0)

Column are zero based.
 

murray83

Games Collector
Local time
Today, 09:47
Joined
Mar 31, 2017
Messages
728
OK thanks for help this is what it look's like now

Code:
Private Sub cmbLocation_AfterUpdate()
txtChkString = DLookup("Check_String", "Q_Check_Strings", "LOCATION_ID=" & [cmbLocation].Column(1))
End Sub

have put 1 as if column 0 is first column then 1 would be the second ( my thinking anyway ) and the second column is what has the Check_String which i'm trying to get into the text box txtChkString

but im sorry to say it still wont play

states

Run Timer error '3464':

Data type mismatch in criteria expression :banghead::banghead:
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:47
Joined
May 7, 2009
Messages
19,169
If the recordsource of your combo is the query itself, you need not use dlookup. Instead assign it immediatelt:

TxtChkString= cmbLocation.Column(1)
 

murray83

Games Collector
Local time
Today, 09:47
Joined
Mar 31, 2017
Messages
728
If the recordsource of your combo is the query itself, you need not use dlookup. Instead assign it immediatelt:

TxtChkString= cmbLocation.Column(1)

That is it THANKS :D:cool:
 

Users who are viewing this thread

Top Bottom