Simple Search Control 1

[CharlieT302]

Hi,

I have a main form with two subforms. Both subforms have a common field and value (Tracker field). On the second form, I want to be able to quickly locate a record whose <Tracker> field matches that displayed on the first subform.

Here is my current code (attached to a button on the 2nd subform):
************************************************************
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Tracker Number] = " & Parent!Frm_Tracker_Sub.Form![Tracker Number]
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

GoTo exit_sub
************************************************************

When ran, the code does locate the value from the first subform. However, rather than using it as a search value on the 2nd subform, I get an error message saying that the value is not recognized as a valid field name or expression.

 

[dhookom]

Is tracker number text or numeric?
I always recommend using something like:
Me.Parent!...

If that doesn't work, I would place a text box on the main form "txtTrackerNumber" and use code in the On Current of the first subform to set the value. Then you code can reference txtTrackerNumber or possibly using it as Link Master/Child for the second subform.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya CharlieT302 . . .

Perhaps this:

[blue]   Dim rs As DAO.Recordset, Cri As String
   
   Set rs = Me.Recordset.Clone
   Cri = "[Tracker Number] = " & Me.Parent!Frm_Tracker_Sub.Form![Tracker Number]
   rs.FindFirst Cri
   
   If Not rs.NoMatch Then
      Me.Bookmark = rs.Bookmark
   Else
      MsgBox "Record Not Found!"
   End If
   
   Set rst = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[CharlieT302]

Dhookum

The field is actually text field. I tried the Me.Parent!... option without success. I would rather not mess with the main form if I do not have to.

There must be a simply syntax error in my code. The error message I receive actually shows the current value, so it is obviously "seeing" the value.

 

[CharlieT302]

Hey AceMan,

I plugged in your code, but received the exact same error message.

The error: 3070 Access database engine does not recognize 'smith10' as a valid field name or expression.

Where 'smith10' is the value from the first subform; so it "is" seeing the value, just not applying it to a search value on the 2nd subform.

 

[CharlieT302]

As a side note, I was looking up the error code on the Microsoft's web site. They mentioned this happening with crosstab queries using parameters. I have neither, but the control source for both subforms is a SQL statement. I converted the statement into a query and used that as the data source for the form. However, it made no difference.

Could there be another structural component to the data source of form that is preventing a search?

 

[dhookom]

If your field is text, you have to treat it like text not a number....

rs.FindFirst "[Tracker Number] = """ & Parent!Frm_Tracker_Sub.Form![Tracker Number] & """"

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

CharlieT302 . . .

... and this:

[blue]   Dim Dat As Long, rs As DAO.Recordset, Cri As String
   
   Set rs = Me.Recordset.Clone
   Cri = "[Tracker Number] = " & Forms![purple][b]MainformName[/b][/purple]!Frm_Tracker_Sub.Form![Tracker Number]
   rs.FindFirst Cri
   
   If Not rs.NoMatch Then
      Me.Bookmark = rs.Bookmark
   Else
      MsgBox "Record Not Found!"
   End If
   
   Set rst = Nothing[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[CharlieT302]

That did it. Thanks much.