Creating multiple records (1 Viewer)

[robtarr]

I need to create a record to keep track of a wrestling meet. When that record is created I'd like to create 17 records (in another table) that would be for each individual match. Each of these records is related to the meet record through a meetID.

Any suggestions on how can I create 17 new records, each with a different value in the weight class field (70,75,80,85,...) leaving all other fields blank, to be filled in later?

 

[pono1]

R,

You can start off by setting up a main and sub form -- if you already haven't.

The main form would be based on your meet table, called, say, "TblMeet," with, as you said, MeetID as the primary key.

The sub form would be based on a table that holds matches, called, say, "TblMatches," with a field called, say, "MatchID" as a primary key. MeetID would also be a field in TblMatches (a foreign key) -- and it would be the link field between the sub and main forms.

(You may also want a table to store a list of wrestlers, and you will certainly want a table for weight classes -- see TblWeights in code-sample below).

Once your main and sub forms are working together, you will probably need to write code to add your various match records to your subform (yes, I read your post and am finally going to answer your question). You can run a SQL append query, or use ADO, or DAO to make it happen at the click of a button.

Here's an ADO code-sample:

Dim lngMeetID As Long
Dim rstMatches As ADODB.Recordset
Dim rstWeights As ADODB.Recordset

lngMeetID = Forms!FrmMeets!meetid
Set rstMatches = New ADODB.Recordset
Set rstWeights = New ADODB.Recordset

'put a chunk of the the matches table data into memory 
'so recs can be added
rstMatches.Open "SELECT * FROM tblMatches WHERE meetid = " & lngMeetID, _
CurrentProject.Connection, adOpenDynamic, adLockOptimistic

'Put the entire weights table data into memory so recs can be 
'added using this table as a guide...
'This way, if the weight classes ever change -- if one is added, if 
'one is dropped -- the app will adjust automatically
rstWeights.Open "SELECT * FROM tblWeights", CurrentProject.Connection, _
adOpenDynamic, adLockOptimistic

'move to the first record (the first weight class) in rstWeights
rstWeights.MoveFirst

'and now move through each weight class one by one, 
'in sync with the matches recordset, 
'adding a new match record for each weight class
    Do Until rstWeights.EOF 'run loop until the End Of File (recordset)
    
        With rstMatches
            .AddNew
               ![meetid] = lngMeetID
		'NOTE: [WeightClass] is a field in two tables --
		'tblWeights, tblMatches
               ![WeightClass] = rstWeights![WeightClass]
             .Update 
            .MoveNext
        End With
       
        rstWeights.MoveNext
        
    Loop 'go back and do it again

'done -- so close everything and free the RAM 
rstMatches.Close
Set rstMatches = Nothing

rstWeights.Close
Set rstWeights = Nothing

'recalc so new match records display in subform
Me.Recalc

Double arm-bar -- is that still a common move? Best of luck with your app.

Regards,
Tim