Filtering Combo #2 from a value in Combo #1

  • Thread starter Thread starter GLT
  • Start date Start date
G

GLT

Hi,

I have two Combo fields:

cmb1 (which has a choice of PROD / DEV)
cmb2 - I want to list dates associated either PROD or DEV.

I have this SQL for cmb1:

strSQL = "SELECT DISTINCT tbl01_FullCompare.ShutType,
tbl01_FullCompare.ImpDate FROM tbl01_FullCompare WHERE
tbl01_FullCompare.ShutType = "" & Me![cmbShutType] & "" ORDER BY
tbl01_FullCompare.ImpDate;"

Me![cmbImpDate].RowSource = strSQL

However when I i select the cmbImpDate drop down box, there are no records
listed... can anyone advise how to get this working?

Any assistance is always greatly appreciated...

Cheers,
GLT.
 
Your quotes are wrong. What you've got ends up with a SQL string

SELECT DISTINCT tbl01_FullCompare.ShutType, tbl01_FullCompare.ImpDate FROM
tbl01_FullCompare WHERE tbl01_FullCompare.ShutType = " & Me![cmbShutType] &
" ORDER BY tbl01_FullCompare.ImpDate;"

In other words, it's looking for the literal string " & Me![cmbShutType] &
", whereas you want it to be looking for whatever's been selected in the
combo box.

Change your code to

strSQL = "SELECT DISTINCT ShutType, " & _
"ImpDate FROM tbl01_FullCompare " & _
"WHERE ShutType = """ & Me![cmbShutType] & _
""" ORDER BY ImpDate;"

That's three double quotes in a row, as opposed to the two double quotes in
a row that you have.
 
Appologies, I made an error in my previous post, the SQL mentioned below is
the row source for cmb2.

I have now updated the SQL that gets set for the cmb2 rowsource to this:

strSQL = "SELECT DISTINCT tbl01_FullCompare.ShutType,
tbl01_FullCompare.ImpDate " & _
"FROM tbl01_FullCompare " & _
"WHERE (((tbl01_FullCompare.ShutType) Like
[forms]![frm01_ProcessErr]![cmbShutType]));"

The filtering works properly, and cmb2 displays the filtered dates correctly
(based on the selection in cmb1) on the form.

However, but the actual value stored in cmb2 (which should be ImpDate), is
actually ShutType - can anyone advise why it displays the correct Date value,
but stores another value? How can I fix this?
 
Just a stab at it but I'd try:

strSQL = "SELECT DISTINCT tbl01_FullCompare.ShutType,
tbl01_FullCompare.ImpDate FROM tbl01_FullCompare WHERE
tbl01_FullCompare.ShutType = '" & Me![cmbShutType] & "' ORDER BY
tbl01_FullCompare.ImpDate;"
 
Appologies, I made an error in my previous post, the SQL mentioned below is
the row source for cmb2.

I have now updated the SQL that gets set for the cmb2 rowsource to this:

strSQL = "SELECT DISTINCT tbl01_FullCompare.ShutType,
tbl01_FullCompare.ImpDate " & _
"FROM tbl01_FullCompare " & _
"WHERE (((tbl01_FullCompare.ShutType) Like
[forms]![frm01_ProcessErr]![cmbShutType]));"

The filtering works properly, and cmb2 displays the filtered dates correctly
(based on the selection in cmb1) on the form.

However, but the actual value stored in cmb2 (which should be ImpDate), is
actually ShutType - can anyone advise why it displays the correct Date value,
but stores another value? How can I fix this?
Click to expand...

Change the Combo's BoundColumn property from 1 (storing the first field in the
query, namely ShutType) to 2 (store the second column, ImpDate).

You can also change the LIKE to = since the LIKE operator is intended for use
with wildcards; you want an exact match, not a partial match.
 
Hi Doug,

Thanks for your reply - I tried your SQL, and it works but the second combo
box always goes back to the 1st selection. For example if I choose the third
date when I select the down arrow, the Combo box displays only the first
selection. Its an unbound combo box, what would be causing this?

Cheers,
GLT

Douglas J. Steele said:
Your quotes are wrong. What you've got ends up with a SQL string

SELECT DISTINCT tbl01_FullCompare.ShutType, tbl01_FullCompare.ImpDate FROM
tbl01_FullCompare WHERE tbl01_FullCompare.ShutType = " & Me![cmbShutType] &
" ORDER BY tbl01_FullCompare.ImpDate;"

In other words, it's looking for the literal string " & Me![cmbShutType] &
", whereas you want it to be looking for whatever's been selected in the
combo box.

Change your code to

strSQL = "SELECT DISTINCT ShutType, " & _
"ImpDate FROM tbl01_FullCompare " & _
"WHERE ShutType = """ & Me![cmbShutType] & _
""" ORDER BY ImpDate;"

That's three double quotes in a row, as opposed to the two double quotes in
a row that you have.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



GLT said:
Hi,

I have two Combo fields:

cmb1 (which has a choice of PROD / DEV)
cmb2 - I want to list dates associated either PROD or DEV.

I have this SQL for cmb1:

strSQL = "SELECT DISTINCT tbl01_FullCompare.ShutType,
tbl01_FullCompare.ImpDate FROM tbl01_FullCompare WHERE
tbl01_FullCompare.ShutType = "" & Me![cmbShutType] & "" ORDER BY
tbl01_FullCompare.ImpDate;"

Me![cmbImpDate].RowSource = strSQL

However when I i select the cmbImpDate drop down box, there are no records
listed... can anyone advise how to get this working?

Any assistance is always greatly appreciated...

Cheers,
GLT.
Click to expand...

.
Click to expand...
 
Thanks QB and John for your replies, I fixed the problem.

John and Doug you were both right - I replaced the string and set the bound
field to 2 and uncommented out some code that I had forgotten about and now
all is fine. Thanks so much for your help :).

GLT said:
Hi Doug,

Thanks for your reply - I tried your SQL, and it works but the second combo
box always goes back to the 1st selection. For example if I choose the third
date when I select the down arrow, the Combo box displays only the first
selection. Its an unbound combo box, what would be causing this?

Cheers,
GLT

Douglas J. Steele said:
Your quotes are wrong. What you've got ends up with a SQL string

SELECT DISTINCT tbl01_FullCompare.ShutType, tbl01_FullCompare.ImpDate FROM
tbl01_FullCompare WHERE tbl01_FullCompare.ShutType = " & Me![cmbShutType] &
" ORDER BY tbl01_FullCompare.ImpDate;"

In other words, it's looking for the literal string " & Me![cmbShutType] &
", whereas you want it to be looking for whatever's been selected in the
combo box.

Change your code to

strSQL = "SELECT DISTINCT ShutType, " & _
"ImpDate FROM tbl01_FullCompare " & _
"WHERE ShutType = """ & Me![cmbShutType] & _
""" ORDER BY ImpDate;"

That's three double quotes in a row, as opposed to the two double quotes in
a row that you have.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



GLT said:
Hi,

I have two Combo fields:

cmb1 (which has a choice of PROD / DEV)
cmb2 - I want to list dates associated either PROD or DEV.

I have this SQL for cmb1:

strSQL = "SELECT DISTINCT tbl01_FullCompare.ShutType,
tbl01_FullCompare.ImpDate FROM tbl01_FullCompare WHERE
tbl01_FullCompare.ShutType = "" & Me![cmbShutType] & "" ORDER BY
tbl01_FullCompare.ImpDate;"

Me![cmbImpDate].RowSource = strSQL

However when I i select the cmbImpDate drop down box, there are no records
listed... can anyone advise how to get this working?

Any assistance is always greatly appreciated...

Cheers,
GLT.
Click to expand...

.
Click to expand...
Click to expand...
 
Back
Top