Help!! Search Form (2 Viewers)

vbaInet

AWF VIP
Local time
Today, 15:25
Joined
Jan 22, 2010
Messages
26,374
It's nothing to to do with your code, it's in your query. There could be a field which isn't related to any of the tables in the query or the way you wrote the query makes Access think it's a paramater. Paste the sql of the query that you think is showing this error on here.
 

lee_gti

Registered User.
Local time
Today, 15:25
Joined
Jan 28, 2010
Messages
20
It's nothing to to do with your code, it's in your query. There could be a field which isn't related to any of the tables in the query or the way you wrote the query makes Access think it's a paramater. Paste the sql of the query that you think is showing this error on here.

im not sure what you mean by "SQL" but when i press the debug button it highlights this line of code:-

Me.Archive_subform.Form.FilterOn = True

Lee
 

vbaInet

AWF VIP
Local time
Today, 15:25
Joined
Jan 22, 2010
Messages
26,374
Zip your db and attach it. I will have a quick look.
 

lee_gti

Registered User.
Local time
Today, 15:25
Joined
Jan 28, 2010
Messages
20
hi, thanks for doing this for me.

At the same time is there a way of making the search less specific (e.g. name like "smi" rather than entering smith?)

thanks
 

Attachments

  • Archive.zip
    41 KB · Views: 87

vbaInet

AWF VIP
Local time
Today, 15:25
Joined
Jan 22, 2010
Messages
26,374
I've done two of the search boxes as an example and put some 1 or 2 things for you to consider. Think about what how does the user undo the filters if they want to see all the results back. You actually used the right word for the Like search. You'll understand what I mean when you look at the code.

Also, your problem was that you didn't use the correct names in your search criteria. E.g. you put "PropertyofFile", but the actual name on your subform is "Property of File". Notice the spaces. The search names must match exactly.

Also enclose field names in square brackets:)

Attached!
 

Attachments

  • Archive.zip
    22.9 KB · Views: 96

smig

Registered User.
Local time
Today, 17:25
Joined
Nov 25, 2009
Messages
2,209
and it's better to avoid spaces in names of forms, fields.... everything :D
 

vbaInet

AWF VIP
Local time
Today, 15:25
Joined
Jan 22, 2010
Messages
26,374
and it's better to avoid spaces in names of forms, fields.... everything :D

Absolutely smig. It's not adviceable in any programming or software dev environment.
 

lee_gti

Registered User.
Local time
Today, 15:25
Joined
Jan 28, 2010
Messages
20
hi, i have been looking at the lines of code and i see that you have entered an "else" command.

but when using the database i cant actually see what this is doing.

and you mention how to undo the filters but i really dont know how to do that.

is there a way of setting up a button that will just clear all searches and bring the database back to its original look?

Lee
 

vbaInet

AWF VIP
Local time
Today, 15:25
Joined
Jan 22, 2010
Messages
26,374
That else statement was put in there for you to figure out ;) It is one way of showing all the records. Yes you could create a button, create a function to go through all the text boxes setting their value to this "" and set the FilterOn property to No.

Do you get the idea?
 

lee_gti

Registered User.
Local time
Today, 15:25
Joined
Jan 28, 2010
Messages
20
hi, no i really dont get it. i feel really stupid as i did all this when i was at sixth form but it has all gone out of my head now.

with the "like" coding it seems to only search for the first charaters or so, is there a way to make it search the whole line (e.g property address like high street)?

Lee
 

vbaInet

AWF VIP
Local time
Today, 15:25
Joined
Jan 22, 2010
Messages
26,374
Notice there's a wildcard character at the end of that Like statement, an asterisk. Have a quick read on what that does and I'm sure you'll be able to figure it out.

Re your past experience, I also did this as an A-level project hehe!!

See attached.
 

Attachments

  • Archive.zip
    24.5 KB · Views: 83

lee_gti

Registered User.
Local time
Today, 15:25
Joined
Jan 28, 2010
Messages
20
Hi, i have looked up wildcards and it appears i cannot get the result i want.

I found on this website (http://www.fontstuff.com/access/acctut06.htm) that the wild card would not be able to find a second word (e.g. Yor* would find York, Yorkshire and Yorktown but not New York).

do you know if there is anyway around this?

I have edited your most recent version so that they all work using the Like command.

Thank you so much for all your help so far :)

Lee
 

Attachments

  • Archive.zip
    28.1 KB · Views: 92

vbaInet

AWF VIP
Local time
Today, 15:25
Joined
Jan 22, 2010
Messages
26,374
You're welcome. Put the star before the field and it should work.

* Yor * will find something like New York
 

lee_gti

Registered User.
Local time
Today, 15:25
Joined
Jan 28, 2010
Messages
20
with the wildcard is there a way of embedded that into the coding or would it always have to be manually entered when searching?

and finally...is there a way of making only the search engine visible to the user (basically to run like a program rather than having to open up access and then open the search).

Lee
 

vbaInet

AWF VIP
Local time
Today, 15:25
Joined
Jan 22, 2010
Messages
26,374
I was referring to your code. Put the asterisk after the first single quote:

... Like '*" & Text1.value & "*'"

You can have any form as your default form when Access starts up. Investigate two things, Startup form and How to create an Access runtime application (if it applies).
 

lee_gti

Registered User.
Local time
Today, 15:25
Joined
Jan 28, 2010
Messages
20
hi,

I ended up using an access startup form as it was the easy option.

thank you so much for all your help.

Lee
 

lee_gti

Registered User.
Local time
Today, 15:25
Joined
Jan 28, 2010
Messages
20
I have been looking at my form and i have now noticed that there is annoying bar (circled in red) that doesn't do anything and i cant find the option to remove it:-



Does anyone know a quick way to remove it or is it because of where it is placed?

Lee
 

vbaInet

AWF VIP
Local time
Today, 15:25
Joined
Jan 22, 2010
Messages
26,374
Open your subform in design view, and under the Format tab look for Record Selectors, then select No.

By the way, it does something. It points to the user the current record they are on.;)
 

lee_gti

Registered User.
Local time
Today, 15:25
Joined
Jan 28, 2010
Messages
20
Thanks...sorted that problem.

now I have noticed a lot of grey space after the last list on the subform, is there a way of stopping this so you can only scroll left to the end of the data rather than continuing into oblivion?

Lee
 

Users who are viewing this thread

Top Bottom