Help with do while loop

[graeme34 via AccessMonster.com]

Hello
Could any body please help me, I am quite new to programming and I am trying
to create an order entry system. I have a Sales order entry form with a child
order entry form for the order details. On the main form I have a cancel
order command button. In the on click property of this button I have code to
check if there have been order details entered to prevent the referential
integrity rules being broken. The problem seems to be in the Do while....loop.
It works in the sense it deletes all the related records, but it is not
coming out of the loop when the final record is deleted, I am getting the no
current record message at the Do while line, can anybody see what I am doing
wrong??
Here is the full code of the command button:
Private Sub cmdCancelOrder_Click()

Dim intMsgRes As Integer

Dim rs As DAO.Recordset
Dim rsDetail As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("tblSalesOrder")
Set rsDetail = CurrentDb.OpenRecordset("tblSalesOrderLine", _
dbOpenDynaset)

strTableName = "tblSalesOrderLine"
rsDetail.FindFirst "[SalesOrderNumber] = " _
& Me!txtSalesOrderNumber


If rsDetail.NoMatch = False Then
intMsgRes = MsgBox("There have been Order details added to this " _
& "Order, are you sure you want to cancel it?", _
vbYesNo, "CANCEL ORDER!")
If intMsgRes = vbNo Then Exit Sub


Do While rsDetail!SalesOrderNumber = Me!txtSalesOrderNumber _
Or Not rsDetail.EOF

rsDetail.Delete
rsDetail.MoveNext
Loop

End If



rs.MoveLast
'If statement to test if the current record has been
' saved into the table prior to deleting
If rs!SalesOrderNumber = Me!txtSalesOrderNumber Then
rs.Delete
DoCmd.Close acForm, "frmSalesOrder", acSaveYes
OpenMainMenu
rs.Close
Exit Sub
Else

DoCmd.Close acForm, "frmSalesOrder", acSaveYes
OpenMainMenu
rs.MoveLast
rs.Delete
rs.Close
End If

End Sub

 

[Rick Brandt]

Hello
Could any body please help me, I am quite new to programming and I am
trying to create an order entry system. I have a Sales order entry
form with a child order entry form for the order details. On the main
form I have a cancel order command button. In the on click property
of this button I have code to check if there have been order details
entered to prevent the referential integrity rules being broken. The
problem seems to be in the Do while....loop. It works in the sense it
deletes all the related records, but it is not coming out of the loop
when the final record is deleted, I am getting the no current record
message at the Do while line, can anybody see what I am doing wrong??
Here is the full code of the command button:
Private Sub cmdCancelOrder_Click()

K.I.S.S.

If you turn on referential integrity and cascade deletes in the relationship of
your tables then deleting an order will automatically delete all items related
to that order.

 

[graeme34 via AccessMonster.com]

Hi Rick

Thanks for your interest, I thought about using cacade delete but as I'm
quite new I suppose I am trying to be over cautious and was worried about
turning on the cascade delete in case of deleting records I didnt want to.
But I suppose it does make it easier.
Thanks.

Hello
Could any body please help me, I am quite new to programming and I am

[quoted text clipped - 9 lines]

Here is the full code of the command button:
Private Sub cmdCancelOrder_Click()

K.I.S.S.

If you turn on referential integrity and cascade deletes in the relationship of
your tables then deleting an order will automatically delete all items related
to that order.

 

[Rick Brandt]

Hi Rick

Thanks for your interest, I thought about using cacade delete but as
I'm quite new I suppose I am trying to be over cautious and was
worried about turning on the cascade delete in case of deleting
records I didnt want to. But I suppose it does make it easier.

Well if you accidentally delete an order would NOT deleting the line items make
you feek any better? That data is useless without a parent.

 

[graeme34 via AccessMonster.com]

Good point Rick......cascade delete it is
Thanks

 

[Tim Ferguson]

Good point Rick......cascade delete it is
Thanks

There is another approach.

First let me agree that switching off RI is not a good idea ever. But I
worry about using cascaded deletes because a wrong move can wipe out
large amounts of data with no recourse except to last Monday week's
backups.

Another idea is to do the deletes deliberately and formally. Start with
the child records (in this case the orderline records) and do a DCount or
a COUNT(*) to find out how many are going to go west, and give the user a
message box: "You are about to delete 249 records. Are you sure?" and see
if she will back out then. Just for good measure, you can follow up with
another one: "This is your last warning. There is no undo. Are you still
sure?". If she has agreed twice, delete the order lines and then the
order record itself. Some models have dependencies that go several layers
deep and you just don't want to wreck your entire accounting system by
deleting one customer (and five contacts, with seven orders each and nine
orderlines on each order).

Hope that helps



Tim F

 

[Rick Brandt]

There is another approach.

First let me agree that switching off RI is not a good idea ever. But I
worry about using cascaded deletes because a wrong move can wipe out
large amounts of data with no recourse except to last Monday week's
backups.

Another idea is to do the deletes deliberately and formally. Start with
the child records (in this case the orderline records) and do a DCount or
a COUNT(*) to find out how many are going to go west, and give the user a
message box: "You are about to delete 249 records. Are you sure?" and see
if she will back out then. Just for good measure, you can follow up with
another one: "This is your last warning. There is no undo. Are you still
sure?". If she has agreed twice, delete the order lines and then the
order record itself. Some models have dependencies that go several layers
deep and you just don't want to wreck your entire accounting system by
deleting one customer (and five contacts, with seven orders each and nine
orderlines on each order).

In situations where I was that worried about deletes I would simply not allow
them. Records would only be flagged as "Void" or similar. In some cases I do
this without the users even being aware that they are not actually deleting the
record.

 

[Tim Ferguson]

In situations where I was that worried about deletes I would simply
not allow them. Records would only be flagged as "Void" or similar.
In some cases I do this without the users even being aware that they
are not actually deleting the record.

Yup: fair comment.


Tim F