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

[brian0721]

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!

 

[RichO]

In the After Update event of your combo box:

    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.

 

[brian0721]

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!

 

[Mile-O]

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)

 

[brian0721]

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)

Bingo, thanks a ton as usualy

 

[RichO]

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.

 

[RichO]

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] ?

 

[RichO]

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?

 

[RichO]

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.

 

[MsLady]

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?

 

[Stacey]

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?

 

[PierreR]

Stacey.

I converted the file to 97 format with Access 2002.

Hope it is still functional.

Pierre.

 

[Stacey]

Thank you.