Code to create multiple records from one form

[g4tv4life]

Hey everyone,
I'm trying to use one form to create a record for each textbox in the form. Below is my code so far which is rough and not working, and I just need a few pokes in the right direction.
The Idea of the form is that the label contains the name of a facility (did them as labels then realized text-boxes would have been smarter so I'm trying to use the label captions in the code)


This goes in 1 field, and in the other field the Yes/No value of the textbox goes in. Now the numbering of the textboxes and labels are 0,2,4,6 basically by 2's. How would I go about looping the
code so that everytime it loops through it bumps up the value 2, I tried to do it through "Count" but I'm getting a invalid quantifier when I run this code. How can I go about fixing it?

Edit:Also, This ones kinda separate from above. I want the labels to update with the master table (I.E. Facility Name changes, the label changes to match). I'm willing to redo all the labels as
text-boxes if necessary. How would I go about coding the text-boxes to display that?

Thanks guys! (and Gals)

Private Sub Command193_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim intNo As Long
Dim Count As Long
Set db = CurrentDb
Set rst = db.TableDefs("Radio Testing")
Do Until rst.EOF
rst.AddNew
rst.Fields("Date of Test") = "Textbox" & Count.Value
rst.Fields("Facility") = "Label" & Count.Caption
rst.Update
rst.NextRecordset
Loop
intNo = intNo + 1
Count = Count + 2
End Sub

[pbaldy]

I see several things, but to get started you can refer to a textbox plus number like:

Me("textbox" & Count)

I may have used a For/Next loop specifying a step value of 2.

[June7]

Put the increment line within the Do Loop, however, using wrong looping structure.

Count is a reserved word, really should not use reserved words other than as intended.

Constructing control name with variable requires different syntax.

Testing for EOF makes no sense because you are creating new records. How many controls are there to loop through?

For i = 0 to 10 Step 2
rst.AddNew
rst![Date of Test] = Me.Controls("Textbox" & i)
rst!Facility = Me.Controls("Label" & i)
rst.Update
Next

Don't really understand your last question.

EDIT: Paul's syntax also works.

[g4tv4life]

There are 80 textboxes, so the numbering starts at 0 and ends with texbox158.
As for the labels, I needed a dlookup from another table, so that the labels stay up to date (I.E. Facility name changes, label changes to match).
as for the code, I didn't mean to use count as it was in a previous line of code I copied over.

As for Junes code, where do i place the next part of the for...next method? If I could the see the whole thing I might understand it better.

[June7]

Sorry, Loop should be Next. Copy/paste strikes again, didn't finish editing the paste. I edited post.

Aside from code to open the editable recordset, that's it.

Although I never used TableDefs just to open a recordset. I use:

Set rst = db.OpenRecordset("Radio Testing")

Maybe one as good as the other.

[g4tv4life]

Its just not working for me. Here's the full code I have so far.

When I run it as it is here, It first says, this item could not find the field "Textbox0".

I tried renaming the box to Textbox0 to see what happens, but then it just's give the error "item not found in this collection"
Both times debugging comes down to this line.

rst.Fields("Date of Test") = Me.Controls("Textbox" & i)

Private Sub Command193_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim intNo As Long


Set db = CurrentDb
Set rst = db.OpenRecordset("Radio Testing")
Do
For i = 0 To 158 Step 2
rst.AddNew
rst.Fields("Date of Test") = Me.Controls("Textbox" & i)
rst.Fields("Facility") = Me.Controls("Label" & i)
rst.Update
rst.NextRecordset
Next
Loop


End Sub

what am i doing wrong?

[June7]

Reread post 5. Loop should be replaced with Next. There is no need for Do or While Loop.

What are the names of the textbox and label controls?

The "Date of Test" field name has spaces, try putting the [] back. This is why I never use spaces or special characters/punctuation (underscore is exception) in naming convention.

[g4tv4life]

The labels are "label0,label2,etc" the textboxes are "text0,text2,etc"

[June7]

Okay, did you try:

Me.Controls("text" & i)

[g4tv4life]

Yes, it gives the "item not found in this collection" as I said above

[drexasaurus]

I don't have all the answers, but I tested a little bit myself and was able to have some success:

-Make sure that the "Name" property in the Other tab of each text box and label matches the
-If you get past the first error, you will have to refer to the labels text using the caption property (that's one of the reasons I started the test thinking, wait, can you DO that? No, not by my testing anyway)
-Like June says, you don't need the outer loop, take it out. It creates an unending loop
-You don't need the NextRecordset function. I'm not that familiar with it, but it errored out on me and you don't need it to create multiple rows in the table
-If you weren't erroring out so soon, I would print a list of all the control names you are generating in the Immediate window, and possibly all the control names to make sure the values your generating are expected.

Here is the code from my test:
Set db = CurrentDb
Set rst = db.OpenRecordset("Table1")
For i = 0 To 2 Step 1
rst.AddNew
rst.Fields("ValOne") = Me.Controls("Text" & i)
rst.Fields("ValTwo") = Me.Controls("Label" & i).Caption
rst.Update
Next
End Sub

[June7]

Ooops, didn't see the NextRecordset line (no idea what that is for, never used it). You are not looping through records of the recordset. You are adding records.

As drex said, remove it.

There is nothing wrong with syntax that has been offered, except maybe I forgot the .Caption.

Although you do need Step 2 unless you rename all the controls to be sequential.

[g4tv4life]

I fixed the obvious errors, like the wrong table name (that one is totally on me)

now the error is the good old "data type conversion" variety, again on the "rst.Fields("[Date of Testing]") = Me.Controls("text" & i).Name" line
It does with both with and without the .Name at the end

Private Sub Command193_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset


Set db = CurrentDb
Set rst = db.OpenRecordset("Radio Testing")
For i = 0 To 158 Step 2
rst.AddNew
rst.Fields("[Date of Testing]") = Me.Controls("text" & i).Name
rst.Fields("Facility") = Me.Controls("label" & i).Caption
rst.Update
Next




End Sub

[June7]

.Name is wrong property. Should be .Value. However, Value is the default property for data controls and need not be specified.

[g4tv4life]

yeah, I tried .value as well which gave the same error, i think the .name was pure desperation. At this point I'm going to leave it up for a bad job and come back to it Thursdays when my brains back in it.
Thanks for the help guys