Combo box and Form_BeforeUpdate

[Adrian Brown]

Hi,
Im sure im missing something obvious, but im having problems with a simple
add data form. I have a table which i have created a form from to allow me
to view / edit the data. I have disabled all the navigation button views
etc. After i created the form i added a combo box using the 'Find a record
on my form based on the value I selected in my combo box' section of the
wizard. This works fine - except if you use the mouse wheel to change
records.

While im not bothered about using mousehook.dll etc to remove this i simply
added the following code to update the combo box based on the new value as
you wheel'd

Private Sub Form_Current()
If Me.ebDrugID <> cbSelectDrug Then
cbSelectDrug = Me.ebDrugID
End If
End Sub

This worked fine. The code created by the wizard for the afterupdate for
the combo box is this :-

Private Sub cbSelectDrug_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[DrugID] = " & Str(Nz(Me![cbSelectDrug], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

All this worked fine until i wanted to validate changes etc. I put in a
function for Form_BeforeUpdate(Cancel As Integer) which checks the Me.Dirty
and if it is asks if they want to update.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim varAnswer As Variant

' See if the form is dirty
If Me.Dirty Then
varAnswer = MsgBox("Would you like to save the changes made to
Record?", vbQuestion + vbYesNoCancel)
Select Case varAnswer
Case vbNo
Me.Undo
Cancel = True
Case vbYes
Exit Sub
Case vbCancel
Cancel = True
End Select
End If
End Sub

The problem is - if you make a change to any field and then select a new
item from the combo box, select cancel from that menu - i get the error that
'Run-time error '2001' You canceled the previous operation. on the ' If
Not rs.EOF Then Me.Bookmark = rs.Bookmark' line of the AfterUpdate on the
combo box. I cant seem to work out a way around this


Many Thanks for your help


Adrian

 

[Graham Mandeno]

Hi Adrian

Yes, you should expect to get this error raised, because your line setting
Me.Bookmark is requesting navigation to a new record. If the
Form_BeforeUpdate event cancels the update, then the navigation to the new
record will fail, thus raising the error.

What you need to do is trap the error and ignore it:

Private Sub cbSelectDrug_AfterUpdate()
' Find the record that matches the control.
On Error Goto ProcErr
With Me.RecordsetClone
.FindFirst "[DrugID] = " & Nz(Me.cbSelectDrug, 0)
If NoMatch.Then
' you may wish to add a MsgBox here
cbSelectDrug = Me.ebDrugID
Else
Me.Bookmark = .Bookmark
End If
End With
ProcEnd:
Exit Sub
ProcErr:
If Err <> 3001 Then MsgBox Err.Description
cbSelectDrug = Me.ebDrugID
Resume ProcEnd
End Sub

Notice that I've made a few other changes also:
1. Using RecordsetClone instead of Recordset.Clone is faster, because the
object already exists.
2. Using With... instead of creating an object variable
3. Using .NoMatch instead of .EOF
4. Resetting the combo to match the current record if the operation fails
for any reason
5. Removing the Str function, as string concatenation (&) implies conversion
to a string.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,
Im sure im missing something obvious, but im having problems with a
simple add data form. I have a table which i have created a form from to
allow me to view / edit the data. I have disabled all the navigation
button views etc. After i created the form i added a combo box using the
'Find a record on my form based on the value I selected in my combo box'
section of the wizard. This works fine - except if you use the mouse
wheel to change records.

While im not bothered about using mousehook.dll etc to remove this i
simply added the following code to update the combo box based on the new
value as you wheel'd

Private Sub Form_Current()
If Me.ebDrugID <> cbSelectDrug Then
cbSelectDrug = Me.ebDrugID
End If
End Sub

This worked fine. The code created by the wizard for the afterupdate for
the combo box is this :-

Private Sub cbSelectDrug_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[DrugID] = " & Str(Nz(Me![cbSelectDrug], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

All this worked fine until i wanted to validate changes etc. I put in a
function for Form_BeforeUpdate(Cancel As Integer) which checks the
Me.Dirty and if it is asks if they want to update.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim varAnswer As Variant

' See if the form is dirty
If Me.Dirty Then
varAnswer = MsgBox("Would you like to save the changes made to
Record?", vbQuestion + vbYesNoCancel)
Select Case varAnswer
Case vbNo
Me.Undo
Cancel = True
Case vbYes
Exit Sub
Case vbCancel
Cancel = True
End Select
End If
End Sub

The problem is - if you make a change to any field and then select a new
item from the combo box, select cancel from that menu - i get the error
that 'Run-time error '2001' You canceled the previous operation. on the '
If Not rs.EOF Then Me.Bookmark = rs.Bookmark' line of the AfterUpdate on
the combo box. I cant seem to work out a way around this


Many Thanks for your help


Adrian

 

[Adrian Brown]

That works like a dream. Thanks for that - cant believe I didn't think to
use on error I spend most my time writing C++ but just felt access was
probably better for some things I need to do, always worth learning
something new

Many MANY Thanks for that

Hi Adrian

Yes, you should expect to get this error raised, because your line setting
Me.Bookmark is requesting navigation to a new record. If the
Form_BeforeUpdate event cancels the update, then the navigation to the new
record will fail, thus raising the error.

What you need to do is trap the error and ignore it:

Private Sub cbSelectDrug_AfterUpdate()
' Find the record that matches the control.
On Error Goto ProcErr
With Me.RecordsetClone
.FindFirst "[DrugID] = " & Nz(Me.cbSelectDrug, 0)
If NoMatch.Then
' you may wish to add a MsgBox here
cbSelectDrug = Me.ebDrugID
Else
Me.Bookmark = .Bookmark
End If
End With
ProcEnd:
Exit Sub
ProcErr:
If Err <> 3001 Then MsgBox Err.Description
cbSelectDrug = Me.ebDrugID
Resume ProcEnd
End Sub

Notice that I've made a few other changes also:
1. Using RecordsetClone instead of Recordset.Clone is faster, because the
object already exists.
2. Using With... instead of creating an object variable
3. Using .NoMatch instead of .EOF
4. Resetting the combo to match the current record if the operation fails
for any reason
5. Removing the Str function, as string concatenation (&) implies
conversion to a string.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,
Im sure im missing something obvious, but im having problems with a
simple add data form. I have a table which i have created a form from to
allow me to view / edit the data. I have disabled all the navigation
button views etc. After i created the form i added a combo box using the
'Find a record on my form based on the value I selected in my combo box'
section of the wizard. This works fine - except if you use the mouse
wheel to change records.

While im not bothered about using mousehook.dll etc to remove this i
simply added the following code to update the combo box based on the new
value as you wheel'd

Private Sub Form_Current()
If Me.ebDrugID <> cbSelectDrug Then
cbSelectDrug = Me.ebDrugID
End If
End Sub

This worked fine. The code created by the wizard for the afterupdate for
the combo box is this :-

Private Sub cbSelectDrug_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[DrugID] = " & Str(Nz(Me![cbSelectDrug], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

All this worked fine until i wanted to validate changes etc. I put in a
function for Form_BeforeUpdate(Cancel As Integer) which checks the
Me.Dirty and if it is asks if they want to update.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim varAnswer As Variant

' See if the form is dirty
If Me.Dirty Then
varAnswer = MsgBox("Would you like to save the changes made to
Record?", vbQuestion + vbYesNoCancel)
Select Case varAnswer
Case vbNo
Me.Undo
Cancel = True
Case vbYes
Exit Sub
Case vbCancel
Cancel = True
End Select
End If
End Sub

The problem is - if you make a change to any field and then select a new
item from the combo box, select cancel from that menu - i get the error
that 'Run-time error '2001' You canceled the previous operation. on the '
If Not rs.EOF Then Me.Bookmark = rs.Bookmark' line of the AfterUpdate on
the combo box. I cant seem to work out a way around this


Many Thanks for your help


Adrian