Autopopulate field in subform based upon textbox value

[Jester0001]

I believe that the AccessionNo is on the main form at that point. I have it hidden in form view, but it is available in layout and design view. The same problems keep happening, now at line:

CurrentDb.Execute strSQL2, dbFailOnError

I have attached the db, which may aid in the help.

While going through these trials, I realized that (once the code is running properly) if the user accidentally put in the wrong value in the [NoOfSamples] textbox and needed to change that value, then new records would be written again, potentially creating duplicate [SampleNo] values for each [AccessionNo]. How would code be written to check for the [AccessionNo] and [SampleNo] to ensure that there are no duplicates with the same [AccessionNo] and [SampleNo] if the user needed to change the value in the [NoOfSamples] field?

[pbaldy]

What error are you getting? It worked as expected for me. I changed the 3 to 4, and presto, records numbered 1-4 appeared below.

If you can count on the user changing the values before adding anything below, it might be simplest to delete all records and re-add them. Otherwise, you'd either need to put keys or indexes on the table that prevented duplicates, or check for the combination before adding.

[Jester0001]

pbaldy,

You are correct, that if you changed the 3 to a 4, then the subform populates as desired. However, that is because it is creating the records on an existing record in the main form [tblSampleLogIn]. When a user wants to add a new record to the main form, I have set the "new record" button to create a new record, and calculate the LabID.

Thus, the error comes up when:

1.) form [frmSampleLogIn01] is opened
2.) the new record button on the form is pressed
3.) the value in the textbox [NoOfSamples] is changed

The error says:

Run-time error '3201':
You cannot add or change a record because a related record is required in table 'tblSampleLogIn'.

Does that mean that a new record is not yet created in the [tblSampleLogIn]? If so, then I am confused as to why, since after hitting the new record button, a new record is (or should be) created with a new LabID.

[pbaldy]

Ah, try adding this line to commit the new record in the main table:

If Me.Dirty Then Me.Dirty = False

[Jester0001]

Yes, it works by adding:

If Me.Dirty Then Me.Dirty = False

Thank you so much. Now I'll have to read about Me.Dirty to find out what it does, and why. Then, I'll tackle the problem where if a user changes the value in the textbox then new duplicate records won't be added. If I can't figure it out, then I'll start a new thread. But for now, this thread is SOLVED!

Working code:

Private Sub NoOfSamples_AfterUpdate()
'To autopopulate the [SampleNo] field in the subform with
'sequential numbers up to the value in the [NoOfSamples] field in the main form.
 Dim intSmplNo As Integer
 Dim strSQL2 As String
 
 intSmplNo = Forms!frmSampleLogIn01!NoOfSamples.Value
 
 If Me.Dirty Then Me.Dirty = False
 
 For i = 1 To intSmplNo
    strSQL2 = "INSERT INTO tblMycoData (AccessionNo, SampleNo) Values(" & Forms!frmSampleLogIn01!AccessionNo & ", " & i & ")"
    CurrentDb.Execute strSQL2, dbFailOnError
    Next i

 Me.subMycoData.Form.Requery
End Sub

[pbaldy]

Happy to help. Using Dirty that way is a backhanded method of forcing the form to save if it's currently unsaved.