Qbf, macros and more!!

[MOTOWN44]

Hello all hopfully you can help me with somthing that i have been racking my brain with for days now

My table data (table called IACS TABLE) is displayed in a form called FrmCORE with in this form I need to be able to filter the data by multiple factors which are chosen from combo boxes called

PriorityCombo
CompletedCombo
NotifiedCombo
StatusCombo,
SiteCloseCombo
OwnerCombo

I can filter by each one individually no problems with an applyfilter macro with the Where condition as

[IACS TABLE][Priority] = Forms![FrmCORE]![PriorityCombo]

the priority bit is the column name with in IACS TABLE.

I can also filter by 2 factors or more like this

[IACS TABLE]![Priority] = Forms![FrmCORE]![PriorityCombo] AND [IACS TABLE]![Task Status] = Forms![FrmCORE]![StatusCombo] AND etc etc

but this wont work if I only need to filter by 1 factor as the string is looking for 2 or more factors.

There is a function called query by form (QBF) which I have been reading up on but I cant seem to get it to work

SELECT [IACS TABLE].*
FROM [IACS TABLE]
WHERE (((IACS TABLE.Priority)= Forms.FrmCORE.PriorityCombo),(IACS TABLE.Task Status)= Forms.FrmCORE.StatusCombo) etc

Or something like that anyway

I think you then apply this to a command button but I’ve been trying for over a day now and I’ve completely run out of ideas. Im probably using macros completely wrong here which is why it isn’t working!!

I have also been messing with macros doing variants of the following;

Like IF(IsNull([Forms]![FrmCORE]![PriorityCombo]),"TRUE",[IACS TABLE]![Priority]=[Forms]![FrmCORE]![PriorityCombo]) AND Like IF (IsNull([Forms]![FrmCORE]![StatusCombo]),"TRUE",[IACS TABLE]![Task Status]=[Forms]![FrmCORE]![StatusCombo])

Also with out any luck

Basically I need to be able to use multiple filters but with some of the filters having Null values if they are not needed.

I am also a self confessed idiot so if you could go slowly that would be brilliant J

Thank you

PS unfortunatly i cannot post a sample of the DB as it contains sensitive data and the network at work wont permit uploads

 

[MOTOWN44]

thread bump!

 

[Core]

One way I would go about this is...

Changing the record source of the form to be based on a query which has the same fields as the table.

In the query under each field that need to be filtered change the criteria to point to the combo box on the form, ie:

=[Forms]![FormName]![ControlName]

FormName = name of form (frmCORE)
ControlName = Name of Control (PriorityCombo)

On the drop down boxes include an entry for "*" and make this the default, (this means search for any text/value) and so will show all results.

property for each combo change the event On Change and make a macro with the "Requery" command, with the form name in the property.

What this will do, is tell the form to recheck the query it is based on each time a combo box has been changed, and as the query is based on the value of the combo boxes, it will filter for that information.

I hope this helps.

 

[MOTOWN44]

thanks for you help

but when i select a combo box it brings up the popup box as if i had written [paramater] in the criteria of the query. so i have to type the criteria rather than using the combo box.

also the * function i have put in the Or section of the query along with the =[Forms]![FormName]![ControlName] you said to put in.

so i have =[Forms]![FormName]![ControlName] Or Like "*" i dont know if thats what you meant?

* is also the default value of the combos.

as it is it work as a searchbut i still have to enter a value in to each combo before it displays any results.

have i followed the instructions wrong?

cheers

 

[Core]

The query should be pointed to the form control and should not prompt the user for information.

If it does then the criteria is not set correctly, double check for a simple spelling mistake. Just to double check, when I said =[Forms]![FormName]![ControlName] you have to replace the FormName and the ControlName with the names of the form and the control.

About the =[Forms]![FormName]![ControlName] Or Like "*"

This would not work as you are giving it a way out. So you are saying check this field of Any result and also check it for this result. No wither or not it finds your result does not matter as it will just bring up every record as they all match "*".

You could try Like [Forms]![FormName]![ControlName]

None of the criteria should be set in the OR row as you want it to search for every criteria from every drop down box, if the user wants to omit any criteria he simply selects the * option from the drop down (combo)

Have you changed the Record Source on the properties of your form to the query and not the table?

 

[MOTOWN44]

IT WORKS! thank you so so much

can i be cheeky and ask 1 tiny thing extra?

is there a way of resetting the combos back to * with a command button or something similar.

when i was using a form rather than a form pointing at a query, i used the showall macro, this showed all the records but the combo boxes still had the value i had searched by in them.

basically is there a way of reseting the query?

thank you agian for all your help

 

[Core]

IT WORKS! thank you so so much

can i be cheeky and ask 1 tiny thing extra?

is there a way of resetting the combos back to * with a command button or something similar.

when i was using a form rather than a form pointing at a query, i used the showall macro, this showed all the records but the combo boxes still had the value i had searched by in them.

basically is there a way of reseting the query?

thank you agian for all your help

Don't worry, I am still a beginner myself however this site has been a great help. Glad I could give a little back.

Now I am sure there is a method to set the value of combo box to an item in the list, but im not sure of it.

You could try though:

Combobox1.value = "*"
Combobox2.value = "*"
me.form.requery

put that in the on click event for the button