What is the best way to retrieve indirectly related data in a subform

[MuskokaMad]

Thanks to all of you for your ongoing assistance

Here is what I want to do.

I have a fairly standard order management hierarchy.

Tables:

tbl_Customers: Primary Key= ID

tbl_Orders: Primary Key = ID
Foreign Keys: Customer_ID

tbl_DeliveryDetails: Primary Key = ID
Foreign Keys: Order_ID

There is also a tbl_OrderDetails but it is irrelevant to my question.

I have a form frm_NewOrderEntry with tab controls that is bound to the Order Table.

It has a subform sfrm_DeliveryDetails.

I would like to put in 2 buttons. The first puts the Primary Address information from the Customer Table into the relevant fields. The second the Secondary address information.

What is the right way to do this?

I have used DLookup in other parts of the database to populate fields but the tables are directly related.

Does this make a difference or is there a better way?

 

[vbaInet]

You didn't mention which one of them is not related? Your question isn't clear.

A DLookup() looks up data in domain, regardless of relationship. Use the third argument of the DLookup() if you've not already done so.

 

[MuskokaMad]

I was using an OnUpdate Event at another point in design and used the DLookup function to retrieve multiple fields from the same record. It was commented that this might not be the "best way? " to get this information so I thought I would see if there is a more elegant, efficient, green friendly whatever make it better way.

 

[vbaInet]

Brings me back to my last point, what you want to do isn't clear? I don't understand what your aim is?

 

[MuskokaMad]

Sorry its early here an I had 5 too many pints at the pub last night.

Here is the goal.

I have to generate an invoice off of this order. So.....

1. I Need to the user to enter a delivery address for the product. Which will be used on the invoice and on a Delivery Report.

2. Each customer has 2 stored addresses Primary and Secondary (Home and Cottage)

3 I would like to avoid having the user manually enter the data.

Table tbl_Customers Contains Fields :

PrimeAddress
PrimeCity
PrimeProvince
PrimeCountry
PrimePostal

SecondAddress etc...

Table tbl_DeliveryDetails Contains Feilds

DeliveryAddress
DeliveryCity etc....


4. My thought was to create buttons that took the data from Customers and put them in DeliveryDetails.

Is my thinking fuzzy?

 

[vbaInet]

Haha!! I hope you don't go deleting stuff in the state you're in lol

Right, back to your problem. How is the OrderDetails linked to Delivery Details and Customer Details?

 

[MuskokaMad]

tbl_Customer PK=ID --> tbl_Orders FK=Customer_ID
tbl_Orders PK=ID ---> tbl_OrderDetails FK=Order_ID
tbl_DeliveryDetails FK=Order_ID

So there is no direct relationship between tbl_DeliveryDetails and tbl_Customers.

Argh... why does Stout taste so good???

 

[vbaInet]

tbl_Customer PK=ID --> tbl_Orders FK=Customer_ID
tbl_Orders PK=ID ---> tbl_OrderDetails FK=Order_ID
tbl_DeliveryDetails FK=Order_ID

So there is no direct relationship between tbl_DeliveryDetails and tbl_Customers.

There doesn't need to be a direct relationship. Do you know how to concatenate fields? And are you proficient in the use of combo boxes or list boxes?

Argh... why does Stout taste so good???

Maybe you should ask yourself, "Why am I so seduced by Stout?"

 

[MuskokaMad]

Proficient is such a relevant term... LOL ComboBoxes...I use them have repeated and ongoing arguments with them.

Concatenation... I use in some of my queries.

Truthfully my exposure to access is only about 2 months now. I am an MBA wearing a developer's hat for what was supposed to be a "Simple" Database design.

I am enjoying the process but couldn't proceed in some of my efforts without the assistance of you and your peers.

 

[vbaInet]

I'm sure you can seamlessly move from the Arts to the Sciences

So basically the Delivery Details is on a different form or is it a tab?

 

[MuskokaMad]

The Form frm_NewOrderEntry has 3 Tabs.

1. tabOrderDetails Contains a SubForm sfrm_OrderDetails
2. tabPayments Contains a SubForm sfrm_PaymentDetails
3. tabDelivery Contains a SubForm sfrm_DeliveryDetails

 

[vbaInet]

I'm guessing the Delivery_Details tab is in synch with the Order_Details, which in turn is in synch with the Customer_Details?

Create an option Frame with 2 option buttons. Inside the frame put a text box there that will show the concatenated string of the chosen address. The option frame would have values 1 and 2, where 1 is the first option and 2 is the second option.

On the control source of the text box, use the Expression Builder to build your expression. Lets say the option Frame is called "DeliveryAdd_Options" that will look something like this:

=IIF([DeliveryAdd_Options] = 1, [AddressLine1_1] & ", " & [AddressLine2_1] & ", " & [Town_1] & ", " & [Postcode_1], [AddressLine1_2] & ", " & [AddressLine2_2] & ", " & [Town_2] & ", " & [Postcode_2])

The red comma marks where the second argument starts which would be second address. You would need to use the expression builder to find the right fields.

Look at the image attached.

 

[MuskokaMad]

Thank you very much. That's Perfect.

 

[vbaInet]

You're welcome!