Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-11-2018, 11:49 PM   #1
murray83
sega collector
 
Join Date: Mar 2017
Location: Daventry, UK
Posts: 314
Thanks: 52
Thanked 0 Times in 0 Posts
murray83 is on a distinguished road
Dlookup from an SQL Query to an ubound textbox

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])

murray83 is offline   Reply With Quote
Old 07-11-2018, 11:55 PM   #2
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,196
Thanks: 54
Thanked 1,990 Times in 1,903 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: Dlookup from an SQL Query to an ubound textbox

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])
__________________
"Never stop learning, because life never stops teaching"

Last edited by arnelgp; 07-12-2018 at 12:01 AM.
arnelgp is offline   Reply With Quote
Old 07-11-2018, 11:59 PM   #3
murray83
sega collector
 
Join Date: Mar 2017
Location: Daventry, UK
Posts: 314
Thanks: 52
Thanked 0 Times in 0 Posts
murray83 is on a distinguished road
Re: Dlookup from an SQL Query to an ubound textbox

Quote:
Originally Posted by arnelgp View Post
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 is offline   Reply With Quote
Old 07-12-2018, 12:01 AM   #4
murray83
sega collector
 
Join Date: Mar 2017
Location: Daventry, UK
Posts: 314
Thanks: 52
Thanked 0 Times in 0 Posts
murray83 is on a distinguished road
Re: Dlookup from an SQL Query to an ubound textbox

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
murray83 is offline   Reply With Quote
Old 07-12-2018, 12:03 AM   #5
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,196
Thanks: 54
Thanked 1,990 Times in 1,903 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: Dlookup from an SQL Query to an ubound textbox

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])
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 07-12-2018, 12:21 AM   #6
murray83
sega collector
 
Join Date: Mar 2017
Location: Daventry, UK
Posts: 314
Thanks: 52
Thanked 0 Times in 0 Posts
murray83 is on a distinguished road
Re: Dlookup from an SQL Query to an ubound textbox

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
murray83 is offline   Reply With Quote
Old 07-12-2018, 12:26 AM   #7
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,196
Thanks: 54
Thanked 1,990 Times in 1,903 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: Dlookup from an SQL Query to an ubound textbox

yes, but, txtChkString should not be Bound or does not have an Expression as it's Control Source, eg:

=[Some Expression].

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 07-12-2018, 12:40 AM   #8
murray83
sega collector
 
Join Date: Mar 2017
Location: Daventry, UK
Posts: 314
Thanks: 52
Thanked 0 Times in 0 Posts
murray83 is on a distinguished road
Re: Dlookup from an SQL Query to an ubound textbox

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
murray83 is offline   Reply With Quote
Old 07-12-2018, 12:54 AM   #9
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,196
Thanks: 54
Thanked 1,990 Times in 1,903 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: Dlookup from an SQL Query to an ubound textbox

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.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 07-12-2018, 01:10 AM   #10
murray83
sega collector
 
Join Date: Mar 2017
Location: Daventry, UK
Posts: 314
Thanks: 52
Thanked 0 Times in 0 Posts
murray83 is on a distinguished road
Re: Dlookup from an SQL Query to an ubound textbox

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
murray83 is offline   Reply With Quote
Old 07-12-2018, 01:15 AM   #11
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,196
Thanks: 54
Thanked 1,990 Times in 1,903 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: Dlookup from an SQL Query to an ubound textbox

If the recordsource of your combo is the query itself, you need not use dlookup. Instead assign it immediatelt:

TxtChkString= cmbLocation.Column(1)
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
murray83 (07-12-2018)
Old 07-12-2018, 01:23 AM   #12
murray83
sega collector
 
Join Date: Mar 2017
Location: Daventry, UK
Posts: 314
Thanks: 52
Thanked 0 Times in 0 Posts
murray83 is on a distinguished road
Talking Re: Dlookup from an SQL Query to an ubound textbox

Quote:
Originally Posted by arnelgp View Post
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

murray83 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
problem with ubound textbox haamt Forms 2 04-18-2016 02:56 PM
DLookup Query and return next record based on value in textbox ccondran08 Forms 2 01-28-2016 09:57 PM
using Ubound in Access 2007 VBA GODZILLA Modules & VBA 1 02-09-2011 06:50 AM
Can't refresh a textbox that DLookUp's the result of a query Colin@Toyota Forms 4 01-09-2009 12:34 PM
Resetting the Ubound value of a multi dimensional array DCrake Modules & VBA 2 02-29-2008 07:10 AM




All times are GMT -8. The time now is 03:22 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World