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

How to create Search form for Access 2007 database.

P: 11
I have found Jim Doherty's "searchdemo.accdb" as well as "searchdemo.mdb" and have successfully run it on Access 2013 in the office.

However, when I run it on my Access 2007 at home, I have run into multiple problems. Same with "searchdemo.mdb".

1) When I enter "ringo" for first name even though it is in the table, and shows a count of one (1) in the txtFNCount text box, I get this message:
"No Person records which match the criteria you entered.",
even though mysource= SELECT * FROM qryPersoninformation WHERE Firstname Like "ringo*"

2) If I leave all the search fields blank or If I enter an asterisk in one field or two , it lists only the 3rd record, George's record.

I would appreciate any help or guidance I'm just stomped.
JC
Jan 3 '17 #1

✓ answered by jforbes

I downloaded the Sample Database and that is one strange problem. It should be working, but it doesn't. I checked everything that I could think of including Repairing, recreating the Form, Decompiling and re-compiling. Nothing worked.

You could try one of these fixes:
Expand|Select|Wrap|Line Numbers
  1. ...
  2.     'set the FILTER of the subform to the resultset
  3.     PleaseWait ("Searching database based on your criteria " & mycriteria)
  4.     Me!Submain.Form.Filter = mycriteria
  5.     Me!Submain.Form.FilterOn = True
  6.  
  7.     '  If no records match criteria, display message.
  8.     '  Move focus to reset button.
  9.     If Me!Submain.Form.RecordsetClone.RecordCount = 0 Then
  10.     PleaseWait ("")
  11. ...
Expand|Select|Wrap|Line Numbers
  1. ...
  2.     'set the recordsource of the subform to the resultset
  3.     PleaseWait ("Searching database based on your criteria " & mycriteria)
  4.     Dim oRst As dao.Recordset
  5.     Set oRst = CurrentDb.OpenRecordset(mysource)
  6.     Set Me!Submain.Form.Recordset = oRst
  7.  
  8.     '  If no records match criteria, display message.
  9.     '  Move focus to reset button.
  10.     If oRst.RecordCount = 0 Then
  11.     PleaseWait ("")
  12. ...


Original, for reference:
Expand|Select|Wrap|Line Numbers
  1. ...
  2.     'set the recordsource of the subform to the resultset
  3.     PleaseWait ("Searching database based on your criteria " & mycriteria)
  4.     Me!Submain.Form.RecordSource = mysource
  5.  
  6.     '  If no records match criteria, display message.
  7.     '  Move focus to reset button.
  8.     If Me!Submain.Form.RecordsetClone.RecordCount = 0 Then
  9.     PleaseWait ("")
  10. ...
Personally, I'm a big fan of Filtering the SubForm.

Share this Question
Share on Google+
9 Replies


jforbes
Expert 100+
P: 1,107
I don't know anything about that database. If you could post the code you are having trouble with, it would go a long way to helping us help you.
Jan 3 '17 #2

P: 11
Thank you for taking the time to help.

I could not see how to upload a database example so here is the link to the thread that has the file to download by Jim Doherty (the second example). https://bytes.com/topic/access/answe...-form-database

I think the problem is in the cmdSearch procedure, these lines:
Me!Submain.Form.RecordSource = mysource
‘ (RESULT: "SELECT * FROM qryPersoninformation WHERE Firstname Like "Ringo*"")
' If no records match criteria, display message.
' Move focus to reset button.
If Me!Submain.Form.RecordsetClone.RecordCount = 0 Then
‘ (THIS LINE KEEPS RETURNING ZERO RECORDS EVEN THOUGH THERE IS A RECORD WITH FIRST NAME RINGO)
Jan 3 '17 #3

jforbes
Expert 100+
P: 1,107
I downloaded the Sample Database and that is one strange problem. It should be working, but it doesn't. I checked everything that I could think of including Repairing, recreating the Form, Decompiling and re-compiling. Nothing worked.

You could try one of these fixes:
Expand|Select|Wrap|Line Numbers
  1. ...
  2.     'set the FILTER of the subform to the resultset
  3.     PleaseWait ("Searching database based on your criteria " & mycriteria)
  4.     Me!Submain.Form.Filter = mycriteria
  5.     Me!Submain.Form.FilterOn = True
  6.  
  7.     '  If no records match criteria, display message.
  8.     '  Move focus to reset button.
  9.     If Me!Submain.Form.RecordsetClone.RecordCount = 0 Then
  10.     PleaseWait ("")
  11. ...
Expand|Select|Wrap|Line Numbers
  1. ...
  2.     'set the recordsource of the subform to the resultset
  3.     PleaseWait ("Searching database based on your criteria " & mycriteria)
  4.     Dim oRst As dao.Recordset
  5.     Set oRst = CurrentDb.OpenRecordset(mysource)
  6.     Set Me!Submain.Form.Recordset = oRst
  7.  
  8.     '  If no records match criteria, display message.
  9.     '  Move focus to reset button.
  10.     If oRst.RecordCount = 0 Then
  11.     PleaseWait ("")
  12. ...


Original, for reference:
Expand|Select|Wrap|Line Numbers
  1. ...
  2.     'set the recordsource of the subform to the resultset
  3.     PleaseWait ("Searching database based on your criteria " & mycriteria)
  4.     Me!Submain.Form.RecordSource = mysource
  5.  
  6.     '  If no records match criteria, display message.
  7.     '  Move focus to reset button.
  8.     If Me!Submain.Form.RecordsetClone.RecordCount = 0 Then
  9.     PleaseWait ("")
  10. ...
Personally, I'm a big fan of Filtering the SubForm.
Jan 3 '17 #4

P: 11
Thank you for your help, however, I was not able to make it work. It is not clear for me, which lines in cmdSearch have to be replaced with these or completely deleted. Do I stop calling the PleaseWait() function?
Jan 4 '17 #5

jforbes
Expert 100+
P: 1,107
I've updated my last post to include the PleaseWait() function calls (Which I removed in my testing) and I highlighted the code that would change.
Jan 4 '17 #6

P: 11
Thank you jforbes for your excellent suggestion, but for some reason both examples did not work, using office2013.

The first set of codes just like the original did not create the record, however, it stopped showing me any records with a message that read: "No Member records which match the criteria you entered". I will explore which line triggered this message, but most likely it is not working.

The second set of code just like the original code did not generate an error but did not create the record, but it did increase the ID's autonumber by 1.

Thank you again, I am going to create all the tables and write all the codes from scratch, it might be something in the original database.
JC
Jan 9 '17 #7

jforbes
Expert 100+
P: 1,107
At this point I really don't understand why it wouldn't be working for you. I've tested it on both 2007 and 2016 (I no longer have 2003 available) and it works in both places.

I've attached the copy of what I messed around with so you could compare your version to the version I used. When testing, I commented out a lot of the stuff that shouldn't matter. I also changed the Form from Dialog to a Tab as working/developing with Dialogs can be troublesome.
Attached Files
File Type: zip SearchDemo_Modified.zip (143.3 KB, 49 views)
Jan 9 '17 #8

NeoPa
Expert Mod 15k+
P: 31,186
This may not help, but whenever I've come across similar issues they've been related to one of these things :
  1. Non-printing characters in the data somewhere.
  2. Characters which are similar to, but different from, that being searched for. For instance a character that has an accent or even one that looks similar like an 'o' or '0'.
  3. The database is corrupted.
To determine if this is the case I normally run a loop showing the ASCII values of the characters in the string.
Jan 9 '17 #9

P: 11
Thank you all for your help, I'm going to put a hold on this and do further search and testing. Thank you.
Jan 9 '17 #10

Post your reply

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