Form/Filter by Form/Subform out of sync

[ddamsgaard]

I'm struggling with this and I'll appreciate any help.

I have a form, frmGLTRANS2, that's populated with an SQL statement. There are many fields, so the form is designed to visually display a single record. The form queries a large Oracle database.

Initially, I used "Filter by Form" to further query the database. When I wanted to see the many records in datasheet view, I just clicked the "View Datasheet" button. This worked fine.

Then I got the bright idea to add a subform, fSubForm1, to the main form. The subform, in datasheet view, would show all the records after the Filter by Form button had been clicked.

In other words, the subform should show same information as the datasheet view of the main form. Using the code below, I can come close to making this work, but...I'm clearly doing something wrong.

There are two problems:
1. The datasheet of the main form has a subdatasheet - I'm not sure why or how to prevent that from happening.
2. The subform results are still not in sync with the main form.

I've tried the On Current event and the Apply Filter event without success.

Thanks in advance for your assistance.

The code:

Private Sub Form_Filter(Cancel As Integer, FilterType As Integer)
strFilter = Me.Filter
Forms!frmGLTRANS2!fSubForm1.Form.RecordSource = Forms!frmGLTRANS2.RecordSource
Forms!frmGLTRANS2!fSubForm1.Form.Filter = strFilter
Forms!frmGLTRANS2!fSubForm1.Form.Requery
End Sub

Private Sub Form_Load()
strFilter = Me.Filter
Forms!frmGLTRANS2!fSubForm1.Form.RecordSource = Me.RecordSource
Forms!frmGLTRANS2!fSubForm1.Form.Filter = strFilter
End Sub

Private Sub Form_Open(Cancel As Integer)

Dim strSQL As String
strSQL = "SELECT *, MID(ACCT_UNIT,9,4) AS RC, IIF(LEN(CSTR(SUB_ACCOUNT))=4 AND MID(CSTR(SUB_ACCOUNT),1,1) = 9, MID(CSTR(SUB_ACCOUNT),2,3),'000') as TOC FROM UUPG_GLTRANS WHERE COMPANY = 10"
Me.RecordSource = strSQL
End Sub