I have seen a number of posts on the subject but I am unable to get
them to work. I have been unable to find any information on the error
that I am experiencing either.

Here is the situation:
I have two tables, let's say tblA and tblB. tblA has the following
fields: ordernum and numberlabels
tblB has the following fields: ordernum and labelnum
What I need to do is this: For each record in tblA I need to create a
number of new records in tblB based on the value of tblA.numberlabels.
So for example:
tblA
ordernum numberlabels
123 5

I need to output to tblB the following:
ordernum labelnum
123 1
123 2
123 3
123 4
123 5

If there are additional order numbers I need to loop through those as
well.

Hopefully you get the idea of what I need to do at this point. Here
is the problem. I setup a form with a button on it and linked in to
the OnClick event. I then placed the following code:

Private Sub Command0_Click()
Dim dbs As DAO.Database
Dim rst As Recordset
Dim rst2 As Recordset
Dim x As Integer
Dim i As Integer

Set dbs = CurrentDb
'Set rst = dbs.OpenRecordset("tblA", dbPessimistic)
'rst.MoveFirst

Set rst = dbs.OpenRecordset("tblB")

'While Not rst.EOF
For i = 1 To 5
rst.AddNew
rst!Fields("ordernum") = 1
rst!Fields("labelnum") = i
rst.Update
Next i
'Wend


End Sub

I have set Option Explicit under the General section. As you can see
by my code I am only trying to update the table with hard-coded data
at this point, not even trying to read from tblA. When I click on my
command button to test this out, I get the following error:

Run Time error '13'
type mismatch

When I click on Debug, this line is highlighted:
Set rst = dbs.OpenRecordset("tblB")

Can someone help me out? What am I missing?

alloway (AT) micheldist (DOT) com (Julie) wrote in message news:<a80e8580.0406231258.5d3ea9b (AT) posting (DOT) google.com>...

Hi Julie,

You're on the right track, try:

Private Sub Command0_Click()
Dim dbs As Database
Dim rst As Recordset
Dim rst2 As Recordset
Dim i As Integer

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblA")
Set rst2 = dbs.OpenRecordset("tblB")

While Not rst.EOF
For i = 1 To rst!numberlabels
rst2.AddNew
rst2!ordernum = rst!ordernum
rst2!labelnum = i
rst2.Update
Next i
Wend
End Sub

Regards,

Peter...

alloway (AT) micheldist (DOT) com (Julie) wrote in message news:<a80e8580.0406231258.5d3ea9b (AT) posting (DOT) google.com>...

Hi Julie,

You're on the right track, try:

Private Sub Command0_Click()
Dim dbs As Database
Dim rst As Recordset
Dim rst2 As Recordset
Dim i As Integer

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblA")
Set rst2 = dbs.OpenRecordset("tblB")

While Not rst.EOF
For i = 1 To rst!numberlabels
rst2.AddNew
rst2!ordernum = rst!ordernum
rst2!labelnum = i
rst2.Update
Next i
Wend
End Sub

Regards,

Peter...

Peter,

Thank you for your response. I tried this out but I now get a
different error:

Run-time error '3265': Item not found in this collection. When I
click on Debug, the following line is highlighted:
For i = 1 To rst!numberlabels

I then tried changing rst!numberlabels to 5 and then the same error
occurred on the following line:
rst2!Fields("ordernum") = rst!ordernum

Any suggestions?
peter (AT) booth (DOT) net.au (Peter) wrote in message news:<c45e3911.0406232108.3979cc1f (AT) posting (DOT) google.com>...

Hi Peter,

I posted a followup prior to this one but it hasn't posted yet.
Anyway, I was able to finally get the records to write to the table!
Yea! Now I am just stuck in an endless loop! But honestly, I would
rather be in an endless loop with data than no data and no endless
loop!

Thanks a bunch!

Julie

Ok, I am out of the endless loop, how do I do a "select" statement so
that I only select records that meet a certain criteria.

I will paste my code below.

Private Sub Command0_Click()
Dim dbs As Database
Dim rst As Recordset
Dim rst2 As Recordset
Dim rst3 As Recordset
Dim i As Integer

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblTest")
Set rst2 = dbs.OpenRecordset("LabelTest")

While Not rst.EOF
For i = 1 To rst.Fields("numlabels")
rst2.AddNew
rst2.Fields("ordernum") = rst.Fields("ordernumber")
rst2.Fields("itemnum") = i
If i < 10 Then
rst2.Fields("zeros") = "0000000000"
ElseIf i < 100 Then rst2.Fields("zeros") = "000000000"
ElseIf i < 1000 Then rst2.Fields("zeros") = "00000000"
Else
rst2.Fields("zeros") = "0000000"
End If
rst2.Fields("orderplusitem") = "*" & rst2.Fields("ordernum") &
rst2.Fields("zeros") & rst2.Fields("itemnum") & "*"
rst2.Update
Next i
rst.Edit
rst.Fields("labelscreate") = True
rst.Update
rst.MoveNext
Wend
rst2.Close
rst.Close
MsgBox "Done"


End Sub

**************************************
For each ordernum record in tblTest where labelscreate is false, I
want to go through the code, if labelscreate is true I want to skip
that record and go to the next one. Right now it creates records for
each record in tblTest regardless of the value in labelscreate in
tblTest.

Any hints would be appreciated.