Append Query Adding Records Based on Quantity

[Lochwood]

Hi,

I have created a popup form with some unbound fields that feeds criteria on an append query. my question is how do i add a set number of records based on an unbound quantity field on the form.

E.G. I am on the mainform and i create a new record which generates a job_Number. This record has a subform linked by job_number. Normally i would create individual records manually on the subform via combobox Trainee_Name Start_Date End_Date. So if i had 10 trainees on the same job, i would have to add it 10 times to the subform to create 10 records.

What i would like to do is use the popup form to choose trainee_name from the combobox, start_date, end_date and quantity (10). when i then run the query it will create 10 records based on my popup form..

Hope this makes sense. and thanks for any help you can give.

 

[plog]

You're gonna need more than an APPEND query to do this. It's going to require VBA.

In general you would trigger a function, that function would contain a loop that runs from 1 to [Number Input] times and in each iteration executes that APPEND query.

 

[Minty]

The way you have described that you would create 10 identical records for one trainee? That doesn't sound correct?

 

[Pat Hartman]

Doesn't makes sense to add 10 records for the same trainee.

But, the technique is to use a table that has as many rows as you are ever going to need. So, if the max number of rows you will have to add is 50, then your numbers table would have 50 rows with 1 field containing the numbers from 1 through 50. You add this table to your append query and add criteria that selects rows where the NumberVal is <= whatever your target number is. There is no join line between this table and any other table in the query.

 

[Uncle Gizmo]

Hi,

I have created a popup form with some unbound fields that feeds criteria on an append query. my question is how do i add a set number of records based on an unbound quantity field on the form.

E.G. I am on the mainform and i create a new record which generates a job_Number. This record has a subform linked by job_number. Normally i would create individual records manually on the subform via combobox Trainee_Name Start_Date End_Date. So if i had 10 trainees on the same job, i would have to add it 10 times to the subform to create 10 records.

What i would like to do is use the popup form to choose trainee_name from the combobox, start_date, end_date and quantity (10). when i then run the query it will create 10 records based on my popup form..

Hope this makes sense. and thanks for any help you can give.

Could you adapt this solution to your requirements?

http://www.niftyaccess.com/subform-record-duplicator/

Sent from my SM-G925F using Tapatalk

 

[Lochwood]

Pat_Hartman you put me on the right track..

Ok so i created a table with a single field numbered 1 to 50 records. added this to my append query and set this to be <= an unbound field value on a popup form as well as the job title. This then created the correct amount of records to append to my table.. i then created 2 additional unbound fields for start and end dates on the popup form and added these to my query using the DateAdd command. i now have exactly what i want. thanks for all your input guys..

 

[Pat Hartman]

You're welcome. This technique is most useful for generating labels.