Auto fill on forms

[Guest]

I have created several tables. One of which is a subcontractor demographics
table, another is a subcontractor activation table if you will. I have built
a form based on the activation table. On the form I have place an autolookup
field for the subcontractor's name, now I would like for the address, city,
state and zip fields to automatically fill when I select the subcontractor
name from the drop down. How?

 

[Pieter Wijnen]

I hope you only want to fill the forms controls for display purposes. It is
bad design to store the data in more than 1 table
Change the Combo's (ContractorID) Rowsource to

SELECT ContractorID, ContractorName, Address, City, .... FROM Contractor

In The AfterUpdate Event place

Private Sub ContractorID_AfterUpdate()
With ContractorID
Me.Address.Value = .Column(2)
Me.City.Value = .Column(3)
'...
End With
End Sub

And in the OnCurrent Of The Form

Private Sub Form_Current()
If Not Me.NewRecord Then
ContractorID_AfterUpdate
End If
End Sub

HTH

Pieter

 

[Guest]

Ok. I understand the principle. Let me ask this. I have two tables because
of the following: 1 table is my master table that stores all subcontractor
information such as name, address, certifications, resources, etc. The
second table is a table that stores data on that contractor should I ever
need to use them such as, employees involved, driving times, official numbers
etc.
Now, I have several subcontractors and it is easy enough to make the drop
down to automatically find a contractor by name, however I need to be able to
automatically fill in the associated address that would subsequently be
stored in that second "event" table.
Does this make sense? Ideally I would need to be able to create reports off
of this table as well. If I am on the wrong track here let me know.

 

[Pieter Wijnen]

The only time redundant data should be stored is if you need to keep the
point-in-time value
ie if you make a salesorder, you'd want to keep the actual price of an item
at that stage.

to report on the combined result of the information only however, you can
simply create a query joining the tables on the common key.
this common key can be the contractors name (in this case) or a system
generated number (autonumber).
There are two good reasons to use system generated (meaningless keys)
1) combining whole numbers is much faster for a computer to do than text
(the main reason)
2) If you need to change the "real" key, you only have to do it in one
place.

one of the main reasons for not storing redundant data (besides wasting
space) is that if the address changes for a contractor you'd have to update
all the related records in your "child" tables.

BTW

The approach I suggested will work in both cases, but you don't have to do
it in the current event if you store it <g>

HTH

Pieter

 

[Guest]

I realize now after I thought about it that the only information I would need
to store about that "event" in another table is information applicable only
to that "event", I can build my form based on both tables, therefore I can
indeed eliminate the redundant address fields in the second table. Correct?
That being said, if I build the form based on the non-redundant tables I will
be able to auto fill the address controls on the form by doing what you
previously said? Also, I am not exactly sure what you mean by store it <g>.
I am just beginning to learn the coding.

 

[Pieter Wijnen]

You are absolutely right
and the <grin> was just to smile since you've grasped one very important
thing about relational databases (no redundant data, please)
good luck with your application & don't hesitate to ask *dumb* questions.
Some of us actually remember to have problems with a thing or two ourselves

Pieter

 

[Guest]

Thank you, I will be trying this today.

You are absolutely right
and the <grin> was just to smile since you've grasped one very important
thing about relational databases (no redundant data, please)
good luck with your application & don't hesitate to ask *dumb* questions.
Some of us actually remember to have problems with a thing or two ourselves

Pieter