Access Form populated from Combo Box with Table Values

[Amoredelmar]

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.

[maximus]

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.

[Amoredelmar]

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

[maximus]

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!!!!!

[Moghees]

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

[ssingh75]

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

[super_girl]

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

[rayCamrass@yahoo.com]

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

[Tracker5950]

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