Combo box showing the wrong column after selection

dibblermail

Member
Local time
Today, 00:41
Joined
Jan 10, 2025
Messages
64
Please can someone put me out of my misery. This cant be as hard as I'm making it.

I've looked at old posts and nothing I've tried from them seems to work, so I'm not understanding something, or I'm looking straight through something.

I have a combo box with 1 column in it. But it also pulls in its Key (ClientID)

Column1 = ClientID
Column2 = ClientName

I don't want the end user to ever see the Client ID, Its just there as the key so it's irrelevant & confusing to that end user.

This form is used to display existing records & for adding new

When adding new & want to be able to see all the old client names in the drop down (obviously no point retyping it). But I also need to be able to add a new name

I can get it to show just the ClientName when I load the form & when I click in it to show the dropdown.

But no matter what I do, the end value displayed after selection is always the Client ID.

Ive tried these 2 to correct it (obviously 1 at a time).

Code:
Private Sub CBOX_ClientName_AfterUpdate()


 CBOX_ClientName.Text = CBOX_ClientName.Column(1)
 
 CBOX_ClientName.Text = CBOX_ClientName.Column(0) & " " & CBOX_ClientName.Column(1)
 
End Sub

but they both give me an error 2115 - Macro or function set to beforeupdate is preventing save (paraphrasing)

presumably this is because the ClientName is being saved into ClientID which is not viable.

What's the normal work around, this seems like such a simple thing to need, but it's solution is really eluding me.
 
You should be storing the clientID, but hide that column, by setting it's width to 0. I always put the key field first.
No need for that code you posted.
1759485973704.png
 
while in Design view of your form.
Set the Bound Column of the Combobox to 1 (ClientID).
Column Count = 2
Column Widths = 0
 
Thanks for the suggestions, I've tried that, but when I set the column width to 0,2 I get an error.

I cant set it to 0 because I need limit to list to be No.

So I set width to 0.01,2 & it ends up with the ClientID in the box
 
I cant set it to 0 because I need limit to list to be No.
You can use the NotInList event for any new entries?
 
Because life, I've just been pulled away from this & onto something else for the rest of the day. I'll send the database home & have a play over the weekend.

Thanks for the help so far, much appreciated.
 
You can use a union query as the row source from your combo box.
Code:
SELECT 0, " Add new client"
FROM tblClients
UNION
SELECT ClientID, ClientName
FROM tblClients
ORDER BY 2

Edited per @Gasman’s catch.
 
Last edited:
Second field should be clientname?
 
The NotInList event procedure should not be used where the combo box returns a personal name as these can legitimately be duplicated. I was once present at a clinic when two patients arrived within minutes of each other, both female, both with the same first and last names and both with the same date of birth. Also I once worked with two Maggie Taylors.

The attached little demo file illustrates the use of the NotInList event procedure in a number of contexts, but also includes an illustration of an alternative method to add a new person by means of a command button in the form. The button opens a form bound to the Contacts table in dialogue mode at a new record with:

Code:
Private Sub cmdNewContact_Click()

    DoCmd.OpenForm "frmContacts", _
        DataMode:=acFormAdd, _
        Windowmode:=acDialog, _
        OpenArgs:="New Contact"
   
End Sub

In the frmContacts form the new contact's names and other details are then inserted by the user. In the form's AfterInsert event procedure code also assigns the new ContactID value to an unbound cboGotoContact navigational control in the calling form, which shows the new contact in the format FirstName LastName::

Code:
Private Sub Form_AfterInsert()

    If Me.OpenArgs = "New Contact" Then
        With Forms("frmGotoContact")
            .cboGotoContact.Requery
            .cboGotoContact = Me.ContactID
        End With
    End If
   
End Sub

Unlike the use of the NotInList even procedure the above allows two or more contacts of the same name to be added. In the combo box in the calling form they are differentiated by showing their address data in a second column in the control's drop down list.

PS: The following code is an example from another of my demo files, which illustrates the use of a combo box based on a UNION query as described earlier by Duane Hookom. In this case the combo box is in the bound form itself rather than an unbound dialogue form:

Code:
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
 

Attachments

Last edited:

Users who are viewing this thread

Back
Top Bottom