Options group question

[integritycare]

Hello,
I have a question on using option groups. I understand the building of them, etc but here is my question.
The scenario is on an invoice form. The invoice form is as normal using a query to gets is information.

However I wanted to use the same invoice form to view archived invoice data.

I know that if I was to put the invoice form on a separate form so that it became a sub form I could use an option group on that mainform to select between "current" and "archived data" by using the this code

Private Sub PanelSelector_AfterUpdate()
On Error GoTo PanelSelector_AfterUpdate_error

Select Case Me![PanelSelector]
Case 1 'Current
Me![Accounts].Form.RecordSource = "qryInvoices"


Case 2 'Archives
Me![Accounts].Form.RecordSource = "qryInvoices-Arch"


   End Select

PanelSelector_AfterUpdate_Exit:
Exit Sub

PanelSelector_AfterUpdate_error:
Select Case Err
   Case Else
      MsgBox Err & "-" & Error$, vbCritical + vbOKOnly, "Error in module PanelSelector_AfterUpdate"
      Resume PanelSelector_AfterUpdate_Exit
End Select

End Sub

What my question is, can the option group be put on the original invoice form so that the selection could be as in the code above.

Many thanks,

Integrity

 

[MajP]

Yes, but I assume the form is continous. So you need to put the option group in the header or footer.

 

[integritycare]

Hi MajP,

The main form does have a continuous sub form. I have put the option group on the Main form, But I just can not get it to work, when it comes to the archives.
The data is held in two archived tables, both linked as a one to many relationship. The main invoice data in one table and the services data in other table.
How would I arrange the queries so that when I select the archives the main form will show the archived invoices with the archived services data?



Integrity

 

[MajP]

I am a little confused on how many tables/queries you have and what you want to change.

For the non-archived data:
What is the main form recordsource?
What is the sub form recordsource?
What is the child master linking fields?

when you want to switch to archive data
For the archived data:
What is the main form recordsource?
What is the sub form recordsource?
What is the child master linking fields?

It sounds like you want to change both the master and child form when you switch to archive data. Then your code would be something like this:

Assume your sub form control is called "subFrmCtlAccounts"
Assume you want the main form recordsource of "qryArchivedInvoices"
Assumbe you want the subform to have a recordsource of "qryArchivedServices"

Case 2 'Archives
Me.RecordSource = "qryArchivedInvoices"
me.subFrmCtlAccounts.form.recordsource = "qryArchivedServices"
'If you now have different fields linking the two
me.subfrmCtlAccounts.linkMasterFields = "masterFieldName"
me.subFrmCtlAccounts.linkChildFields = "childFieldName