Form works once, but not again?

[92hilux]

I have created a Form, with 8 check boxes that create a dynamic query that will allow the user to search a table for matching records.

The search works Wonderfully the first try... BUT... any additional search is returned with additional results (not correct).

If i close the form and open it again, it works correctly, but again only is useable once!

What would cause this error??

 

[beetee]

I would suggest you use the debugger, print out the dynamically - created query, and see if something has gone wrong with it.

More information is needed to solve your problem.

 

[92hilux]

I hate to ask a stupid question... BUT./.
how do you run the debugger?

 

[ajdesalvo]

Is the Form bound to a table, or are the check box fields unbound.

If they are bound, you may have to save the changes before running the query for the report:

DoCmd.RunCommand accmdSaveRecord

then the query code. Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[92hilux]

AJDESALVO:

I checked the Form props.. and.. it is not bound, nor are the check boxes.

code i used was..

[Forms]![catagorie]![checkBoxName]

in the OR Criteria fields in the query.

Again to all.. any help is greatly appreciated.

 

[ajdesalvo]

Okay, can you give us the sql code, maybe I can see something there! Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[92hilux]

This is the code behind the SEARCH button on my form.
the WHERE field contains all the optional query variables.
Each catagorie is represented by a check box in the Form.

>>
Private Sub Command18_Click()

DoCmd.OpenReport "rptSelection1", acViewPreview, qrySelection1, CategoryDesign = yes Or CategoryOilGas = yes Or CategoryCatalogue = yes Or CategoryCodes = yes Or CategoryElectrical = yes


End Sub
>>

 

[ajdesalvo]

Add some double quotes around the WHERE clause would be one thing.

DoCmd.OpenReport "rptSelection1", acViewPreview, qrySelection1, "CategoryDesign = yes Or CategoryOilGas = yes Or CategoryCatalogue = yes Or CategoryCodes = yes Or CategoryElectrical = yes"

Also, what does the filter query (qrySelection1) do?
Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[92hilux]

qrySelection1 is utilized to filter the data table with the Catagorie* = yes.

for example if the user checks (on the form) CatagoryCodes, and CatagoryElectrical... they are both returned to the query as equal to YES

The query then filters the table of data for any records that match these criteria.

The results are returned in the form of a Report for the users viewing.

I'll try the quotation marks. Out of curiousity. Why must they be in quotes?

 

[ajdesalvo]

Actually the double quotes should be around the filter name also.

Why? I believe in most methods and actions in VBA the quotes are used. The technical exlpanation as to why it is needed can probably be explained by another forum participant.

It's one of those things that I accept as "that's just the way it is". Helps keep me partially sane!

Good luck! Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[Frink]

Hallo,

In the help (in Access '97) for the OpenReport method it says:

wherecondition A string expression that's a valid SQL WHERE clause without the word WHERE.

that's why you need to put it in quotes,

- Frink