Linking address tables

[Brad108]

Hi

I wonder if anybody can help with this problem.

I am setting up a basic contact list, with 3 tables - contact, address and company.

What I want to do is be able to add numerous addresses to the company table/form. Then, when I add a contact, I can go to the contact form and select an existing address from a drop down (from the address table), this will then assign this address to the contact, as well as showing this contacts name on company screen.

I have got this working as far as having a drop down of company names on the contacts screen but I can't work out how I can get the address to show on the contact screen when I select a company name from a drop down list?

I have spent ages on this, and its driving me mad, please can somebody hep?

I think i might need to sort out the relationships, but am getting very confused.

Apologies if this is straightfoward - but would really appreciate any suggestions.

Please help!

Thanks

Brad

 

[CrystalSurfer]

Brad,
It sounds like you need to sort out your DB relationships first.
You possibly want Contacts to have a Foreigh Key to Addresses (ie: have the AddressID on the Contacts table), and then have the Addresses table with the Company ID.
That would mean you can use subforms on the Company form for Addresses and Contacts with them linked to each other. So, when you clicked on an Address the Contacts would show for that Address.
To get that working you have to requery the child form within the Parent's OnCurrent event.

HTH

 

[Brad108]

Hi

Thanks for the reply.

I have CompanyID (from Company) linked to CompanyID (from Address) and then I have AddressID (from Address) linked to AddressID (from Contact).

Is this right. I am not sure which join type they should be though.

Thanks for the subform info.

How do I do it for the Contact form? i.e. so I find a contact on the Contact form, then click on a company drop down and then, from that, click on the address drop down (each address at a company has got a description e.g. Head Office etc).

Thanks so much for your help with this, its invaluable and a real life saver!

Kind Regards

Brad

 

[CrystalSurfer]

mmm..
depends what you are wanting to achieve Brad. What do you mean when you say "so I find a Contact on the Contact form"? Do you have a form where you can search for a Contact (by name maybe)? In which case you probably just want to have cascading combo boxes for Company and Address (plenty about that if you search on the forum here). That will enable you to edit and switch Contacts from one Address or Company to another.

Or are you wanting to enable users to edit/create a Contact searching by Company/Address?
You sound like you have your DB relationships setup correctly, so its just a matter of arranging the forms together.
If you're doing this to maintain Contacts within Companies then I would imagine you would have a main form for the Company. Then another form inside that (a subform) showing the Addresses for that Company. The Address form will have its 'Link Child Fields' and 'Link Master Fields' properties set to CompanyID.
Then another subform (maybe alongside the Address one) for the Contacts. This will have something like "[NameOfAddressSubform].Form![AddressID]" in the 'Link Master Fields' property and "AddressID" in the Link Master one.
That will link up the forms together with the correct IDs.

Then on the Address subform: Within its 'On Current' event put a:
Me.Parent![NameOfContactSubform].Requery
This will refresh the Contact subform when a different Address is selected.

HTH,
Surfer

 

[Brad108]

Hi

Thanks so much for the reply. This is a task given to me in my new job and am struggling somewhat. I seemed to have got those bits working ok (it is the second option I went for) but still the following problem:

on company form:

I want to view a contacts details. I have set up a button which I can click to go to the contact form, but it doesn't go to the a specific contacts records, just the first record.

on contact form:

I then want to do it the other way around, when I find (or enter) a contact, I want to click 'view company' and for it then to go to the company details or, if it is a new company, a new record screen.

Can you help with this?

Thanks so, so much...

Kind Regards

Brad

 

[Brad108]

Hi

Sorry to be a pain, but have one last problem (aside from the above one).

Once these are done - i'll be all sorted!

When I enter a contacts details, and there address into the address subform, is there a way I can do a drop down combo box for Address.Name which then brings up the address?

Most of the contacts will be assigned to a head office, so it would save time to drop down 'Head Office' and then for that companys address to fill itself it.

I have now spent hours trying to sort out these last 2 problems and am very frustrated now

So any help would be so helpful and mean so much

All the best

Brad

 

[CrystalSurfer]

I want to view a contacts details. I have set up a button which I can click to go to the contact form, but it doesn't go to the a specific contacts records, just the first record.

In your button click event are you using the ContactID in the Link criteria when opening the Contact Form?
eg:

stLinkCriteria = "[ContactID]=" & Me![ContactID]
stDocName = "frmContactDetails"
DoCmd.OpenForm stDocName, , , stLinkCriteria

When I enter a contacts details, and there address into the address subform, is there a way I can do a drop down combo box for Address.Name which then brings up the address?
Most of the contacts will be assigned to a head office, so it would save time to drop down 'Head Office' and then for that companys address to fill itself it.

It sounds like you want to be adding a field to your Address table for the name of it. eg: "Head Office" or "Halifax site", etc so you can use it elsewhere on other forms. You could even create a query and concatenate some of the address fields together into one field (not too practical on a form though)
But Im not sure what you are trying to do with a combo box for that on the Contacts form? Is this a form for entering a new contact? ie: you enter the Contact first then select the Company they belong to?

Would be easier to go with selecting (or adding) the Company first then Address, then Contact details, as Access easily does that if you have the 2 subforms together on the Company form.
Others may have something to say about the design of that since Im relatively new to Access.

 

[Brad108]

Thanks so much for the info.

Am almost there now - the link from company form to contact form works fine. However I have tried to put it in so that I can do the same from contact to company (i.e. when I am on a contacts details, I can click there company name and it goes to the company screen showing there details). The company index field on the contacts table is called 'CompanyID' and the company index field on the company table is called 'CompaniesID'.

I have tried this:

stLinkCriteria = "[CompaniesID]=" & Me![CompanyID]
stDocName = "Company"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

But it is coming up with a compile error: variable not defined.

Do you know why this isn't working?

Thanks so much for the help!

Kind Regards

 

[Brad108]

Hi

On the contacts form, i have the contact details, plus an address subform.

I can currently enter there details, then select their company from a drop down box (which i need to change so I can add to it, but don't know how to do that - when I change it from 'limit to list', the list turns into numbers?!) and then it goes onto the address subform.

At this point I want to be able to have a drop down box (Address.Name) which brings up the address information for that particular company.

These are my last problems - so your help would be really appreciated.

Thanks

Brad

 

[CrystalSurfer]

At this point I want to be able to have a drop down box (Address.Name) which brings up the address information for that particular company.

How about having a listbox (underneath the Address combo box) that has its Source as a query. This query can then concatenate your address fields together (add a new field after the AddressID, in the query with something like: [tblCompany].[Add1]+Chr$(13)+Chr(10) & [tblCompany].[Add2] AS AddressBlock
- the 13/10 codes are line feeds that make the Address fields into an Address 'block' as opposed to a long line). Make sure your list box is high enough to display all the lines.
So, when a user clicks on the Address combo box it requeries the list box with the full address.
Hope that points you in the right direction.

 

[Brad108]

Thanks for that - that should work really well.

Any idea what I am doing wrong here?:

Thanks so much for the info.

Am almost there now - the link from company form to contact form works fine. However I have tried to put it in so that I can do the same from contact to company (i.e. when I am on a contacts details, I can click there company name and it goes to the company screen showing there details). The company index field on the contacts table is called 'CompanyID' and the company index field on the company table is called 'CompaniesID'.

I have tried this:

stLinkCriteria = "[CompaniesID]=" & Me![CompanyID]
stDocName = "Company"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

But it is coming up with a compile error: variable not defined.

Do you know why this isn't working?

Thanks so much for the help!

Kind Regards

 

[Brad108]

How about having a listbox (underneath the Address combo box) that has its Source as a query. This query can then concatenate your address fields together (add a new field after the AddressID, in the query with something like: [tblCompany].[Add1]+Chr$(13)+Chr(10) & [tblCompany].[Add2] AS AddressBlock
- the 13/10 codes are line feeds that make the Address fields into an Address 'block' as opposed to a long line). Make sure your list box is high enough to display all the lines.
So, when a user clicks on the Address combo box it requeries the list box with the full address.
Hope that points you in the right direction.

Have tried this, but can't get it to work.

The problem is I need it to fill in field boxes.

This should be quite straightforward and am frustrated I can't do it

The process will be:

go to contact screen
add new contact
find company
add address details to address subform, either by typing in or by clicking a drop down for the address.name (i.e head office) and then choosing one, which then populates the rest of the address fields (storing this in address table).

Any help would be really appreciated with these last 2 probs.

Thanks!!!!

 

[Brad108]

Just found another silly problem

When I change my contact subform to data entry (so I can add a new contact), the data disappears??!?



Sorry for all the questions, but i promise once these last 3 problems have been resolved, my database should be fully functional

I have spent so many hours trying to get it to work

 

[neileg]

Data entry means an empty form with no way of seeing existing records. Turn this off. As long as you have Allow Additions and Allow Edits (and probably Allow Deletions) set to Yes, you can enter new records.

 

[Brad108]

Hi

Thanks so much for the help.

Got the 1st one sorted out.

The second one I have set to allow additions and data entry set to no, however when I try and type in it it comes up with a 'you can't assign a value to this object error'.

Any ideas of whats going wrong here?

Thanks

Brad

 

[Brad108]

Sorry, meant to say its the 1st one I still have problems with (adding data to a form).

Any ideas?