Automatically filling fields with 'standing' data

[JMGreen]

I think this should be easy but cannot get my head round it.
I have a database that includes a table of clients and a table of contacts.
Each contact is linked to one client.
When I input a new contact (John Smith say), into the contact table and
select from my pull down menu which client he is linked with (ABC Ltd, say),
from the client table, I want the address and contact details of that client
to automatically fill, in the John Smith contact form.

The relevant information resides in the Client table

I cannot seem to be able to make this link

 

[Fred]

I don't know the easiest way to answer your question, if taken literally.

But, unless there is some unusual and unknown reason for doing otherwise,
what you are trying to do violates database design 101 which would call for
just accessing the client data from the client table (using the link)
whenever needed, not loading the client data into the contact table.

Sincerely,

Fred

 

[Allen Browne]

So you have tables like this:
- The Client table holds one address for each company.
- The Contact table holds one address for each contact.
- The Contact table has a ClientID foreign key field, so any conctact can be
associated with just one company.

The Contact may need to have an address that's different than their company
address, but you want it to default to the same company whenever you set the
ClientID field for the contact.

If that's the story, use the AfterUpdate event procedure of the ClientID
combo box on the contacts form, to assign the address. This kind of thing:

Private Sub ClientID_AfterUpdate()
Dim rs As DAO.Recordset
Dim strSql As String
If Not IsNull(Me.ClientID) Then
strSql = "SELECT Address, City, State, Zip FROM tblClient WHERE
ClientID = " & Me.ClientID & ";"
Set rs = dbEngine(0)(0).OpenRecordset(strSql)
If rs.RecordCount > 0 Then
Me.Address = rs!Address
Me.City = rs!City
Me.State = rs!State
Me.Zip = rs!Zip
End if
rs.Close
Set rs = Nothing
End If
End Sub

 

[TravelingHT]

If you use a combobox based on a query you can just reference the colums in
the combobox as the record source for the fileds you want automatically
filed. So make your query for the combobox contain all the info you will need
to populate all the fields you want.

So the macro/code would look like this: =[cbCustomer].Column(1)

Where the name of the combobox is "cbCustomer"

Remember with comboboxes the colums start from 0. I got this from "Microsoft
Access 2003 Bible" its on page 523. (I like this book)

Please dont kill me if this is wrong, this is my first post trying to help
someone instead of asking for help.


Yours truly,

Traveling Tech.

 

[Allen Browne]

Your post is correct, and useful. Hopefully you will continue to offer help.

The approach you suggest is very useful for lookup lists where you need some
additional default info from other columns. It may be less appropriate if
you have thousands of customers and lots of columns to load (address1,
address2, city, state, zip, country, ...) as loading all this data into the
combo could make the form slow to load (i.e. it could take several seconds
to open.)

But the technique is a useful one for small lists, or where you need lots of
info available. (I will confess to having up to 15 columns in a combo on
occasion.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

If you use a combobox based on a query you can just reference the colums
in
the combobox as the record source for the fileds you want automatically
filed. So make your query for the combobox contain all the info you will
need
to populate all the fields you want.

So the macro/code would look like this:
=[cbCustomer].Column(1)

Where the name of the combobox is "cbCustomer"

Remember with comboboxes the colums start from 0. I got this from
"Microsoft
Access 2003 Bible" its on page 523. (I like this book)

Please dont kill me if this is wrong, this is my first post trying to help
someone instead of asking for help.


Yours truly,

Traveling Tech.

So you have tables like this:
- The Client table holds one address for each company.
- The Contact table holds one address for each contact.
- The Contact table has a ClientID foreign key field, so any conctact can
be
associated with just one company.

The Contact may need to have an address that's different than their
company
address, but you want it to default to the same company whenever you set
the
ClientID field for the contact.

If that's the story, use the AfterUpdate event procedure of the ClientID
combo box on the contacts form, to assign the address. This kind of
thing:

Private Sub ClientID_AfterUpdate()
Dim rs As DAO.Recordset
Dim strSql As String
If Not IsNull(Me.ClientID) Then
strSql = "SELECT Address, City, State, Zip FROM tblClient WHERE
ClientID = " & Me.ClientID & ";"
Set rs = dbEngine(0)(0).OpenRecordset(strSql)
If rs.RecordCount > 0 Then
Me.Address = rs!Address
Me.City = rs!City
Me.State = rs!State
Me.Zip = rs!Zip
End if
rs.Close
Set rs = Nothing
End If
End Sub