Find Record on Subform

  • Thread starter Shelly via AccessMonster.com
  • Start date
S

Shelly via AccessMonster.com

Hi.

I've got a table of machine parts that I frequently search for key words in
the description field. I would like to simplify the search process for other
users of my database. My original idea was to have a datasheet subform with
some navigation controls, including a search function, on the main form.
However, I have not been able to code a Findrecord function that will work in
the subform. After searching this and other forums and help menus, etc. I
haven't found an answer that really speaks to my specific case.

I'm using Access 2003. My wish is to have a user open the table and be able
to scroll and browse through it and have a find function with the parameters
(field name, "any part of field") automatically set. My other idea, to use
DoCmd.RunCommand acCmdFind - it appears to just open the Find window and
doesn't give me any options to set the parameters automatically.

Any ideas or help would be greatly appreciated. Thanks!
 
K

Ken Ismert

Shelly,

Use the DAO FindFirst method. Below is sample code you can put in your
subform.

Notes:
* A reference to DAO 3.6 is required.
* To call in parent form, use:
bOK = subFormControl.Form.FindName("Hello")

Public Function FindName(vName As Variant) As Boolean

Dim sCriteria As String
Dim sName As String
Dim rRs As DAO.Recordset

On Error GoTo HandleErr

' Init
FindName = True
' Move to First record if Null
If IsNull(vName) Then
Me.Recordset.MoveFirst
Exit Function
End If
' Parse Name
sName = CStr(Nz(vName, ""))
sName = Replace(sName, "'", "''")
If Right(sName, 1) <> "*" Then
sName = sName & "*"
End If
' Criteria - use Like comparison
sCriteria = "[Name] Like '" & sName & "'"

' Search Clone
Set rRs = Me.RecordsetClone
With rRs
.FindFirst sCriteria
If .NoMatch Then
FindName = False
Beep
Else
FindName = True
Me.Recordset.Bookmark = rRs.Bookmark
' Force Scroll to selected Record
' Control must be able to recieve focus
txtName.SetFocus
End If
End With

Exit Function

HandleErr:
Err.Raise Number:=Err.Number, _
Description:=Err.Description, _
Source:="Form_MyForm.FindName" & vbCr & Err.Source
Exit Function
End Function
 
S

Shelly via AccessMonster.com

Ken:
Thank you for your prompt reply! I'm unfortunately getting stuck on the call
in the parent form. I'm getting the error, "Run-time error 91, Object
variable or With variable not set."

What am I missing? Here is my code:

Private Sub cmdFind_Click()
Dim Value As Variant
Me.Text4.SetFocus
Value = Text4.Text
bOK = Part_Lookup_Form.Form.FindName(Value)
End Sub

Thanks again, especially for your patience as I work through unfamiliar
territory.
 
S

Shelly via AccessMonster.com

OK - figured out where my mistakes were and how to resolve my error messages.
This code works great (thank you!).

NOW my question is: once I've found the first record, how do I find the next
record? If I change criteria in my text box, how do I start it over from the
beginning?
 
S

Shelly via AccessMonster.com

In case anyone might be interested, this is the solution I came up with,
which seems to work fine. But I'm always open to improvement, if you have a
better idea:

Text4 is the textbox where the user enters criteria. When it is updated, I
reset the variable Counter to 0.

Within the FindName function, I use FindFirst
Within the FindNext function, everything is the same, I just use FindNext.

Public Sub cmdFind_Click()
Dim bOK As Variant
Dim Value As String

Counter = Counter + 1

Me.Text4.SetFocus
Value = Text4.Text
If Counter = 1 Then
bOK = [Form_Part Lookup Form].FindName(Value)
Else
bOK = [Form_Part Lookup Form].FindNext(Value)
End If
 
K

Ken Ismert

Shelly,

Glad you're making progress. Your approach is fine, however, you may
not want to duplicate the FindName function for such a small change.
You could do this:

Public Function FindName(vName As Variant, Optional bFindNext As
Boolean = False) As Boolean
...
With rRs
If bFindNext Then
.FindNext sCriteria
Else
.FindFirst sCriteria
End If
...

Then, your cmdFind_Click() code would be just:
Counter = Counter + 1
bOK = [Form_Part Lookup Form].FindName(vName:=Text4.Value,
bFindNext:=(Counter > 1))

Oh, and this calling convention is problemmatic:
[Form_Part Lookup Form].FindName(...

Try this:
subFormControl.Form.FindName(...
(Where "subFormControl" is the name of the subform control holding
your sub form)

Or this:
Dim rFrm As Form_Part Lookup Form
...
Set rFrm = subFormControl.Form
rFrm.FindName(...

(Again, "subFormControl" is the name of the subform control holding
your sub form)

-Ken
 
S

Shelly via AccessMonster.com

Ken:

You rock! Thanks.

I did not have any luck with "subFormControl.Form.FindName". My subform
control is called "Part Lookup Form" - I always got an error. Perhaps I
didn't figure out how to get the syntax right with the spaces. Anyway, I
just experimented with a million different things before discovering this
worked...that's just my inexperience. I will try your suggestion.
 
K

Ken Ismert

Shelly:

That's one of the reasons that spaces are discouraged in control names.
Two options:

* Rename the control: PartLookupForm.Form.FindName

* Use square brackets: [Part Lookup Form].Form.FindName

Square brackets look ugly to me, thus I either remove the space, or
replace it with an underscore "_".

-Ken
 
Top