Need help with creating a customized search form

[teephish]

Hello,

I'm currently in the process of creating a small access database and I'm having some problems with creating a customized search. I would like the user to be able to search a record by last name or street address. I have two questions:

1. I am currently using the built in "find and replace" dialog box, which works fine for the most part, but I need to be able to add some code to make the search more customized. I'm new to access, so my VBA skills are still limited. I have been unable to find code examples that are similar to the "find and replace" dialog box. Does anyone have code examples that might be similar?

2. I also tried adding two combo box search options on my form (one for last name and one for street address). The last name search works fine, but to do a search by street address, I must enter the street number first, or I get an error. I would like to be able to enter the street name (or better yet, part of the street name) first. Here is the code that I;m currently using:

Me.RecordsetClone.FindFirst "[ID] = " & Me![Addressdropdown]
Me.Bookmark = Me.RecordsetClone.Bookmark

Any help or code examples would be appreciated.

teephish

[blad3runn69]

hey there
you can find an excellent tutorial/example provided by allen browne here
how to build a search form which can handle multiple criteria

[Jim Doherty]

Welcome to the scripts! and fun honing your VBA skills.

Have a look at this example db I knocked up for newbies to give an idea on one technique that provides for multi field searching in combination with a wildcard thrown in if needed. It is fairly complex to look at particularly if you are new but as you develop your skills and if you are persistent and break each code segment down and follow it through you will see one or two other helpful things.

Included is the ability to output the resultset to excel and also output a listboxs rowsource using an intermediary dialog box, set a reports recordsource on the fly based on the search return, open a form to a particular form record from an existing form record, display a custom please wait message centrally on screen, use conditional formatting based on a row value

Regards

Jim :)

Footnote: Whilst the attachment works completely fine in Access versions prior to 2007 Having since tested the attachment out on Access 2007 I note that subform recordsources when set on the fly fail to set properly and only returns the first record as opposed to the expected continuous list.

[blad3runn69]

hey Jim, lotta stuff going on there, thank you for sharing! :)