Search Text Box for String

[pjordan@drcog.org]

Hi,

I have a FORM linked to a single table. In many previous forms, I've been using this code described here http://www.dreamincode.net/forums/to...n-access-form/ To create a simple search text box for various numeric ID fields in tables.

This time, however, I'm wanting to do the same thing, however in this database and Table, the field [Project_Name] I'm searching for is a string.

I've been playing around with my code (based on that website) and just can't seem to get the string search to work.

 
Private Sub TXT_SEARCHBOX_PrjName_AfterUpdate()
  If IsNull(TXT_SEARCHBOX_PrjName) = False Then
          Me.Recordset.FindFirst "[Project_Name]=" & TXT_SEARCHBOX_PrjName
          Me!TXT_SEARCHBOX_PrjName = Null
          If Me.Recordset.NoMatch Then
                 MsgBox "Project Not Found", vbOKOnly + vbInformation, "Invalid Propject Name"
                        Me!TXT_SEARCHBOX_PrjName = Null
          End If
   End If
End Sub

From everything I've been reading and researching, I need to somehow put quotes around the TXT_SEARCHBOX_PrjName in the

Me.Recordset.FindFirst "[Project_Name]=" & TXT_SEARCHBOX_PrjName

shown above.

I'm not sure how to do that. I've tried the following all without success and with or without errors

Me.Recordset.FindFirst "[Project_Name]=" & "TXT_SEARCHBOX_PrjName"
Me.Recordset.FindFirst "[Project_Name]=" & 'TXT_SEARCHBOX_PrjName'
Me.Recordset.FindFirst "[Project_Name]=" & /'TXT_SEARCHBOX_PrjName/'
Me.Recordset.FindFirst "[Project_Name]="" & TXT_SEARCHBOX_PrjName"
Me.Recordset.FindFirst "[Project_Name]=" & '"TXT_SEARCHBOX_PrjName"'
Me.Recordset.FindFirst "[Project_Name]=" & "'TXT_SEARCHBOX_PrjName'"

and a few others I can't remember, but the main thing is when I use single quotes ' it tries of course to comment out the information after that, so not sure how to correctly use it if it does require single quotes.

What is the proper way to turn this search into a string search?


Secondly when that is solved, I have another field in the same table called [API_or_CC]. I would like the above search to only search for the [Project_Name] that has"API" associated with it (and not any that have 'CC​')

Thanks

[ranman256]

DONT use findfirst, instead FILTER the results. Then all records are the ones you want.

sub txtFind_Afterupdate()
if isNull(txtFind) then
   me.filterOn = false

else
   me.filter = "[field]='" & txtFind & "'"
   me.filterOn = true
end if

[June7]

Me.Recordset.FindFirst "[Project_Name]='" & Me.TXT_SEARCHBOX_PrjName & "'"

[pjordan@drcog.org]

Thanks for the help in how to do the quotes guys. I thought I had tried the '" & Me.TXT_SEARCHBOX_PrjName & "'"
already, but guess it was just one variation I didn't. Worked great.

And did try this method:

sub txtFind_Afterupdate()
if isNull(txtFind) then
   me.filterOn = false

else
   me.filter = "[field]='" & txtFind & "'"
   me.filterOn = true
end if

Though in my case we have other subsets of that project one still needs to see in the records and this only allows one to see just that one record without being able to jump to the next one.

The next part is trying to add in a Where or maybe a strCriteria where [API_or_CC] = "API"?

I tried

strCriteria = "[API_or_CC]= '" & API & "'"

but that didn't work and did try a where statement too that was similar, but that didn't work either...

[aytee111]

In your first code, add after the End If: Me.Requery

For multiple variations use LIKE
Me.Filter="Field Like '*" & me!txtfind & "*'"

[Missinglinq]

strCriteria = "[API_or_CC]= '" & API & "'"

is only the correct syntax if API_or_CC is defined as a String. If it's defined as a Number the syntax would be

strCriteria = "[API_or_CC]= " & API

Linq ;0)>

[pjordan@drcog.org]

strCriteria = "[API_or_CC]= '" & API & "'"

is only the correct syntax if API_or_CC is defined as a String. If it's defined as a Number the syntax would be

strCriteria = "[API_or_CC]= " & API

Linq ;0)>

Yes the API_or_CC is a Short Text

Here's what I have using the main code:

Private Sub TXT_SEARCHBOX_PrjName_AfterUpdate()
  If IsNull(TXT_SEARCHBOX_PrjName) = False Then
          strCriteria = "[API_or_CC]= '" & API & "'"
          Me.Recordset.FindFirst "[Project_Name]='" & Me.TXT_SEARCHBOX_PrjName & "'"
          Me!TXT_SEARCHBOX_PrjName = Null
          If Me.Recordset.NoMatch Then
                 MsgBox "Project Not Found", vbOKOnly + vbInformation, "Invalid Propject Name"
                        Me!TXT_SEARCHBOX_PrjName = Null
          End If
   End If


End Sub

It still doesn't search for Project_Names that only state the API_or_CC is = API

[June7]

API is literal text? - it must be within the quote marks. You are not including the API criteria in the FindFirst.

Me.Recordset.FindFirst "[Project_Name]='" & Me.TXT_SEARCHBOX_PrjName & "' AND [API_or_CC]='API'"

Did you consider ranman's suggestion to use Filter and FilterOn? If there are multiple records that meet the criteria and you want to view them all together, use Filter.

[orange]

Give us some sample data to show
a) some Project_names you have
b) a sample search
c) the result you want
d) the result you actually get

[pjordan@drcog.org]

API is literal text? - it must be within the quote marks. You are not including the API criteria in the FindFirst.

Me.Recordset.FindFirst "[Project_Name]='" & Me.TXT_SEARCHBOX_PrjName & "' AND [API_or_CC]='API'"

Did you consider ranman's suggestion to use Filter and FilterOn? If there are multiple records that meet the criteria and you want to view them all together, use Filter.

Yes that was it thank you. I'm all set everyone, thanks again for all the help and helping new ways of doing things as well...