W
Wydra Training Centers
Hi;
I have a sporadic bug with the "Find_BeforeUpdate" function, (Microsoft
Knowledge Base Article - 210239 ACC2000: How to Use a Bound Control to Find
a Record, (Full code below). If a customer ID already exists, on enter you
are taken to that record, if not it enters that number for the new record.
The events are:
BeforeUpdate: =Find_BeforeUpdate(Form)
OnExit: =Find_OnExit()
My problem, sporadically the function returns an error at the first line of
code:
Dim rs As DAO.Recordset, C As Control
Specifically at:
rs As DAO.Recordset
And occasionally at the 6th line:
rs.FindFirst "[" & C.ControlSource & "]=" & C
The last C is highlighted. Stops right there, one or the other.
Normally I just re-paste the code and alls well. But after months that just
isn't acceptable any longer.
The only requirements is that:
"The field to which the control is bound cannot be a required field, and it
cannot have a validation rule that allows Null values. Make sure that the
field's Required property is set to No, and that its ValidationRule property
does not allow Null values."
Microfoft uses the "Northwind.mdb" as an example. I've matched my fields to
it.
Does anyone have a solution?
Andy
How the Sample Functions Work
The Find_BeforeUpdate() function uses the FindFirst method to search the
recordset that the form is based on to determine whether the value typed in
the control exists in the table.
If the value does not exist, the global variable Found is set to NULL and
the function exits. If the value does exist, the global variable Found is
set to the bookmark of the found record to be used by the Find_OnExit()
function.
Before the found record can be presented, the BeforeUpdate event must be
canceled, and a SendKeys action must send two ESC keys to undo changes to
the current record.
Next, the SendKeys action sends a TAB key to exit the field. This event
triggers the Find_OnExit() function, which checks to see if the find was
successful. If it was, the CancelEvent action is run to prevent exiting the
control, and then the form record is synchronized with the found record by
setting its bookmark equal to the bookmark of the found record. Found is
then reset back to NULL.
Option Explicit
Dim Found
Function Find_BeforeUpdate (F As Form)
Dim RS As Recordset, C As Control 'Most often the bug is at RS As
Recordset
Set C = Screen.ActiveControl
Set RS = F.RecordsetClone
On Error Goto Err_Find_BeforeUpdate
' Try to find a record with a matching value.
Select Case RS.Fields(C.ControlSource).Type
' Find using Numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & C.ControlSource & "]=" & C 'Occasionally bug at
last C
' Find using Date data type key value?
Case DB_DATE
RS.FindFirst "[" & C.ControlSource & "]=#" & C & "#"
' Find using Text data type key value?
Case DB_TEXT
RS.FindFirst "[" & C.ControlSource & "] = """ & C & """"
Case Else
MsgBox "ERROR: Invalid data type for '" & C.Name & "'!"
DoCmd.CancelEvent
Exit Function
End Select
' If a record is found, save the found record's bookmark.
If RS.NoMatch Then
Found = Null
Else
Found = RS.Bookmark
End If
' If the record is found...
' ...cancel the BeforeUpdate event
' ...undo changes made to the current record
' ...and TAB to the next control to trigger the OnExit routine.
If Not IsNull(Found) Then
DoCmd.CancelEvent
SendKeys "{ESC 2}{TAB}", False
End If
Exit Function
Err_Find_BeforeUpdate:
MsgBox "ERROR: Err " & Err & ": " & Error$, 48
DoCmd.CancelEvent
Exit Function
End Function
Function Find_OnExit ()
' If the record is found, cancel the OnExit routine to stay
' in the control and go find the record.
If Not IsNull(Found) And Len(Found) <> 0 Then
DoCmd.CancelEvent
' Synchronize the form record with the found record.
Screen.ActiveForm.Bookmark = Found
Found = Null
End If
End Function
I have a sporadic bug with the "Find_BeforeUpdate" function, (Microsoft
Knowledge Base Article - 210239 ACC2000: How to Use a Bound Control to Find
a Record, (Full code below). If a customer ID already exists, on enter you
are taken to that record, if not it enters that number for the new record.
The events are:
BeforeUpdate: =Find_BeforeUpdate(Form)
OnExit: =Find_OnExit()
My problem, sporadically the function returns an error at the first line of
code:
Dim rs As DAO.Recordset, C As Control
Specifically at:
rs As DAO.Recordset
And occasionally at the 6th line:
rs.FindFirst "[" & C.ControlSource & "]=" & C
The last C is highlighted. Stops right there, one or the other.
Normally I just re-paste the code and alls well. But after months that just
isn't acceptable any longer.
The only requirements is that:
"The field to which the control is bound cannot be a required field, and it
cannot have a validation rule that allows Null values. Make sure that the
field's Required property is set to No, and that its ValidationRule property
does not allow Null values."
Microfoft uses the "Northwind.mdb" as an example. I've matched my fields to
it.
Does anyone have a solution?
Andy
How the Sample Functions Work
The Find_BeforeUpdate() function uses the FindFirst method to search the
recordset that the form is based on to determine whether the value typed in
the control exists in the table.
If the value does not exist, the global variable Found is set to NULL and
the function exits. If the value does exist, the global variable Found is
set to the bookmark of the found record to be used by the Find_OnExit()
function.
Before the found record can be presented, the BeforeUpdate event must be
canceled, and a SendKeys action must send two ESC keys to undo changes to
the current record.
Next, the SendKeys action sends a TAB key to exit the field. This event
triggers the Find_OnExit() function, which checks to see if the find was
successful. If it was, the CancelEvent action is run to prevent exiting the
control, and then the form record is synchronized with the found record by
setting its bookmark equal to the bookmark of the found record. Found is
then reset back to NULL.
Option Explicit
Dim Found
Function Find_BeforeUpdate (F As Form)
Dim RS As Recordset, C As Control 'Most often the bug is at RS As
Recordset
Set C = Screen.ActiveControl
Set RS = F.RecordsetClone
On Error Goto Err_Find_BeforeUpdate
' Try to find a record with a matching value.
Select Case RS.Fields(C.ControlSource).Type
' Find using Numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & C.ControlSource & "]=" & C 'Occasionally bug at
last C
' Find using Date data type key value?
Case DB_DATE
RS.FindFirst "[" & C.ControlSource & "]=#" & C & "#"
' Find using Text data type key value?
Case DB_TEXT
RS.FindFirst "[" & C.ControlSource & "] = """ & C & """"
Case Else
MsgBox "ERROR: Invalid data type for '" & C.Name & "'!"
DoCmd.CancelEvent
Exit Function
End Select
' If a record is found, save the found record's bookmark.
If RS.NoMatch Then
Found = Null
Else
Found = RS.Bookmark
End If
' If the record is found...
' ...cancel the BeforeUpdate event
' ...undo changes made to the current record
' ...and TAB to the next control to trigger the OnExit routine.
If Not IsNull(Found) Then
DoCmd.CancelEvent
SendKeys "{ESC 2}{TAB}", False
End If
Exit Function
Err_Find_BeforeUpdate:
MsgBox "ERROR: Err " & Err & ": " & Error$, 48
DoCmd.CancelEvent
Exit Function
End Function
Function Find_OnExit ()
' If the record is found, cancel the OnExit routine to stay
' in the control and go find the record.
If Not IsNull(Found) And Len(Found) <> 0 Then
DoCmd.CancelEvent
' Synchronize the form record with the found record.
Screen.ActiveForm.Bookmark = Found
Found = Null
End If
End Function