Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-10-2017, 05:17 PM   #1
access17401
Newly Registered User
 
Join Date: Aug 2017
Posts: 23
Thanks: 7
Thanked 1 Time in 1 Post
access17401 is on a distinguished road
Using Text box as search function

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.

access17401 is offline   Reply With Quote
Old 10-10-2017, 06:55 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 30,586
Thanks: 8
Thanked 3,602 Times in 3,546 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Using Text box as search function

What does your attempt look like? What's the data type of the other field?
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 10-10-2017, 08:14 PM   #3
arnelgp
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 4,039
Thanks: 47
Thanked 1,367 Times in 1,295 Posts
arnelgp has a spectacular aura about arnelgp has a spectacular aura about arnelgp has a spectacular aura about
Re: Using Text box as search function

you might want to take a look
at this and adapt the code
to your database.
Attached Files
File Type: zip AutoCompleteCombo.zip (57.5 KB, 13 views)

__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
Old 10-11-2017, 03:31 AM   #4
access17401
Newly Registered User
 
Join Date: Aug 2017
Posts: 23
Thanks: 7
Thanked 1 Time in 1 Post
access17401 is on a distinguished road
Re: Using Text box as search function

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.
access17401 is offline   Reply With Quote
Old 10-11-2017, 03:41 AM   #5
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 4,419
Thanks: 88
Thanked 1,206 Times in 1,180 Posts
Minty will become famous soon enough Minty will become famous soon enough
Re: Using Text box as search function

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.
__________________
A little thanks goes a long way
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

(Please use the scales on the left if we have helped!) Mark threads as Solved once you have an answer.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 10-11-2017, 03:58 PM   #6
access17401
Newly Registered User
 
Join Date: Aug 2017
Posts: 23
Thanks: 7
Thanked 1 Time in 1 Post
access17401 is on a distinguished road
Re: Using Text box as search function

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
access17401 is offline   Reply With Quote
Old 10-11-2017, 03:59 PM   #7
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 25,770
Thanks: 12
Thanked 1,079 Times in 1,025 Posts
Pat Hartman is just really nice Pat Hartman is just really nice Pat Hartman is just really nice Pat Hartman is just really nice Pat Hartman is just really nice
Re: Using Text box as search function

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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 10-12-2017, 03:39 PM   #8
access17401
Newly Registered User
 
Join Date: Aug 2017
Posts: 23
Thanks: 7
Thanked 1 Time in 1 Post
access17401 is on a distinguished road
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]='" & [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'
access17401 is offline   Reply With Quote
Old 10-12-2017, 03:57 PM   #9
Mark_
Newly Registered User
 
Join Date: Sep 2017
Posts: 118
Thanks: 1
Thanked 17 Times in 17 Posts
Mark_ is on a distinguished road
Re: Using Text box as search function

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

Code:
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]= '" & Me.[txt.URN] & “' And [AppealID] = " & Me.[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.
Mark_ is offline   Reply With Quote
The Following User Says Thank You to Mark_ For This Useful Post:
access17401 (10-12-2017)
Old 10-12-2017, 04:11 PM   #10
access17401
Newly Registered User
 
Join Date: Aug 2017
Posts: 23
Thanks: 7
Thanked 1 Time in 1 Post
access17401 is on a distinguished road
Re: Using Text box as search function

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

Last edited by access17401; 10-12-2017 at 04:36 PM.
access17401 is offline   Reply With Quote
Old 10-12-2017, 05:33 PM   #11
access17401
Newly Registered User
 
Join Date: Aug 2017
Posts: 23
Thanks: 7
Thanked 1 Time in 1 Post
access17401 is on a distinguished road
Re: Using Text box as search function

Sorted it out thanks for the pointers Mark, works perfect
access17401 is offline   Reply With Quote
Old 10-13-2017, 12:00 AM   #12
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 4,419
Thanks: 88
Thanked 1,206 Times in 1,180 Posts
Minty will become famous soon enough Minty will become famous soon enough
Re: Using Text box as search function

Quote:
Originally Posted by access17401 View Post
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/for...d.php?t=225837

Have a read.

__________________
A little thanks goes a long way
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

(Please use the scales on the left if we have helped!) Mark threads as Solved once you have an answer.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
access17401 (10-15-2017)
Reply

Tags
querey , search , search box

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Instant Search Box In Access Form (Search Function in Navigation Bar) vdanelia Forms 44 08-26-2016 03:44 AM
Search Text Box with Option Box as criteria for the search hemolytucus Forms 3 03-05-2014 12:23 AM
Text Box search on ID and populate other text boxes in same form srinutsrao Forms 2 11-13-2013 07:55 PM
search function and update function beanbeanbean Modules & VBA 4 11-03-2008 12:30 PM
Text Box & Combo Search Function expublish Forms 3 03-05-2002 01:30 PM




All times are GMT -8. The time now is 06:30 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World