stop query run on load or allow to run on null string

[rivereridanus]

Hi,



I need help to make a form load without running the query attached to it, or by allowing the query to run on null values.

I have a form called itemqueryform2. It has a combo box (combo2) for searching by restaurant, a text box (text4) that I will use to search by keyword, and a subform (itemquery2 subform) for displaying the results of the query. A button (runquerybutton) has the following vba code. It works.

Private Sub runquerybutton_Click()
Me.itemquery2_subform.Requery
End Sub

The query, itemquery2 is the following:

SELECT item.id, item.restaurant, item.itemname, item.section, item.type, item.subtype
FROM item
WHERE (((item.itemname) Like "*" & Forms!itemqueryform2!text4 & "*") And ((item.restaurant)=Forms!itemqueryform2!combo2));

However, when I embed itemqueryform2 in another form called order, order prompts for the query values Forms!itemqueryform2!combo2 and Forms!itemqueryform2!text4 upon opening, and then does not display.

Can I either stop this query running at the beginning or put something in the query to allow it to run when the text box and combo box only have the empty string? Default values for combo2 and text4 are "" empty strings, by the way.

Thank you all so much!!

[Rod]

OK. There are probably a number of ways to get round this 'problem.' Before describing a possible way may I state my understanding of the situation. This ensures I am not answering the wrong problem.

You have nested subforms to two levels. The master/parent form is called 'order.' (You do not name the control that displays the intermediate form but for now this is not important.)

The intermediate form is called 'itemqueryform2.' This form contains a command button called 'runquerybutton.' Clicking this button requeries the lowest level (child) subform. The control that displays this child subform is called 'itemquery2_subform.' The intermediate form contains a combo box called 'combo2' and a text box called 'text4.' The values in these controls are used for filtering the content of the child subform.

You don't say what the lowest level form is called but for now this is not important.




Assuming the above you need to do two things:

1. Remove the WHERE clause from the SQL behind the child subform. This eliminates the prompts issued by Access thinking you have named parameters.
2. Change the code behind 'runquerybutton' to be:

 
Me!itemquery2_subform!Form.Filter = "itemname Like ""*" & Me!Text4 & "*"" AND restaurant = """ & Me!combo2 & """"
Me!itemquery2_subform!Form.FilterOn = True

I think I've got the quotes right in the first line (but I wouldn't be surprised if I haven't). I think FilterOn triggers a requery - can't remember offhand.

You could be more fancy testing for "" before forming the filter.

May I encourage you to start following a naming standard.