Autofill multiple tables using an event procedure

[mstan]

Hello,

I'm very new to Access, but I'm trying to set up a contact database for my employer. Just for some background:
It's got multiple tables, including a Master table, STI table, TB table, HIVAIDS table, etc. The Master table has a one-to-many relationship with the other tables, and ID number being the primary and foreign key.
I'm trying to set up a data entry form that could auto fill multiple tables by using a command button or event procedure. Problem is, I'm very new, and really have no idea what the best way to do this is. So I have a Yes/No field in the form, and when I click it, I'd like it to autofill email address and mailing address in another table (ie HIVAIDS), with the ID. Is this even possible? Any help would be appreciated!

[JoeM]

You shouldn't really need to autofill fields in other tables because in well-designed relational database, values should only be stored in one table. You typically link your tables together to get values of other fields to present in queries, forms, reports.
Do you still think you need to do this? If so, can you explain a little more about why?
If we see where you are trying to go with this, we may be able to provide better alternatives if it looks like you are headed down the wrong path.

[kagoodwin13]

Definitely possible. You'll need a little bit of VBA to make this happen though. Here is a good tutorial: http://www.techrepublic.com/blog/mso...selection/1330

1. You need to do is set up a query that grabs all of the data you need for each ID number. Use the Query Wizard to set that up. So for ID 1, you'll have info from the STI table, TB table, HIV table, all on one row.
2. Create a combo-box on your form that selects the ID. The combo-box should be based on the query from Step 1. Although the combo-box will only show ID after everything is selected, you'll need the entire query for later steps.
3. Make an event for the Yes/No field After Update to trigger the Email and Mailing Address text boxes to "auto-fill" based on the ID. You will need the Code Builder. The code you need will be similar to the tutorial.

Assuming your query looks like this:

ID	Name	Mailing Address	Email	STI	TB	HIV
1	Bob	1 Main St, Anywhere, USA 12345	bob@comcast.net	x	x	x
2	Joe	2 Main St, Anywhere, USA 12345	joe@juno.com	x	x	x
3	Tom	3 Main St, Anywhere, USA	tom@aol.com	x	x	x

Combo-box ID should connect to the Contact Master table. Call it "comboID" on your form.

Yes/No After Update code should look something like this:

Private Sub comboID_AfterUpdate()
Me.textMailing_Address = Me.comboID.Column(3)
Me.txtEmail_Address = Me.comboID.Column(4)
End Sub

Hopefully that works. If not, the Mailing Address and Email Address text boxes may need to use DLookup instead.

[mstan]

You shouldn't really need to autofill fields in other tables because in well-designed relational database, values should only be stored in one table. You typically link your tables together to get values of other fields to present in queries, forms, reports.
Do you still think you need to do this? If so, can you explain a little more about why?
If we see where you are trying to go with this, we may be able to provide better alternatives if it looks like you are headed down the wrong path.

I'm designing the database for my employer, (I'm only here temporarily) and they want to be able to grab the emails from the tables (I tried to direct them away from duplicating, but no dice). With the emails on the tables of interest, they can use the select/union select query to pull emails without duplicates (because some entries are on the HIVAIDS and STI and HepC tables) and it has to be a simple enough query, because some people needing the database have no experience with Access. If there's an equally simple query that could perform the same function, I'd be more than happy to try it.

[mstan]

Thanks for your help - I'm a little confused as to how to create that query though (just a simple SELECT ID, Email, Address FROM Master?)

[kagoodwin13]

Yes, that simple. Access has a Query Wizard on the "Create" tab in the ribbon, so if you're not familiar with SQL, it can walk you through the steps.

[kagoodwin13]

they want to be able to grab the emails from the tables (I tried to direct them away from duplicating, but no dice)

AutoFill shouldn't be used to duplicate data in any way. It should serve as a "convenience" to make the screen look nice. For instance, if the Yes/No box is "Would you like this person's contact information?" and the user selects Yes, then auto-fill could be used to pull info to the form so it can be copy/pasted to another program.

Auto-fill is often used to fill in customer info from a customer number. For example, if you give a sales database a customer number (which is most likely the primary key), auto-fill spits back the customer's name, contact info, buying history, etc. so the salesperson can look at that info while talking to the customer. The salesperson isn't going to remember all that on their own, or build their own queries, that's why the database exists: to do the hard work for them.

If you're using Auto-Fill to submit data from one form to another, you'd be better off connecting your tables with a reference and using queries to pull info.