Searching Records using a text box instead of ctrl+f? Access 2010

[Sophia Olivas]

I used to be able to do this in 2003... but it has been a while.

I want a text box at the top of the form that will allow the user to type in an asset# to search the database

I know this is the Me.RecordsetClone feature... but for the life of me - it cannot get it to work.... (I think the bold part is the problem... vba autohelp shows this is supposed to be in parens... but that didnt' work either - online i found it encapsulated in "" but i keep getting an error of
Syntax error (missing operator) in expression.

I have a text box at top of form called searching
I have another text box which is called txtAssetID
then the specific fields are on the form that are needed for this view... the searching text box is where I want the user to put the asset# they want to look up.

right now we are using the ctrl+f function to do a search on the active field... but it looks rudamentary...

here is the code i have... any tweaks would be most appreciated!

Private Sub txtGoTo_AfterUpdate()
    If (txtGoTo & vbNullString) = vbNullString Then Exit Sub
    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
   rs.FindFirst "[Asset ID]" & "txtAssetID"    
    If rs.NoMatch Then
        MsgBox "Sorry, no such record '" & txtGoTo & "' was found.", _
               vbOKOnly + vbInformation
    Else
        Me.Recordset.Bookmark = rs.Bookmark
    End If
    rs.Close
    txtGoTo = Null
 
End Sub

[Rabbit]

1) I think you want the Asset ID to actually equal something so you need to put in equal.
2) txtAssetID is probably a variable or control on the form. If it's surrounded by quotes, then that makes it a string, it won't access the value of the variable/control.
3) If Asset ID is a string, you will need to surround the value it is matching to it in quotes.

"[Asset ID]=" & txtAssetID

[Sophia Olivas]

Asset ID is the field name in the table that is already populated.

txtAssetID is the contorl on the form for Asset ID.

I have changed the field name to AssetID just to see if the space was the issue... and still am unable to use the textbox as the search.

Another thing I need to be able to do is update the record found... i keep seeing things that imply that the recordset is not updateable...am i trying the wrong thing all together? I just don't want to have the user use the popup find feature...

[Rabbit]

You haven't shown the change you made. That is something I need to see because from what you've said, it sounds like you didn't fix any of the issues I brought up in my post.

If the recordset is not updateable, that's a different question and should be asked in a different thread.

[Sophia Olivas]

sorry - am still trying to make it work... here is what I have currently. The records need to be updateable - after they are found.

Private Sub txtGoTo_AfterUpdate()
    If (txtGoTo & vbNullString) = vbNullString Then Exit Sub
    Dim rs As DAO.Recordset
    Dim searchAsset As String
 
    Set rs = Me.RecordsetClone
    searchAsset = Me!txtGoTo
    rs.FindFirst "txtAssetID=" & searchAsset
 
 
 
    If rs.NoMatch Then
        MsgBox "Sorry, no such record '" & txtGoTo & "' was found.", _
               vbOKOnly + vbInformation
    Else
        Me.Recordset.Bookmark = rs.Bookmark
    End If
    rs.Close
    txtGoTo = Null
 
End Sub
 
 

[Rabbit]

That doesn't look like what I posted. Did you try that?

[Sophia Olivas]

Ok put original code back in - changed the rs.findfirst line to your line - modified Asset ID to AssetID to match the table - and still not working get the following error:

Data Type mismatch in criteria expression

which when i debug points to that line.

Field in table is a text field so that it can handle both # & char's.

Private Sub txtGoTo_AfterUpdate()
    If (txtGoTo & vbNullString) = vbNullString Then Exit Sub
    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
   rs.FindFirst "[AssetID]=" & txtAssetID
 
 
    If rs.NoMatch Then
        MsgBox "Sorry, no such record '" & txtGoTo & "' was found.", _
               vbOKOnly + vbInformation
    Else
        Me.Recordset.Bookmark = rs.Bookmark
    End If
    rs.Close
    txtGoTo = Null
 
End Sub
 

[Rabbit]

Yes, that's point 3 in the post I made above.

3) If Asset ID is a string, you will need to surround the value it is matching to it in quotes.

You'll just need to account for it by surrounding the value it is matching in quotes.

"[Asset ID]='" & txtAssetID & "'"

[Sophia Olivas]

sorry - i am not getting this for some reason - I am sure its simple... that is usually when I get lost lol....

You have now added a single quote into the "[Asset ID]" " and at the end in its own set of quotes - what is the single quote going to do - or what is it to represent?

[Asset ID] is now [AssetID] i changed it at the table level.
txtAssetID is the space on the form that displays the data for the current record.

txtGoTo is the name of the text box where i want the user to enter the asset # he is looking for, when he hits enter i want the database to display the contents of the record that has that asset #. It is currently unbound.

maybe I am not asking the question correctly....

i noticed if i take out the & sign though - at least it will look to run... though it cannot find a match even though I have copied it straight from the record and pasted it into the txtGoTo box...

[Rabbit]

The quotes is to tell the SQL engine that what's inside the quotes is a string.

"[AssetID]='" & txtGoTo & "'"

If txtGoTo is ABC then the SQL engine will see this

[AssetID]='ABC'

Without the quotes, it sees this

[AssetID]=ABC

The quotes tell it that it's a string.

[Sophia Olivas]

@Rabbit
Ah! I see the single quote is actually for the last half of the line... gotcha... trying that now.

[Sophia Olivas]

well drat... that didn't work either - however i did get a combo box to do the same thing i was looking for - just has the drop down list if you click the arrow - i think i can live with that solution. Appreciate your help though!

[Rabbit]

If a drop down list of the AssetID's work, that probably means that what was being typed into the textbox wasn't matching up exactly with what is in the table.