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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|