Join to a combobox column (1 Viewer)

[Tallica]

I am trying to create a query using the following code.

Them problem is the joined field on one query is a 4 column combobox that is bound to column 1. Can't seem to make it work. Anywhere you see DPM1 in my code below is actually trying to pull the information of DPM1.Column(1)

Can this be done and if so, what would the code be,

Here is my code.
Dim sSQL As String 'query string
sSQL = "SELECT [Target_Projects Query].[Target_Projects_Project #],"
sSQL = sSQL & " [Target_Projects Query].Location,"
sSQL = sSQL & " [Target_Projects Query].DPM1,"
sSQL = sSQL & " [Target_Projects Query].Target_Projects.t_num,"
sSQL = sSQL & " [Contacts Extended].[Contact Name],"
sSQL = sSQL & " [Contacts Extended].Company,"
sSQL = sSQL & " [Contacts Extended].Address,"
sSQL = sSQL & " [Contacts Extended].City,"
sSQL = sSQL & " [Contacts Extended].[State/Province],"
sSQL = sSQL & " [Contacts Extended].[ZIP/Postal Code]"
sSQL = sSQL + " FROM [Target_Projects Query]"
sSQL = sSQL + " LEFT JOIN [Contacts Extended]"
sSQL = sSQL & " ON [Target_Projects Query].DPM1 = [Contacts Extended].[Contact Name]"
sSQL = sSQL & " WHERE ((([Target_Projects Query].Target_Projects.t_num)='" & rcd & "'));"

Set qdf = CurrentDb.CreateQueryDef("Trans", sSQL)

 

[boblarson]

Column 1 in the binding sense is 0 in the columns code sense. Column 2 in the combo's bound property would be referred in code to Column(1).

 

[vbaInet]

Cannot be done. Joins are between fields that exist in tables or queries.

Create a query that will return the four columns. Join to this query and use the field from there.

 

[Tallica]

When I create a query, how do I specify which column it returns?

 

[vbaInet]

The field will be available in your query to select from.

 

[boblarson]

When I create a query, how do I specify which column it returns?

In a regular query you can only specify the combo box and it will refer to the bound column. If you want another column you can't do that with a saved query. You would have to do the whole thing in VBA.

But I just noticed something. Your "combo box" isn't on a form, is it? You are trying to refer to it in a query which means you are using Lookups at Table level which is bad, bad, bad, bad, bad. (did I mention it was bad?)

See here for why?

Also you cannot reference lookups in tables like that at all, so unless you use a form with a combo box for it, you can't do it.

 

[Tallica]

My combobox is on a form and it is from a lookup column in a table(which is bad, bad, bad I guess)

 

[boblarson]

My combobox is on a form and it is from a lookup column in a table(which is bad, bad, bad I guess)

Tables to store lookup values are fine. But putting lookups on a field directly in the table is where it causes things to go wonky. In fact it is fine when starting a database to have the lookup field in the table so the wizard will create your combo boxes for you on your forms you make. But then you need to remove them from the field within the table.

 

[Tallica]

OK, I changed the lookup in the table to a text field. Now when I change the value in the combobox on the form, I get a number in the field in the table instead of the bound column data in the combobox.

 

[boblarson]

You should be getting a number in the table. That is storing the ID of the item from the other table (the lookup table). If you need that description you then join in the lookup table into your query so that you can have the text instead of the ID. That is the correct way to set it up.

 

[Simon_MT]

Here is an example:

Main Query OriginalsQuery
LookupQry OriginalsLookup

The Equivalent ID [Orig Old Stock] = Pos1 or Column 0. I then use a Function on the On Enter in the relevant combi

Function LookupOriginal()

    With Screen.ActiveControl
        .RowSource = "SELECT OriginalsLookup.[Orig Old Stock], OriginalsLookup.[Orig Artist Ref], OriginalsLookup.[Original Name], OriginalsLookup.[Medium Desc], OriginalsLookup.[Size Short], OriginalsLookup.[Orig Status], OriginalsLookup.[Orig Stock Status] FROM OriginalsLookup;"
    End With
End Function