Resetting a query afterupdate, Best Practice

[BlueJacket]

I feel like I got this working, but I also feel like I went on a roundabout way to do it, using more code than I need to. Currently I have cboCounty updating and filtering cboJudgeName when a county is selected. This part works fine. For reference, here's that code:

Public Sub cboCounty_AfterUpdate()

    If gEnableErrorHandling Then On Error GoTo Errhandle

'Limits the selection of Judges based off what the user picks in the County combobox

    Dim stSQL As String
    
    stSQL = ""
    stSQL = stSQL & "SELECT "
    stSQL = stSQL & "  tblJudges.JudgeID "
    stSQL = stSQL & ", tblJudge_County.CountyID "
    stSQL = stSQL & ", tblJudges.JudgeName "
    stSQL = stSQL & "FROM tblJudges "
    stSQL = stSQL & "INNER JOIN tblJudge_County "
    stSQL = stSQL & "ON tblJudge_County.JudgeID = tblJudges.JudgeID "
    stSQL = stSQL & "WHERE tblJudge_County.CountyID = '" & Me![cboCounty].Column(0) & "'"
         
    Me![cboJudgeName].RowSource = stSQL
    'Me![cboJudgeName].SetFocus
    'Me![cboJudgeName].Dropdown
    Exit Sub
    
Errhandle:
        MsgBox "Error " & Err.Number & ": " & Err.Description & _
               vbCrLf & "Please make a note of this error and when it occured." & _
               vbCrLf & " " & FormatDateTime(Now(), vbGeneralDate)

End Sub

The problem is, that filter stays. Without further code, when I go to a different record that has a different county, cboJudgeName appears blank because the judge linked to that recorded isn't linked to the previously selected county.

So my solution is to reset the query whenever the GoToRecord search is used to select a different property.

Private Sub cboGoToRecord_AfterUpdate()

'Searches properties based off the info entered in the GoToRecord searchbox
    If gEnableErrorHandling Then On Error GoTo Errhandle
    
    Dim rst As Object
    Dim stSQL As String
        
    stSQL = ""
    stSQL = stSQL & "SELECT "
    stSQL = stSQL & " tblJudges.JudgeID, "
    stSQL = stSQL & " tblJudge_County.CountyID, "
    stSQL = stSQL & " tblJudges.JudgeName "
    stSQL = stSQL & "FROM tblJudges "
    stSQL = stSQL & "INNER JOIN tblJudge_County "
    stSQL = stSQL & "ON tblJudges.JudgeID = tblJudge_County.JudgeID;"
    [COLOR="Blue"]Difference: No WHERE clause. Also realized how I order my code on a subroutine matters.[/COLOR]    

    Me![cboJudgeName].RowSource = stSQL
    
    Set rst = Me.RecordsetClone
    
    rst.FindFirst "[PropertyID]=" & Me.cboGoToRecord.Value
    Me.Bookmark = rst.Bookmark
    Exit Sub
    
Errhandle:
        MsgBox "Error " & Err.Number & ": " & Err.Description & _
               vbCrLf & "Please make a note of this error and when it occured." & _
               vbCrLf & " " & FormatDateTime(Now(), vbGeneralDate)
    
End Sub

I also feel that I would have to use the same SQL code on cboCounty's After Update event on the form's OnLoad event to have all my bases covered.

This all gets the job done, but it seems like a lot. Is this the best way to about doing this?

Thanks in advance.

 

[CJ_London]

basic principle is OK, but would probably try putting the cbo_county_afterupdate code in the cboJudgeName onenter or gotfocus event

 

[BlueJacket]

basic principle is OK, but would probably try putting the cbo_county_afterupdate code in the cboJudgeName onenter or gotfocus event

Ok. So there isn't really a better practice for this sort of thing? I actually started with what you said, with the mindset of resetting the query after the filter has served its purpose, but then I figured that would happen automatically by putting it on the GoToRecord After Update event. This also has the added benefit that if the user happens to misclick on the wrong judge, the filter will still be there.

 

[CJ_London]

your are effectively using cascading combo principles.

I don't know how your form is intended to work, but if it has more than one record in the recordset a user can go to another record without using your gotorecord code