Find Record on Subform

  • Thread starter Thread starter Shelly via AccessMonster.com
  • Start date 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!
 
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
 
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.
 
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?
 
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
 
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
 
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.
 
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
 
Back
Top