Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2008
    Posts
    150

    Unhappy Unanswered: Problem with populating an input form using a lookup table

    Hello All,

    I have a master service form (input form) with 3 text boxes (LastName, FirstName and Duty) that I am trying to auto-populate from a lookup table (tblAssignment). I would like the auto-populated data to record in my service table as well.

    I have a combo box (cboLastName) with the following properties set:

    Control Source: LastName
    Row Source Type: SELECT Assignment.LastName, Assignment.FirstName, Assignment. Duty, Assignment.dd_id FROM Assignment;
    Bound Column: 1

    I am populating the Last Name, but cannot get the First Name and Duty data populated. How can I populate the other two fields?

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    I think you're asking the wrong question: If LastName, FirstName and Duty always come together, than why are you storing all three? I think you need to have a table with people's information (LastName, FirstName and Duty) and an ID, and then you only store the ID field. Have a look at database normalization.
    Last edited by nckdryr; 03-26-09 at 15:08.
    Me.Geek = True

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ▲ What he said
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Sep 2008
    Posts
    150
    My tblAssignment has the field names (ID, LastName, FirstName and Duty) you suggested. I want to populate another table (master server table) with the LastName, FirstName and Duty. So, what you are suggesting is to store the ID in the master service table instead of the name and duty data? Is this correct or am I off base?

  5. #5
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by mlrnet
    My tblAssignment has the field names (ID, LastName, FirstName and Duty) you suggested. I want to populate another table (master server table) with the LastName, FirstName and Duty. So, what you are suggesting is to store the ID in the master service table instead of the name and duty data? Is this correct or am I off base?
    I'd again point to the linked article Rudy has on relational database design (or this one, or heck even Microsoft gets it). Too many people don't worry enough about database design as I too often hear "But I just want to get this to work". The truth is, the tables and their relationships ought to be one of the first things you design in the database, and then, and only then, can you start to think about forms/reports/etc if you want to have anything that resembles a good database.

    So what I'm suggesting is to take out the LastName, FirstName and Duty and only store the person's ID in the Assignments table, and to store the ID, Last Name, First Name, Duty, etc. in a separate table. My question for you is how does the master server table relate to the tblAssignment? Unless I'm misunderstanding you, it sounds like you're trying to store data twice, which again I'd have to point you to the articles above.
    Me.Geek = True

  6. #6
    Join Date
    Sep 2008
    Posts
    150
    My daughter-in-law works as a service coordinator for a catering business. I am developing a quick database to input the event info, assigned servers and hours worked.

    I have the following tables and fields:

    tblAssignment
    a_id (PK)
    LastName
    FirstName
    Duty


    tblServices
    s_id (PK)
    EventDate
    EventTime
    CustName
    CustAddress,
    CustCity
    ContNumber
    ServTimeIn
    ServTimeOut
    ServTotalHrs


    I created an input form called “Service Input Form”. I have the following fields on the form.

    Event Info:
    Event Date
    Event Time
    Customer Name
    Customer Address,
    Customer City
    Contact Number


    Server Hrs:
    Server Time In
    Server Time Out
    Server Total Hrs


    Assigned Servers:
    Last Name
    First Name
    Duty


    I as using the “tblAssignment” as my lookup table for the “Assigned Servers” section on the form.

  7. #7
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by mlrnet
    I have a combo box (cboLastName) with the following properties set:

    Control Source: LastName
    Row Source Type: SELECT Assignment.LastName, Assignment.FirstName, Assignment. Duty, Assignment.dd_id FROM Assignment;
    Bound Column: 1

    I am populating the Last Name, but cannot get the First Name and Duty data populated. How can I populate the other two fields?
    So how are you storing the Assigned Servers against the Event? I assume there's another table involved with a many-to-many relationship (multiple servers can be on a single event, but a single server can also be on multiple events)?

    Also, and this is again going back to relational design (see how important it is ), I would recommend storing the Customers Information in their own table and just store the Customer ID in the Event Table.
    Me.Geek = True

  8. #8
    Join Date
    Sep 2008
    Posts
    150
    Nick,

    I thank you for taking the time to share your comments and recommendations.

    I am going to do some modifications to my database. I think I am on the right track, since I have most of the tables created. I need one more table, as you suggested which is to store the Customer information. I will update the Service table to add the Customer and Assignment IDs.

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Do you have 4 columns for the # of columns property?

    Are the widths: 2;2;2;2 (or 2;0;0;0) you must have the 0's.

    Then write a bit of code to populate the values in the other table (in the afterupdate event of the combobox/listbox). (this is ADO code)
    Dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from AssignedServers" <- Note: no spaces - get rid of ALL your spaces EVERYWHERE in the namings.
    rs.open strSQL,currentproject.connection,adopendynamic, adlockoptimistic
    rs.addnew
    rs!LastName = Forms!MyComboXField!LastName
    rs!FirstName = Forms!MyComboXField.colomn(1)
    rs!Duty = Forms!MyComboXField.column(2)
    rs.update
    rs.close
    set rs = nothing
    msgbox "Record Written."
    Last edited by pkstormy; 03-29-09 at 02:46.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I totally agree with what precedes, storing all the fields from one table into another is generally a bad idea and is a practice against the principles of data normalization.

    However, to answer your original question, the linked field (bound column if you prefer) of the cboLastName combo should have been Assignment.dd_id as it is the identifying field of the Assignment table.

    If you want to retrieve data from a column of a combo different from the bound column you can use the following syntax:
    Code:
    Combo.Column(x).Value
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •