Auto create records

[RamiMohaisen]

Hello all,



Need some help from you experts. This seems simple to me but I am a super rookie and have no idea how to accomplish it.

I have one very simple table with three fields:

RequisitionNumber
LineNumber
OrderNumber

When I need to enter a new requisition, say with 10 line items on it, I have to enter 10 separate records which is annoying. Especially the fact that I always make a typo. I have to enter "requisition number" 10 times and 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 and so on for each line number.

I would like a MAGICAL BUTTON on a form or code I can run straight from the table to do the following:

When I want to add a new requisition is asks for requisition number. Lets say i type "201294843".
Then it asks for the number of line items on that requisition. Say i type "7".

I want it to automatically go and create the 7 records for me in my table. like shown below:

Requisition Number	Line Number	Order Number
201294843	1
201294843	2
201294843	3
201294843	4
201294843	5
201294843	6
201294843	7

[RamiMohaisen]

Ok,

I think ive made it one step closer. I created an append query with the SQL as follows:

INSERT INTO PRAllocation ( PRNo, LineNo )
VALUES ([forms]![Form1]![txtPRNo], [forms]![Form1]![txtLineNo]);


Now this will create one record in the table with the PR number and the Last line number. All i need is some way to keep re-running this same query but with lineNo-1 each time until it reaches 1.

[alansidman]

Why not create a form and a subform. The form will hold the Req Number and the subform will hold the details. Look at this tutorial.

http://www.datapigtechnologies.com/f...subforms1.html

[RamiMohaisen]

Why not create a form and a subform. The form will hold the Req Number and the subform will hold the details. Look at this tutorial.

http://www.datapigtechnologies.com/f...subforms1.html

OK, but

How do I make the subform populate. If i get a req with 100 lines i have to sit there in the subform and type 1 ENTER, 2 ENTER 100 times.

[Bob Fitz]

Maybe use something like this in the SubForm's Before Update event to auto populate the Line Number field

If Nz(Me.[Line Number], 0) = 0 Then
  Me.[Line Number] = DMax("[Line Number]", "[TblOfLineNums]", "[Requisition Number]= " & Me.[Requisition Number]) + 1
End If

Change TblOfLineNumsto the name of the table that has the Line Number field.