Which combobox event is best to reset the rowsource (1 Viewer)

[t00ley]

Hi All,

I am developing a simple query builder which enables the user to create rules to automate data changes. When creating the criteria, the user selects a FieldName, Operator & Search value. The operators available are based on the Table.Def DataTypeName for each subform record. The following works, but there is a minor annoyance in that the user has to click on the Operator combobox twice to show the dropdown. I guess I'm using the wrong event.

Some detail:

Operator Combobox rowsource:

SELECT tblLULItms.ListItem, tblLULHdrs.ListName
FROM tblLookUpLists_Headers AS tblLULHdrs INNER JOIN tblLookUpLists_Items AS tblLULItms ON tblLULHdrs.LULH_ID = tblLULItms.LULH_ID
WHERE (((tblLULHdrs.ListName)=[Forms]![frmRules_Main_Rules]![frmRules_Sub_Criteria]![txtDataType]));

Control Events (ps_SQL = the above code):

Private Sub cboFieldName_Change()
    With Me.cboOperator
        .SetFocus
        .RowSource = ps_SQL
        .Value = "="
        .Requery
        .Dropdown
    End With
End Sub

The above ensures the Operator rowsource is correct when the FieldName is changed which works fine.

Private Sub cboOperator_GotFocus()
    With Me.cboOperator
        .RowSource = ps_SQL
        .Value = "="
        .Requery
    End With
End Sub

The above ensures the Operator rowsource is correct when the user jumps from one subform record to another by directly clicking on another records Operator combobox. Works, but user has to click twice.

There are no other events for these controls.


Can anyone advise why the user has to click twice?

Thanks

Tooley

 

[Minty]

It look to me as if the ps_SQL doesn't change based on the change on cboFieldName, unless I'm being a bit think?

Even if I'm not right, I would only requery the rowsource for cboOperator on the after update event of cboFieldName, not on change which will react to every keystroke.

If it hasn't changed then you don't need the on got focus requery. If you can scroll through records on the form then add a cbo requery to the On_Current event of the form to keep things correct per record.

 

[t00ley]

Thanks Minty. I think it may have been the requery causing the user having to click twice. I've changed the module to the following, which appears to be working as requied:

Option Compare Database
Option Explicit

Const ps_SQL As String = _
    "SELECT tblLULItms.ListItem" _
    & ", tblLULHdrs.ListName " _
    & "FROM tblLookUpLists_Headers AS tblLULHdrs " _
        & "INNER JOIN tblLookUpLists_Items AS tblLULItms " _
            & "ON tblLULHdrs.LULH_ID = tblLULItms.LULH_ID " _
    & "WHERE (((tblLULHdrs.ListName)=[Forms]![frmRules_Main_Headers]![frmRules_Sub_Criteria]![txtDataType]));"

Dim pbool_OnNewRecord As Boolean

Private Sub Form_Current()
    If Me.NewRecord Then
        pbool_OnNewRecord = True
    Else
        pbool_OnNewRecord = False
    End If
End Sub

Private Sub cboFieldName_AfterUpdate()
    Call sRefreshOperatorList
End Sub

Private Sub cboOperator_Enter()
    Call sRefreshOperatorList
End Sub

Public Sub sRefreshOperatorList()
    With Me.cboOperator
        .SetFocus
        .RowSource = ps_SQL
        If Not pbool_OnNewRecord Then .Value = "="
        .Dropdown
    End With
End Sub

 

[Minty]

Glad you fixed it.

For a more verbose code you can change your on current code to

   pbool_OnNewRecord = Me.NewRecord