Query

[Matt]

I have a form called "Graphs.frm" with one drop down, a table called
"Distribution.tbl", and a query called "Brands.qry". The Distribution.tbl
includes zipcode as Primary and columns for each brand.

My goal is to somehow have a user select a brand value from the drop down in
Graphs.frm, which will then automatically add the column from
Distribution.tbl to the Brands.qry. An example would be a user selecting
ABERLOUR in the drop down, the column with the header ABERLOUR from
Distribution.tbl will be added to Brands.qry.

I tried using the fomrula below in the field cell box of Brands.qry, however
I included additinal brands causing the # of characters to exceed 1024.

Switch (
[Forms]![Graphs]![Brand1] ="ABERLOUR", [Distribution]![ ABERLOUR] ,
[Forms]![Graphs]![Brand1] ="ARAK RAZZOUK", [Distribution]![ ARAK RAZZOUK] ,
[Forms]![Graphs]![Brand1] ="BENRIACH", [Distribution]![ BENRIACH] ,
[Forms]![Graphs]![Brand1] ="BOODLES", [Distribution]![ BOODLES] ,
[Forms]![Graphs]![Brand1] ="BUSHMILLS IRISH CREAM", [Distribution]![
BUSHMILLS IRISH CREAM] ,

I see you can set the drop down to include field values from
Distribution.tbl, however how can I link the selected value to the query.

Also I do not have too much experience with the VB coding so the easir the
better.

Thanks

 

[andrei]

Matt,

I think you have to build the query dynamically. So, after the user selects
a brand, a procedure will be triggered that
builds the new query string, taking into account the selection the user has
just made.
As a trigger you can use the AfterUpdate event for the combobox.

ex:

<
create your SQL string first, somethnig like:
strSQL = "select Zipcode, " & Brand1 & ", ...(other fields) from
Distribution"dim q as QueryDef
Dim rs as recordset
Set q = db.CreateQueryDef("", strSQL)
set rs = q.OpenRecordset()
....

Andrei.

I have a form called "Graphs.frm" with one drop down, a table called
"Distribution.tbl", and a query called "Brands.qry". The Distribution.tbl
includes zipcode as Primary and columns for each brand.

My goal is to somehow have a user select a brand value from the drop down in
Graphs.frm, which will then automatically add the column from
Distribution.tbl to the Brands.qry. An example would be a user selecting
ABERLOUR in the drop down, the column with the header ABERLOUR from
Distribution.tbl will be added to Brands.qry.

I tried using the fomrula below in the field cell box of Brands.qry, however
I included additinal brands causing the # of characters to exceed 1024.

Switch (
[Forms]![Graphs]![Brand1] ="ABERLOUR", [Distribution]![ ABERLOUR] ,
[Forms]![Graphs]![Brand1] ="ARAK RAZZOUK", [Distribution]![ ARAK RAZZOUK] ,
[Forms]![Graphs]![Brand1] ="BENRIACH", [Distribution]![ BENRIACH] ,
[Forms]![Graphs]![Brand1] ="BOODLES", [Distribution]![ BOODLES] ,
[Forms]![Graphs]![Brand1] ="BUSHMILLS IRISH CREAM", [Distribution]![
BUSHMILLS IRISH CREAM] ,

I see you can set the drop down to include field values from
Distribution.tbl, however how can I link the selected value to the query.

Also I do not have too much experience with the VB coding so the easir the
better.

Thanks