strsql basics - confused vba dabbler

[Emblem1]

I have 2 tables that list names of locations. I have then built a union query to compile a list of all data that is similar between the 2 tables, which includes things like 'Location Name', 'Location Number', 'Location Type', etc.

In an unbound form, I have a combo box (Combo37) that lists the 'Location Name(s)', and a text box (Location_Number) in which I want to show the location number from the union query based on the name selected in the combo box.

Should be easy, but I can't get either the syntax for a strsql or dlookup code right.

Here is my attmpt at the strsql code:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("getLocation_Number")


strSQL = "SELECT [qryAllLocations_AllTypes].[Location Number]"
strSQL = strSQL & "FROM [qryAllLocations_AllTypes]"
strSQL = strSQL & "WHERE [qryAllLocations_AllTypes].[Location Name] = '" & Me.Combo37 "'"
qdf.SQL = strSQL

Me.Location_Number.Value = "getLocation_number"
Set db = Nothing
Set qdf = Nothing

I am trying to use the query as my datsource since the form is unbound...? I am sure I have a few things wrong, but would really like to get this strsql stuff down....

Once I get this right, I will then use an append query to save the forms' data to a 3rd table.

Thanks

Kevin

 

[HarleyQuinn]

The first thing I notice is that you'll need some space in you SQL string (also a final ampersand)

strSQL = "SELECT [qryAllLocations_AllTypes].[Location Number] "
strSQL = strSQL & "FROM [qryAllLocations_AllTypes] "
strSQL = strSQL & "WHERE [qryAllLocations_AllTypes].[Location Name] = '" & Me.Combo37 [red]&[/red] "'"
qdf.SQL = strSQL

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Emblem1]

OK, so that fixed the strsql syntax.

However, ignoring my other code, what is the correct way to place the result into the text box on the form? The getLocation_Number was a misunderstading on my part....

I tried this with no luck...

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryAllLocations_AllTypes")


strSQL = "SELECT [qryAllLocations_AllTypes].[Location Number]"
strSQL = strSQL & "FROM [qryAllLocations_AllTypes]"
strSQL = strSQL & "WHERE [qryAllLocations_AllTypes].[Location Name] = '" & Me.Combo37 & "'"
qdf.SQL = strSQL

Me.Location_Number.Value = [qryAllLocations_AllTypes].[Location Number]

 

[oharab]

If your combo box is bound to qryAllLocations_AllTypes then all you need to do is

me.Location.value = me.comboo37.value

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.