Creating multiple records (For Loop statement?)

[Oimoi]

Hello,

I've run into a smidge of a programming problem on my form, and I'd appreciate any help whatsoever.

I have a table called "Class" with a subform called "ClassSeat". The ClassSeat subform has the following fields: ClassSeatID (AutoNumber), ClassID, StudentID, and SeatNumber. The field of interest on the Class form is ClassSize. I want to be able to submit a ClassSize on the main form, and then have that many records created in ClassSeat, updating the SeatNumber only. Basically, if ClassSize = 20, then the table should look something like:

ClassSeatID | Class ID | StudentID | SeatNumber
000001 --------null---------null---------1-----
000002 --------null---------null---------2-----
...
000020 --------null---------null---------20----

After looking around the web, I've been led to believe that this might involve a For Loop statement. Could anyone run me through the syntax for my particular scenario, or give me any tips? Many, many thanks in advance!

 

[JANR]

For a fixed number of loops:

Sub MySub()
Dim x As Integer
 
For x = 1 To 21
    'Do stuff
Next x
End Sub

For a variable number of loops

Sub MySub(NumLoops As Integer)
Dim x As Integer
 
For x = 1 To NumLoops
     ' Do Stuff
Next x
End Sub

JR

 

[timf79]

In the loop you would then use a SQL statement to create your recordset in usage of the looping variable.

If I am not wrong you can also use ADO (but I still struggle with that).

 

[Oimoi]

Thanks for responding. I plugged in my variables, and it did wonders!

Though, I have one more question. Here's what I have:

Private Sub ClassSize_AfterUpdate()

Dim x As Integer

x = 1

For x = 1 To Me.ClassSize

[Forms]![Class]![Class Seat subform1].[Form]![SeatNumber] = x

[Forms]![Class].[Class Seat subform1].SetFocus

DoCmd.GoToRecord , , acNewRec

Next x

End Sub​

If ClassSize = 10, the loop creates 10 sequential records listing the seat numbers for the class (good), but it then creates an eleventh record for the same class but with no seat number (not good). I'm guessing this probably has something to do with the line "DoCmd.GoToRecord , , acNewRec".

I've thought about embedding an If-Then-Else statement somewhere in the loop, but I'm not sure how to go about it. I've tried:

If x = Me.ClassSize Then

[Forms]![Class].SetFocus

Else

[Forms]![Class].[Class Seat subform1].SetFocus

DoCmd.GoToRecord , , acNewRec​

​

... but this still creates an extra ClassSeat record for the class. I'd appreciate any tips!

 

[timf79]

Did you try to move the:
DoCmd.GoToRecord , , acNewRec

under here:
For x = 1 To Me.ClassSize

You can use the IF

Private Sub ClassSize_AfterUpdate()

Dim x As Integer

x = 1

For x = 1 To Me.ClassSize
If X < Me.ClassSize Then

[Forms]![Class]![Class Seat subform1].[Form]![SeatNumber] = x

[Forms]![Class].[Class Seat subform1].SetFocus

DoCmd.GoToRecord , , acNewRec

Else
[Forms]![Class]![Class Seat subform1].[Form]![SeatNumber] = x

[Forms]![Class].[Class Seat subform1].SetFocus


Next x

End Sub

:

 

[Oimoi]

Hello,

I tried that just now, and it moved onto the next record in the main form, and then it plugged in seats 1 through 10, but with no ClassID.

So I basically had one record with just a ClassID on the main form, and a second record with the seat numbers, but no ClassID. D:

 

[timf79]

Yeah, I forgott that your form basically creates a new record automatically.

 

[Oimoi]

And tried out your suggestion, but I'm still getting that extra record. *strokes chin*

Still, appreciate your input. =) Gonna keep fiddling around with If-Then statements, and I'll drop a note if I stumble upon a solution. But if there's a better way to do this, I'd appreciate any hints.

Thanks in advance!

 

[timf79]

I forgott to END IF in my code above.

But I tested it and it worked fine.:

Dim x As Integer
x = 1
For x = 1 To Me.TotalSeats
If x < Me.TotalSeats Then
[Forms]![Table1]![Table2 subform].[Form]![Seat] = x
[Forms]![Table1].[Table2 subform].SetFocus
DoCmd.GoToRecord , , acNewRec
Else
[Forms]![Table1]![Table2 subform].[Form]![Seat] = x
[Forms]![Table1].[Table2 subform].SetFocus
End If
Next x

 

[Oimoi]

I included the End If, and I'm still getting the same problem.

My master field (ClassID) links to the ClassID on the subform, so once you go to a new record in the subform, it automatically plugs in a ClassID. I need to somehow prevent it from moving onto a new record entirely after the last SeatNumber is inputted. Sorry if this doesn't make sense. D:

 

[Oimoi]

Huh. I just decided to check the actual table for Class, and it's not showing that extra record. =D Otherwise, the standalone ClassID shows up on the datasheet in the subform, but it's not saved as an entry either way.

Case closed. *noob in the house* Thanks for all the help, Tim!

 

[JANR]

Glad you got it sorted but for future refrence annther methode is to insert the values directly in the forms underlying table and requery the form to display new values.

Private Sub ClassSize_AfterUpdate()
Dim x As Integer
For x = 1 To Me.ClassSize
    CurrentDb.Execute " Insert Into Class(SeatNumber)" & _
                      " Values ( " & x & ")", dbFailOnError
Next x
Forms!Class![Class Seat subform1].Form.Requery
End Sub

JR

 

[Oimoi]

Might try that actually! I want to hide the subform, but then I can't SetFocus on a hidden object. Really appreciate the tip, JR. =)