How do I add the same ID twice in a query line?

[Frank Martin]

I have the one address system that incorporates all types of names and
addresses in the one query "qryAddresses"

The "qryAddresses" is made up of the following tables:

1/ tblAddressType,
2/ tblNameType
3/ tblAddressDetails
4/ tblPeopleName
5/ tblAddressName

My problem relates to the invoice form based on the above "qryAddresses"
which has two address sections: DeliveryAddress and AccountAddress.

I can store the AccountAddress ID in the "qryAddresses", but since the
DeliveryAddress is derived from the SAME query "qryAddresses" there seems
to be no way to add the DeliveryAddressID since this conflicts with the
already-present AccountAddress.

It is necessary to store both types of addresses for archiving and invoice
reprinting.

Can someone help me with a start to solving this problem?

Regards, Frank

 

[Sylvain Lafontaine]

I'm not sure but in my opinion, you should have any problem is you use
subforms for displaying each block of address. Otherwise, you will have to
use unbound fields and some VBA code to refresh parts of your form in the
OnCurrent and the OnBeforeUpdate event.

You can also try to add another field with a boolean value telling if the
delivery address is the same as the account address. Finally, yet another
option would be to create a second address block for the delivery and copy
all the information from the first block to the second block but,
personally, I don't like this option.

S. L.

 

[Frank Martin]

Thank you.
I have partly the problem by inserting a new control "DeliveryID" into the
CustOrders table, and therefore to the qryINVOICE, and giving this a value
(via a macro) equal to the AddressesID selected for the delivery address
fields. In the Invoice report I link AddressesID to DeliveryID for the
delivery subform, and this seems to work.
Curiously only a macro has worked so far to insert the value into the
DeliveryID field because the expression builder does not work.

It feels great to have figured this out.

Regards, Frank

 

[Sylvain Lafontaine]

The expression builder is rather short in its capabilities. You should use
VBA code instead of macro and design your own SQL queries instead of using
the query builder.

S. L.