Why Logic only looks at record n Focus? 2

[Dirtbike]

I need to loop through a table and create a new table. Basically it should check for True/False if True then create a record for each of Qty. If False then create 1 record with Qty of many. The manipulation works great. My issue is that the true/false test is only looking at the record in focus on the form. See the line:

If Me![GangWO?].Value = False ? That logical test should run for each record and not the record in focus.

I'm using DAO. Thanks for the help here.

Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

' On Error GoTo ErrHandler
Dim rst As Recordset
Dim strSQL As String
Dim strBase As String
Dim lngCtr As Long

Set rst = Me.RecordsetClone

strBase = "INSERT INTO PartOrders (FinishedGood,Qty) VALUES ('%1',%2)"

With rst
If .RecordCount > 0 Then
.MoveFirst
While Not .EOF
'add FinishedGood to sql first.
strSQL = Replace(strBase, "%1", .Fields("FinishedGood"))

If Me![GangWO?].Value = False Then
'if not checked, insert Qty times, using 1 for Qty:
strSQL = Replace(strSQL, "%2", "1")

For lngCtr = 1 To .Fields("Qty")
DoCmd.RunSQL strSQL
Next lngCtr

Else
'if checked, insert as is:
strSQL = Replace(strSQL, "%2", .Fields("Qty"))
DoCmd.RunSQL strSQL
End If
.MoveNext
Wend
End If
End With

ExitHere:
On Error Resume Next
Set rst = Nothing
Exit Sub
' ErrHandler:
'

' Resume ExitHere

Exit_Command4_Click:
Exit Sub

Err_Command4_Click:
MsgBox Err.Description
Debug.Print Err, Err.Description
Resume Exit_Command4_Click

End Sub

 

[RoyVidar]

You are using a clone of the recordset, where you loop, but the reference

[tt]Me![GangWO?].Value[/tt]

Refers to the control GangWO? on the form, at the current record. What you probably are looking for, is

[tt]If rst![GangWO?].Value = False Then[/tt]

Which should point to the recordset.

Roy-Vidar

 

[PHV]

Replace this:
If Me![GangWO?].Value
By this:
If .Fields("GangWO?").Value

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244