Autopopulate field in subform based upon textbox value

[Jester0001]

Name: AccdbFormPhoto1.jpg  Views: 36  Size: 81.6 KB  ID: 6584" class="thumbnail" style="float:CONFIG" />
The photo above is a screenshot of a form [frmSampleLogIn] with a subform [subMycoData]. What I am trying to do is to have the [SampleNo] field on the subform autopopulate with sequential, numerical numbers based upon the value in the [NoOfSamples] field in the main form.

I started by creating an [Event Procedure] in the "After Update" event of the [NoOfSamples] text box. Here is what I have so far:

Private Sub NoOfSamples_AfterUpdate()
'INSERT INTO subMycoData(SampleNo) VALUES(NoOfSamples)
Dim strSQL As String

strSQL = "INSERT INTO tblMycoData (SampleNo) VALUES(Me.NoOfSamples);"

CurrentDb.Execute strSQL, dbFailOnError
End Sub

I know that the SQL statement is premature right now, but when I run the code as it stands, an error pops up saying:

"Run-time error '3061': Too few parameters. Expected 1."

I wanted to start with the simple code above first, then ultimately figure out how to increment the numbers (starting with 1) to create and autopopulate the records in the subform.

Am I on the right track? Is using the "After Update" event the right place to be putting such a code? How can I effectively get the field on the subform to autopopulate in the way I described?

[pbaldy]

Well, your error would be resolved by:


strSQL = "INSERT INTO tblMycoData (SampleNo) VALUES(" & Me.NoOfSamples & ")"

but I don't think that will do what you want. You'd want a For/Next loop that incremented from 1 to that value, and use the value from the loop to populate the field.

[Jester0001]

Does that mean that I wouldn't need a SQL statement in my VBA code?

[pbaldy]

No, you would either need SQL or a recordset to add records to the table (or manipulate the form I suppose). The point is that to insert 3 records numbered 1-3 you'd need the loop (or a table to join on).

[Jester0001]

Ok, I think that I am getting closer, but yet still so far away.

Here is what I have come up with so far. I hope I am still on the right track:

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

intSmplNo = Forms!frmSampleLogIn01!NoOfSamples.Value
strSQL = "SELECT SampleNo FROM tblMycoData WHERE SampleNo Is Null"

Set rsMycoData = CurrentDb.OpenRecordset(strSQL)

rsMycoData.Edit
    For i = 1 To intSmplNo
    Next
rsMycoData.Update

End Sub

I have many questions related to this code. I have never written any SQL before, but there's a first time for everything. I know my logic is off somewhere. If it helps me to learn where I am making mistakes (which I do want to learn), I will talk you through my thought process while writing the code above.

1.) Declaring and defining variables

Dim db As Database
Dim rsMycoData As DAO.Recordset
Dim intSmplNo As Integer
Dim strSQL As String

intSmplNo = Forms!frmSampleLogIn01!NoOfSamples.Value
strSQL = "SELECT SampleNo FROM tblMycoData WHERE SampleNo Is Null"

2.) Setting rsMycoData as a new record in the tblMycoData

Set rsMycoData = CurrentDb.OpenRecordset(strSQL)

3.) Creating the For Next Loop to insert the integers into (I'm hoping) the [SampleNo] field on the subform.

rsMycoData.Edit
    For i = 1 To intSmplNo
    Next
rsMycoData.Update

The problem now is that I don't get any errors when the code runs, and I don't see any values inserted anywhere, so I'm not exactly sure what is happening (if anything at all) when the code is run. Any help/suggestions?

[pbaldy]

You don't get values because you never execute anything. If you use the recordset, I suspect you'd want the AddNew method rather than the Edit method. I'm not clear on what you're planning on using all that for, but based on what you had earlier you'd set and execute SQL inside the For/Next loop:

"INSERT INTO tblMycoData (SampleNo) VALUES(" &intSmplNo & ")"

[Jester0001]

pbaldy,

What I would like to do is to have the subform autopopulate with numbers 1-x, based upon the value that is in the [NoOfSamples] textbox on the main form. The two forms are bound by the [LabID] field, so I'd like for the new records in the subform to also have the [LabID] inserted as well (to link the two tables). Then, I would need the form to refresh so that the new records are shown in the subform. I'm getting a little confused myself now, so any help is appreciated.

[pbaldy]

Did you incorporate the above into your loop? Since you're adding directly to the table, you'd want to add the LabID, along the lines of:

"INSERT INTO tblMycoData (LabID, SampleNo) VALUES(" &Forms!frmSampleLogIn01!LabID & ", " & intSmplNo & ")"

[Jester0001]

pbaldy,

I changed the "rsMycoData.Edit" to "rsMycoData.AddNew", and inserted the SQL statement as you suggested. I was a little confused at first with the quotation marks in the SQL string, but now I understand the proper placement of the quotations marks to return the SQL string.

For example:

If:
LabID = 0305-022
intSmplNo = 7

Then:

strSQL = "INSERT INTO tblMycoData (LabID, SampleNo) Values(" & Forms!frmSampleLogIn01!LabID & ", " & intSmplNo & ")"

Will return the string:
INSERT INTO tblMycoData (LabID, SampleNo) Values(0305-022, 7)

Then this string will be passed to the strSQL variable further down the code.

With that out of the way, here is my updated 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

intSmplNo = Forms!frmSampleLogIn01!NoOfSamples.Value
strSQL = "INSERT INTO tblMycoData (LabID, SampleNo) Values(" & Forms!frmSampleLogIn01!LabID & ", " & intSmplNo & ")"

Set rsMycoData = CurrentDb.OpenRecordset(strSQL)

rsMycoData.AddNew
    For i = 1 To intSmplNo
    Next
rsMycoData.Update
End Sub

When the code runs now, I get a Run-time error '3219': Invalid operation at the line:

Set rsMycoData = CurrentDb.OpenRecordset(strSQL)

[pbaldy]

No, I said inside the loop. Try

 intSmplNo = Forms!frmSampleLogIn01!NoOfSamples.Value  
  For i = 1 To intSmplNo
    strSQL = "INSERT INTO tblMycoData (LabID, SampleNo) Values('" & Forms!frmSampleLogIn01!LabID & "', " & i & ")"
    CurrentDb.Execute strSQL 
  Next i

[pbaldy]

Oh, and note the single quotes I added, so your result is

INSERT INTO tblMycoData (LabID, SampleNo) Values('0305-022', 7)

That's because the LabID field is text (I assume, because of the hyphen).

[Jester0001]

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:

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:

Set rsMycoData = CurrentDb.OpenRecordset(strSQL)

So, I modified the code a little bit more to arrive at:

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.

[pbaldy]

I don't think you need/want any of the recordset related code (my original recommendation was for SQL or recordset, not both). Try this:

  Dim intSmplNo As Integer 
  Dim strSQL2 As String   
  
  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
  
  Me.NameOfSubformControl.Form.Requery

[Jester0001]

Thank you for the clarification pbaldy. I knew I was getting confused somewhere. I put in your code (modified below), but I still get the same problem: Nothing happens if there is no data/records in the subform when the code runs. If there is data/records in the subform, then the code creates new (additional) records with the correct [AccessionNo] and sequential [SampleNo].

Am I doing something else wrong?

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
 
 For i = 1 To intSmplNo
    strSQL2 = "INSERT INTO tblMycoData (AccessionNo, SampleNo) Values(" & Forms!frmSampleLogIn01!AccessionNo & ", " & i & ")"
    CurrentDb.Execute strSQL2
    Next i

 Me.subMycoData.Form.Requery
End Sub

[pbaldy]

Would the AccessionNo be on the form at that point? Just in case the append is failing, tweak this line to:


CurrentDb.Execute strSQL2, dbFailOnError

Otherwise it fails silently, which you sometimes want.