Autofill Multiple Fields in a Form from One Record in a Table

[dunamisqueen]

Hi There:

I'm relatively new to access. I have a form for Sales Transactions in which I have a combo box to select a producer's company name via a query. I want to be able to have an event automatically fill in the producer's contact name and information (i.e. mailingaddress, telephone, etc.) from the ProducerContactInformation table. The goal with this Sales Transactions form is to be able to create a report that is actually a sales agreement that gets sent to the producer. I know how to set up creating the report, I just need to know how to autofill the contact information fields. How can I do this relatively easily and quickly?

I really appreciate any help. I'm a bit stumped. Thanks a bunch!

[ajetrumpet]

use dlookup()

it's slow, but you probably don't have many fields to fill. use this syntax:

private sub combox_afterupdate

   me.address = dlookup("addressfield", "table", _
      "[criteriafield] = '" & me.companyname & "'")

end sub

write a lookup for every field that's needed.

[dunamisqueen]

I've tried the dlookup method for the group of fields that I need to autofill, but it won't work. It fills the first field only and nothing happens with the rest of them. Have I input the dlookup incorrectly or have I created a part of my database incorrectly. My dlookup that didn't work correctly was all listed under the one combox_afterupdate.

Any further assistance would be greatly appreciated. Thanks.

[ajetrumpet]

if you did do something incorrectly in the database it's not worth fixing unless it's big. But you won't know unless you've studied proper setup techniques.

It's impossible to know what the problem is here, but it is most likely a lack of proper criteria to input because that particular piece of data is not in one of the tables you want to pull from (hence no criteria to use in the lookup function).

but if you upload it I'm sure I or someone else can get it back to you in a matter of minutes. If you do, give the:

form
cbox control name
fields to fill
what to fill them with

[dunamisqueen]

if you did do something incorrectly in the database it's not worth fixing unless it's big. But you won't know unless you've studied proper setup techniques.

It's impossible to know what the problem is here, but it is most likely a lack of proper criteria to input because that particular piece of data is not in one of the tables you want to pull from (hence no criteria to use in the lookup function).

but if you upload it I'm sure I or someone else can get it back to you in a matter of minutes. If you do, give the:

form
cbox control name
fields to fill
what to fill them with

Thanks so much for all your help! I've found the issue. Like you said, some of the criteria wasn't quite right. Got it corrected and it is all working now! Thank you again, so much!

[NateJames]

Thanks for your help, I was able to read this post and solve a similiar problem