Table record not reflecting form combo box selection

[cp1981]

Hello Chappys!



Im in quite a pickle here and Im falling down a rabbit hole.

I have a form that has a combo box that when selecting an account name it populates an unbound text box with an Account ID. Works flawlessly.
My issue is when I make sure that the "right hand is telling what the left hand is doing" i.e. when "Saved" via the form, it saves the record in the appropriate field on the table. It does not.
Instead of populating the "account name" field with the selected account name from the combo box, it populates with the ID and my Account ID field is a "0".

I have changed "bound column". I have swapped AccountID and AccountName fields in the query. Ive rearranged the VBA for the combo box...I'm lost and tired.

The VBA for the combo box on the form is from OnChange. textbox = combobox. Other than the usual VBA wording and lingo, thats the bread and butter. So then that populates the Unbound textbox to the Account ID. Looks great on the form but doesnt mean anything if its not recorded as such.

Someone please throw me a frickin' bone here. Thanks all.

[ArviLaanemets]

Why do you want the Account name field in your table. All you need there is AccountID field? And in form you have a combo linked to this AccountID field, which displays on form the account name instead of it's ID.
E.g.
tblYourTable: YourTableID, ..., AccountID, ...;
tblAccounts: AccountID, AccountName, ...;
In your form you have a combo box e.g. cbbAccount
Combo box properties essential for current case will be set as:
ControlSource = AccountID (the AccountID field of tblYourTable);
RowSource = "SELECT AccountID, AccountName FROM tblAccounts ORDER BY 2";
BoundColumn=1;
ColumnsWidth = "0,2.5"

The value of combo will be value of AccountID, but the combo displays the visible column in RowSource query - which is AccountName

[Welshgasman]

You store the ID (better to call it AccountID) not the name.
Also probably better to use the combo AfterUpdate event, not the OnChange

I always have a combo as
TableID
Name to Display
Any other columns needed.

So the code for the AfterUpdate could be
Me.txtAccountName = Me.combo.column(1)

[June7]

Also set ColumnCount property.

Agree, save ID not descriptive text.

Expression in textbox to reference combobox column by its index if you want to display AccountName (no VBA). If AccountName is in second column: =comboboxname.Column(1)

Is AccountID set as primary key in Accounts table?

Foreign key field in receiving table should be a number type to populate with AccountID key.

Advise not to use spaces nor punctuation/special characters in naming convention.

[cp1981]

Much thanks! Works like a dream. I had my head wrapped around when I see the table I want to see both the "ID" and the associated Name, account, entity, etc. I guess thats what queries are for..(?) Im new but Im not. I know enough to get me by...but I also know enough to get me in trouble too.

Again. Thamks a million. you saved the day. Im not worthy. 1000 atta boys for ya!

[cp1981]

The text box is unbound. But I believe I have found my solution. But yes, I dont use spaces or special characters in my naming. Ive ALWAYS seen and heard that so I dont question why. But have always wondered why templates and other Access DB's use spaces and whatnot. Not for me to worry I guess. But again, I appreciate your response and I will definitely take it under advisement.

[June7]

I only mentioned because I saw "Account ID" in your narrative.

I also wonder why MS published templates violated that guidance. I think latest version of Northwind addresses this.

If used, must enclose in [] so queries will recognize where name starts and ends.