cascade combo boxes in access form

zleug 51 Reputation points
2021-11-13T00:16:51.277+00:00

Hi All
I created cascade combo boxes form and I have 2 such problems:

  1. When I selected in cboBrand value "_N/A" then the cboDeviceType will display "Other" and cboHardware populated by according values. Up to this moment all is OK. But I cannot select value from cboHardware. At the bottom of the screen I see message: "Control can't be edited; it's bound to AutoNumber field HardwareID." All my combo boxes bound to AutoNumber fields. Why that combo box has such a problem?
  2. When form is ran and I filled fields by some values, it automatically changing value in according fields of the Device table. How to avoid it? I would like to insert a new record.

I will appreciate if someone will show how to fix those problems.
Thanks.

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
898 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Ken Sheridan 2,846 Reputation points
    2021-11-14T01:14:20.467+00:00

    A combo box can never be bound to an autonumber column, i.e. its ControlSource property cannot be the name of an Autonumber. Its BoundColumn property, on the other hand, will often reference an autonumber column in the control's RowSource.

    From your description it sounds like the combo box is intended to be a navigational control, with the option of going to a new empty record in the form. For this the combo box should be unbound, and code in io9ts AfterUpdate event procedure should move the form to an empty new record if the appropriate option is selected in the control, or go to an existing record otherwise. You'll find an example in FindRecord.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    In this little demo file an unbound combo box in a contacts form has the following as its RowSource property:

    SELECT ContactID, FirstName & " " & LastName,
    1 As SortColumn, LastName, FirstName
    FROM Contacts
    UNION
    SELECT 0, "<New Contact>", 0,"",""
    FROM Contacts
    ORDER BY SortColumn, LastName, FirstName;

    The UNION operation adds the <New Contact> option to the list, in addition to the list of contact names. The code for the control's AfterUpdate event procedure is:

    Private Sub cboGotoContact_AfterUpdate()

    Const MESSAGETEXT = "No matching record"
    Dim ctrl As Control
    
    Set ctrl = Me.ActiveControl
    
    If Not IsNull(ctrl) Then
        If ctrl = 0 Then
            ' go to new record and move focus to FirstName control
            DoCmd.GoToRecord acForm, Me.Name, acNewRec
            Me.FirstName.SetFocus
        Else
            With Me.RecordsetClone
                .FindFirst "ContactID = " & ctrl
                If Not .NoMatch Then
                    ' go to record by synchronizing bookmarks
                    Me.Bookmark = .Bookmark
                Else
                    MsgBox MESSAGETEXT, vbInformation, "Warning"
                End If
            End With
        End If
    End If
    

    End Sub

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.