Recordset Help

[mattc66 via AccessMonster.com]

I am having trouble with the below code - it is stoping on stSQL1 and giving
me the the following message: "No Current Record" Run Time 3201.

What I am trying to do is update the "sfrmOrdersSubform" on those Items that
match in the
"qryICStockPrice_C1" query. Update ListPrice, AvgPrice and etc.

Can anyone offer me a suggestion on what may be wrong with my code?

Thanks
Matt

Private Sub cmdUpdateDisc_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim stSQL1 As String

Set db = CurrentDb()

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Forms![frmOrder]![sfrmOrdersSubform].Form.RecordsetClone
stSQL1 = "Select * From qryICStockPrice_C1 Where Item = " & rs![Item] & "
"

Set rs1 = db.OpenRecordset(stSQL1)

rs.MoveFirst
While Not rs.EOF
With rs
rs.Edit
rs![ListPrice] = Nz(rs2![ListPrice], "")
rs![AvgCost] = Nz(rs2![AvgCost], "")
rs![Multiply] = Nz(rs2![Multiply], "")
rs![AR_CODE] = Nz(rs2![AR_CODE], "")
rs.Update
End With
rs.MoveNext
Wend

Set rs = Nothing
Set rs1 = Nothing

End Sub

 

[Klatuu]

You are missing the enclosing quotes. The first version below assumes Item
is a text field. The second assumes it is a numeric field:

Set rs = Forms![frmOrder]![sfrmOrdersSubform].Form.RecordsetClone
stSQL1 = "Select * From qryICStockPrice_C1 Where Item = '" & rs![Item] & "'"

Set rs = Forms![frmOrder]![sfrmOrdersSubform].Form.RecordsetClone
stSQL1 = "Select * From qryICStockPrice_C1 Where Item = " & rs![Item]

 

[mattc66 via AccessMonster.com]

ITEM is text - I applied the changes and it worked almost. The first time it
updated the ListPrice, AvgCost and AR_Code to be the same as the first item
in the subform for all subsequint items. Then I ran it again and the second
time I ran it I got the same error message.

You are missing the enclosing quotes. The first version below assumes Item
is a text field. The second assumes it is a numeric field:

Set rs = Forms![frmOrder]![sfrmOrdersSubform].Form.RecordsetClone
stSQL1 = "Select * From qryICStockPrice_C1 Where Item = '" & rs![Item] & "'"

Set rs = Forms![frmOrder]![sfrmOrdersSubform].Form.RecordsetClone
stSQL1 = "Select * From qryICStockPrice_C1 Where Item = " & rs![Item]

I am having trouble with the below code - it is stoping on stSQL1 and giving
me the the following message: "No Current Record" Run Time 3201.

[quoted text clipped - 44 lines]

End Sub

 

[Klatuu]

The code you posted does not show an action query, only a select query, so
how is it updating anything? Secondly, you are not joining anything. You
are filtering on the value in item in the current record.

What is it you are trying to do?

ITEM is text - I applied the changes and it worked almost. The first time it
updated the ListPrice, AvgCost and AR_Code to be the same as the first item
in the subform for all subsequint items. Then I ran it again and the second
time I ran it I got the same error message.

You are missing the enclosing quotes. The first version below assumes Item
is a text field. The second assumes it is a numeric field:

Set rs = Forms![frmOrder]![sfrmOrdersSubform].Form.RecordsetClone
stSQL1 = "Select * From qryICStockPrice_C1 Where Item = '" & rs![Item] & "'"

Set rs = Forms![frmOrder]![sfrmOrdersSubform].Form.RecordsetClone
stSQL1 = "Select * From qryICStockPrice_C1 Where Item = " & rs![Item]

I am having trouble with the below code - it is stoping on stSQL1 and giving
me the the following message: "No Current Record" Run Time 3201.

[quoted text clipped - 44 lines]

End Sub

 

[AccessVandal via AccessMonster.com]

Is the "rs2" recordset somewhere else? "rs2" Not Closed? Should it be "rs1"?

If "rs" recordset has more than one records, this code will update all fields
of "rs2" as it does not move to another record.

If "rs1" is a single record, are you trying to copy the "rs1" fields to the
current records of your form?

I am having trouble with the below code - it is stoping on stSQL1 and giving
me the the following message: "No Current Record" Run Time 3201.

What I am trying to do is update the "sfrmOrdersSubform" on those Items that
match in the
"qryICStockPrice_C1" query. Update ListPrice, AvgPrice and etc.

Can anyone offer me a suggestion on what may be wrong with my code?

Thanks
Matt

Private Sub cmdUpdateDisc_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim stSQL1 As String

Set db = CurrentDb()

If Me.Dirty Then
Me.Dirty = False
End If

Set rs = Forms![frmOrder]![sfrmOrdersSubform].Form.RecordsetClone
stSQL1 = "Select * From qryICStockPrice_C1 Where Item = " & rs![Item] & "
"

Set rs1 = db.OpenRecordset(stSQL1)

rs.MoveFirst
While Not rs.EOF
With rs
rs.Edit
rs![ListPrice] = Nz(rs2![ListPrice], "")
rs![AvgCost] = Nz(rs2![AvgCost], "")
rs![Multiply] = Nz(rs2![Multiply], "")
rs![AR_CODE] = Nz(rs2![AR_CODE], "")
rs.Update
End With
rs.MoveNext
Wend

Set rs = Nothing
Set rs1 = Nothing

End Sub

 

[mattc66 via AccessMonster.com]

Typo on the posting. It should have read rs1.

rs1 - In the underlining table there is 4000 item. Each one is a unique item.
By my stSQL1 I thought I was pulling up the items that matched from rs.
stSQL1 = "Select * From qryICStockPrice_C1 Where Item = '" & rs![Item] &
"'"

Is the "rs2" recordset somewhere else? "rs2" Not Closed? Should it be "rs1"?

If "rs" recordset has more than one records, this code will update all fields
of "rs2" as it does not move to another record.

If "rs1" is a single record, are you trying to copy the "rs1" fields to the
current records of your form?

I am having trouble with the below code - it is stoping on stSQL1 and giving
me the the following message: "No Current Record" Run Time 3201.

[quoted text clipped - 44 lines]

End Sub

 

[AccessVandal via AccessMonster.com]

Why don’t you just use the update query? It much more simple.

Anyway, here I my edited version.

If your query is correct and the row order and sequence of the two recordsets
are correct. (order by Item and if unique)

rs.MoveFirst
rs1.MoveFirst
With rs
Do While Not rs1.EOF
If rs!Item = rs1!Item Then ‘are they the same?
rs.Edit
rs![ListPrice] = Nz(rs2![ListPrice], "")
rs![AvgCost] = Nz(rs2![AvgCost], "")
rs![Multiply] = Nz(rs2![Multiply], "")
rs![AR_CODE] = Nz(rs2![AR_CODE], "")
rs.Update
rs.MoveNext
rs1.moveNext
Else
rs1.MoveNext ‘if not move to next record for rs1
End If
Loop
End With

Note: rs may not be updated if there are no matching records.

mattc66 wrote:
Typo on the posting. It should have read rs1.
rs1 - In the underlining table there is 4000 item. Each one is a unique item.
By my stSQL1 I thought I was pulling up the items that matched from rs.
stSQL1 = "Select * From qryICStockPrice_C1 Where Item = '" & rs![Item] &

 

[mattc66 via AccessMonster.com]

That works great almost - If it doesn't find match it stops. I'd like it to
just past by it without an error.

Matt

Why don’t you just use the update query? It much more simple.

Anyway, here I my edited version.

If your query is correct and the row order and sequence of the two recordsets
are correct. (order by Item and if unique)

rs.MoveFirst
rs1.MoveFirst
With rs
Do While Not rs1.EOF
If rs!Item = rs1!Item Then ‘are they the same?
rs.Edit
rs![ListPrice] = Nz(rs2![ListPrice], "")
rs![AvgCost] = Nz(rs2![AvgCost], "")
rs![Multiply] = Nz(rs2![Multiply], "")
rs![AR_CODE] = Nz(rs2![AR_CODE], "")
rs.Update
rs.MoveNext
rs1.moveNext
Else
rs1.MoveNext ‘if not move to next record for rs1
End If
Loop
End With

Note: rs may not be updated if there are no matching records.

mattc66 wrote:
Typo on the posting. It should have read rs1.
rs1 - In the underlining table there is 4000 item. Each one is a unique item.
By my stSQL1 I thought I was pulling up the items that matched from rs.
stSQL1 = "Select * From qryICStockPrice_C1 Where Item = '" & rs![Item] &

 

[AccessVandal via AccessMonster.com]

Which one stops? rs or rs1? What error? Line stop where?

if it is rs, than try this. Try using MoveNext or MoveFirst

If rs.EOF = True and rs1.EOF = False Then rs.MoveNext
If rs!Item = rs1!Item Then ‘are they the same?
rs.Edit
rs![ListPrice] = Nz(rs2![ListPrice], "")
rs![AvgCost] = Nz(rs2![AvgCost], "")
rs![Multiply] = Nz(rs2![Multiply], "")
rs![AR_CODE] = Nz(rs2![AR_CODE], "")
rs.Update
rs.MoveNext
rs1.moveNext
Else
rs1.MoveNext ‘if not move to next record for rs1
End If
Loop
End With

I'm not sure about your Form's Recordsetclone, if the form's record move to
new record, did the Recordset rs.EOF = True?

 

[mattc66 via AccessMonster.com]

The below modification works great - except if the "rs!Item = rs1!Item"
doesn't match. It stops and doesn't move past it. Do you have any more
suggestions.

rs.MoveFirst
With rs
Do While Not rs.EOF
If rs!Item = rs1!Item Then 'are they the same?
rs.Edit
rs![ListPrice] = Nz(rs1![ListPrice], "")
rs![AvgCost] = Nz(rs1![Avg_Cost], "")
rs![AR_CODE] = Nz(rs1![AR_CODE], "")
rs.Update
rs.MoveNext
Else
rs1.MoveNext 'if not move to next record for rs1
End If
Loop
End With

Set rs = Nothing
Set rs1 = Nothing

Which one stops? rs or rs1? What error? Line stop where?

if it is rs, than try this. Try using MoveNext or MoveFirst

If rs.EOF = True and rs1.EOF = False Then rs.MoveNext
If rs!Item = rs1!Item Then ‘are they the same?
rs.Edit
rs![ListPrice] = Nz(rs2![ListPrice], "")
rs![AvgCost] = Nz(rs2![AvgCost], "")
rs![Multiply] = Nz(rs2![Multiply], "")
rs![AR_CODE] = Nz(rs2![AR_CODE], "")
rs.Update
rs.MoveNext
rs1.moveNext
Else
rs1.MoveNext ‘if not move to next record for rs1
End If
Loop
End With

I'm not sure about your Form's Recordsetclone, if the form's record move to
new record, did the Recordset rs.EOF = True?

mattc66 wrote:
That works great almost - If it doesn't find match it stops. I'd like it to
just past by it without an error.

 

[AccessVandal via AccessMonster.com]

Please refer to my previous post about recordset rs.

What is the error number?
What is the last value of rs field? When the error appears, move your cusor
to rs!Item and you should see something like “†or “Null†etc.

Your code, I hope not a typo.

“Do While Not rs.EOF†– Change to rs1.EOF, not rs.EOF.

Since there is more than one records in rs1, according to you. We need to
cycle this record to match Recordset rs with rs1. If the record matches,
update and move rs and rs1 to next record. If not, move next record for rs1
until rs1 = True to exit loop.

If rs1 is only a single record, than reverse the recordsets accordingly.
(only if rs.EOF =True)

Add below after “rs.Updateâ€

rs1.MoveNext – you missed this one.


There another way to deal with this error. Use error handler.

Add this a handler, something like this.

Add this line before the Dims

On Error GoTo Error_cmdUpdateDisk_Click

Add this line before End Sub.

Error_cmdUpdateDisk_Exit:
Exit Sub

Error_cmdUpdateDisk_Click:
If Err.Number = XXXX Then ‘XXXX is your error code
‘msgbox “if you need to inform userâ€
Resume Error_cmdUpdateDisk_Exit
End If