OnCurrent not running with Requery (1 Viewer)

JJSHEP89

Registered User.
Local time
Today, 07:34
Joined
Aug 18, 2016
Messages
121
Im sure this is something simple but i cant seem to get it working and my deadline is fast approaching.

I have a simple form that contains some combo boxes used for filtering data and a listbox to display the results of the query. The query SQL is executed with the forms OnCurrent event, which from what i read should run every time the form is re-queried. I have added a Me.requery line to the OnChange event for my combo boxes used for filters but it does nothing. I have verified that all the code in the OnCurrent Event is working just fine, but why is it not running with a re-query? FYI: controls are contained on the form itself, no subforms

Code:
Private Sub btnClearFilters_Click()
    Me.cboFilterAsignee = Null
    Me.cboFilterOriginator = Null
    Me.Requery
End Sub

Private Sub cboFilterAsignee_Change()
    Forms("Communicators").Refresh
End Sub
Private Sub cboFilterOriginator_Change()
    Me.Requery
End Sub
Private Sub Form_Current()
Dim strSQL, strWhereSQL, strEmpID As String
    On Error GoTo ErrorHandler:
    
    If Len(Nz(Me.cboFilterAsignee, "")) <> 0 Then
        strWhereSQL = strWhereSQL & "AND ((Communicator.Send_To) = '" & Me.cboFilterAsignee & "')"
    End If
    If Len(Nz(Me.cboFilterOriginator, "")) <> 0 Then
        strEmpID = DLookup("EmpID", "dbo_ICEp_vwEmpBasic", "dbo_ICEp_vwEmpBasic.EmpName = '" & Me.cboFilterOriginator & "'")
        strWhereSQL = strWhereSQL & "AND ((dbo_ICEp_vwEmpBasic.EmpName) = '" & Me.cboFilterOriginator & "')"
    End If
    
    strSQL = "SELECT Communicator.CommunicatorID, dbo_ICEp_vwEmpBasic.EmpName AS Originator, Communicator.Date_Today AS Submitted, Communicator.Equip_ID_Num AS [Equipment ID], " & _
             "Communicator.Problem, Communicator.[Completion Date] FROM dbo_ICEp_vwEmpBasic INNER JOIN Communicator ON dbo_ICEp_vwEmpBasic.EmpID = Communicator.EE_NUM " & _
             "WHERE ((Communicator.[Completion Date]) Is Null) " & strWhereSQL & " ORDER BY Communicator.Date_Today DESC;"
    Me.lstOpenCommunicators.RowSource = strSQL
    Debug.Print strSQL
    Me.Requery
Exit Sub
ErrorHandler:
        MsgBox Err.Number & " - " & Err.Description & vbNewLine & "If the error persists, close and re-open the Info Center.", , "Info Center Database Error"
        Exit Sub
End Sub
 

Minty

AWF VIP
Local time
Today, 12:34
Joined
Jul 26, 2013
Messages
10,354
Try using the After update event for picking up changes in the combo data. On Change fires when data is changed but if these are unbound combo's effectively there is no data changed .

Also Me.Requery in the form current event may well not work, as that would trigger a current event to fire and...

I think you would need to requery specific controls (e.g. your listbox)
 

isladogs

MVP / VIP
Local time
Today, 12:34
Joined
Jan 14, 2017
Messages
18,186
Use the after update event on your combos instead of the change event.
What do you think the form refresh line will do?

The form current event will run when anything is done on your form.
This may not be what you want.
I'd move the code to the after update event or events
 

JJSHEP89

Registered User.
Local time
Today, 07:34
Joined
Aug 18, 2016
Messages
121
Try using the After update event for picking up changes in the combo data. On Change fires when data is changed but if these are unbound combo's effectively there is no data changed .

Also Me.Requery in the form current event may well not work, as that would trigger a current event to fire and...

I think you would need to requery specific controls (e.g. your listbox)

Good point. I've made the recommended changes but still no luck. would it be possible to call the Form_Current event from another event? so rather than requery from the after update, i simply call the Form_Current from within it as if it was a function?
 

Minty

AWF VIP
Local time
Today, 12:34
Joined
Jul 26, 2013
Messages
10,354
The form Current event Occurs when the focus moves to a record, making it the current record, or when the form is refreshed or re-queried.

Move all your the OnCurrent events into a Private Sub.
In the on current event call that Private sub, you can then also call all the same events whenever you want, rather than trying to force the event to happen illogically.

Whilst testing make sure you don't create a weird loop.
 
Last edited:

JJSHEP89

Registered User.
Local time
Today, 07:34
Joined
Aug 18, 2016
Messages
121
Just for anyone who may be having the same issue, to fix this i simply created a Private subroutine and called this subroutine for each event that needed it.

Code:
Private Sub UpdateComm(intTabNumber As Integer)
Dim strSQL, strWhereSQL, strEmpID As String
Dim CommCount As Integer
    On Error GoTo ErrorHandler:
    
    Select Case (intTabNumber)
        Case 0 'Open Communicators
            If Len(Nz(Me.cboFilterAssignee, "")) <> 0 Then
                strWhereSQL = strWhereSQL & "AND ((Communicator.Send_To) = '" & Me.cboFilterAssignee & "')"
            End If
            If Len(Nz(Me.cboFilterOriginator, "")) <> 0 Then
                strEmpID = DLookup("EmpID", "dbo_ICEp_vwEmpBasic", "dbo_ICEp_vwEmpBasic.EmpName = '" & Me.cboFilterOriginator & "'")
                strWhereSQL = strWhereSQL & "AND ((dbo_ICEp_vwEmpBasic.EmpName) = '" & Me.cboFilterOriginator & "')"
            End If
    
            strSQL = "SELECT Communicator.CommunicatorID, dbo_ICEp_vwEmpBasic.EmpName AS Originator, Communicator.Date_Today AS Submitted, Communicator.Equip_ID_Num AS [Equipment ID], " & _
                    "Communicator.Problem, Communicator.[Completion Date] FROM dbo_ICEp_vwEmpBasic INNER JOIN Communicator ON dbo_ICEp_vwEmpBasic.EmpID = Communicator.EE_NUM " & _
                    "WHERE ((Communicator.[Completion Date]) Is Null) " & strWhereSQL & " ORDER BY Communicator.Date_Today DESC;"
            Me.lstOpenCommunicators.RowSource = strSQL
            Me.lstTotalOpen.RowSource = "SELECT Count(*) AS Total FROM dbo_ICEp_vwEmpBasic INNER JOIN Communicator ON dbo_ICEp_vwEmpBasic.EmpID = Communicator.EE_NUM " & _
                    "WHERE ((Communicator.[Completion Date]) Is Null) " & strWhereSQL & ";"
            Me.Requery
        
        Case 1 'Closed Communicators
            If Len(Nz(Me.cboFilterAssigneeClosed, "")) <> 0 Then
                strWhereSQL = strWhereSQL & "AND ((Communicator.Send_To) = '" & Me.cboFilterAssigneeClosed & "')"
            End If
            If Len(Nz(Me.cboFilterOriginatorClosed, "")) <> 0 Then
                strEmpID = DLookup("EmpID", "dbo_ICEp_vwEmpBasic", "dbo_ICEp_vwEmpBasic.EmpName = '" & Me.cboFilterOriginator & "'")
                strWhereSQL = strWhereSQL & "AND ((dbo_ICEp_vwEmpBasic.EmpName) = '" & Me.cboFilterOriginatorClosed & "')"
            End If
    
            strSQL = "SELECT Communicator.CommunicatorID, dbo_ICEp_vwEmpBasic.EmpName AS Originator, Communicator.Date_Today AS Submitted, Communicator.Equip_ID_Num AS [Equipment ID], " & _
                    "Communicator.Problem, Communicator.[Completion Date] FROM dbo_ICEp_vwEmpBasic INNER JOIN Communicator ON dbo_ICEp_vwEmpBasic.EmpID = Communicator.EE_NUM " & _
                    "WHERE ((Communicator.[Completion Date]) Is Not Null) " & strWhereSQL & " ORDER BY Communicator.Date_Today DESC;"
            Me.lstClosedCommunicators.RowSource = strSQL
            Debug.Print strSQL
            Me.lstTotalClosed.RowSource = "SELECT Count(*) AS Total FROM dbo_ICEp_vwEmpBasic INNER JOIN Communicator ON dbo_ICEp_vwEmpBasic.EmpID = Communicator.EE_NUM " & _
                    "WHERE ((Communicator.[Completion Date]) Is Not Null) " & strWhereSQL & ";"
            Me.Requery
    End Select
Exit Sub
ErrorHandler:
        MsgBox Err.Number & " - " & Err.Description & vbNewLine & "If the error persists, close and re-open the Info Center.", , "Info Center Database Error"
        Exit Sub
End Sub
The subroutine itself does the requerying, not the form events. The OnCurrent event was changed to OnLoad to prevent a loop.
 

Minty

AWF VIP
Local time
Today, 12:34
Joined
Jul 26, 2013
Messages
10,354
Glad that sorted it.

Just as FYI be aware that this ;
Code:
Dim strSQL, strWhereSQL, strEmpID As String

Only declares strEmpID as a string. Because the other two aren't specifically declared as a type they will be created as Variant data type. It can lead to some strange faults with null values and other things you may not expect.
 

isladogs

MVP / VIP
Local time
Today, 12:34
Joined
Jan 14, 2017
Messages
18,186
Glad you got a working solution.
Out of interest, did you try just putting all the code in the combo after update event as I suggested in post 3
 

JJSHEP89

Registered User.
Local time
Today, 07:34
Joined
Aug 18, 2016
Messages
121
Glad you got a working solution.
Out of interest, did you try just putting all the code in the combo after update event as I suggested in post 3

I did, the only issue i had was that i had to click out of the combo box for it to execute. I tried the Before Update as well with the same result. It worked ok with OnChange so i stuck with that.
 

Users who are viewing this thread

Top Bottom