correct code for setting RowSource of listbox based on combobox?

[NBRJ]

I have a very simple query about the correct syntax for defining the RowSource of a listbox (lstRequest) based on a combo box value in the same form (fRequest.cboAcademicYear)

[B]Private Sub FilterRequest()[/B]

    Dim strREQUEST As String

    strREQUEST = "SELECT qRequest.IDRequest, qRequest.AcademicYear, qRequest.RequestType, qRequest.Subject FROM qRequest"
    strREQUEST = strREQUEST & " WHERE IDAcademicYear = "[COLOR=Red] & cboAcademicYear[/COLOR]
    strREQUEST = strREQUEST & " ORDER BY qRequest.IDRequest;"

    Me.lstRequest.RowSource = strREQUEST
    Me.lstRequest.Requery

[B]End Sub[/B]

The above works for me as it is. I got to this starting at: " WHERE IDAcademicYear = 1" (or 2 or 3, etc, being one of the IDs) which worked. What is confusing me is all the articles I've read state that the syntax to refer to the combo box (in red) should be:

strREQUEST = strREQUEST & " WHERE IDAcademicYear = " & Me.cboAcademicYear

That does not work for me. Why would that be? (Access 2013).

I also found Me.cboAcademicYear.Value didn't work.
Neither did Me.cboAcademicYear.Column(0). That should point to first column (hidden or not) shouldn't it, which is IDAcademicYear...

Isn't Me defining where to look in relation to current form?

I even tried fRequest!cboAcademicYear.Column(0) when Me wasn't working.... which didn't work.

Just curious if somecome could explain why (and why not) these do/don't work.... (and blindingly obvious probably) :banghead: I'd just like to learn why and not waste god-knows how much time over simple things.

 

[sneuberg]

Would you mind temporarily replacing your code with what follows and then post what you got in the immediate window after the code is run,

Private Sub FilterRequest()

    Dim strREQUEST As String

    Debug.Print "Works: " & " WHERE IDAcademicYear = " & cboAcademicYear
    Debug.Print "With Me: " & " WHERE IDAcademicYear = " & Me.cboAcademicYear
    Debug.Print "With column 0: " & " WHERE IDAcademicYear = " & Me.cboAcademicYear.Column(0)
    
    strREQUEST = "SELECT qRequest.IDRequest, qRequest.AcademicYear, qRequest.RequestType, qRequest.Subject FROM qRequest"
    strREQUEST = strREQUEST & " WHERE IDAcademicYear = " & cboAcademicYear
    strREQUEST = strREQUEST & " ORDER BY qRequest.IDRequest;"

    Me.lstRequest.RowSource = strREQUEST
    Me.lstRequest.Requery

End Sub

 

[arnelgp]

Me refers to the form where the current code is running.
so if FilterRequest sub resides on the same form it is called you can use Me.ControlName.
if FilterRequest sub is in the subform of your form, you reference the control using its Parent form's name:

Me.Parent!ControName or
Forms!MainFormName!ControlName

now, if FilterRequest sub is declared Public and is in a Module, you reference the control:
Forms!MainFormName!ControlName

 

[NBRJ]

sneuberg I don't get any window pop up. It's like it ignores the print.debug and just does the SELECT section. ???

 

[sneuberg]

You don't get a pop up. To see the results of the Debug.Print go to the VB editor (Alt F11), Click View and then Immediate Window or press Ctrl+G

You can copy and paste the result into you post.

 

[NBRJ]

Sorry, yeah found that just after I posted. That is going to be so useful. I've rebuilt the form and it's all now working. Thanks for the help! Which is the better one to use, by the way. Which is considered to be better form?

 

[Cronk]

Incidentally, if the combo or listbox rowsource is changed, Access does a requery. Adding a specific requery ie Me.lstRequest.Requery in your case, is superfluous and just a waste of system time.