Results 1 to 5 of 5
  1. #1
    Richard12 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Oct 2014
    Location
    UK
    Posts
    9

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

    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

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Quote Originally Posted by Richard12 View Post

    ......I find AgentSurname, AgentFirmID, and AgentMobile are correctly posted. However the AgentPersonID, a number, appears in AgentKnownAs, and the AgentPersonID field is blank...
    Code:
    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)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Richard12 is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Oct 2014
    Location
    UK
    Posts
    9
    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

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 05-21-2014, 02:55 PM
  2. Replies: 5
    Last Post: 02-12-2014, 11:52 PM
  3. Form Combo Box Saving Wrong Information
    By Steve62 in forum Reports
    Replies: 5
    Last Post: 11-12-2012, 02:29 PM
  4. Replies: 1
    Last Post: 09-30-2011, 06:54 PM
  5. Replies: 5
    Last Post: 04-06-2011, 09:02 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums