pbaldy,
Thank you for your persistence. Please don't hate me when I say this, but I was wrong about the [LabID] field being the link between the two tables. It is actually the [AccessionNo] field (which is part of the calculated LabID field). Nevertheless, I learned the use of the single quote outside of the double quotes in the SQL string when returning a text variable.
I had to modify the code a little to reflect the LabID/AccessionNo mistake mentioned above to arrive at:
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 db As Database
Dim rsMycoData As DAO.Recordset
Dim intSmplNo As Integer
Dim strSQL As String
strSQL = "INSERT INTO tblMycoData (AccessionNo, SampleNo) Values(" & Forms!frmSampleLogIn01!AccessionNo & ", " & intSmplNo & ")"
Set rsMycoData = CurrentDb.OpenRecordset(strSQL)
rsMycoData.AddNew
intSmplNo = Forms!frmSampleLogIn01!NoOfSamples.Value
For i = 1 To intSmplNo
strSQL = "INSERT INTO tblMycoData (AccessionNo, SampleNo) Values('" & Forms!frmSampleLogIn01!AccessionNo & "', " & i & ")"
CurrentDb.Execute strSQL
Next i
rsMycoData.Update
End Sub
Running the code above gave me an error : Run-time error '3219': Invalid operation at the line:
Code:
Set rsMycoData = CurrentDb.OpenRecordset(strSQL)
So, I modified the code a little bit more to arrive at:
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 db As Database
Dim rsMycoData As DAO.Recordset
Dim intSmplNo As Integer
Dim strSQL1 As String
Dim strSQL2 As String
strSQL1 = "SELECT SampleNo FROM tblMycoData WHERE SampleNo Is Null"
Set rsMycoData = CurrentDb.OpenRecordset(strSQL1)
rsMycoData.AddNew
intSmplNo = Forms!frmSampleLogIn01!NoOfSamples.Value
For i = 1 To intSmplNo
strSQL2 = "INSERT INTO tblMycoData (AccessionNo, SampleNo) Values(" & Forms!frmSampleLogIn01!AccessionNo & ", " & i & ")"
CurrentDb.Execute strSQL2
Next i
rsMycoData.Update
End Sub
With the new modified code, nothing happens when there are no values in the subform. However, when I do put some records in the subform, the code will put in new (additional) records into the [tblMycoData] with the correct [AccessionNo] and accending [SampleNo].
Here are some pics of what I am getting now:
Thus, what I would like to do is to have the records in the subform to autopopulate as soon as the AfterUpdate of the [NoOfSamples] textbox is changed.
Thank you for your support and patience thus far.