Help with code for RecordSet.AddNew

[tclarke]

Hi,

I've created a command button on a subform (continuous forms) which
runs the code below in it's On_Click event. It adds 8 new records if
the RecordSet is empty. It also assigns a value to each
(Me.txtModuleID) control and it works just fine.

Private Sub cmdSetExams_Click()
Dim i As Long

With Me.Recordset
If .EOF = True And .BOF = True Then
For i = 1 To 8
Me.Recordset.AddNew
Me.txtModuleID = i
Next
End If
End With
End Sub

I would like dispense with the command button and get the subform to
automatically add 8 new records to the recordset in the OnCurrent Event
whenever the RecordSet is empty.

But when I put the above code in the OnCurrent event of the subform it
continually adds multiple records, each containing the value 1 for
txtModuleID and crashes. Can you help?

N.B. the Main form (ExamFrm)is linked to StudentTbl and the subform
(Exam_details_Subform) is linked to ExamTbl. The two tables have a
relationship based on the field LogBookNo.

 

[tina]

well, i wasn't able to duplicate your error, but i wasn't able to get the
code to work right, either.

you might try moving the code to the main form, and run it from the subform
CONTROL's Enter event. you'll have to adjust the syntax to refer to the
subform objects from outside the subform. and i'd add an If statement to
check for a value in the main form's primary key field - otherwise, you
could be adding records to the subform table when there are no related
records in the main form table.

hth

 

[tclarke]

Hi,

Thanks for taking the time to look at my code Tina. Just to put you in
the picture I'm using an Acess 2000 database and VB 6.3. I have a form
which displays individual student details in the Main Form and
individual student exam modules taken in the Sub Form.

The main form is displayed as a Single Form and the Sub Form is
diaplayed as Continuous Forms and includes records of exams taken by
the student named in the Main Form. So I can navigate through the forms
one by one and see individual sudent details in the top half of each
form and individual exam details (continuous forms) in the bottom half.

The Main Form is named ExamFrm - (Single Form format)(RecordSource
StudentTbl) , and the Sub Form is named Exam_details_Subform
(Continuous Forms format)- (RecordSource - ExamTbl).

I tried your sugestion and placed the code in the main form's OnCurrent
Event as follows:-

Private Sub Form_Current()
Dim i As Long
With Me.Exam_details_Subform
If Me.RecordsetClone.EOF = True And Me.RecordsetClone.BOF = True Then
For i = 1 To 8
Me.RecordsetClone.AddNew
Me.Exam_details_Subform!txtModuleID = i
Next
End If
End With
End Sub

The code compiled OK and no errors occurred...but nothing happened.

What I'm finding it difficult to understand is that when I run the code
in the Sub Form, new records are DEFINITELY added to the Sub Form and
also to ExamTbl (I've double checked this), even though there are no
existing related records. Is this because Allow Filters, Edits,
Deletions and Additions are all set to Yes? By the way, the Main Form
has the same settings.

If records can't be added using this method could you tell me how to
write some code that will add the 8 new records to ExamTbl on the
condition that no records exis in the Sub Form?

Terry

 

[tina]

well, the code you originally posted is adding records to the subform's
Recordset, while the code you tried running from the Main form is adding
records to the subform's RecordsetClone. you could try changing that
mainform code to write directly to the subform's Recordset.

if that doesn't work, you could execute an Append query to add the records,
looping through a count like you did with the recordset, as

Dim i As Long

If Me!Exam_details.Form.Recordset.RecordCount = 0 And _
Not IsNull(Me!PrimaryKeyField) Then
for i = 1 to 8
CurrentDb.Execute "INSERT INTO ExamTbl ( LogBookNo, " _
& "ModuleID ) SELECT " & Me!LogBookNo _
& ", " & i, dbFailOnError
Next
Me!Exam_details.Form.Requery
End If

for "PrimaryKeyField" substitute the correct name of the primary key field
in StudentTbl. the above SQL statement assumes that the primary key field of
ExamTbl is an autonumber that doesn't have to be generated in code when each
append executes. *if that's not correct*, then i'll need more detail about
the primary key of ExamTbl: the field name, and how the value is generated.

hth

 

[tclarke]

Hi Tina, Tried your new code but had no success. However I eventually
cracked it with the use of your Recordcount condition. This now works
perfectly. Thanks once again. Below is the code I'm now using.

Private Sub Form_Current()
Dim i As Long
With Me.RecordsetClone
If Me.Recordset.RecordCount = 0 And .BOF = True Then
MsgBox "Exams are now being set"
For i = 1 To 8
Me.Recordset.AddNew
Me.txtModuleID = i
Next
End If
End With

Terry

 

[tina]

good job; too heads are usually better than one! and you're welcome