adizukerman
IS-IT--Management
First I would thank everyone for taking the time to read this.
I have a two tables, order (parent) and order detail (child). An order can be either for a person (aka party) or an institution or both. On my form I have order detail as a subform. I have created another subform, order summary, that displays statistics for either a person or institution. For example, on the order summary sub-form, if you select person it will show the avg order dollar amount for all orders associated with that person. If you select institution it will do the same for an institution. The order summary sub-form has a frame control with two option buttons to select if you want summary statistics for a person or an institution. The Update function code is as follows:
Private Sub frameSummary_AfterUpdate()
Select Case Me.frameSummary
Case srcParty
' set recordset
If Me.RecordSource <> "getOrderSummaryByParty" Then
Me.RecordSource = "getOrderSummaryByParty"
Me.Parent.frmOrderSummary.LinkMasterFields = "Party ID"
Me.Parent.frmOrderSummary.LinkChildFields = "Source ID"
End If
Case srcInstitution
' set recordset
If Me.RecordSource <> "getOrderSummaryByInstitution" Then
Me.RecordSource = "getOrderSummaryByInstitution"
Me.Parent.frmOrderSummary.LinkMasterFields = "Institution ID"
Me.Parent.frmOrderSummary.LinkChildFields = "Source ID"
End If
End Select
End Sub
As you can see, I get statistics by using different queries. I link the sub-form using the master / child fields.
When a user moves to a different order on the order form (parent form) the Current function of the order summary form is called. In there, I look at the parent form to see if the order is for a person, institution or both. If it is for only an institution I disable the person option button in the option frame. Same for if the order is just for a person.
All this works fine except in the following situation. If I look at order 1 which is for both an institution and party. I select in the order summary subform to look at statistics for an institution. Then I move to order 2 which is only for a party. What I'm guessing happens is that the recordset for order summary returns no records since there are no statistics available. The sub form becomes blank (can't see any controls, nothing except gray) and the sub form current event is not executed. How do I change the recordset to party before the order summary sub form becomes blank? That is my major issue.
To make matters more complicated, I would like (but getting a solution is more important) to place this sub-form on multiple forms. The only things I would like to have to do on the parent form is ensure I have a Party ID and Institution ID field and call the subform frmOrderSummary.
What can I do? Again, thank you for taking the time to read this.
- Adi Zukerman
adizukerman@hotmail.com
I have a two tables, order (parent) and order detail (child). An order can be either for a person (aka party) or an institution or both. On my form I have order detail as a subform. I have created another subform, order summary, that displays statistics for either a person or institution. For example, on the order summary sub-form, if you select person it will show the avg order dollar amount for all orders associated with that person. If you select institution it will do the same for an institution. The order summary sub-form has a frame control with two option buttons to select if you want summary statistics for a person or an institution. The Update function code is as follows:
Private Sub frameSummary_AfterUpdate()
Select Case Me.frameSummary
Case srcParty
' set recordset
If Me.RecordSource <> "getOrderSummaryByParty" Then
Me.RecordSource = "getOrderSummaryByParty"
Me.Parent.frmOrderSummary.LinkMasterFields = "Party ID"
Me.Parent.frmOrderSummary.LinkChildFields = "Source ID"
End If
Case srcInstitution
' set recordset
If Me.RecordSource <> "getOrderSummaryByInstitution" Then
Me.RecordSource = "getOrderSummaryByInstitution"
Me.Parent.frmOrderSummary.LinkMasterFields = "Institution ID"
Me.Parent.frmOrderSummary.LinkChildFields = "Source ID"
End If
End Select
End Sub
As you can see, I get statistics by using different queries. I link the sub-form using the master / child fields.
When a user moves to a different order on the order form (parent form) the Current function of the order summary form is called. In there, I look at the parent form to see if the order is for a person, institution or both. If it is for only an institution I disable the person option button in the option frame. Same for if the order is just for a person.
All this works fine except in the following situation. If I look at order 1 which is for both an institution and party. I select in the order summary subform to look at statistics for an institution. Then I move to order 2 which is only for a party. What I'm guessing happens is that the recordset for order summary returns no records since there are no statistics available. The sub form becomes blank (can't see any controls, nothing except gray) and the sub form current event is not executed. How do I change the recordset to party before the order summary sub form becomes blank? That is my major issue.
To make matters more complicated, I would like (but getting a solution is more important) to place this sub-form on multiple forms. The only things I would like to have to do on the parent form is ensure I have a Party ID and Institution ID field and call the subform frmOrderSummary.
What can I do? Again, thank you for taking the time to read this.
- Adi Zukerman
adizukerman@hotmail.com