In order to do this correctly and avoid future problems you should have a
Primary Key Field in each table. Usually, but not always, in a Customer
Table you will have CustomerID as the Primary Key Field. It must be indexed
with no duplicates allowed. You can set these properties in Table Design
View. Right Click on the box to the left of the CustomerID Field and select
Primary Key.
Next, add a CustomerID Field (or whatever you name it) to the Visit Log
Table. Set it's Data Type to Number and it's Format as Long Integer.
Now you will need to create a relationship. Close your tables. On the Menu
Bar click Tools and then Relationships. If you do not already see your
tables in the window that appears, right click on the window and select Show
Table. Select one of the tables and click Add. Do the same with the other
table. Next, click on CustomerID in the Customer Table and drag it to
CustomerID in the Visit Log Table. A dialog box will appear. Put a check in
the Enforce Relational Integrity Box and then in the Cascade Update Related
Fields box. Then click Create. You should see a line that conects the two
tables. Next click the Save button on the menu. After that you can close
that window.
Now your Tables are related and "connected". Assuming that Company Name and
Plant Location are in the same table, one of the Forms you create will have
the them when you create the form.
There are several ways to add these fields to your other form. You can base
your form on a query that has all of the fields in the table and the Company
Name and Plant Location from the other table too. That is not the most
efficient way to do it, but it may be the easiest for you.
I often do it this way: I create my forms. Then I add an Unbound combo
box to the form that does not have the fields I want. In the Combo Box's
Properties sheet , the RowSource query will have CustomerID in the first
column. The next two columns will have CompanyName and PlantLocation. I am
assuming that CompanyName and PlantLocation are in the same row on the table.
On the Format tab of the Properties sheet of the combo box I will enter 3
for Column Count. Then I will set the Column Widths to 0";1.5";1.5". I will
set the List Width to 3".
Next I add an Unbound text boxes to my for PlantLocation.
In Design View I right click on the Combo Box and select Properties. I then
click the Event tab, and double-click to the far right where I see After
Update. Then I select Code Builder. The VBA Editor opens and my cursor is
located where I see
Private Sub mycomboboxname_AfterUpdate ()
End Sub
Between these two line I add this:
Me.PlantLocation = Me.mycomboboxname.Column(2)
So I end up with this:
Private Sub mycomboboxname_AfterUpdate ()
Me.PlantLocation = Me.mycomboboxname.Column(2)
End Sub
Next I go to the box at the top that says mycomboboxname and to the right
click on the arrow and select Form from the drop down list.
In the box to the right of that I select Current. I then see:
Private Sub Form_Current()
End Sub
Between these line I add the following:
Me.mycomboboxname.Requery
Me.PlantLocation = Me.mycomboboxname.Column(2)
When I am finished I see this:
Private Sub Form_Current()
Me.mycomboboxname.Requery
Me.PlantLocation = Me.mycomboboxname.Column(2)
End Sub
This way whenever your form opens or you move to another record your
information stays current on your Form. I then close the VBA Editor Window.
Now when I open my form, I can select the Company Name I need from the Combo
Box and the PlantLocation will automatically appear in my unbound text box.
Doing it this way avoids duplicate information which would otherwise bloat
your database and slow it down.
If you have any questions you can post them here or email me at pwood57 at
gmail dot com.
Hunter57
http://churchmanagementsoftware.googlepages.com