Multiple linked criteria to run append query

[antonycat]

Hi all

Am I able to setup a dialog box (form) in order to select multiple linked criteria in order to run a series of append queries ? At the moment each customer has 2 different append queries to gather the pricing data for 2 separate tables, with the delivery date field in each query setup as a prompt. So if I run the macro, 2 delivery date prompt boxes appear for each customer, ie 2 queries x 4 customers so you get 8 prompt boxes.

I was wanting to setup a form with a list of the customers and the user would then select / type in a delivery date appropriate to that customer only, because each customer may have a different dly date to another customer. Then click OK to run all the append queries using the data entered on the form.

Something like this:
List of Customers Delivery Date
Customer 1 28/02/2005
Customer 2 28/02/2005
Customer 3 01/03/2005

Any help would be appreciated.
Regards
Antony

 

[lupins46]

In you append queries, instead of using criteria like
[Please enter the date] (which will then prompt the user), you refer to the date field on the form
forms!nameofform!nameofdatecontrol

You might find that you have to declare this parameter formally (Query>Parameters on the query design menu)as a datetime field.

 

[Remou]

Hi
What I would do is create a small table [tblDeliveryDates], which could be cleared after each use:
List of Customers Delivery Date
Customer 1 28/02/2005

Then use a query like this (SQL view):

INSERT INTO tblAppendTo ( Customer, Product, DeliveryDate )
SELECT tblDeliveryDates.Customer, tblAppend.Product, tblDeliveryDates.DeliveryDate
FROM tblDeliveryDates INNER JOIN tblAppend ON tblDeliveryDates.Customer = tblAppend.Customer;

But I may be missing something.

 

[lupins46]

Yes - Remou has understood your question better than I did.. ignore my response.

 

[antonycat]

Remou

Thanks for the reply. The table would give me the flexibility to select any number of customers & delivery date combinations, without having to create a new query / prompts for each new customer we deal with.

I don't quite understand your code but I will look at that idea and give it a go and let you know what happens

Thanks
Antony