Make my combo auto fill text fields, I searched but...

B

brian0721

Registered User.
Local time
Today, 16:21
Joined
Dec 5, 2001
Messages
103
I've searched but I cant seem to find the right code I need. I have a table that stores a persons name, fax, and phone number. On my form I have a combo box that has the person's name. When I pick the person, I want the phone and fax text fields to automatically fill in.

I've looked in the search, but no luck.

Please help!

Thanks a ton!
 
In the After Update event of your combo box:

Code: 
    Me.Filter = "Name = '" & comboName & "'"
    Me.FilterOn = True
    Me.Refresh

Make sure the control source for the combo box is blank and either name your combo box "comboName" to match the code or change the code to match the name of your combo box. Also make sure the name of your combo box is not the same as the "Name" field.
 
I'm confused. What does "Name" represent. And how does the code know which text box is for fax number and which is for phone?

Thanks alot!
 
RichO's solution was based on the recordset in the combo filtering the recordset of the form. I don't think this is what you want.

Make a query that selects the fields you want from the table. Set this query as the combobox's RowSource. Set the ColumnWidths to 0 except the field with the person's name (I like to concatenate the person's name in the query so that my combo only shows one column). Set the ColumnCount to reflect the number of fields in the query.

In the textboxes that you want to display the other data, set their ControlSource to be, for example: =[MyCombo].[Column](1)
 
Mile-O-Phile said:
Set the ColumnWidths to 0 except the field with the person's name...In the textboxes that you want to display the other data, set their ControlSource to be, for example: =[MyCombo].[Column](1)
Click to expand...

Bingo, thanks a ton as usualy :)
 
Mile-0,

Is the recordset filtering method the best way to go when the form contains more than just a few fields? This is the way I've always done it, but the forms I use with a combo box to select a record all have at least 8 fields.
 
The best way to go is to use the power of the relational database engine rather than code. Simply base your form on a query that joins the two tables. That way all the columns you need will be in the form's recordsource so they can be bound directly to form controls. If you don't want to allow updates from this form, you should set their Locked properties to Locked = Yes.
 
So what you are saying is to design a query as the record source for the form when using a combo box to select a record, and set the query field criteria to [Forms]![MyForm]![MyComboBox] ?
 
No. You don't need criteria to make this work. You just join the two tables on the linking key field.

Select O.OrderID, C.CustName, O.OrderTotal
From tblCustomer as C Inner Join tblOrder as O On C.CustID = O.CustID;

As soon as you select an order id from the combo on your form, the custname and orderTotal autopopulate.
 
Pat,

I just used this method for a particular form and it works fine for filling in the fields with the correct record data, but in this case it doesn't appear that you can edit any of the fields. Neither the form nor the fields are locked for editing.

Is this the way it's supposed to be, and if so, is filtering the recordset (mentioned above) the best way to go when you need to be able to make changes to the records/fields?
 
It is not the join that is making the query not updatable. Does the query use any aggregate functions? Is the table linked? If so it must have a primary key defined. There are other reasons. Search for updatable query in help or here for more info. If you still can't figure it out, post your query and we'll have a go at it.
 
Pat,

I am lost on getting the combo box to autofill the rest of the fields on the form. I put together a simple Table/Form/Query and attached it. There is just one table here with a few records. I understand that you can use the combo column method described above but when you have a lot of fields in the table, then what?

I've always used the recordset filtering method but after this thread, I'm interested in learning how to do it with a query. I see questions about how to do this posted almost daily here. It would be worthwhile to put a sticky about this topic at the top of the forms section here.

Thanks for your help.
 

Attachments

Rich O, :)
did you figure out how to do this. If so can you tell me.
I have the same problem, only that i am typing into a textbox (as opposed to picking from a combo list). when i type a requestnumber in a text box, i want it to populate the rest of the fields, 30 fields or so.
How do i achieve this?
 
Here's a sample db that shows three ways to populate form fields from related tables:

RecordSource of Form - BEST
Copy fields from Combo's RowSource - GOOD
DLookup() - BAD
 

Attachments

Hi,
I try to search the forum before I post a question. A lot of times, I find a thread (like this one) that answers my question. However, when you attach the sample databases for people to look at for ideas, I can never open any of them. I am on Access 97. I get a message that says the format is unrecognizable. What version of Access are the sample databases on?
 
They are usually in A2K format. This particular example is nice (I posted it:) ) but not really necessary. Just make a query that joins the main table to the lookup table. Select the fields you want from both tables and save the query. Use that query as the RecordSource for your form rather than a table.
 
Stacey.

I converted the file to 97 format with Access 2002.

Hope it is still functional.

Pierre.
 

Attachments

You must log in or register to reply here.

Similar threads

J
Auto-Filling 2 Form Fields
2
Replies
32
Views
726
Gasman
Gasman
B
Combo Box Search Issue
2
Replies
34
Views
1,044
MajP
MajP
J
Can't fill in a subform
Replies
13
Views
441
Jen-Jen
J
M
Open Unbound form and filter subform
Replies
7
Views
319
Gasman
Gasman
J
Form Not Showing All Records From Table
Replies
4
Views
332
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom