2 problems with code

[terry w]

hello to all

I have some code that works OK but has a few problems that I'd really like
to fix. My form has 2 comboboxes called cboA and cboB. When the user
selects an AB combination then clicks cmdDelete, the code finds the matching
record then deletes it. But, there are 2 bugs:

1) If the user chooses an AB combination that is NOT in the recordset then
clicks cmdDelete, the code goes into an endless loop. I get around this by
using a counter (see the 6 lines marked ****), but I know that this is bad
code. How should it be handled?

2) Also, the user is unable to delete the FIRST record displayed. I suspect
this has to do with the rs.FindNext, but I'm not sure how to fix it.

Here's a code fragment:

Private Sub cmdDelete_Click()

Dim rs As Object
Set rs = Me.Recordset.Clone

Dim counter As Integer '****
counter = 0 '****

If IsNull(cboA) Or IsNull(cboB) Then
MsgBox ("Fill BOTH comboboxes then try again.")
Exit Sub
End If

Do While Not rs.EOF
rs.FindNext "[A_ID]=" & str(Me![cboA])
Me.Bookmark = rs.Bookmark

If [B_ID] = Int(Me![cboB]) Then
DoCmd.RunCommand acCmdDeleteRecord
Exit Do
End If

counter = counter + 1 '****
If counter > 2000 Then '****
MsgBox ("ENDLESS LOOP CATCHER!!! - FIX " & vbCrLf & "No Match")
Exit Sub '****
End If '****
Loop

.... code goes on ...

 

[Allen Browne]

Suggestions:

1. Solve the problem that the current record is dirty first.
Either Save:
If Me.Dirty Then Me.Dirty = False
or Undo:
If Me.Dirty Then Me.Undo

2. If these are Access tables, I find it better to be specific about the
type of recordset, and use the RecordsetClone of the form rather than the
Clone of its Recordset, i.e.:
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

3. After the FindFirst, test for NoMatch:
If rs.NoMatch Then
MsgBox "Not found"
Else
'remaining code here
End If

4. After deleting a record, you may find there is no current record.

 

[terry w]

Allen - Thanks for responding. I'll incorporate these ideas.

Suggestions:

1. Solve the problem that the current record is dirty first.
Either Save:
If Me.Dirty Then Me.Dirty = False
or Undo:
If Me.Dirty Then Me.Undo

2. If these are Access tables, I find it better to be specific about the
type of recordset, and use the RecordsetClone of the form rather than the
Clone of its Recordset, i.e.:
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

3. After the FindFirst, test for NoMatch:
If rs.NoMatch Then
MsgBox "Not found"
Else
'remaining code here
End If

4. After deleting a record, you may find there is no current record.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

hello to all

I have some code that works OK but has a few problems that I'd really like
to fix. My form has 2 comboboxes called cboA and cboB. When the user
selects an AB combination then clicks cmdDelete, the code finds the
matching
record then deletes it. But, there are 2 bugs:

1) If the user chooses an AB combination that is NOT in the recordset then
clicks cmdDelete, the code goes into an endless loop. I get around this
by
using a counter (see the 6 lines marked ****), but I know that this is bad
code. How should it be handled?

2) Also, the user is unable to delete the FIRST record displayed. I
suspect
this has to do with the rs.FindNext, but I'm not sure how to fix it.

Here's a code fragment:

Private Sub cmdDelete_Click()

Dim rs As Object
Set rs = Me.Recordset.Clone

Dim counter As Integer '****
counter = 0 '****

If IsNull(cboA) Or IsNull(cboB) Then
MsgBox ("Fill BOTH comboboxes then try again.")
Exit Sub
End If

Do While Not rs.EOF
rs.FindNext "[A_ID]=" & str(Me![cboA])
Me.Bookmark = rs.Bookmark

If [B_ID] = Int(Me![cboB]) Then
DoCmd.RunCommand acCmdDeleteRecord
Exit Do
End If

counter = counter + 1 '****
If counter > 2000 Then '****
MsgBox ("ENDLESS LOOP CATCHER!!! - FIX " & vbCrLf & "No
Match")
Exit Sub '****
End If '****
Loop

... code goes on ...

 

[DrGUI]

Looks like the FindNext line is looking for a string which you must surround
with quotes. Try this:

rs.FindNext "[A_ID]= '" & str(Me![cboA]) & "'"