Using Text box as search function (1 Viewer)

[access17401]

Good morning,

I have created a search function using a text box and command button bellow. This searches and returns from table according to the URN.

Private Sub Command200_Click()
If (txtGoTo & vbNullString) = vbNullString Then Exit Sub
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[URN]=""" & txtGoTo & """"
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

I have a very large database and searching for the URN may have 20 results so I want to create a combo search also using 'AppelID' and textbox "txtAppeal"

So i am then searching the database by URN and AppealID, I have been trying to update the code above to include this, but have had no success.

Would someone be able to help me work out how to do this? Thank you.

 

[pbaldy]

What does your attempt look like? What's the data type of the other field?

 

[arnelgp]

you might want to take a look
at this and adapt the code
to your database.

 

[access17401]

Thanks I have seen the combo method through my research. I just think I should be able to find a way to do it through 2 text boxes.

So say I have a table which is three columns AppealID, URN and Year. The urn might be present 12 times but the appealid will differentiate. i should be able to input the first two and through a command find the third. The place I work for uses some old school database programs such as ffenics and it seems that some standard things are difficult to achieve in Access (mind you i am still very new to this area).

I also figure that I have made a login form that inputs username and password, it identifies username and confirms corresponding password from table and then shows username on all forms during the session, this same logic should be able to be used in this instance. Input URN, correspond AppealID show year.

I will post some of attempts when i get to work.

Thanks for the responses.

 

[Minty]

The reason to steer you towards a combo is simply that by restricting your user to search only for available field values, you simplify the entire process.

If you only make the combo show the unique URN's, then second combo would then only allow the 12 Appeal ID's to be selected. Thee is no point searching for a non existent record. Your user can't mistype the search item and then wonder why it hasn't appeared.

 

[access17401]

I do understand that, for most of the records we will input in this database the user will scan in the URN and then scan in AppealID via barcodes, the year field that i am aiming to populate is purely a visual double check

 

[Pat Hartman]

Using the Find method is very old school and will not work well if you find that you need to convert to SQL Server or other RDBMS.

Your form will use two combos as described in the cascading combo example.

Then the form that displays the returned records, the query will reference those two combos to filter the records returned.

Select ...
From ...
Where (fldA = Forms!yourform!cboFldA OR Forms!yourform!cboFldA is Null)
AND (fldB = Forms!yourform!cboFldB OR Forms!yourform!cboFldB is Null)

The form will open empty since the combos will be empty. In the AfterUpdate event of each combo, use one line of code.

Me.Requery

That will filter the records shown by the form.

 

[access17401]

I have implemented example as suggested thanks.

I did have a thought though, just in the interest of learning I tried using Dlookup. Would this work?

I have received error 2465, it might be in my code

Private Sub txt_FirstName_Click()
txt_FirstName = DLookup("[ADP_FirstName]", "Master File", "[URN]='" & [Forms]![Cash].[Form].[txt.URN] & [“’] And [AppealID] = [’”] & [Forms]![Cash].[Form].[txt.AppealID] & [“’”])
End Sub

So what I was trying to say is on click (i wanted it to just auto appear) that first name = ADP_First Name from table 'Master File' when txt.URN = URN from 'Master File' and txt.AppealID = AppealID from 'Master FIle'

All fields are in form 'Cash'

 

[Mark_]

To make it a little easier, rather than going [Forms]![FormName] you can use Me. in your references.

Re: Using Text box as search function
I have implemented example as suggested thanks.

I did have a thought though, just in the interest of learning I tried using Dlookup. Would this work?

I have received error 2465, it might be in my code

Private Sub txt_FirstName_Click()
   txt_FirstName = DLookup("[ADP_FirstName]", "Master File", "[URN]= [COLOR="DarkOrchid"]'[/COLOR]" & [COLOR="red"]Me.[/COLOR][txt.URN] & “[COLOR="DarkOrchid"]'[/COLOR] And [AppealID] = " & [COLOR="red"]Me.[/COLOR][txt.AppealID] )
End Sub

Also, why do you have quotes in brackets? [“’”] would be read as a field name. Likewise do you have an actual field called txt.URN?

Double check when and where you use quotes. For numbers you would insert the number. Is AppealID a text field or a numeric?

Having variables on screen with names like Txt.URN will confuse some things when you are trying to reference then unless you are very careful. Best to use a name like TxtURN to avoid confusion.

 

[access17401]

Not sure about the brackets, they weren't there, I should of checked after i pasted the code in.

The fields are there, i only use txt. for the field names as that is the suggestion i received when i started learning a month ago.

and AppealID is a text field

I keep getting Compile error, Expected list seperator of )

UPDATE I have removed all errors, but nothing appears in the lookup box

 

[access17401]

Sorted it out thanks for the pointers Mark, works perfect

 

[Minty]

Not sure about the brackets, they weren't there, I should of checked after i pasted the code in.

The fields are there, i only use txt. for the field names as that is the suggestion i received when i started learning a month ago.

Just as a follow up, I think the recommendation would have been to call a text box control txtYourField . Adding a . to a field name will cause you a lot of problems going forwards, and would not be advisable. It will also not scale as it would be illegal in SQL Server.

This is about as good a guide to naming conventions as I have seen. https://access-programmers.co.uk/forums/showthread.php?t=225837

Have a read.