Im very new to Access (using version 2002), and Im trying to figure out how to search my table with a text field...
For simplicity, say I have a table which has columns "Name" and "Age"
I know that I can create a form and make a text field, but I have no idea
how to make it so what I type in that text field is what I search for.
What I ultimately hope to have is a form that looks like this:
<combo box> <text field> <submit search button>
where the combo box has the columns (in this case "Name" and "Age") and
depending on what I used for my combo box, the text field will search that
column for the value I entered.
If someone could help me, I would greatly appreciate it!
Thanks for the reply. So far what you said is correct. I also wanted a button to submit the search. I know some SQL since Ive worked very briefly with it, but I dont know any VBA ...
but Im trying my best to learn.
Private Sub txtFind_AfterUpdate()
Dim Bkmrk As Variant
Dim Criteria as string
If nz(txtFind,””)=”” then exit sub
Bkmrk = Me.Bookmark
If cboSerchTyp = “Name”
Criteria = “NameFieldName Like ‘*” & txtFind & “*’”
Criteria = “AgeFieldName = ‘” & txtFind & “’”
If Me.RecordsetClone.NoMatch = True Then
Me.Bookmark = Bkmrk
MsgBox "No record found!", vbExclamation, “Title”
If isnumeric(txtFind) then
Me.Bookmark = Me.RecordsetClone.Bookmark
MsgBox “Age must be numeric!” !", vbExclamation,”Title”
Just an idea
Usually you would normally store birthdays in the DB as Age changes each year!!
I assumed you have a combo control with two rows containing “Name” and “Age” to indicate which field to search. Whichever is selected gives the combo control a value of either “Name” or “Age”.
This can then be check ie
If cboCotrol = “Name” then etc. and then use the appropriate field name to constructed search criteria as required, as indicated in the previous code.
However if the comboControl contains the actual FieldName then it is even simpler
Then the Criteria would be
Criteria = cboSearchType & “ = “ & txtFind
However if Age and Name fields are not the same types ie. both Text, then the Criteria will need different syntax, also as previously indicated. Therefore you will need to know which field is being searched.
Sorry about the typo in the code but I was interrupted when editing (work does get in the way!).
May be I have interpreted your requirements, if so, sorry.