AfterUpdate with "All" in Combo Box

[nuke_girl]

Hi everyone,

I've put together bits of things that I've been working on learning from this site and created a form that almost works.

The user selects a location, or "---All Locations---" from a combo box (used AddAllToList to add All). This is supposed to update two subforms on the form. I used the code from Mile's sample db located here and modified it to get:

Private Sub Station_AfterUpdate()
  
  On Error GoTo Err_Station_AfterUpdate

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Dim var As Variant
    
    Set db = CurrentDb
    
    If Me.Station = "---All Locations---" Then
        strSQL = "{mySQL - no filter for location}"
    Else
        strSQL = "{mySQL - filtered for location}"
        End If
    
    On Error Resume Next
    db.QueryDefs.Delete "qryStationDose"
    On Error GoTo Err_Station_AfterUpdate
    
    Set qdf = db.CreateQueryDef("qryStationDose", strSQL)
    
    Me.Subform1.Requery
    Me.Subform2.Requery
    
Exit_Station_AfterUpdate:
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub
    
Err_Station_AfterUpdate:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_Station_AfterUpdate

End Sub

My problem is that, when I open the form, if the FIRST selection I make is "---All Locations---" I get the data for all locations, no matter how many times I change my combo box. If the FIRST selection I make is out of the rest of the list, the subforms will change when I make a different selection, and give me no data when I select "---All Locations---". I think this means that my code is not running when I update the combo box, but I can't figure out why.

Any help anyone can give me to fix this problem would be greatly appreciated.
Thanks,
~Nuke~

 

[Mod]

Private Sub Station_AfterUpdate()
  
  On Error GoTo Err_Station_AfterUpdate
    Dim strSQL As String

    If Me.Station = "---All Locations---" Then
        strSQL = "SELECT * FROM tblStations;"
    Else
        strSQL = "SELECT * FROM TblStations WHERE StationName = '" & Me.Station & "';"
    End If
    
    Me.Subform1.RecordSource = strSQL
    Me.Subform1.RecordSource = strSQL
    Me.Subform2.RecordSource = strSQL
    Me.Subform1.Requery
    Me.Subform2.Requery
    
Exit_Station_AfterUpdate:
   Exit Sub
    
Err_Station_AfterUpdate:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_Station_AfterUpdate

End Sub

What I've done here is make two query strings. One has a WHERE clause, and one doesn't. Depending on the selection of the combo box, your subforms will either have a restricted record source, or an unrestricted one.

I think this is what you are going for.

Edit:
Replace tblStations and StationName with the name of the table and the field that the combo box is based on, respectively.

 

[nuke_girl]

Thanks, Mod!

Do you think that it would still work if I set up two strings (eg. strSQL1 and strSQL2) so I could have different record sources for my two subforms? Both are dependant on the same combo box, but contain different data.

 

[nuke_girl]

It didn't work.

I created the code as you suggested, but modified it so I have two different SQL statements for my two separate subforms.

When I select a location from my combo box, I get a compile error ("Method or data member not found"). It seems to have a problem with the first RecordSource line.

I'm not sure what I need to fix because I can't find anything wrong.

My SQL statement is sorta complicated, so I'm not going to post my code, but let me know if you need to see it.

~ N ~

 

[Mod]

Ah, ok, I have to be careful what object I'm refering to.

I'm guessing now that SubForm1 and SubForm2 are the names of the subform controls in the parent form and not the actual forms. Try replacing these two lines with

Me.Subform1.Form.RecordSource = strSQL1
Me.Subform2.Form.RecordSource = strSQL2

 

[nuke_girl]

That sure feels good!

Mod,

Thanks so much!!

It now all works perfectly.

And I think I actually learned something in the process (which is always a nice bonus!)

Thanks again,
~ N ~