Find control on a form

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?

Al Camp wrote:

Jay,
Have you tried the FilterByForm function?
Just right click on your form, select FilterByForm, and enter your
criteria.


That is ok for searching and editing data. However if you enter alot
it is not good.

I found this using google.com

I had problems with the very first part of the function.
Function Find_BeforeUpdate (F As Form)
Dim RS As Recordset, C As Control
Set C = Screen.ActiveControl
Set RS = F.RecordsetClone


I changed it to
Function Find_BeforeUpdate()
Dim F as Form
Dim RS As Recordset, C As Control
Set C = Screen.ActiveControl
Set RS = F.RecordsetClone

and then it errors on Set RS = F.RecordsetClone

I am at a loss at this time.


Below is the function:



SUMMARY
This article shows you how you can use a single, bound control for both
finding records and for typing data in a form.
You can use this technique to find whether the value typed in a field
already exists in another record. If the value does
exist in another record, the record containing that value is displayed. If
the value does not exist in another record,
you can continue typing data for the current record.
Back to the top Back to the top

MORE INFORMATION
Microsoft Access online Help demonstrates how to use the Combo Box Wizard to
find records in your database by selecting a
key value from a list. However, this method is limited in that it is
designed to be used with a control not based on any
table or query (unbound control). The following example demonstrates how to
use a single, bound control to achieve the
same functionality.

NOTE: 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.

To use a single bound control both for finding records and for typing data
in a form, follow these steps:
1. Start Microsoft Access and open the sample database Northwind.mdb.
2. Create a new module with the following declarations and functions:

' ************************************************** *****
' DECLARATIONS SECTION
' ************************************************** *****
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



3. Open the Customers form in Design view, and then set the properties for
the Customer ID control as follows:
BeforeUpdate: =Find_BeforeUpdate(Form)
OnExit: =Find_OnExit()
4. View the Customers form in Form view. Click the New Record button at the
bottom of the form. In the CustomerID
control, type AROUT, and then press ENTER.

Note that Microsoft Access finds and displays the Around The Horn customer.
5. Again, click the New Record button at the bottom of the form. In the
Customer ID control, type POPSI, and then
press ENTER. Because this key value does not exist, you can continue typing
data for the record.
Back to the top Back to the top
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.

Jay,
Have you tried the FilterByForm function?
Just right click on your form, select FilterByForm, and enter your criteria.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


"Jay" wrote in message news:JtrGg.1722$y61.11@fed1read05...
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?

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.

wrote:

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

Jay wrote:

wrote:

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








I don't know if this post is working. I have not had a response for awhile.
Any ideas on how to solve the problem below?





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, _