Combo box showing the wrong column after selection (2 Viewers)

[dibblermail]

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).

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.

 

[Gasman]

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.

 

[arnelgp]

while in Design view of your form.
Set the Bound Column of the Combobox to 1 (ClientID).
Column Count = 2
Column Widths = 0

 

[dibblermail]

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

 

[Gasman]

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?

 

[dibblermail]

You can use the NotInList event for any new entries?

That's not a property I can see, so I assume that's an argument in the code? maybe in the change event for the combobox?

 

[Gasman]

Event.

https://www.youtube.com/results?search_query=access+not+inlist+event

 

[sonic8]

CBOX_ClientName.Text =

You shouldn't set the .Text property but the .Value property.

 

[dibblermail]

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.

 

[DHookom]

You can use a union query as the row source from your combo box.

SELECT 0, " Add new client"
FROM tblClients
UNION
SELECT ClientID, ClientName
FROM tblClients
ORDER BY 2

Edited per @Gasman’s catch.

 

[Gasman]

Second field should be clientname?