Clearing a combo box bound to a primary key issue

Question

I'm currently trying to add an event to a 'Save' button that queries & updates a record and then clears all controls on the form, but the combo box that populates all of them--that's also bound to the primary key of the table--won't clear unless it has a value.

I've tried

cboName.Clear()      'This method doesn't exist
Me.cboName.Clear()   'Same issue as above
Me.cboName = Null    'The table field must have a value error
Me.cboName = ""      'Also must have a value error

Dim ctl As Control          'This returns an invalid use of index
For Each ctl in Me.Controls
   Select Case TypeName(ctl)
      Case "ComboBox"
         ctl.ListIndex = -1
   End Select
Next ctl

This is the save button code:

Private Sub Save_Click()
    Dim db As DAO.Database
    Dim rst As Recordset
    Dim rec As Recordset
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset( _
        "SELECT Count(*) FROM [Records] WHERE [Name] = '" & [Forms]![Record Form]!cboName.Value & "'")
    If rst.Fields(0) > 0 Then
        Set rec = db.OpenRecordset("SELECT * FROM [Records] WHERE [Name] = '" & Me.cboName.Value & "'")
        rec.Edit
        rec("Date1") = Me.Date1.Value
        rec("Date2") = Me.Date2.Value
        rec("Check1") = Me.Check1.Value
        rec("Check2") = Me.Check2.Value
        rec("Incidents") = Me.Incidents.Value
        rec.Update
        rec.Close
        
        Dim ctl As Control ' Removed MSForms.

        For Each ctl In Me.Controls
            Select Case TypeName(ctl)
                Case "TextBox"
                    ctl.Value = ""
                Case "CheckBox", "ToggleButton" ' Removed OptionButton
                    ctl.Value = False
                Case "OptionGroup" ' Add OptionGroup
                    ctl = Null
                Case "OptionButton" ' Add OptionButton
                    ' Do not reset an optionbutton if it is part of an OptionGroup
                    If TypeName(ctl.Parent) <> "OptionGroup" Then ctl.Value = False
                Case "ListBox"
                    ctl.ListIndex = -1
            End Select
        Next ctl
        Me.cboName.RowSource = ""
    Else
        DoCmd.RunCommand (acCmdRecordsGoToNew)
    End If

And the code for the cboName:

Private Sub cboName_AfterUpdate()
    Dim dQry As String
    Dim dupeItems As String
    
    dQry = Me.cboName.Value
    
    On Error Resume Next
    dupeItems = DJoin("[Incidents] & '- out:' & [TimeOut] & ' / in:' & [TimeIn]", "[Outage Records]", "[Name] = '" & dQry & "'", vbCrLf)
    
    If dupeItems <> "- out: / in:" Then
        Me.Incidents.Value = dupeItems
    Else
        Me.Incidents.Value = Nothing
    End If
End Sub

And the RowSource for cboName:

SELECT [Outage Records].[Name], [Outage Records].[Date1], [Outage Records].[Date2] FROM [Outage Records] ORDER BY [Name];

Would my best option be to unbound and add the cboName value to the table through vba?

Thank you!

Can't 'clear' a bound control unless you want to remove data from record. If purpose of control is to enter search criteria then it should be unbound. If you want to 'clear' bound controls then move to new record row. Why using recordset instead of bound controls to enter data to record? — June7
I have no problem setting a bound combobox with Null when it does not already have value. — June7

young_student young_student · Accepted Answer · 2020-06-27T07:47:07

After my current DB started to corrupt, I duplicated the DB and changed the cboName to unbound, and made that populate a bound textbox.

Seems to work like a charm.

Thank you!

Clearing a combo box bound to a primary key issue

1 Answers