Change Row Source

[NISMOJim]

I have a query (qryParts) with fields Cat (number) and Desc (text). Based on this query is a form called frmParts with the following fields;

cboCat which has a row source from Table tblLUCat. The bound column, 1, is the CatID number, but the user sees column 2, which is the category (Proto, Production, Office...).

cboDesc is a description field that I would like to be able to show a dropdown list of possibilities depending on which category is selected in cboCat.



Here is the code I have tried (a few different ways) but doesn't work. I still get a blank dropdown list for cboDesc.

Private Sub cboCat_AfterUpdate()

Dim strSQL As String

If Me.cboCat = "1" Then
Me.ProtoID = Me.PartsID
Me.lblReturn.Visible = True
More code to make labels etc. visible or not
ElseIf Me.cboCat = "2" Then
More code to make labels etc. visible or not
ElseIf Me.cboCat = "3" Or Me.cboCat = "4" Then
More code to make labels etc. visible or not
End If

strSQL = "SELECT qryParts.Descrip FROM qryParts WHERE qryParts.Cat = " & Me.cboCat
Me.cboDesc.RowSource = strSQL

End Sub

Does someone know how I can fix this?
Thank You

[pbaldy]

What is the column widths property of that combo? Since you only return one field, the first field must be visible. This will help make sure the SQL is correct:

http://www.baldyweb.com/ImmediateWindow.htm

[NISMOJim]

The cboCat width is 0 (for the CATID number);2 (for the written category).
The immediate window is showing the correct results;

SELECT qryParts.Descrip FROM qryParts WHERE qryParts.Cat = 4

which is how it is logged in qryParts. There are no errors, but the dropdown list is still blank.
It is ok to use a row source from a query that you are writing to, isn't it? I also tried going straight from the table with the same results.

I just added a Debug.Print cbo.Desc.RowSource to the code and the immediate window displays
SELECT tblParts.Descrip FROM tblParts WHERE tblParts.Cat = 4
This sure looks right to me, but it isn't working.

[NISMOJim]

One other thing, when I change the selection in cboCat, cboDesc turns transparent, then it goes back to white when I click on it. Is there a way to keep this from happening? I tried adding Me.cboDesc.BackColor = RGB(250, 250, 250) after the Row Source line, but it didn't work either.

[NISMOJim]

OK, I got the row source working by changing the column width on cboDesc, thank you. Now I'm just concerned about that box being transparent.

[pbaldy]

Yes, I meant the second combo. As to the transparent thing, see if it's this one:

http://allenbrowne.com/Access2007.html#Bugs

Specifically the one called "Combo becomes transparent". Note that it's lined out because there's a fix.

[GeekInOhio]

One other thing, when I change the selection in cboCat, cboDesc turns transparent, then it goes back to white when I click on it. Is there a way to keep this from happening? I tried adding Me.cboDesc.BackColor = RGB(250, 250, 250) after the Row Source line, but it didn't work either.

On your form select the Detail bar, and in the Property Sheet change the "Alternate Back Color" to anything other than "No Color". Took me forever to solve that annoying little behavior.

Edit: Whoops... beat to the punch.

[NISMOJim]

That did the trick. Now, as it turns out, the users want to interface in a different way, so the whole thing was for nothing. Good learning experience for me though, so thank you very much!

[pbaldy]

Happy to help. It wasn't for nothing if knowledge was gained!