Using a Combo for Record Selection

[Eagle]

I would like to know if there is a way to seamlessly prevent a form from
advancing to another record when the current record is determined to be
invalid or in need of additional information.

I've been looking at dozens of articles regarding record selection and the
code at the botton of this post is probably one of the best I've seen. But,
none of them seem to address the problem that occurs when moving to the
selected record is cancelled due to a record validation failure on the
current record.

The problem occurs with this line:

If me.dirty then me.dirty = false

This fires the Form's BeforeUpdate event, if the current record is Dirty.
This is also the place where my record validation occurs. If something is
amiss, Cancel is set to true and the record is not updated. Upon return to
the FindRecord function Access begins popping up Error Messages.

I've tried placing a call to this code in the combo's AfterUpdate and Change
events and in each case, when the Form's BeforeUpdate is cancelled due to one
reason or another, like a field validation failure, I get the following Error
Message.

Error 2101: The setting you entered isn't valid for this property.

I've also placed the call in the combo's BeforeUpdate event, like the author
of the code suggested, and get a different error.

Error 2115
The macro or function set to the BeforeUpdate or ValidationRule property for
this field is preventing the Database from saving the data in the field.

I guess I don't understand why I can be prevented from moving from one
record to another seamlessly using the Built-In interfaces like PGUP/PGDN and
the Mouse Wheel, but using a combo to select a new record generates runtime
error messages if the current record has been determined to be invalid and
movement to the selected record is cancelled. Afterall, the native functions
also call the Form's BeforeUpdate Event and what happens if the current
record is dirty and gets cancelled? Surprise, No error messages. The form
remains on the current record since the BeforeUpdate event was cancelled.

Any responses appreciated,

Posted By: Crystal In: microsoft.public.access.formscoding

Subject: Re: Bookmark the record in the Combo and return to it 2/22/2006
8:25 PM PST

Private Function FindRecord()

If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

Dim mRecordID As Long, mPassNumber as integer
Dim mRecordIDcurrent As Long

mRecordIDcurrent = nz(IDfield)
mPassNumber = 0
mRecordID = Me.ActiveControl

'set lookup combo to be empty
Me.ActiveControl = Null

FindRecord_FindIt:
mPassNumber = mPassNumber + 1
Me.RecordsetClone.FindFirst "IDfield = " & mRecordID

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Function
Else
select case mPassNumber
case 1
'remove the filter and find the record
me.filterOn = false
me.Requery
goto FindRecord_FindIt
case 2
'filter was already removed and record wasn't found
'set record back to where it was
mRecordID = mRecordIDcurrent
msgbox "Record not found",,"Record not found"
goto FindRecord_FindIt
end select
End If

End Function

 

[Klatuu]

The error 2115 you are getting is because your combo is a bound control.
When you change the value in the combo, you have dirtied the form. Now you
try to navigate away from the current record, but the new value in the combo
is not valid for the current record.

If you are going to use a combo to look up records, it needs to be an
unbound control.

 

[Eagle]

First of all..

Thanks for the response,

Actually the combo is "unbound". The Control Source is unassigned. It is
also positioned on the Form Header with the LimitToList property set to yes.

The reason the form is dirtied is because some of the actual fields have
been modified.

Here is the actual event handlers:

Private Sub cboJobNumber_BeforeUpdate(Cancel As Integer)
FindRecord
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Cancel = Not IsCurrentRecordValid
End Sub

IsCurrentRecordValid checks the current record whether it is valid or not
and returns TRUE or FALSE based on the results.

The only event that fires is the cboJobNumber_BeforeUpdate event. Then the
error message pops up and I must press either END or DEBUG. If I place the
FindRecord call in the cboJobNumber_AfterUpdate event I get past the
Form_BeforeUpdate event but the 2101 error message pops up forcing me to also
press END or DEBUG.

I've tried various ways to overcome the problem, but without success.

This doesn't seem like it should be such a big problem.