CustomerF Address ShipTo and BillTo - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

CustomerF Address ShipTo and BillTo

Peter Yates

11 months ago

In a test DB I set up 3 sets of addresses in the CustomerT, the aim being for the user to enter the address fields once (I've reduced to "Address", "Town" and "PostCode") and for the other 2 blocks ("DelAddress","BillAddress" etc) to autofill. I put two hidden check boxes on the Form, defaulted to "Yes" and named "chkAutoDelFlag" and "chkAutoBillFlag". I hoped that code in the "Address_AfterUpdat()" would work (with help from AI) but I get constant errors and CoPilot doesn't help.
Private Sub Address_AfterUpdate()
    If Me.chkAutoDelFlag = True Then
        Me.DelAddress = Me.Address
    End If
    If Me.chkAutoBillFlag = True Then
        Me.BillAddress = Me.Address
    End If
End Sub
Help? This isn't easy or straightforward. Perhaps Rick could explain the problems in a  Quick Queries?

	Lisa Snider @Reply 11 months ago
If all three addresses are the same, why three sets of addresses in CustomerT?

	Raymond Spornhauer @Reply 11 months ago
Multiple Addresses Hope this helps, -Raymond

	Jose Melean @Reply 11 months ago
Ray, do you want to update the fields (delivery and billing addresses) with the default address using the form's text boxes? Or you only want to show them in the form?

Peter Yates OP @Reply

11 months ago

Lisa,
I should have explained that the application I had in mind is a business delivering meals to the elderly, many of whom manage themselves but some have family do stuff for them. So the dellivery address is almost always the customer address (unless a relative or carer is considered the customer). Similarly the invoice need to go to someone else sometimes.
This can all be done in Queries and Reports, but I was experimenting to see if it worked better to set all this in the customer details, just like establishing their date of birth, age and any other relevant details.

	Lisa Snider @Reply 11 months ago
I see, but perhaps, instead of having all three addresses in CustomerT, set up the addresses with a one-to-many relationship and use a Junction table with the CustomerID as the foreign key in an address table. This way you won't have the same data in multiple fields of your Customer table. See Richard's video on Relationships. (https://599cd.com/blog/display-article.asp?ID=1724)

	Martin Fairbairn @Reply 11 months ago
I agree with Lisa but, if you want to create a form for a specific process, I would expect your address assignment to be the opposite way around. e.g. Me.Address = Me.DelAddress or Me.Address = Me.BillAddress. That would pull in the relevant address type from the junction table to the current form record to be used for this specific process.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View