Results 1 to 9 of 9
  1. #1
    Amoredelmar is offline Novice
    Windows 7 Access 2007
    Join Date
    Mar 2010
    Posts
    4

    Access Form populated from Combo Box with Table Values

    Okay, this seems easy but I have spent hours of days trying to figure it out.

    What I have.

    1. A Table with a Customer Database (name, phone, email, address, etc...)
    2. A Form with the same fields

    What I intend to do

    Open Form
    On Form open fields are blank (unpopulated)
    Then I have the option to:
    1. Select a Customer from the Combo Box which would populate all fields
    or


    2. Input a new Customer which would then populate the table.

    I can not get the combo box to populate the form fields from the table data. The box just selects a random customer. The information inputed in the form updates fine in the table, but I would like a button for "Save and New" that clears the form and prepares for a new entry or new search from the combo box.

    Using Access 07. The table fields and form fields are the same.

  2. #2
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931

    populating Form using combobox

    Find mdb attached. This contains the solution to your minimum stock level. I have made a re-order level in inventory table which is our minimum stock level. When a transaction makes the stock equal to or less than reorder level it is taged



    What I have here us a simple mdb with a single table Employee and a Form1. Form1 has Two Combo boxes meant to select employee name.

    In technique 1 we use the following code to populate our Text boxes:
    =[Combo11].[column](1)
    =[Combo11].[column](2)
    =[Combo11].[column](3)
    =[Combo11].[column](4)
    Remember in the combo box property the no.of.columns should be equal to the fields that you want populate on the form.




    the second Techique is Dlookup and the code as follows:

    =IIf(IsNull(DLookUp("[Employee_id]","Employee","[Employee_id]=" & [Forms]![Form1]![Combo22])),"",DLookUp("[Employee_id]","Employee","[Employee_id]=" & [Forms]![Form1]![Combo22]))

    =IIf(IsNull(DLookUp("[Employee_name]","Employee","[Employee_id]=" & [Forms]![Form1]![Combo22])),"",DLookUp("[Employee_name]","Employee","[Employee_id]=" & [Forms]![Form1]![Combo22]))

    =IIf(IsNull(DLookUp("[Employee_Designation]","Employee","[Employee_id]=" & [Forms]![Form1]![Combo22])),"",DLookUp("[Employee_Designation]","Employee","[Employee_id]=" & [Forms]![Form1]![Combo22]))

    =IIf(IsNull(DLookUp("[Employee_Phone_Number]","Employee","[Employee_id]=" & [Forms]![Form1]![Combo22])),"",DLookUp("[Employee_Phone_Number]","Employee","[Employee_id]=" & [Forms]![Form1]![Combo22]))

    Remember default value of the combobox is set to 0 to prevent Error# being displayed.

  3. #3
    Amoredelmar is offline Novice
    Windows 7 Access 2007
    Join Date
    Mar 2010
    Posts
    4
    Excellent. Is there a way to be able to edit from that same form, it looks like the solution you provided works but does not allow for edits/updates/new entries with that configuration. Thanks

  4. #4
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    You can populate forms now I am attaching the same db with the same two combo boxes. Now the Forms record source is Employee_2 a table with similar fields like Table employee. Codes have been attached to the AfterUpdate Event of the Combo Boxes. You will see that the Fields of the Form is Updated by using the either of them. More over you can also use the data in the unbound text boxes to populate the employee_2 table. select use combo box to load data onto the unbound text boxes then select option and click on the button.

    Anything can be done if you know how!!!!!

  5. #5
    Moghees is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Apr 2011
    Posts
    8
    Hi

    I like what you have done it has been very beneficial Thank you very much. I have another problem you might be able to help me with

    I am creating a web database and trying to populate information from a combo box when a certain department is selected, i.e when "IT" is selected a list of the people working for "IT" will be available.

    I have managed to do this but when I incorporate it into a subform it stops working. I am using access 2010.

    the way i got it to work was to create 2 tables. Table "Depart" with a field called with department and the 2nd table called "People" with a lookup\relations to pick the departments up and then a second field with peoples names.

    Once the tables were created i made a query called "CP" selected both the tables (person from table People & department from table depart) under the the department i put in the following [Forms]![Choose].[Department] and unticked the box.

    the query runs fine then i created the a form called "Choose" with 2 combo boxes the first one was linked to the depart table and this picks the different departments up the second combo box has the query linked "CP"


    this works fine and everything works until i move the form into a subform part of a multitabbed form which is available in web database

    can you shed some light on what i might be doing wrong and is this how it should be done or have i complicated it, is they any way you can help


    Any help will be appriciated

  6. #6
    ssingh75 is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2011
    Posts
    1
    I have a table name Region in access database as .accdb format not .mdb
    and want to populate the unique Region into Combobox of access form using VBA..
    Please help its urgent

  7. #7
    super_girl is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    2
    Hi maximus,

    I've found your reply to this thread very useful. I'd like to take it one step further - I have the following:

    One table of client data
    One table of practitioner data
    One table of Visit Info data

    I have a form for the client data that includes a sub-form for visit info. There is also a combo box which allows you to select the practitioner. When you add an entry into the sub-form, the Client ID carries into the sub-form properly, but I haven't figured out how to carry over the Practitioner ID. Using =[Forms]![Client Details]![Combo842], the proper value is displayed in the form, but not actually saved to the table.

    I'm using Microsoft Access 2010. Thanks for your help.

    SG

  8. #8
    rayCamrass@yahoo.com is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    1

    Thanks for that. It enabled me to finish my Accces2010 form.Thanks!... not obvious~!

    Quote Originally Posted by maximus View Post
    Find mdb attached. This contains the solution to your minimum stock level. I have made a re-order level in inventory table which is our minimum stock level. When a transaction makes the stock equal to or less than reorder level it is taged



    What I have here us a simple mdb with a single table Employee and a Form1. Form1 has Two Combo boxes meant to select employee name.

    In technique 1 we use the following code to populate our Text boxes:
    =[Combo11].[column](1)
    =[Combo11].[column](2)
    =[Combo11].[column](3)
    =[Combo11].[column](4)
    Remember in the combo box property the no.of.columns should be equal to the fields that you want populate on the form.




    the second Techique is Dlookup and the code as follows:

    =IIf(IsNull(DLookUp("[Employee_id]","Employee","[Employee_id]=" & [Forms]![Form1]![Combo22])),"",DLookUp("[Employee_id]","Employee","[Employee_id]=" & [Forms]![Form1]![Combo22]))

    =IIf(IsNull(DLookUp("[Employee_name]","Employee","[Employee_id]=" & [Forms]![Form1]![Combo22])),"",DLookUp("[Employee_name]","Employee","[Employee_id]=" & [Forms]![Form1]![Combo22]))

    =IIf(IsNull(DLookUp("[Employee_Designation]","Employee","[Employee_id]=" & [Forms]![Form1]![Combo22])),"",DLookUp("[Employee_Designation]","Employee","[Employee_id]=" & [Forms]![Form1]![Combo22]))

    =IIf(IsNull(DLookUp("[Employee_Phone_Number]","Employee","[Employee_id]=" & [Forms]![Form1]![Combo22])),"",DLookUp("[Employee_Phone_Number]","Employee","[Employee_id]=" & [Forms]![Form1]![Combo22]))

    Remember default value of the combobox is set to 0 to prevent Error# being displayed.

    Thanks very much for that it enabled me to put up my Access 2010 form

  9. #9
    Tracker5950 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2012
    Posts
    1

    Populate mutiple fields with table of static data

    Quote Originally Posted by maximus View Post
    Find mdb attached. This contains the solution to your minimum stock level. I have made a re-order level in inventory table which is our minimum stock level. When a transaction makes the stock equal to or less than reorder level it is tagedWhat I have here us a simple mdb with a single table Employee and a Form1. Form1 has Two Combo boxes meant to select employee name.In technique 1 we use the following code to populate our Text boxes:=[Combo11].[column](1)=[Combo11].[column](2)=[Combo11].[column](3)=[Combo11].[column](4)Remember in the combo box property the no.of.columns should be equal to the fields that you want populate on the form.the second Techique is Dlookup and the code as follows:=IIf(IsNull(DLookUp("[Employee_id]","Employee","[Employee_id]=" & [Forms]![Form1]![Combo22])),"",DLookUp("[Employee_id]","Employee","[Employee_id]=" & [Forms]![Form1]![Combo22]))=IIf(IsNull(DLookUp("[Employee_name]","Employee","[Employee_id]=" & [Forms]![Form1]![Combo22])),"",DLookUp("[Employee_name]","Employee","[Employee_id]=" & [Forms]![Form1]![Combo22]))=IIf(IsNull(DLookUp("[Employee_Designation]","Employee","[Employee_id]=" & [Forms]![Form1]![Combo22])),"",DLookUp("[Employee_Designation]","Employee","[Employee_id]=" & [Forms]![Form1]![Combo22]))=IIf(IsNull(DLookUp("[Employee_Phone_Number]","Employee","[Employee_id]=" & [Forms]![Form1]![Combo22])),"",DLookUp("[Employee_Phone_Number]","Employee","[Employee_id]=" & [Forms]![Form1]![Combo22]))Remember default value of the combobox is set to 0 to prevent Error# being displayed.
    From looking at your forms in design view, I learned the form should not be bound to a table. The control source of combo box field I bound to a query based on a table of static values (Standard Scores, Percentiles, Stanies, Scaled Scores, & T-Scores)SELECT [Scaled Score T-Score Equivalents].[Standard Score], [Scaled Score T-Score Equivalents].Percentile, [Scaled Score T-Score Equivalents].Stanine, [Scaled Score T-Score Equivalents].[Scaled Score], [Scaled Score T-Score Equivalents].[T-Score] FROM [Scaled Score T-Score Equivalents] ORDER BY [Scaled Score T-Score Equivalents].[Standard Score]; ). Otherwise, whenever someone clicked the drop-down on a Standard Score, it revealed the other four scores in the form fields, but was treating it as a new record. Thanks Tracker5950

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

Similar Threads

  1. Replies: 1
    Last Post: 01-21-2010, 02:36 AM
  2. Auto Populated Field not Saving to Table
    By EstesExpress in forum Forms
    Replies: 4
    Last Post: 10-09-2009, 03:33 AM
  3. Replies: 1
    Last Post: 08-26-2009, 10:45 AM
  4. Access 2000 Auto Populated Fields
    By Cylena in forum Access
    Replies: 1
    Last Post: 05-18-2009, 07:50 AM
  5. CallTicketID populated to Order Form
    By EisBlade in forum Forms
    Replies: 0
    Last Post: 04-03-2006, 10:50 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