Results 1 to 15 of 15
  1. #1
    b123 is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Sep 2010
    Posts
    24

    COMBO BOX Populating

    Hey guys I am tring to set up a combo box that pulls from a reference table and saves the data on a master table. Everthing works great for the column referenced by the combo box. Where I am getting hung up is I also want all data in the same row as selected by the combo box to be save onto the master table with corresponding fields. For instance the combo box is refering to the label of an item and all of the parameters for the item are on the same row in the reference table and I would like to pull all at the same time when I select the label from the combo box. Is this possible? I am reading a lot of info, but it does not address this specific task, I am sure this is simple for you guys. Please help and Thanks.

  2. #2
    michaeljohnh is offline More Human than Human
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    61
    I probably should sit this one out, since I only just got this kind of dynamic to work myself.

    To make it work in my case, the combobox has a Control Source from the table you're writing to but a Row Source of the table youre reading from. In my case I was reading user names and associated contact info from a users database but writing their name and associated issue into a trouble ticket table.

  3. #3
    b123 is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Sep 2010
    Posts
    24
    This sounds like what I want to do except there are four data fields I want write along with the part name

  4. #4
    michaeljohnh is offline More Human than Human
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    61
    As for what else I did to make it work, the Record Source of the form is the table I am writing to. To populate the fields youre pulling that will then be written to the other table you might be able to use the Dlookup command. Although personally, I used an SQL command but I kind of mickey moused it.

    For example, to pull the phone number associated with the user, I created another combo box with a Control source that is the phone field of the table I am writing to but a Row Source that looks to the table that is being read :

    SELECT Users.[Phone] FROM Users WHERE (((Users.FullName)=Forms![Form2]!cboUser)) ORDER BY Users.[Phone];

    Someone who really knows what theyre doing wouldnt need to use a combo box but I couldnt get it to work other wise.

  5. #5
    b123 is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Sep 2010
    Posts
    24
    I dont think multiple combo boxes are going to work for my app. It would give the user a chance to mistakenly choose the wrong associated data with the current record selected. I really need to figure a way to use the one combo box. I will investigate the DLookUp option maybe this is what I need.

  6. #6
    michaeljohnh is offline More Human than Human
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    61
    Just to clarify, even though its a combo box, only one choice will appear in it. Basicly, the selection made in the first combo box will restrict the subsequent combo boxes to a single choice. So, yes the user needs to click on the other combo boxes but only the corresponding field will be in the box. Like I mentioned, its mickey moused but couldnt figure out how else to do it.

  7. #7
    slave138's Avatar
    slave138 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    WI
    Posts
    233
    I may not be understanding this completely but you should be able to use an Append (or Update) query in the Query builder. Append/Update the info from the lookup table to the main table where the tblLookup.Label = Forms!frmMyForm.cmbMyCombo... or something like that.

  8. #8
    b123 is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Sep 2010
    Posts
    24
    Slave138 I am not sure exactly what you are saying. I looked at the query builder and i had all of the fields included in the query but they dont update to the main table. Is there a way that I can set them up to report to the main table and edit when the main search is changed from the combobox on the form.

  9. #9
    slave138's Avatar
    slave138 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    WI
    Posts
    233
    I'm trying to make a sample for you but I need one quick bit of info: Are the records in the reference table unique?

  10. #10
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    someone may suggest you to normalize you table design.

    Actually, you don't need to (should not ) save all information from reference table into master table, just save the key is enough. then you won't have this kind of problem at all.

  11. #11
    b123 is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Sep 2010
    Posts
    24
    Yes they are. They are indentified by the nozzel number code. I did not link them as a primary key to the master table I figured once the info if populated to the master table it would be available to me there. Thanks a lot for your help this is driving me crazy.
    I have included two attachments. The first is the FrgNozzelRefTbl and the second is a portion of the vessel input table. I have a combo box set up on the first column of the reftbl and it is populating to the vesselinput tbl. I need the rest of the info in the row to populate to the other fields on the master table.

  12. #12
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    If you insist in your way, then you need put an update statement in the after_update event of the combo box to update other information to the master table.

    the update statement may look like the following:
    Code:
    private sub combo1_after_update()
    'if combo1 is text
        currentdb.execute "update mastertable inner join referencetable on mastertable.key=referencetable.key set mastertable.field1=referencetable.field1, mastertable.field2=referencetable.field2, mastertable.field3=referencetable.field3 where referencetable.key='" & combo1 & "'"
     
    end sub

  13. #13
    b123 is offline Novice
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Sep 2010
    Posts
    24
    Weekend my problem is that I have to use the referenced data in a different calulated field. If i use the primary key method, how can I set up the calculated fields to use the correct values when a different key record is chosen in the combo box. I other words when the components is selected it has some base attributes and from those the remaining data can be acheived by calculation. This is my resaoning for having it populate, I could be wrong in my approach. If my calculation can reference the new data when a new components is selcted in the combo box then you are correct I would not need to populate to the main table. I am still new and learning so the structural approach may not always be correct. I think that either way it would require some type of coding to accomplish.

  14. #14
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    I am not clear with you logic, but I think update query can handle most cases, just modify a little bit in the statement.

  15. #15
    slave138's Avatar
    slave138 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    WI
    Posts
    233
    It would probably be most efficient to just keep the data in individual tables. Instead of linking your form directly to a table, use a query which includes both joined on the nozzel (nozzle?) number. You can still calculate fields the same way you are already - the form doesn't really care where its datasource comes from.

    See the sample DB for an example

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

Similar Threads

  1. Populating fields
    By bjr001 in forum Forms
    Replies: 4
    Last Post: 10-11-2010, 10:19 AM
  2. Replies: 3
    Last Post: 06-29-2010, 12:08 PM
  3. Populating Fields
    By deiniolj in forum Forms
    Replies: 21
    Last Post: 12-08-2009, 10:51 AM
  4. Self Populating Fields
    By kellyd in forum Access
    Replies: 0
    Last Post: 09-17-2009, 10:20 AM
  5. Populating other fields from combo box.
    By nkenney in forum Forms
    Replies: 1
    Last Post: 05-14-2009, 10:39 PM

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