querying based on a form

[joemeshuggah]

i have a form that displays rep name (RepNm combo box), but also has an ad id
(hidden). i want to have a cascading combo box automatically populate ad
name (with associated director id hidden), and transfer the rep and ad name
information to the control source table.

what needs to be used in the where clause to match the ad id in the ad table
to the ad id that is hidden in the RepNm combo box?

SELECT AD.AD_NM, AD.DIR_ID
FROM AD
WHERE AD.AD_ID=[FORMS]![ALL_DATA]![RepNm];

 

[KARL DEWEY]

What is the question?

 

[joemeshuggah]

what needs to be used in the where clause to match the ad id in the ad table
to the ad id that is hidden in the RepNm combo box?

for example, if i select a name in the rep drop down box, and then run the
query below, i get no results. i am assuming that it is because the query is
not linking ad_id from the ad table to the ad_id in the rep combo box. so,
what would need to be used in the where clause to tell the query to pull
records where the ad_id in the ad table matches the ad_id found in the rep
combo box ?

What is the question?

i have a form that displays rep name (RepNm combo box), but also has an ad id
(hidden). i want to have a cascading combo box automatically populate ad
name (with associated director id hidden), and transfer the rep and ad name
information to the control source table.

what needs to be used in the where clause to match the ad id in the ad table
to the ad id that is hidden in the RepNm combo box?

SELECT AD.AD_NM, AD.DIR_ID
FROM AD
WHERE AD.AD_ID=[FORMS]![ALL_DATA]![RepNm];

 

[KARL DEWEY]

Maybe you are using the label name instead of object name. Try checking if
combo is something like Combo0 then it should be --
WHERE AD.AD_ID=[FORMS]![ALL_DATA]![Combo0];

what needs to be used in the where clause to match the ad id in the ad table
to the ad id that is hidden in the RepNm combo box?

for example, if i select a name in the rep drop down box, and then run the
query below, i get no results. i am assuming that it is because the query is
not linking ad_id from the ad table to the ad_id in the rep combo box. so,
what would need to be used in the where clause to tell the query to pull
records where the ad_id in the ad table matches the ad_id found in the rep
combo box ?

What is the question?

i have a form that displays rep name (RepNm combo box), but also has an ad id
(hidden). i want to have a cascading combo box automatically populate ad
name (with associated director id hidden), and transfer the rep and ad name
information to the control source table.

what needs to be used in the where clause to match the ad id in the ad table
to the ad id that is hidden in the RepNm combo box?

SELECT AD.AD_NM, AD.DIR_ID
FROM AD
WHERE AD.AD_ID=[FORMS]![ALL_DATA]![RepNm];

 

[joemeshuggah]

RepNm is the object name...

Maybe you are using the label name instead of object name. Try checking if
combo is something like Combo0 then it should be --
WHERE AD.AD_ID=[FORMS]![ALL_DATA]![Combo0];

what needs to be used in the where clause to match the ad id in the ad table
to the ad id that is hidden in the RepNm combo box?

for example, if i select a name in the rep drop down box, and then run the
query below, i get no results. i am assuming that it is because the query is
not linking ad_id from the ad table to the ad_id in the rep combo box. so,
what would need to be used in the where clause to tell the query to pull
records where the ad_id in the ad table matches the ad_id found in the rep
combo box ?

What is the question?

:

i have a form that displays rep name (RepNm combo box), but also has an ad id
(hidden). i want to have a cascading combo box automatically populate ad
name (with associated director id hidden), and transfer the rep and ad name
information to the control source table.

what needs to be used in the where clause to match the ad id in the ad table
to the ad id that is hidden in the RepNm combo box?

SELECT AD.AD_NM, AD.DIR_ID
FROM AD
WHERE AD.AD_ID=[FORMS]![ALL_DATA]![RepNm];

 

[KARL DEWEY]

Try removing the WHERE statement and in design view add AD.AD_ID to output
and a calculated field like this --
MyCheck: [FORMS]![ALL_DATA]![RepNm]

With the form open and a selection made run the query to see what appears
from the form and AD.AD_ID. For the criteria to work they must match.

RepNm is the object name...

Maybe you are using the label name instead of object name. Try checking if
combo is something like Combo0 then it should be --
WHERE AD.AD_ID=[FORMS]![ALL_DATA]![Combo0];

what needs to be used in the where clause to match the ad id in the ad table
to the ad id that is hidden in the RepNm combo box?

for example, if i select a name in the rep drop down box, and then run the
query below, i get no results. i am assuming that it is because the query is
not linking ad_id from the ad table to the ad_id in the rep combo box. so,
what would need to be used in the where clause to tell the query to pull
records where the ad_id in the ad table matches the ad_id found in the rep
combo box ?

:

What is the question?

:

i have a form that displays rep name (RepNm combo box), but also has an ad id
(hidden). i want to have a cascading combo box automatically populate ad
name (with associated director id hidden), and transfer the rep and ad name
information to the control source table.

what needs to be used in the where clause to match the ad id in the ad table
to the ad id that is hidden in the RepNm combo box?

SELECT AD.AD_NM, AD.DIR_ID
FROM AD
WHERE AD.AD_ID=[FORMS]![ALL_DATA]![RepNm];

 

[joemeshuggah]

not familiar with mycheck or calculated fields...

i think i am a little closer, though. i added an additional list box, so
after the rep name is selected, the new list box queries for the ad_id. i
then have a third list box that queries the ad_id in the new list box for the
ad_id to get the associated ad name.

the only problem is the in order for the third list box to show the ad name,
i need to click on the second click box that shows the ad id. how can i get
the ad name to appear in the third box without having to click on the second
box? here is the code i have...

Private Sub RepNm_AfterUpdate()
RepNMAD = ""
RepNMAD.Requery
End Sub

Private Sub RepNMAD_BeforeUpdate(Cancel As Integer)
ADNm = ""
ADNm.Requery
End Sub

Try removing the WHERE statement and in design view add AD.AD_ID to output
and a calculated field like this --
MyCheck: [FORMS]![ALL_DATA]![RepNm]

With the form open and a selection made run the query to see what appears
from the form and AD.AD_ID. For the criteria to work they must match.

RepNm is the object name...

Maybe you are using the label name instead of object name. Try checking if
combo is something like Combo0 then it should be --
WHERE AD.AD_ID=[FORMS]![ALL_DATA]![Combo0];

:

what needs to be used in the where clause to match the ad id in the ad table
to the ad id that is hidden in the RepNm combo box?

for example, if i select a name in the rep drop down box, and then run the
query below, i get no results. i am assuming that it is because the query is
not linking ad_id from the ad table to the ad_id in the rep combo box. so,
what would need to be used in the where clause to tell the query to pull
records where the ad_id in the ad table matches the ad_id found in the rep
combo box ?

:

What is the question?

:

i have a form that displays rep name (RepNm combo box), but also has an ad id
(hidden). i want to have a cascading combo box automatically populate ad
name (with associated director id hidden), and transfer the rep and ad name
information to the control source table.

what needs to be used in the where clause to match the ad id in the ad table
to the ad id that is hidden in the RepNm combo box?

SELECT AD.AD_NM, AD.DIR_ID
FROM AD
WHERE AD.AD_ID=[FORMS]![ALL_DATA]![RepNm];

 

[KARL DEWEY]

not familiar with mycheck or calculated fields...
MyCheck is the alias I gave to the field I created in the query design view
by doing calculations on an existing field.

You would open the query in design view and scroll to the right to find an
empty spot in the top row (It is titled Fields. You would paste what I
posted in that position.

Then I said to remove the WHERE which is SQL but in design view it is the
Criteria: row.

When you find [FORMS]![ALL_DATA]![RepNm] in the criteria row delete it.

Open the form, make a selection, and run the query to see what appears in
the output for the alias MyCheck. This will let you know if what is being
entered in the form corresponds to what should be for criteria of the AD_ID
field.

not familiar with mycheck or calculated fields...

i think i am a little closer, though. i added an additional list box, so
after the rep name is selected, the new list box queries for the ad_id. i
then have a third list box that queries the ad_id in the new list box for the
ad_id to get the associated ad name.

the only problem is the in order for the third list box to show the ad name,
i need to click on the second click box that shows the ad id. how can i get
the ad name to appear in the third box without having to click on the second
box? here is the code i have...

Private Sub RepNm_AfterUpdate()
RepNMAD = ""
RepNMAD.Requery
End Sub

Private Sub RepNMAD_BeforeUpdate(Cancel As Integer)
ADNm = ""
ADNm.Requery
End Sub

Try removing the WHERE statement and in design view add AD.AD_ID to output
and a calculated field like this --
MyCheck: [FORMS]![ALL_DATA]![RepNm]

With the form open and a selection made run the query to see what appears
from the form and AD.AD_ID. For the criteria to work they must match.

RepNm is the object name...

:

Maybe you are using the label name instead of object name. Try checking if
combo is something like Combo0 then it should be --
WHERE AD.AD_ID=[FORMS]![ALL_DATA]![Combo0];

:

what needs to be used in the where clause to match the ad id in the ad table
to the ad id that is hidden in the RepNm combo box?

for example, if i select a name in the rep drop down box, and then run the
query below, i get no results. i am assuming that it is because the query is
not linking ad_id from the ad table to the ad_id in the rep combo box. so,
what would need to be used in the where clause to tell the query to pull
records where the ad_id in the ad table matches the ad_id found in the rep
combo box ?

:

What is the question?

:

i have a form that displays rep name (RepNm combo box), but also has an ad id
(hidden). i want to have a cascading combo box automatically populate ad
name (with associated director id hidden), and transfer the rep and ad name
information to the control source table.

what needs to be used in the where clause to match the ad id in the ad table
to the ad id that is hidden in the RepNm combo box?

SELECT AD.AD_NM, AD.DIR_ID
FROM AD
WHERE AD.AD_ID=[FORMS]![ALL_DATA]![RepNm];