CODEWITHSUNDEEP
vbams-accessms-access-2016
young_student
young_student

Reputation: 23

Clearing a combo box bound to a primary key issue

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!

Upvotes: 0

Views: 122

Answers (1)

young_student
young_student

Reputation: 23

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!

Upvotes: 1

Related Questions