Add variable records to a table 1

[A10Instructor]

All,

Been looking through the help files, books and these forums and can't find an answer to my question/need (or I'm too dense and missed them altogether).

I have a form that the user selects data from combo boxes. When the user clicks the 'Continue' button, this data is added to table "test1". Thanks to TheAceMan1's help, this part is working great.

I now wish (and can't figure out) for the continue button to pop up a form asking how many students are in the class. Next, a form is generated, that contains text boxes for "Rank, Firstname, MI, Lastname, and Suffix" and then transfer this data to an associated table called 'student info'.

For example: 'continue' is clicked, form appears stating "How many students are in the class?" User enters 4. Now a new form appears with four lines of textboxes asking for rank, firstname, MI, lastname, and suffix. When data entry is completed, info is saved to student info table for printing onto a certificate using a report when print certificates is selected. Then delete the records of table 'student info' when printing is done.

I have the form 'class size' and table 'student info' created. Here is the code for the 'continue' button which kicks the whole chain of events off:

Private Sub continue_button_Click()
On Error GoTo Err_continue_button_Click

Dim SQL As String
   
   SQL = "INSERT INTO test1 (crscode, " & _
                                 "crsname, pdscode, " & _
                                 "crshours, ccafhours, " & _
                                 "squadron, detnumber, " & _
                                 "baseloc, state_country, " & _
                                 "first, MI, last, " & _
                                 "suffix, rank, branch, position, " & _
                                 "graddate) " & _
         "Values (Forms!test!txtcrscode, " & _
                 "Forms!test!txtcrsname, Forms!test!txtpdscode, " & _
                 "Val(Forms!test!txtcrshours), Val(Forms!test!txtccafhours), " & _
                 "Forms!test!txtsquadron, Forms!test!txtdetnumber, " & _
                 "Forms!test!txtbase, Forms!test!txtstate_country, " & _
                 "Forms!test!txtfirst, Forms!test!txtMI, Forms!test!txtlast, " & _
                 "Forms!test!txtsuffix, Forms!test!txtrank, Forms!test!txtbranch, Forms!test!txtduty_position, " & _
                 "Forms!test!txtgraddate);"

   DoCmd.RunSQL SQL


    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "class size"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_continue_button_Click:
    Exit Sub

Err_continue_button_Click:
    MsgBox Err.Description
    Resume Exit_continue_button_Click
    
End Sub

Is this possible to do? Or am I asking for too much? Any clues/suggestions?

Thanks


A10 Instructor
"The World is My Classroom

 

[Pampers]

Maybe a strange question, but why use a database, if you gonna delete the data afterwards?

I would suggest to make it a bit simpler and store the data you enter. Make two tables and join them (Pk to FK):

tblClass
========
ClassID PK
ClassName

tblStudent
==========
StudentID
ClassID FK
StudentNameFirst
StudentNameLast
StudentRank
...

Make a form (recordsource tblClass) with a subform (tblStudent). For every Class you can now enter an infinite number of StudentRecords. Make a print button to print a certificate for the current Class. Save the data for later use.





Pampers
Just back from holiday...
that may it explain it.

 

[TheAceMan1]

A10Instructor . . .

For class size, have a look at the [blue]InputBox[/blue] function in VBE help . . .

See Ya! . . . . . .

 

[A10Instructor]

TheAceMan1,

Thanks for the helpful info...exactly what I was looking for. Is there a quick reference guide or listing to all VBA functions that I can download? I am not extremely knowledgeable in all of the functions, commands, etc that VBA has to offer.

A10 Instructor
"The World is My Classroom

 

[TheAceMan1]

Roger That A10Instructor . . .

In VBE Help - Contents Tab - Visual Basic Language Reference.

[blue]This is the listing you should get to know![/blue] . . . including the functions (of course!} . . .

See Ya! . . . . . .