1

Hello. I have a form with combo boxes that have VBA Enter events. These events log the RowSource values when the combo boxes are “entered”. This works for the first three combo boxes, but not the last two. The exact same logic is applied to all five combo boxes, and the last four combo box RowSource values are set using the AfterUpdate events of each previous combo box.

This one works perfectly (RowSource never changes; set during form design):

Private Sub cboFamily_Designator_Enter()
    'FETCH DEBUG MODE OPTION FROM tblAdminOptions
    blnDebugMode = DLookup("[Debug_Mode]", "tblAdminOptions")
    If blnDebugMode = True Then
        'ADD AN EVENT LOG ENTRY
        strEventLogObjectName = Me.Name
        strEventLogEntryText = "Control [cboFamily_Designator] RowSource = " & Me.cboFamily_Designator.RowSource
        Call Event_Log_Entry(strEventLogComputerName, strEventLogUserName, strEventLogObjectName, strEventLogEntryText)
    Else
        'NOTHING SPECIFIC TO DO HERE
    End If
End Sub

This one works perfectly (RowSource changes during AfterUpdate event of ‘cboFamily_Designator’):

Changed using:

Me.cboSystem_Designator.RowSource = "qsrcSystems_Family"

Private Sub cboSystem_Designator_Enter()
    'FETCH DEBUG MODE OPTION FROM tblAdminOptions
    blnDebugMode = DLookup("[Debug_Mode]", "tblAdminOptions")
    If blnDebugMode = True Then
        'ADD AN EVENT LOG ENTRY
        strEventLogObjectName = Me.Name
        strEventLogEntryText = "Control [cboSystem_Designator] RowSource = " & Me.cboSystem_Designator.RowSource
        Call Event_Log_Entry(strEventLogComputerName, strEventLogUserName, strEventLogObjectName, strEventLogEntryText)
    Else
        'NOTHING SPECIFIC TO DO HERE
    End If
End Sub

This one works perfectly (RowSource changes during AfterUpdate event of ‘cboSystem_Designator’):

Changed using:

Me.cboConfiguration_Designator.RowSource = "qsrcConfigurations_System"

Private Sub cboConfiguration_Designator_Enter()
    'FETCH DEBUG MODE OPTION FROM tblAdminOptions
    blnDebugMode = DLookup("[Debug_Mode]", "tblAdminOptions")
    If blnDebugMode = True Then
        'ADD AN EVENT LOG ENTRY
        strEventLogObjectName = Me.Name
        strEventLogEntryText = "Control [cboConfiguration_Designator] RowSource = " & Me.cboConfiguration_Designator.RowSource
        Call Event_Log_Entry(strEventLogComputerName, strEventLogUserName, strEventLogObjectName, strEventLogEntryText)
    Else
        'NOTHING SPECIFIC TO DO HERE
    End If
End Sub

This one does not work (RowSource value from ‘Me.cboComponent_Designator’ missing; changes during AfterUpdate event of ‘cboConfiguration_Designator’):

Changed using:

Me.cboComponent_Designator.RowSource = "qsrcComponents_Configuration"

Private Sub cboComponent_Designator_Enter()
    'FETCH DEBUG MODE OPTION FROM tblAdminOptions
    blnDebugMode = DLookup("[Debug_Mode]", "tblAdminOptions")
    If blnDebugMode = True Then
        'ADD AN EVENT LOG ENTRY
        strEventLogObjectName = Me.Name
        strEventLogEntryText = "Control [cboComponent_Designator] RowSource = " & Me.cboComponent_Designator.RowSource
        Call Event_Log_Entry(strEventLogComputerName, strEventLogUserName, strEventLogObjectName, strEventLogEntryText)
    Else
        'NOTHING SPECIFIC TO DO HERE
    End If
End Sub

This one does not work (RowSource value from ‘Me.cboSubComponent_Designator’ missing; changes during AfterUpdate event of ‘cboComponent_Designator’):

Changed using:

Me.cboSubComponent_Designator.RowSource = "qsrcSubComponents_Component"

Private Sub cboSubComponent_Designator_Enter()
    'FETCH DEBUG MODE OPTION FROM tblAdminOptions
    blnDebugMode = DLookup("[Debug_Mode]", "tblAdminOptions")
    If blnDebugMode = True Then
        'ADD AN EVENT LOG ENTRY
        strEventLogObjectName = Me.Name
        strEventLogEntryText = "Control [cboSubComponent_Designator] RowSource = " & Me.cboSubComponent_Designator.RowSource
        Call Event_Log_Entry(strEventLogComputerName, strEventLogUserName, strEventLogObjectName, strEventLogEntryText)
    Else
        'NOTHING SPECIFIC TO DO HERE
    End If
End Sub

What could be the reason(s) for the last two Enter events not producing the same results as the first three?

2 Spice ups
2

Additionally, placing a Debug.Print or MsgBox at the top of the offending Enter events result in positive results (RowSource values displayed as expected).

3

Hello @donaldfisher3 ​
Access is telling you the problem: the record source is missing for those two combo boxes.

Did you double check the query names qsrcComponents_Configuration and qsrcSubComponents_Component?

Are they in the query list exactly as you wrote?

Bye,
Mat.

4

Yes. Both queries do in fact exist. Also, the RowSource property for these combo boxes can be displayed using Debug.Print and/or MsgBox at the beginning of their Enter events, which should mean the RowSource properties are set and retrievable.

5

Could you try to place a debug.print just after setting strEventLogEntryText value:


```
strEventLogEntryText = "Control [cboSubComponent_Designator] RowSource = " & Me.cboSubComponent_Designator.RowSource
debug.print strEventLogEntryText 
```

Do you see the expected value?

If the answer is ‘yes’ then the problem is the log procedure Event_Log_Entry.

Mat.

1 Spice up
6

@spiceuser-qp8re ​ - Thank you for this. The value does appear in the strEventLogEntryText variable after assembly. The Event_Log_Entry procedure is as follows:

Public Sub Event_Log_Entry(EventLogComputerName As String, EventLogUserName As String, EventLogObjectName As String, EventLogEntryText As String)
    Dim strSQL As String
    strSQL = "INSERT INTO tblEventLog (Event_Log_Computer_Name, Event_Log_User_Name, Event_Log_Object_Name, Event_Log_Entry_Text) VALUES ('" & EventLogComputerName & "', '" & EventLogUserName & "', '" & EventLogObjectName & "', '" & EventLogEntryText & "')"
    DoCmd.RunSQL strSQL
    Forms!frmMain.sfrmEventLog.Form.Requery
End Sub

I am not seeing anything wrong here, and would appreciate any insight offered.

7

It seems that the docmd.runsql fails silently, maybe you have the warnings disabled.

Enable warnings before RunSql

DoCmd.Setwarnings True
DoCmd.RunSQL strSQL

If an error occours during the INSERT INTO you will see the message popup.

Mat.

1 Spice up
8

There are no warnings that pop up. The log entry is created, but contains nothing after the “=” in the string.

9

My apologies. The view of the data in which I observed the missing values did not contain all of the offending field’s text. It has been there the whole time; I just couldn’t see it.

10

Ook… all’s well that ends well
Bye,
Mat.

1 Spice up