By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,222 Members | 1,129 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,222 IT Pros & Developers. It's quick & easy.

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

P: 16
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!



Expand|Select|Wrap|Line Numbers
  1. Private Sub txtGoTo_AfterUpdate()
  2.     If (txtGoTo & vbNullString) = vbNullString Then Exit Sub
  3.     Dim rs As DAO.Recordset
  4.     Set rs = Me.RecordsetClone
  5.    rs.FindFirst "[Asset ID]" & "txtAssetID"    
  6.     If rs.NoMatch Then
  7.         MsgBox "Sorry, no such record '" & txtGoTo & "' was found.", _
  8.                vbOKOnly + vbInformation
  9.     Else
  10.         Me.Recordset.Bookmark = rs.Bookmark
  11.     End If
  12.     rs.Close
  13.     txtGoTo = Null
  14.  
  15. End Sub
Apr 5 '12 #1
Share this Question
Share on Google+
12 Replies


Rabbit
Expert Mod 10K+
P: 12,357
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.
Expand|Select|Wrap|Line Numbers
  1. "[Asset ID]=" & txtAssetID
Apr 5 '12 #2

P: 16
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...
Apr 5 '12 #3

Rabbit
Expert Mod 10K+
P: 12,357
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.
Apr 5 '12 #4

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


Expand|Select|Wrap|Line Numbers
  1. Private Sub txtGoTo_AfterUpdate()
  2.     If (txtGoTo & vbNullString) = vbNullString Then Exit Sub
  3.     Dim rs As DAO.Recordset
  4.     Dim searchAsset As String
  5.  
  6.     Set rs = Me.RecordsetClone
  7.     searchAsset = Me!txtGoTo
  8.     rs.FindFirst "txtAssetID=" & searchAsset
  9.  
  10.  
  11.  
  12.     If rs.NoMatch Then
  13.         MsgBox "Sorry, no such record '" & txtGoTo & "' was found.", _
  14.                vbOKOnly + vbInformation
  15.     Else
  16.         Me.Recordset.Bookmark = rs.Bookmark
  17.     End If
  18.     rs.Close
  19.     txtGoTo = Null
  20.  
  21. End Sub
  22.  
  23.  
Apr 5 '12 #5

Rabbit
Expert Mod 10K+
P: 12,357
That doesn't look like what I posted. Did you try that?
Apr 5 '12 #6

P: 16
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.



Expand|Select|Wrap|Line Numbers
  1. Private Sub txtGoTo_AfterUpdate()
  2.     If (txtGoTo & vbNullString) = vbNullString Then Exit Sub
  3.     Dim rs As DAO.Recordset
  4.     Set rs = Me.RecordsetClone
  5.    rs.FindFirst "[AssetID]=" & txtAssetID
  6.  
  7.  
  8.     If rs.NoMatch Then
  9.         MsgBox "Sorry, no such record '" & txtGoTo & "' was found.", _
  10.                vbOKOnly + vbInformation
  11.     Else
  12.         Me.Recordset.Bookmark = rs.Bookmark
  13.     End If
  14.     rs.Close
  15.     txtGoTo = Null
  16.  
  17. End Sub
  18.  
Apr 5 '12 #7

Rabbit
Expert Mod 10K+
P: 12,357
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.
Expand|Select|Wrap|Line Numbers
  1. "[Asset ID]='" & txtAssetID & "'"
Apr 5 '12 #8

P: 16
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...
Apr 5 '12 #9

Rabbit
Expert Mod 10K+
P: 12,357
The quotes is to tell the SQL engine that what's inside the quotes is a string.
Expand|Select|Wrap|Line Numbers
  1. "[AssetID]='" & txtGoTo & "'"
If txtGoTo is ABC then the SQL engine will see this
Expand|Select|Wrap|Line Numbers
  1. [AssetID]='ABC'
Without the quotes, it sees this
Expand|Select|Wrap|Line Numbers
  1. [AssetID]=ABC
The quotes tell it that it's a string.
Apr 5 '12 #10

P: 16
@Rabbit
Ah! I see the single quote is actually for the last half of the line... gotcha... trying that now.
Apr 5 '12 #11

P: 16
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!
Apr 5 '12 #12

Rabbit
Expert Mod 10K+
P: 12,357
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.
Apr 5 '12 #13

Post your reply

Sign in to post your reply or Sign up for a free account.