Combo Box Error (1 Viewer)

[brsawvel]

I know this is an easy fix...

I have cascading combo boxes.

The first combo box returns the value of tblA.fldA (which is a number field).

The second combo box is supposed to return the value of tblA.fldB Where tblA.fldA = the first combo box.

Nothing is coming up in the second combo box. Here is the code I am using in the AfterUpdate Event:

 Combo11.RowSource = "Select tblSurveys.fldSurveyName FROM tblSurveys WHERE tblSurveys.fldYear = '" & Combo9.Value & "' "

 

[pbaldy]

If it's a numeric field, you don't want the single quotes around the value.

 

[brsawvel]

I thought it might be, but it still appears blank.

 

[pbaldy]

So what is the code now? Have you set a breakpoint so you can see what the SQL is and make sure it's what you expect?

 

[brsawvel]

The code is the same but without the apostrophes. I'm not sure what you mean by setting a breakpoint...

 

[pbaldy]

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

 

[brsawvel]

OK, So what I've figured out is that the problem is somewhere in the WHERE clause. What exactly, though, I don't know because I'm not getting an error. Any thoughts?

 " WHERE tblSurveys.fldYear = " & Combo9.Value & " "

 

[pbaldy]

Well, you don't need the bit at the end since you're not adding the single quote anymore. If that doesn't fix it, what's the full SQL you get in debug mode? Will that SQL run in a query?

 

[brsawvel]

Which bit are you referring to? I'll run it through a query and see what comes up...

 

[brsawvel]

I created a query using the code and I drilled down the issue further to the problem being somewhere in -

" & Combo9.Value & "
Still not sure how to resolve the problem though. I've tried everything I can think of.

 

[pbaldy]

I'm saying you don't need the bit in red:

"...WHERE tblSurveys.fldYear = " & Combo9.Value & " "

While in debug mode, after the rowsource is set type this into the immediate window:

?Forms!FormName.Combo11.RowSource

and see what the SQL is.

 

[brsawvel]

I don't know what happened, but I got it to work!...

Do you mind answering another quick question though (promise it's the last with this issue)?

I created an open form button that I want to take the values of the 2 combo boxes and display all records with matching values in the opened form.

I have the...

 stLinkCriteria = "[fldYear]=" & Me![Combo11]

written, but how do I tell it to also consider...

 "[fldSurveyName]=" & Me![Combo9]

I tried using an AND in between the two, but it came up with an error. (fldSurveyName is a text field, so the coding might be off too)

 

[pbaldy]

The mistake most people make is not getting the "AND" inside the quotes. It would look like:

stLinkCriteria = "[fldYear]=" & Me![Combo11] & " AND [fldSurveyName]=" & Me![Combo9]

If the field is actually a name (text field), it would need to be surrounded by single quotes.