Generate new record in related table automatically

[Sammie]

Question 1: My database is generating a report called “Invoice”. The
report contains 2 addresses: a “SoldTo” address and a “ShipTo” address.
The query bound to the report comes from a form called “frmInvoices.
On this form, the SoldTo address comes from the query bound to the form,
and the ShipTo address comes from a combo box look-up field. The ShipTo
address is stored in another table called ShipTo. My not-in-list event
gives a pop-up form based on the ShipTo table to enter the new address.

Sometimes the SoldTo and ShipTo address are the same. How can I
automatically create a new record in the ShipTo table which has the same
address as the SoldTo for the related record?

frmInvoices Combo box Control Source: ShipToID
Combo box Row source: SELECT ShipTo.ShipToID, ShipTo.ShipName
FROM ShipTo;

Two of my SoldTo address fields:
[ShipmentsAddresses]![Region]
[ShipmentsAddresses]![POSTALCODE]

Two corresponding ShipTo address fields: (don’t always match)
ShipTo.State
ShipTo.ShipPostalCode


Question 2: I would also like to be able to filter the ShipTo addresses
in the lookup combo box to view only records for the client pertaining
to the SoldTo address, and still allow me to add a new record via my
not-in-list procedure. Is this possible?

Not-in-list procedure:
strShipName = NewData
DoCmd.OpenForm "frmShipTo", , , , acFormAdd, acDialog
Response = acDataErrAdded

I hope I've given enough (hopefully correct!) information to enable
someone to help me. Thanks! You newsgroup people who answer are really
helpful!
Sammie

 

[Squirrel]

Hi Sammie,

This doesn't answer your question directly but offers a different design
consideration. I would
have kept the SoldTo and ShipTo addresses together in the invoice record
with a flag
"UseSoldToAddressAsShippingAddress" true/false and leave the ShipTo address
empty
when appropriate or else write code to copy the SoldTo address data to the
ShipTo fields on the before_update event for the flag.

With regard question 2, not sure about that. Your lookup table of addresses
must have a field for
the CustomerID. Could you use a query referencing the CustomerID as the
rowsource for your
combobox and then use the Not In List event to enable you to open a form to
add a new address?

HTH -Linda

Question 1: My database is generating a report called “Invoice”. The
report contains 2 addresses: a “SoldTo” address and a “ShipTo” address.
The query bound to the report comes from a form called “frmInvoices.
On this form, the SoldTo address comes from the query bound to the form,
and the ShipTo address comes from a combo box look-up field. The ShipTo
address is stored in another table called ShipTo. My not-in-list event
gives a pop-up form based on the ShipTo table to enter the new address.

Sometimes the SoldTo and ShipTo address are the same. How can I
automatically create a new record in the ShipTo table which has the same
address as the SoldTo for the related record?

frmInvoices Combo box Control Source: ShipToID
Combo box Row source: SELECT ShipTo.ShipToID, ShipTo.ShipName
FROM ShipTo;

Two of my SoldTo address fields:
[ShipmentsAddresses]![Region]
[ShipmentsAddresses]![POSTALCODE]

Two corresponding ShipTo address fields: (don’t always match)
ShipTo.State
ShipTo.ShipPostalCode


Question 2: I would also like to be able to filter the ShipTo addresses
in the lookup combo box to view only records for the client pertaining
to the SoldTo address, and still allow me to add a new record via my
not-in-list procedure. Is this possible?

Not-in-list procedure:
strShipName = NewData
DoCmd.OpenForm "frmShipTo", , , , acFormAdd, acDialog
Response = acDataErrAdded

I hope I've given enough (hopefully correct!) information to enable
someone to help me. Thanks! You newsgroup people who answer are really
helpful!
Sammie

=----