bound control to find a record

[Dave]

I am trying to use the folowing code as per Microsoft article 210239,
but I get a compile error "Method or Data Member not found". It stops
on the first C.

Any ideas?

Dave B

Option Explicit
Dim Found


Function Find_BeforeUpdate(F As Form)
Dim RS As Recordset, C As Control
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
' 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

 

[Guest]

Are you "calling" this function as part of the Before_Update event of a
control (as it is intended)?

Are you providing Access with the name of the form it is supposed to use to
create the RecordsetClone?

If you provide a little more info, someone may be able to help you.

 

[Dave]

I'm sorry, I should have mentioned that yes, The beforeupdate event is
=Find_BeforeUpdate(Form) and OnExit event is Find_OnExit().

Dave B