Entry in Forms of a Combo Box posts information to the wrong field in the underlying Table

[Richard12]

I have created a form, frmContacts, which is used to enter contact names and details.

One entry box is a Combo which fills in the contact's Agent and their details (AgentKnownAs, AgentSurname, AgentFirmID, AgentMobile) from a separate table. Note, the primary key, AgentPersonID (an autonumber) is not used

However, when I look at tblContacts I find AgentSurname, AgentFirmID, and AgentMobile are correctly posted. However the AgentPersonID, a number, appears in AgentKnownAs, and the AgentPersonID field is blank.

Why, and how do I correct this?


The coding from the wizard is: Private Sub cboAgent_Change()
Me.txtAgentSurname.Value = Me.cboAgent.Column(2)
Me.txtAgentFirmID.Value = Me.cboAgent.Column(3)


Me.txtAgentMobile.Value = Me.cboAgent.Column(4)
End Sub

Richard

[June7]

Why are you duplicating data between tables?

What are the combobox properties:
RowSource
ControlSource
BoundColumn
ColumnCount
ColumnWidths

If you want to provide db for analysis, follow instructions at bottom of my post.

[Missinglinq]

......I find AgentSurname, AgentFirmID, and AgentMobile are correctly posted. However the AgentPersonID, a number, appears in AgentKnownAs, and the AgentPersonID field is blank...

Private Sub cboAgent_Change()
  Me.txtAgentSurname.Value = Me.cboAgent.Column(2)
  Me.txtAgentFirmID.Value = Me.cboAgent.Column(3)
  Me.txtAgentMobile.Value = Me.cboAgent.Column(4)
End Sub

How were you expecting AgentPersonID and AgentKnownAs to be entered into the Table? There's no code in the above for assigning these two Fields, only code to assign AgentSurname, AgentFirmID, and AgentMobile, which you say is working.
The

AgentPersonID, a number, appears in AgentKnownAs, and the AgentPersonID field is blank

sounds as if the AgentKnownAs Field has been set up as a Lookup Field at the Table-level, which is shunned by experienced developers, for the reasons listed here:

The Evils of Lookup Fields in Tables

And as June7 suggested, most developers are going to ask why you're storing all of this redundant information in multiple Tables; normally you'd store the AgentPersonID and other agent data in one, primary Table, then store only AgentPersonID, in the other, ancillary Tables, and merely pull the agent details, as needed, for other Forms, Reports, etc..

In other words, only Fields that are unique to the one, primary Table should be stored in multiple Tables. This is what Relational Databases are all about, and differentiates them from the old-style flat files.

Linq ;0)>

[Richard12]

Hi June7 and Missinglinq,
Thank you for your interest in my Combo Box question. I am sorry about the delay in replying but I have been concerned with other matters.
The overall plan is to create a database centred on a Contact List, a Contracts List, and a Record of Payments, with queries and reports as needed.
Some of the underlying parts, for instance the Contacts list, may look like a flat-database. However, June7, this is not the case.
Several Agents may work for one firm. Thus there is a table/form for the unique elements of the AgentFirm – name, address, switchboard. Agent’s firm is unique, and is used as the primary key. The form is, in effect, flat.
There is another table/form for the individual AgentPersons, together with personal details, eg phone, and secretary info. This also draws on some of the AgentFirm data but it is taken from that database by combo-box, not re-entered for each Agent. Because there could be several Agents with the same name working at the same firm the primary key is autonumber. Again, in isolation this table/form appears flat.
Finally, there is the Contacts table/form. This has the unique features of the particular contact – name, address, phone nos, etc. But there is also a combo box to enter the Agent, if any, from the AgentPerson table.
In all cases, data is only entered once.
Now to my question. The auto-numeric primary key AgentPersonID is used to create the one-to-many relationship set up between the AgentPerson and Contacts. However it has no meaning: I refer to an Agent as Joe Bloggs, not as number 33!
The combo box that fills Agent details into the Contact form works using the following ‘boxes’ AgentKnownAs, AgentSurname, AgentFirmID (which is alphameric so makes sense!), AgentMobile, and AgentLandline. Note that the numeric primary key is not used. However in the underlying Contact table the primary key value AgentPersonID appears in the AgentKnownAs field, and AgentKnownAs is lost. Other values are OK, although AgentPersonID is left blank.
The combo-box populates everything properly if the AgentPersonID is included in the form. Given that, as I say, the AgentPersonID has no meaning it is irksome that it has to be included. Is it necessary.
Thank you for the information about the relative uselessness of Lookup tables. This will no doubt be of concern to me later.
I cannot see how to attach the database!
Thank you
Richard12

[June7]

What exactly do you not understand about the instructions for attaching file?

Autonumber primary keys are not supposed to have meaning, users aren't even supposed be aware of them. You don't have to use autonumber as primary key and if you don't want to then don't define it as primary and don't include it in combobox RowSource.