UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Search Form Help, Access 2010    
 
   
Surico
post Oct 10 2016, 06:52 PM
Post#1



Posts: 47
Joined: 22-August 16



Hey guys,

I have created a search form and a search box to find me an archive number when I type it in, the idea was to create something that pulls ALL results relatiing to what is in the search box. But it doesn't quite work as I would like but it's very close and I just need help finalising it.

Example i have 4 records saved with the number 2 inside it (2, 12, 21, 22) and currently when I type in "2" in only brings back 3 results (it brings back 2, 21, 22) and NOT 12. It seems to only bring results back starting with whatever I search for, when I want any result with the number 2 for example.

Can anyone please help?
Attached File(s)
Attached File  Archive_System.zip ( 206.84K )Number of downloads: 19
 
Go to the top of the page
 
RJD
post Oct 10 2016, 07:38 PM
Post#2


UtterAccess VIP
Posts: 7,834
Joined: 25-October 10
From: Gulf South USA


Hi: Modify your command to add a * before the search value. This will find the search value anywhere in the field rather than just at the first...

DoCmd.OpenForm "Search for File", acNormal, , "[Archive Number] like '*" & Me.TxtSearch & "*'"

You could use the ApplyFilter command rather than opening the form again, using the same Like approach. Actually, that is my preferred approach. But that is up to you how you approach this.

I typically also add an ALL button as well, to return the form to showing all records after filtering/limiting.

HTH
Joe
Go to the top of the page
 
Surico
post Oct 11 2016, 01:46 AM
Post#3



Posts: 47
Joined: 22-August 16



Thanks Joe!

What would the code be for the ALL button?

Cheers
Go to the top of the page
 
RJD
post Oct 11 2016, 10:37 AM
Post#4


UtterAccess VIP
Posts: 7,834
Joined: 25-October 10
From: Gulf South USA


Try...

DoCmd.ShowAllRecords

I think that's right - I am not in my office, just using my phone right now.

HTH
Joe
Go to the top of the page
 
Surico
post Oct 11 2016, 05:19 PM
Post#5



Posts: 47
Joined: 22-August 16



Thanks Joe, it works great!

I just had a thought, I would like there to be a message prompt to say "Sorry there is no number" if I search for a number not recorded.

Do you have any thoughts on the code required?

I really appreciate your help! smile.gif
Go to the top of the page
 
RJD
post Oct 11 2016, 05:38 PM
Post#6


UtterAccess VIP
Posts: 7,834
Joined: 25-October 10
From: Gulf South USA


Hi again: You could try something like this ...

CODE
Private Sub Search_Button_Click()
DoCmd.OpenForm "Search for File", acNormal, , "[Archive Number] like '*" & Me.TxtSearch & "*'"
If IsNull([Archive Number]) Then
MsgBox "There are no Archive Numbers containing " & Me.TxtSearch & ".", , "Warning"
DoCmd.ShowAllRecords
Me.TxtSearch = Null
End If
End Sub


HTH
Joe
Go to the top of the page
 
Surico
post Oct 11 2016, 06:24 PM
Post#7



Posts: 47
Joined: 22-August 16



Perfect!

I just had a thought I'm thinking it would serve my database best to have have a new search form with fields like "Archive Number" "Client Name" etc and whe I type in the archive number and I click on the search button and it opens up the form with the results I tried to use that code on the new form and it works except for when I enter a number that isn't there the error prompt doesn't work.

Can you help?
Attached File(s)
Attached File  Archive_System.zip ( 227.71K )Number of downloads: 5
 
Go to the top of the page
 
RJD
post Oct 11 2016, 06:58 PM
Post#8


UtterAccess VIP
Posts: 7,834
Joined: 25-October 10
From: Gulf South USA


Take a look at how the search is now done from your new search form. Look at the code behind the Search command button. Since you are referring to controls on multiple forms in the If block, the form has to be specified.

HTH
Joe
Attached File(s)
Attached File  Archive_System_Rev1.zip ( 73.59K )Number of downloads: 11
 
Go to the top of the page
 
Surico
post Oct 14 2016, 03:50 AM
Post#9



Posts: 47
Joined: 22-August 16



Thank you so much!!
Go to the top of the page
 
RJD
post Oct 14 2016, 10:55 AM
Post#10


UtterAccess VIP
Posts: 7,834
Joined: 25-October 10
From: Gulf South USA


You are very welcome. Glad that worked for you. thumbup.gif

Regards,
Joe
Go to the top of the page
 
Surico
post Oct 29 2016, 06:43 PM
Post#11



Posts: 47
Joined: 22-August 16



Hey,

I'm very confused, it was working fine but now it's not and I don't understand why, when I search for a number that is not saved the message prompt doesn't come up and it doesn't show all the records.
It's very confusing!

Is there a reason why it's not woking?
Attached File(s)
Attached File  Archive_System_Rev1.zip ( 73.59K )Number of downloads: 5
 
Go to the top of the page
 
RJD
post Oct 29 2016, 07:10 PM
Post#12


UtterAccess VIP
Posts: 7,834
Joined: 25-October 10
From: Gulf South USA


Hmmm ... I can't break it. I put in 999 and it told me there aren't any Archive Numbers containing 999 - then when I click on OK all the records are shown. (When I enter a good number, the correct record(s) are shown.

Procedure: From the main menu, I clicked on SEARCH FOR FILE, then on the Search for File form I entered 999 in the search box and got the correct message.

Is this the procedure you are using? The file you posted looks like the one I posted last time. Is this the one you meant to post?

HTH
Joe
Go to the top of the page
 
Surico
post Oct 29 2016, 07:40 PM
Post#13



Posts: 47
Joined: 22-August 16



Sorry Joe, that appears to be the wrong one, I have attached the updated one that ain't working.
Attached File(s)
Attached File  Archive_System_Rev1.zip ( 145.62K )Number of downloads: 2
 
Go to the top of the page
 
RJD
post Oct 29 2016, 08:29 PM
Post#14


UtterAccess VIP
Posts: 7,834
Joined: 25-October 10
From: Gulf South USA


Hi: Since you changed the ability of the form to add a new record to No, there is nothing for the code to check to see if the Archive Number is now Null. So, the process will have to be changed a bit ...

CODE
Private Sub Search_Button_Click()
If DCount("*", "[Add New File]", "[Archive Number] like '*" & Me.TxtSearch & "*'") > 0 Then
DoCmd.OpenForm "Search for File", acNormal, , "[Archive Number] like '*" & Me.TxtSearch & "*'"
Else
MsgBox "There are no Archive Numbers containing " & Forms!Search!TxtSearch & ".", , "Warning"
DoCmd.ShowAllRecords
Me.TxtSearch = Null
End If
End Sub


See the revised db attached. It addresses the Archive Number only. I assume you will develop the other search part later.

HTH
Joe
Attached File(s)
Attached File  Archive_System_Rev2.zip ( 147.06K )Number of downloads: 16
 
Go to the top of the page
 
Surico
post Oct 29 2016, 08:35 PM
Post#15



Posts: 47
Joined: 22-August 16



Did I? not sure how on earth I did that LOL!

Would you recommend I put it back to yes or work from this revised DB?
Go to the top of the page
 
RJD
post Oct 29 2016, 08:39 PM
Post#16


UtterAccess VIP
Posts: 7,834
Joined: 25-October 10
From: Gulf South USA


I'd work with the new code I sent you. The other approach was just an easy convenience based on how you had set the form properties. The new approach does not rely on the form properties, but just the record count before you even get to the form. So you can now change the properties any time and it should still work just fine.

You could also use the RecordSetClone approach for the opened form and get the record count, but I tend to use the DCount since it is before I even get to the form.

HTH
Joe
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 11:55 AM