A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Automatically filling fields with 'standing' data



 
 
Thread Tools Display Modes
  #1  
Old November 19th, 2008, 10:49 AM posted to microsoft.public.access.tablesdbdesign
JMGreen
external usenet poster
 
Posts: 15
Default Automatically filling fields with 'standing' data

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
  #2  
Old November 19th, 2008, 02:04 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Automatically filling fields with 'standing' data

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

  #3  
Old November 19th, 2008, 02:17 PM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default Automatically filling fields with 'standing' data

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"JMGreen" wrote in message
...
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


  #4  
Old November 19th, 2008, 07:53 PM posted to microsoft.public.access.tablesdbdesign
TravelingHT
external usenet poster
 
Posts: 35
Default Automatically filling fields with 'standing' data

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" wrote:

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"JMGreen" wrote in message
...
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



  #5  
Old November 20th, 2008, 03:37 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default Automatically filling fields with 'standing' data

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"TravelingHT" (stopthespamer) wrote in message
...
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" wrote:

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

"JMGreen" wrote in message
...
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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:32 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.