Form Sychronisation help

[inkman]

Guys I have spent two days trying to solve this forms problem, and now have no hair left.
I have a "customer" form which accesses the customer table, I also have an address table (having recently split all addresses into this table).The Tables have a one to many relationship (ie several addresses per customer). I have tried sub forms, macros and command buttons to achieve the following (with spectacular lack of success).
I want to be able to show the billing address (identified in the address table) on the customer form, be able to add new addresses (which is why I tried to launch the address form)for that customer/current record, and when a new customer is created be able to complete the address fields on the address table (with the new customerID already filled in).
I am going round in circles any help really appreciated.

Thanks

 

[ClydeData]

Assuming 2 tables

tblCustomer
tblAddress with PK= AddressID (autonumber)


Add the field AddressID to tblCustomer (as long integer), have this field as a combo box pulling the data from tblAddress.

The combo box sql should have 2 fields AddressID and Address, with column count=2, columnwith 0;4 , bound column=1

Add the combo box to your customer form naming it AddressID


And have the subform sFrmAddress with Link Child and Link Master properties =AddressID


Hope this helps

Jimmy

 

[MajP]

Unless I am misreading this, Clyde's response is backward. He describes an address with multiple people associated with it. You want simply multiple addresses per person.

tblPeople
personID
other people fields

tblAdresses
addressID
other address fields
peopleID_fk (foriegn key to people table)


Make a main form bound to a query based on the people table. Make a subform bound to the address table data.

linkMaster: peopleID
linkChild: peopleID_fk

 

[inkman]

Hi Guys thanks so much for the help. majP you are correct (although i just transposed Clydes response). I have just got it all working. which is a massive relief. I really appreciate the help. thx again.
ian

 

[ClydeData]

I am reading it that each customer has multiple address's IE these may be delivery addresses or whatever

But ony one of these is the billing address. therefore addressID in table customers does this

See upload

http://www.box.net/shared/u3ltj1vsuc

Hope this explains my thoughts


Jimmy

 

[MajP]

Add the field AddressID to tblCustomer (as long integer)

No way in that design can you get multiple addresses for the same person. You can only get multiple people with the same address. You need to add a foriengn person ID key to table addresses.

 

[inkman]

Jimmy, thx for the download. I had the customerID field already in the address table (as per MajP).
You are spot on in what I am trying to achieve. The way I have determined the billing address is put a yes/no box on the subform (ie those addresses with 'yes' are the billing addresses). I think your drop down box method is better. I am just about to do the same thing with the users names (ie a users table). I wish I had built the database with these tables in the first place , it has been a huge job splitting all the data up the tables (i mention this in case someone else is about to embark on a new database).Thx again for the help it has been fantastic.
Ian

 

[inkman]

Jimmy, MajP, I have been sleeping on the above solution, and just wondered is it possible to achieve the above but with a command button on the main form activating a pop up "address form" (with all the same capabilities of the embedded sub form). I tried for ages to get that to work but failed?

 

[ClydeData]

Yes in the coomand button wizard you should get the code for this


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "NameOfYourAddressForm"

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

However rather than using a pop up if you are use a pge control then have the billing address as a sub form on one of the pages

Jimmy

 

[inkman]

Thx Jimmy, what you have suggested is exactly what I ended up doing.I was just interested (as i couldn't get it to work). if it was possible (for future reference).I do have one question. I saw from your downloaded file (that you knocked up in a couple of mins and it took me days), that you have the customerid and Address id in both customer and address tables. I have the Customerid and addressid in the address table but not the addressid in the customer table. Is there a problem with the way i have it (is it bad practice)?

 

[MajP]

No, Clyde's design is not normalized nor does it make sense. Sorry. You are correct in that you should only have a custmerID (foriegn key) in the address table. And you should only have a field in the table to notate the type of address it is (billing, primary, secondary etc.)

popping open the form as Clyde suggests will allow you to edit the current address, but will not create a new record for that customer.

 

[inkman]

Thanks MajP, that is exactly the problem I was having (not being able to get a new record).Now I know it can't be done.
BTW I am now making a tweak to my Purchase order forms. I would like to fill my PO form(and table)from my customer order form. A customer places an order which is processed. we then place an order on our supplier to deliver(drop ship). so we are copying the customer order into our PO Form. Is it possible to hit a command button and fill the PO form from the customer order form (there is no relationship between the relevant tables/forms) . I just thought should this question be on another thread?

 

[MajP]

It can not be done, but that specific solution is designed to allow you to edit a specific record. So you could have a "edit" button as described by Clyde and a "add New" button to open a pop up form to add a new address for the given person. Something like

DoCmd.OpenForm "yourFrmName", , , , acFormAdd
forms("yourFrmName").customerID = me.customerID



A subform is provides both of these capabilities with one interface.

Yes you can create a PO record from a customer record. There are probably many ways to do this. Most likely an insert query will copy information from one table into another. I would post a new thread and describe your table structure. My guess is that you have some normalization issues because clearly there is a relationship between customers and purchase orders, but it looks like you are copying data.

 

[ClydeData]

My reading of you post was that each customer had a single billing address. If however in you are billing to multiple addresses for the same customer then a billing Y/N field in address would cover this


For multiple billing addresses in your purchase orders you should have something like

Purchase order Table
tblPurchaseOrder
PurchaseOrderID (PK)
customerID
addressID
Date
etc
etc




Purchase Order Items Table
tblPOitems
PurchaseOrderID
Qty
Cost
etc
etc

 

[inkman]

Apologies for the late response (we are in opposite time zones).Jimmy you understood the problem correctly there is only one billing address, but multiple ship addresses. The solution that you gave me for the addresses works perfectly.On the customer form I can enter new customer addresses, mark them (with yes/no box) as billing address and only display the billing address on the customer page (which is what I need to do).I can then go to the order details and from a combo box select any of the addresses for that customer,I couldn't happier. I am just getting ready to post my next problem.I have never used a forum before but you have all been so helpful I will be a regular.

Thanks again for your help and advice