Findfirst with Variable Field Name

[Trucktern]

I've recently been rewriting the code for a search function I had, so I don't have to replicate the code on every single form I create, but can call it using a sub on a module. However, I can't get findfirst to work properly. Currently, my code is set up like this:

___________________________________________

Private Sub SearchButtonClick()
SearchFor Me, [Field].Name, Me.SearchBox.Text
End Sub

______________________________________________

This bit of code is present in every form that currently has search functionality. The [Field] here is variable, depending on what's being searched for.

In the module portion, the code looks like this:
_______________________________________________
Public Sub SearchFor (frmCurr, varf, valf)

If IsNumeric(valf) 'Check if the value for the field is an integer
frmCurr.RecordsetClone.FindFirst frmCurr(varf) & "=" & valf
Else
'String version of the above code
End If

If frmCurr.RecordsetClone.NoMatch = False Then
frmCurr.Bookmark = frmCurr.RecordsetClone.Bookmark
End If

_____________________________

I've used Message Boxes to spit out both the name and the value of frmCurr(varf) so I know it's properly passing and looking up the field name. However, the Findfirst method either fails to return anything, or gives me an error.

I'm separating the module into frmCurr, which means form current. varf and valf are for "variable field" and "value field" to get the field name and the value we want to search the field for. The idea being that I can just pass the field to search and item to search for, and the module can handle it to reduce code duplication across the database (since I'll be handing this off in a month or two, and the team I'm handing it off to doesn't have too much Acccess experience- I'd rather make it as easy to learn as possible.)

What am I doing wrong?

 

[JHB]

What is the error message when you get it?

 

[Cronk]

The FindFirst syntax for a numeric value is

FindFirst "FieldName=9" (for example)

Your code is generating

FindFirst FieldName=9
ie criteria not in quotes

You could have seen this by pausing the run and pasting the criteria value into the immediate window

Try

 frmCurr.RecordsetClone.FindFirst  chr(34) & [varf] & "=" & valf &  chr(34)

 

[Galaxiom]

If IsNumeric(valf) 'Check if the value for the field is an integer

This is not a reliable technique because it is not the apparent nature of the value being matched that matters when determining the appropriate delimiters but the actual Type of the field.

Get the Type property for the field. It will return a number. Configure your code according to these enumerations.

 

[Galaxiom]

FindFirst FieldName=9
ie criteria not in quotes
Try

 frmCurr.RecordsetClone.FindFirst  chr(34) & [varf] & "=" & valf &  chr(34)

No. The criteria parameter does not have to be in quotes. It has to be a String. The quotes that are typically seen in this context are the delimiters of that string within the code and do not get passed to the method. That code would pass the quotes.

Since we are looking a relatively complex logic to build the condition with appropriate delimiters inside the string, I would do this so the strFind can be seen in a Break before being applied to the command.

 Dim strFind as String
  
 strFind = varf & "=" & valf
  frmCurr.RecordsetClone.FindFirst  strFind

 

[Trucktern]

 frmCurr.RecordsetClone.FindFirst  chr(34) & [varf] & "=" & valf &  chr(34)

This code returns "Runtime Error 3001: Invalid Argument". I had tried adding quotes to this code before with similar results before.

Dim strFind as String
  
 strFind = varf & "=" & valf
  frmCurr.RecordsetClone.FindFirst  strFind

This code returns "Run-time Error 3077, Syntax error (missing operator) in expression."

Is there a problem with how I'm passing the field information? Currently I pass the Field Name, then look it up using FrmCurr(varf) in the module. I tried to find out how to pass an entire field and failed, so I don't know if that's actually possible. Passing [Field] rather than [Field].Name passes the value for some reason.

 

[Galaxiom]

You have not made it clear what you are meaning to pass as parameters to the Sub. I expect you are getting an error because they are not what I guessed.

You should always declare the datatype and whether they are ByRef or ByVal.

 

[gemma-the-husky]

I do this by searching a field on the form.
The field has to be enabled in order to be searched.

Then I use docmd.findrecord

so effectively
enter the searchstring in a textbox called txtSearch
then enter the field you wish to search (called here "searchfield")

searchfield.setfocus
DoCmd.FindRecord txtSearch, acAnywhere, False, acSearchAll, False, acCurrent, True

subsequent searches can be made with slightly different parameters

 DoCmd.FindRecord txtSearch, acAnywhere, False, acSearchAll, False, acCurrent, False

This doesn't use the recordsetclone. It is equivalent code to using the binoculars.

Note that in later versions A2010 maybe/A2013m the navigation bar now also offers a search facility, although it is a bit of a blunt instrument.

 

[Trucktern]

@Gemma: This solution worked, thank you

@Galaxiom:

 SearchFor Me, [Field].Name, SearchBox.text

I was passing the form, the name of the field I wanted to search, and the text inside the searchbox to use as the value to search for. I'm fairly certain the error occurred due to how I was passing parameters into the sub, however I'm quite new to VBA so I'm still not sure exactly what I should be doing instead, and I couldn't find a solution online.

I might still play with the code until I can get it to work just for the sake of learning, but I'll likely start implementing Gemma's solution in the meantime as it seems to work just as well.

EDIT:

I figured out how to solve this while trying to adapt Gemma's code to work with variable field names as well. Galaxiom's solution gave me the idea:

Dim newfield as string
newfield = "[" & varf & "]"
frmCurr.RecordsetClone.FindFirst newfield & "=" & valf

Using the new variable allows me to treat the field name like an actual field in reference to the proper form. Using Form(Field Name) just returned a value, and trying [Variable storing Field Name] would cause Access to try to look up the variable name as a field, however creating a new variable that was the equivalent of [Field Name] gets Access to look things up properly.

I figured I'd post the solution here in case anyone else has that problem, and stumbles onto this thread from Google.

 

[gemma-the-husky]

pleased you found it useful, and you were also able to get your original idea working.

I find the binoculars cumbersome to use, and tend to add dedicated first/next buttons to search fields that are often searched - customer names, and so on. My users seem to find it a friendly idea, as you can find text anywhere in the searchfield, not just at the start. (as with a combo box)