J
Jay
Is there a way to use a control on a form as a find
function and then have a record that it finds appear
on the same form?
function and then have a record that it finds appear
on the same form?
Al said:Jay,
Have you tried the FilterByForm function?
Just right click on your form, select FilterByForm, and enter your
criteria.
and then it errors on Set RS = F.RecordsetClone
Try a couple of things:
1. change your declaration to:
Dim RS As DAO.Recordset
2. make sure DAO is registered. You're probably using a version of
Acces after 97, and ADO is the default in 2000 and after. (open a code
module just any old where, go to Tools, References, and find
"Microsoft DAO 3.6". Recompile your code. Run. If the library's
missing, it won't compile.
Jay said:and then it errors on Set RS = F.RecordsetClone
Try a couple of things:
1. change your declaration to:
Dim RS As DAO.Recordset
2. make sure DAO is registered. You're probably using a version of
Acces after 97, and ADO is the default in 2000 and after. (open a code
module just any old where, go to Tools, References, and find
"Microsoft DAO 3.6". Recompile your code. Run. If the library's
missing, it won't compile.
Ok. I entered the DAO. I forgot the DAO. I have been using it alot so I
know it is working find.
I still get the error:
"object variable or with block not set"
for
Set RS = F.RecordsetClone
My 2 questions are
1.
I put the =Find_BeforeUpdate() in the forms BeforeUpdate event procedure
and the =Find_OnExit() in the number control OnExit event procedure that
is
on the form. This is where I will type in the number for searching.
2.
I am not sure about the Dim F As Form? It seems to be ok, but
something is triggering the error.
Could the F. be not set right?
Before it was Dim Find_BeforeUpdate(F As Form)
I had to change it to Dim Find_BeforeUpdate() just to get it to run
the function.
So, I am stuck at Set RS = F.RecordSetClone
Option Compare Database
' *******************************************************
' DECLARATIONS SECTION
' *******************************************************
Option Explicit
Dim Found
Function Find_BeforeUpdate()
Dim F As Form
Dim RS As DAO.Recordset
Dim 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