Duplicate queries to view a form 2

[integritycare]

Hi,
Here is a query.., but I am not sure if its possible.

I have taken over a database for a company for their ISO9001.
On the Corrective action forms they have a lot of data im the table.

The company wants to view the data which is viewing issues closed or open.

The key is the "date closed field".
I know I can create 2 queries and on the date closed field criteria use either "Is Null" or is Not Null, and use 2 forms to view the data.

Is it possible to use the option buttons to set up the form to either get its data from either query and then requery the form.
So that there is only 1 form.

Or is there another way of doing this?

Thank you
Integrity

 

[MajP]

Yes you use the forms recordsource property
if something then
me.recordsource = "qryOpen"
else
me.recordsource = "qryClosed"
end if

depending on what type of control you are using would dictate the something.

 

[integritycare]

Hi MajiP,
Many thanks for your reply.It works great.

Integrity

 

[dhookom]

I wouldn't create 2 queries. Consider applying a filter to the recordsource of the form. You could place code in the after update of your option group like:

  Dim strFilter as String
  Select Case Me.opgClosed
    Case 1  'Open option
      strFilter = "DateClosed Is Null"
    Case 2  'closed option
      strFilter = "DateClosed Is Not Null"
    Case Else 'show open and closed
      strFilter = ""
  End Select
  Me.Filter = strFilter
  Me.FilterOn = True

Duane
Hook'D on Access
MS Access MVP

 

[integritycare]

Hi dhookom,
Many thanks for your reply. Yes I can see the value in what you have presented.
I have tried this. When I run the code I get a debug in the Code.
It highlights

Select Case Me.opgClosed.

I am just not sure what this means.

Would appreiate it if you could inform me of the error.
Secondly I wanted to put the option Group on the mainform. The Corrective action form is a subform. Was not sure just how to relate the option group to hte subform.

Many thanks,
Integrity

 

[dhookom]

Since you hadn't provided the names of you objects/controls, I used best guesses and expected you to modify to match your names.
"opgClosed" is a placeholder for your option group name. "DateClosed" is the placeholder for your date field.

  Dim strFilter as String
  Select Case Me.opgClosed
    Case 1  'Open option
      strFilter = "DateClosed Is Null"
    Case 2  'closed option
      strFilter = "DateClosed Is Not Null"
    Case Else 'show open and closed
      strFilter = ""
  End Select
  Me!YourSubformControlName.Form.Filter = strFilter

Duane
Hook'D on Access
MS Access MVP

 

[integritycare]

Hi dhookham, thanks for you reply.
Yes it works great