Opening one form based on multiple criteria delected in another form (1 Viewer)

[SteveD]

Hello everybody,

I am a little stuck on how to go about a particular task.. I have a form with several combo boxes and text boxes for user entry. What I would like to do is have a user go in and select the different values from the different combo boxes + enter values in text boxes, then press a command button that would open another form filtered with the user's selections and values they have entered in the text boxes. I want the user to have the option of leaving some combo boxes/text boxes blank to return all records for that field.
I have been able to do this with one combo box using the following simple code attached to the 'on click' event of a command button:
.........................................
Dim stLinkCriteria As String
Dim stDocName As String

stDocName = "Form3"
stLinkCriteria = "[Customer Name]=" & "'" & Me![Customer] & "'"

DoCmd.OpenForm stDocName, ,,stLinkCriteria
..........................................
I am unsure of how I would do this for a form with multiple combo boxes/text boxes.
Does anyone have any suggestions/samples??


Thanks in advance,

SteveD

 

[D-Fresh]

Just run through all of your controls and build the string...

stLinkCriteria = ""

if not isnull(me![Customer]) then stLinkCriteria = stLinkCriteria & " [Customer Name]=" & "'" & Me![Customer] & "'"

if not isnull(me![CustomerID]) then stLinkCriteria = stLinkCriteria & " [CustomerID]=" & "'" & Me![CustomerID] & "'"

And so on for each control. This way, if the field was left blank, it won't filter by that particular field. Hope this helps.

Doug

 

[charityg]

You need to go a step further when combining criteria by using an "AND" operator

if not isnull(me![Customer]) then stLinkCriteria = stLinkCriteria & " [Customer Name]=" & "'" & Me![Customer] & "' And "

After all possible criteria is covered use the left function to wipe out the last "AND"

strLinkCriteria=left(strLinkCriteria, len(strLinkCriteria)-4)

 

[D-Fresh]

oh yeah, forgot about that... Always the little things... hehe.. Thanks Charity

 

[charityg]

It's what we're here for. Believe me...I've posted code missing endifs and end selects on MANY occasions. After all, I don't think any of us proclaims to be perfect, although Pat Harman appears to be pretty darn close!

 

[SteveD]

Thanks for all your help people,
Just a quick query-could you explain why the left function is needed at the end?

 

[SteveD]

Thinking about the advice given......
Would I only be able to link one field?
How do I go about covering all the possibilites that a user might select-for example if you have 6 combo boxes/text boxes and want to be able to bring up filtered records based on any combination of combo boxes/text box values. Is there a better way of doing what I want to achieve?

Thanks,

SteveD

 

[D-Fresh]

Steve, the left function at the end will get rid of the final "And" in your criteria string.. The code that myself and Charity wrote will loop through each of your text boxes and check if it is null.. If it is, then it will not include the field in the criteria(It will find all values for that field). If it is not null, then it will concatenate it to the criteria string and put an "and" after it. This will happen for each text box you put into the above code... As I said before, the left statement at the end, will chop off that last "and" statement that is concatenated to your criteria string. Hope that explains it for you.

Doug

 

[SteveD]

Thanks for your help Doug (and the charitable G person)!

 

[SteveD]

Thanks again to the people who helped me with this, I've only just got round to finishing it off, but it works OK
I just like to have a couple of enhancements to it:-
1. If a user does not select any criteria then I'd like a msgbox to appear saying 'no criteria entered..' At the moment I get 'run time error 5-Invalid procedure call or argument'

2. If the user selects criteria that returns no records, then I'd like a msgbox to apppear. I have put some code on the 'on load' event of the form I am linking to-the message apears but after this message I also get a 'run time error 2501-the open form action method was cancelled' Do you know how I can stop this message appearing?

3. On a couple of the text boxes on the form I'd like the linking criteria to be greater than the value in the textbox not equal to it. Can this be done? I've tried changing the = to > but this doesn't seem to work. Do you have to format the code differently if it is a numeric field instead of text?

If you can help with this then I would be a very happy man!

 

[charityg]

To answer your questions in reverse order
3. Yes, if your comparitive value is numeric the string is formatted differently. You don't need single quotes for numbers, so:

if not isnull(me![CustomerID]) then stLinkCriteria = stLinkCriteria & " [CustomerID]>" & Me![CustomerID]

2. Instead of using a cancel event for the onNoDate, use Docmd.close asform, "frmName"

1. In the onclick event of the button you are using to open the form, after all of the code that sets the criteria use something like:
if stLinkCriteria="" then
msgbox "YO! Select something!"
else
open the form
endif

Good Luck!