Recordset within a recordset?

A

Abbey Normal

Hi. I don't know if this is possible to do this the way I am doing it, but I
need to update the status of the order header by checking the status of the
order details.
I am using a recordset within a recordset? It is erroring out saying "End
with without with" Here is my code, and if it looks like I don't know what I
am doing, you'd be right! I copied code that I had from something else and
modified it.
Can anyone help? Thank you.

Private Sub Command0_Click()

On Error GoTo ErrorHandler
'Uses a DAO recordset
Dim db As DAO.Database
Dim rst As DAO.RecordSet
Dim rstd As DAO.RecordSet
Dim strSQL As String
Dim strSQLd As String
Dim Count As Integer

Set db = CurrentDb

'select the order headers
strSQL = "SELECT orderid,orderstatus from Orders where orderstatus
<>'complete'"
Set rst = db.OpenRecordset(strSQL)

With rst
Do Until .EOF
strSQLd = "SELECT orderid,status from [order details] where orderid =
orders.orderid"
Set rstd = db.OpenRecordset(strSQLd)
Count = 0

'order details recordset anyohter status than blank or null means
its still open
With rstd
Do Until .EOF
If Status <> " " Then Count = 1
If IsNull(Status) Then Count = 1
End With
'The above line is where it's stopping
.Edit
If Count = 1 Then order.orderstatus = "OPEN"
If Count = 0 Then order.orderstatus = "complete"
.Update
.MoveNext
Loop
.Close
End With
Exit Sub
 
D

Dirk Goldgar

Abbey Normal said:
Hi. I don't know if this is possible to do this the way I am doing it, but
I
need to update the status of the order header by checking the status of
the
order details.
I am using a recordset within a recordset? It is erroring out saying "End
with without with" Here is my code, and if it looks like I don't know what
I
am doing, you'd be right! I copied code that I had from something else
and
modified it.
Can anyone help? Thank you.

Private Sub Command0_Click()

On Error GoTo ErrorHandler
'Uses a DAO recordset
Dim db As DAO.Database
Dim rst As DAO.RecordSet
Dim rstd As DAO.RecordSet
Dim strSQL As String
Dim strSQLd As String
Dim Count As Integer

Set db = CurrentDb

'select the order headers
strSQL = "SELECT orderid,orderstatus from Orders where orderstatus
<>'complete'"
Set rst = db.OpenRecordset(strSQL)

With rst
Do Until .EOF
strSQLd = "SELECT orderid,status from [order details] where orderid =
orders.orderid"
Set rstd = db.OpenRecordset(strSQLd)
Count = 0

'order details recordset anyohter status than blank or null means
its still open
With rstd
Do Until .EOF
If Status <> " " Then Count = 1
If IsNull(Status) Then Count = 1
End With
'The above line is where it's stopping
.Edit
If Count = 1 Then order.orderstatus = "OPEN"
If Count = 0 Then order.orderstatus = "complete"
.Update
.MoveNext
Loop
.Close
End With
Exit Sub
Click to expand...


You;ve got a couple of problems there that strike me right off. You're
probably getting the message you report because you don't close the inner
"Do Until .EOF" loop, the one for rstd. Also, your statement that builds
strSQLd needs to embed the current orderid into it as a literal value, not
refer to it as a field in Orders, since the table Orders doesn't participate
in that query. In other words, that SQL statement should be like this:

strSQLd = "SELECT orderid,status from [order details] " & _
"where orderid = " & rst!orderid

That is, unless orderid is not a numeric field. If it's a text field, you'd
need to concatenate quotes around the value of orderid.

That said, I think you could approach this more efficiently. You don't need
to loop through the records in [order details], you just need to find out if
there is any open item. So you could write this:

'----- start of revised code #1 (air code) -----
Private Sub Command0_Click()

On Error GoTo ErrorHandler
'Uses a DAO recordset
Dim db As DAO.Database
Dim rst As DAO.RecordSet
Dim rstd As DAO.RecordSet
Dim strSQL As String
Dim strSQLd As String

Set db = CurrentDb

'select the order headers
strSQL = "SELECT orderid,orderstatus from Orders " & _
"where orderstatus <>'complete'"

Set rst = db.OpenRecordset(strSQL)

With rst

Do Until .EOF

Set rstd = CurrentDb.OpenRecordset( _
"SELECT TOP 1 orderid FROM [order details] " & _
"WHERE orderid = " & rst!orderid & _
" AND (Status Is Null OR Status = '' OR Status = ' ')", _
dbOpenSnapshot)

.Edit

If rstd.EOF Then
!orderstatus = "OPEN"
Else

!orderstatus = "complete"
End If

rstd.Close

.Update
.MoveNext

Loop

.Close

End With

End Sub
'----- end of revised code #1 -----

Note -- I may have got the logic reversed for when the order should be
marked open and when it should be marked complete. I'm not sure your
original logic was correct and consistent with your comment. So please
check that. Also, when checking for a blank status, you compared to " " (a
single space). It seemed to me likely that you might really need to be
checking for "" (a zero-length string), so I added that extra test. Feel
free to take that out if I was wrong.

Assuming that the above logic works, even that could probably be replaced by
a couple of update queries, involving no looping or recordsets at all. That
would probably be more efficient than anything else you could do. You might
try something like this instead:

'----- start of revised code #2 (air code) -----
Private Sub Command0_Click()

On Error GoTo ErrorHandler

Dim db As DAO.Database
Set db = CurrentDb

db.Execute _
"UPDATE orders " & _
"SET orderstatus = 'OPEN' " & _
"WHERE orderstatus <>'complete' " & _
"AND EXISTS " & _
"(" & _
"SELECT orderid FROM [order details] d " & _
"WHERE d.orderid = orders.orderid " & _
"AND NOT (Status Is Null OR " & _
"Status = '' OR " & _
"Status = ' ') " & _
")", _
dbFailOnError

db.Execute _
"UPDATE orders " & _
"SET orderstatus = 'complete " & _
"WHERE orderstatus <>'complete' " & _
"AND NOT EXISTS " & _
"(" & _
"SELECT orderid FROM [order details] d " & _
"WHERE d.orderid = orders.orderid " & _
"AND NOT (Status Is Null OR " & _
"Status = '' OR " & _
"Status = ' ') " & _
")", _
dbFailOnError

End Sub
'----- end of revised code #2 -----
 
A

Abbey Normal

WoW. 2 Great solutions, Thank you. I had a sneaking suspicion that I could be
doing something with query, but not familiar enough to know where to start.
I will try the Update queries you suggested first and save the recordset
info for future use. Thanks again for your time.

Dirk Goldgar said:
Abbey Normal said:
Hi. I don't know if this is possible to do this the way I am doing it, but
I
need to update the status of the order header by checking the status of
the
order details.
I am using a recordset within a recordset? It is erroring out saying "End
with without with" Here is my code, and if it looks like I don't know what
I
am doing, you'd be right! I copied code that I had from something else
and
modified it.
Can anyone help? Thank you.

Private Sub Command0_Click()

On Error GoTo ErrorHandler
'Uses a DAO recordset
Dim db As DAO.Database
Dim rst As DAO.RecordSet
Dim rstd As DAO.RecordSet
Dim strSQL As String
Dim strSQLd As String
Dim Count As Integer

Set db = CurrentDb

'select the order headers
strSQL = "SELECT orderid,orderstatus from Orders where orderstatus
<>'complete'"
Set rst = db.OpenRecordset(strSQL)

With rst
Do Until .EOF
strSQLd = "SELECT orderid,status from [order details] where orderid =
orders.orderid"
Set rstd = db.OpenRecordset(strSQLd)
Count = 0

'order details recordset anyohter status than blank or null means
its still open
With rstd
Do Until .EOF
If Status <> " " Then Count = 1
If IsNull(Status) Then Count = 1
End With
'The above line is where it's stopping
.Edit
If Count = 1 Then order.orderstatus = "OPEN"
If Count = 0 Then order.orderstatus = "complete"
.Update
.MoveNext
Loop
.Close
End With
Exit Sub
Click to expand...


You;ve got a couple of problems there that strike me right off. You're
probably getting the message you report because you don't close the inner
"Do Until .EOF" loop, the one for rstd. Also, your statement that builds
strSQLd needs to embed the current orderid into it as a literal value, not
refer to it as a field in Orders, since the table Orders doesn't participate
in that query. In other words, that SQL statement should be like this:

strSQLd = "SELECT orderid,status from [order details] " & _
"where orderid = " & rst!orderid

That is, unless orderid is not a numeric field. If it's a text field, you'd
need to concatenate quotes around the value of orderid.

That said, I think you could approach this more efficiently. You don't need
to loop through the records in [order details], you just need to find out if
there is any open item. So you could write this:

'----- start of revised code #1 (air code) -----
Private Sub Command0_Click()

On Error GoTo ErrorHandler
'Uses a DAO recordset
Dim db As DAO.Database
Dim rst As DAO.RecordSet
Dim rstd As DAO.RecordSet
Dim strSQL As String
Dim strSQLd As String

Set db = CurrentDb

'select the order headers
strSQL = "SELECT orderid,orderstatus from Orders " & _
"where orderstatus <>'complete'"

Set rst = db.OpenRecordset(strSQL)

With rst

Do Until .EOF

Set rstd = CurrentDb.OpenRecordset( _
"SELECT TOP 1 orderid FROM [order details] " & _
"WHERE orderid = " & rst!orderid & _
" AND (Status Is Null OR Status = '' OR Status = ' ')", _
dbOpenSnapshot)

.Edit

If rstd.EOF Then
!orderstatus = "OPEN"
Else

!orderstatus = "complete"
End If

rstd.Close

.Update
.MoveNext

Loop

.Close

End With

End Sub
'----- end of revised code #1 -----

Note -- I may have got the logic reversed for when the order should be
marked open and when it should be marked complete. I'm not sure your
original logic was correct and consistent with your comment. So please
check that. Also, when checking for a blank status, you compared to " " (a
single space). It seemed to me likely that you might really need to be
checking for "" (a zero-length string), so I added that extra test. Feel
free to take that out if I was wrong.

Assuming that the above logic works, even that could probably be replaced by
a couple of update queries, involving no looping or recordsets at all. That
would probably be more efficient than anything else you could do. You might
try something like this instead:

'----- start of revised code #2 (air code) -----
Private Sub Command0_Click()

On Error GoTo ErrorHandler

Dim db As DAO.Database
Set db = CurrentDb

db.Execute _
"UPDATE orders " & _
"SET orderstatus = 'OPEN' " & _
"WHERE orderstatus <>'complete' " & _
"AND EXISTS " & _
"(" & _
"SELECT orderid FROM [order details] d " & _
"WHERE d.orderid = orders.orderid " & _
"AND NOT (Status Is Null OR " & _
"Status = '' OR " & _
"Status = ' ') " & _
")", _
dbFailOnError

db.Execute _
"UPDATE orders " & _
"SET orderstatus = 'complete " & _
"WHERE orderstatus <>'complete' " & _
"AND NOT EXISTS " & _
"(" & _
"SELECT orderid FROM [order details] d " & _
"WHERE d.orderid = orders.orderid " & _
"AND NOT (Status Is Null OR " & _
"Status = '' OR " & _
"Status = ' ') " & _
")", _
dbFailOnError

End Sub
'----- end of revised code #2 -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
D

Dirk Goldgar

Abbey Normal said:
WoW. 2 Great solutions, Thank you. I had a sneaking suspicion that I could
be
doing something with query, but not familiar enough to know where to
start.
I will try the Update queries you suggested first and save the recordset
info for future use. Thanks again for your time.
Click to expand...


Be sure you have a backup of the table or database, in case I've got the
logic wrong!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need to create a dynamic recordset using generic field names 7
DAO recordset object 7
Creating a recordset 8
Assign the records shown in a subform to a recordset 2
Update Recordset with VBA 4
Need help opening ADO recordset. 14
Utilizing Recordset field in vb Requery Command Verbiage 1
Message from Access 2007 on sub function 3

Top