|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
February 8th, 2010, 02:21 AM
posted to microsoft.public.access.forms
|
|||
|
|||
Filtering Combo #2 from a value in Combo #1
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. 
|
|
#2
February 8th, 2010, 02:58 AM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Filtering Combo #2 from a value in Combo #1
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 http://I.Am/DougSteele (no e-mails, please!) "GLT" wrote in message ... 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.
|
|
#3
February 8th, 2010, 03:19 AM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Filtering Combo #2 from a value in Combo #1
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? "GLT" wrote: 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.
|
|
#4
February 8th, 2010, 03:19 AM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Filtering Combo #2 from a value in Combo #1
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;" "GLT" wrote: 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.
|
|
#5
February 8th, 2010, 03:26 AM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Filtering Combo #2 from a value in Combo #1
On Sun, 7 Feb 2010 19:19:01 -0800, GLT wrote:
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? 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. -- John W. Vinson [MVP]
|
|
#6
February 8th, 2010, 03:57 AM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Filtering Combo #2 from a value in Combo #1
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" wrote: 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 http://I.Am/DougSteele (no e-mails, please!) "GLT" wrote in message ... 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. .
|
|
#7
February 8th, 2010, 04:30 AM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Filtering Combo #2 from a value in Combo #1
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" wrote: 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" wrote: 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 http://I.Am/DougSteele (no e-mails, please!) "GLT" wrote in message ... 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. .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
