Generate new records via code

[legendv]

What I need to do is generate a new record(s) based on selections made by the user that gets calcualted by a query then the appropriate number of records become generated. For instance:
Form 1:
User chooses if it is a one time purchase order or recurring purchase order. (One time orders - no issues) If the user chooses to have the purchase order recur, they choose frequency, (2,3,4 days) and duration, (once, twice...). Then the recurring purchase orders,(having their own autonumber) make new records.

So lets say, I have a purchase order that needs to recur every two days for three times. The original purchase order say has a unique autonumber of 8 and by the user entering the number 2 into a field (for days) and then 3 into a field (for 3 times) the recuring purchase orders are generated with their own autonumber say 1, 2, 3.

Anyone know how to make/code VBA to generate new records based on a calculation/ or maybe via queries.

 

[KenReay]

Hi

Well you can can create the records by using an INSERT SQL command or by using the .ADDNEW .UPDATE methods of the recordset object, so in pronciple all you need to do is set youself a loop so

For i = 1 to NoReperts
Docmd.RUNSQL "INSERT ....blah"
Next i

or
For i = 1 To NoRepeats
With Rs
.Addnew
!FieldName = ... etc
.Update
End With
Next i

Is this enough for you to figure it out, or do you need more?
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[legendv]

Ken,
Your input is very appreciated, unfortunately, yes I do need more.
Ultimately a query will be ran to gather all purchase orders that are due for a specific day. Which means it needs to find the "One-time" entered purchase orders and the "Recur" purchase orders that would be generated by the code. Since the "Recur" code generated orders need to be queried in the future would it matter which of your examples I should use?
Also in option 1 how is the format of blah, i.e. do field names go there?
In option 2, is there a limit of field names that can be used?

Thanks for your help

 

[KenReay]

Hi

Since the "Recur" code generated orders need to be queried in the future would it matter which of your examples I should use?

No, As long as your query picks out the Recur Orders, you are simply using them as a 'template' to create further Order records

Also in option 1 how is the format of blah, i.e. do field names go there?

You need here an INSERT INTO statement, seee Access help for the syntax, which will depend on what your SELECT query looks like

In option 2, is there a limit of field names that can be used?

By that I assume you mean the number of field names, in which cas ethe answer is NO



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[legendv]

Thank you for your help