Filter a form

[Guest]

Hey,

I have a main form 'frmStartup' with a subform 'cldMain' that I change the
recordsource of via code depending on which command button the user chooses...

cldMain when the Orders button is chosen changes the source to
'frmInventorybyOrders' which has two sub forms on it - cldInventoryItems
and cldInventory.

In the on Current event of the InventoryItems form I run code that
requeries the Inventory form but applies a filter as well... The code runs
fine when the form is loaded and changes but when it is opened for the first
time I recieve an error...

The error is: You entered an expression that has an invalid reference to the
property Form/Report.

Here is the code

Dim sStart As String
Dim sStop As String
sStart = Format(Me.Parent![txtStart] - 1, "\#mm/dd/yyyy\#")
sStop = Format(Me.Parent![txtStop] + 1, "\#mm/dd/yyyy\#")

On Error Resume Next
sFilter = "tblInventory.InvDate > " & sStart & " AND tblInventory.InvDate <
" & sStop

If Err <> 0 Then
GoTo Form_Current_Exit
Else
On Error GoTo Form_Current_Err
Forms!frmStartup!cldMain.Form!cldInventory.Form.Filter = sFilter
Forms!frmStartup!cldMain.Form!cldInventory.Form.FilterOn = True
Me.Parent![cldInventory].Requery
End If

Form_Current_Exit:
Exit Sub
Form_Current_Err:
MsgBox Error$
Resume Form_Current_Exit

Forms!frmStartup!cldMain.Form!cldInventory.Form.Filter = sFilter is the line
that causes the error...

I just don't get it.. ANy help would be great...

Thanks,
Ernst.

 

[Graham Mandeno]

Hi Ernst

I don't know why this is happening, but the fact that it occurs only when
the subform's SourceObject is being loaded suggests a timing problem. I
think the code must be running before the SourceObject load is complete, so
your subform control cldMain does not *yet* have a Form property.

I suggest you try referring to cldInventory only in terms of its parent
form. In other words, change these lines:
Forms!frmStartup!cldMain.Form!cldInventory.Form.Filter = sFilter
Forms!frmStartup!cldMain.Form!cldInventory.Form.FilterOn = True
to:
Me.Parent!cldInventory.Form.Filter = sFilter
Me.Parent!cldInventory.Form.FilterOn = True

 

[Guest]

Still works but still generates the error on load...

Any other ideas?

Ernst.

Hi Ernst

I don't know why this is happening, but the fact that it occurs only when
the subform's SourceObject is being loaded suggests a timing problem. I
think the code must be running before the SourceObject load is complete, so
your subform control cldMain does not *yet* have a Form property.

I suggest you try referring to cldInventory only in terms of its parent
form. In other words, change these lines:
Forms!frmStartup!cldMain.Form!cldInventory.Form.Filter = sFilter
Forms!frmStartup!cldMain.Form!cldInventory.Form.FilterOn = True
to:
Me.Parent!cldInventory.Form.Filter = sFilter
Me.Parent!cldInventory.Form.FilterOn = True

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hey,

I have a main form 'frmStartup' with a subform 'cldMain' that I change
the
recordsource of via code depending on which command button the user
chooses...

cldMain when the Orders button is chosen changes the source to
'frmInventorybyOrders' which has two sub forms on it - cldInventoryItems
and cldInventory.

In the on Current event of the InventoryItems form I run code that
requeries the Inventory form but applies a filter as well... The code runs
fine when the form is loaded and changes but when it is opened for the
first
time I recieve an error...

The error is: You entered an expression that has an invalid reference to
the
property Form/Report.

Here is the code

Dim sStart As String
Dim sStop As String
sStart = Format(Me.Parent![txtStart] - 1, "\#mm/dd/yyyy\#")
sStop = Format(Me.Parent![txtStop] + 1, "\#mm/dd/yyyy\#")

On Error Resume Next
sFilter = "tblInventory.InvDate > " & sStart & " AND tblInventory.InvDate
<
" & sStop

If Err <> 0 Then
GoTo Form_Current_Exit
Else
On Error GoTo Form_Current_Err
Forms!frmStartup!cldMain.Form!cldInventory.Form.Filter = sFilter
Forms!frmStartup!cldMain.Form!cldInventory.Form.FilterOn = True
Me.Parent![cldInventory].Requery
End If

Form_Current_Exit:
Exit Sub
Form_Current_Err:
MsgBox Error$
Resume Form_Current_Exit

Forms!frmStartup!cldMain.Form!cldInventory.Form.Filter = sFilter is the
line
that causes the error...

I just don't get it.. ANy help would be great...

Thanks,
Ernst.

 

[Graham Mandeno]

OK, I think what must be happening is the InventoryItems subform is loading
first and its Current event is firing before the Inventory subform has been
loaded.

Does the same problem occur if you just open frmInventorybyOrders from the
database window?

Are the two date fields on the main form linked to the current record in
cldInventoryItems? If not, why is it necessary to set the filter in the
Current event of that subform?

One workaround might be to have cldInventory set its own filter in its Load
event, and use a static flag to suppress setting it the first time the
InventoryItems Current event fires. You could put the code in a common
procedure in the main form so as not to duplicate code:

In the main form:

Public Sub SetInventoryFilter()
Dim sFilter As String
sFilter = "tblInventory.InvDate > " _
& Format(Me.Parent![txtStart] - 1, "\#mm/dd/yyyy\#") _
& " AND tblInventory.InvDate < " _
& Format(Me.Parent![txtStop] + 1, "\#mm/dd/yyyy\#")
Me!cldInventory.Form.Filter = sFilter
Me!cldInventory.Form.FilterOn = True
End Sub

In cldInventory's Load event:
Call Me.Parent.SetInventoryFilter

In cldInventoryItems' Current event:
Static fNotFirstTime As Boolean
If fNotFirstTime Then
Call Me.Parent.SetInventoryFilter
Else
fNotFirstTime = True
End If


--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Still works but still generates the error on load...

Any other ideas?

Ernst.

Hi Ernst

I don't know why this is happening, but the fact that it occurs only when
the subform's SourceObject is being loaded suggests a timing problem. I
think the code must be running before the SourceObject load is complete,
so
your subform control cldMain does not *yet* have a Form property.

I suggest you try referring to cldInventory only in terms of its parent
form. In other words, change these lines:
Forms!frmStartup!cldMain.Form!cldInventory.Form.Filter = sFilter
Forms!frmStartup!cldMain.Form!cldInventory.Form.FilterOn = True
to:
Me.Parent!cldInventory.Form.Filter = sFilter
Me.Parent!cldInventory.Form.FilterOn = True

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hey,

I have a main form 'frmStartup' with a subform 'cldMain' that I change
the
recordsource of via code depending on which command button the user
chooses...

cldMain when the Orders button is chosen changes the source to
'frmInventorybyOrders' which has two sub forms on it -
cldInventoryItems
and cldInventory.

In the on Current event of the InventoryItems form I run code that
requeries the Inventory form but applies a filter as well... The code
runs
fine when the form is loaded and changes but when it is opened for the
first
time I recieve an error...

The error is: You entered an expression that has an invalid reference
to
the
property Form/Report.

Here is the code

Dim sStart As String
Dim sStop As String
sStart = Format(Me.Parent![txtStart] - 1, "\#mm/dd/yyyy\#")
sStop = Format(Me.Parent![txtStop] + 1, "\#mm/dd/yyyy\#")

On Error Resume Next
sFilter = "tblInventory.InvDate > " & sStart & " AND
tblInventory.InvDate
<
" & sStop

If Err <> 0 Then
GoTo Form_Current_Exit
Else
On Error GoTo Form_Current_Err
Forms!frmStartup!cldMain.Form!cldInventory.Form.Filter = sFilter
Forms!frmStartup!cldMain.Form!cldInventory.Form.FilterOn = True
Me.Parent![cldInventory].Requery
End If

Form_Current_Exit:
Exit Sub
Form_Current_Err:
MsgBox Error$
Resume Form_Current_Exit

Forms!frmStartup!cldMain.Form!cldInventory.Form.Filter = sFilter is the
line
that causes the error...

I just don't get it.. ANy help would be great...

Thanks,
Ernst.

 

[Guest]

That's what I ended up doing. In the load event I created the filter and
toggled the flag on. Then the current event check for it...

Thanks for the help,
Ernst.

OK, I think what must be happening is the InventoryItems subform is loading
first and its Current event is firing before the Inventory subform has been
loaded.

Does the same problem occur if you just open frmInventorybyOrders from the
database window?

Are the two date fields on the main form linked to the current record in
cldInventoryItems? If not, why is it necessary to set the filter in the
Current event of that subform?

One workaround might be to have cldInventory set its own filter in its Load
event, and use a static flag to suppress setting it the first time the
InventoryItems Current event fires. You could put the code in a common
procedure in the main form so as not to duplicate code:

In the main form:

Public Sub SetInventoryFilter()
Dim sFilter As String
sFilter = "tblInventory.InvDate > " _
& Format(Me.Parent![txtStart] - 1, "\#mm/dd/yyyy\#") _
& " AND tblInventory.InvDate < " _
& Format(Me.Parent![txtStop] + 1, "\#mm/dd/yyyy\#")
Me!cldInventory.Form.Filter = sFilter
Me!cldInventory.Form.FilterOn = True
End Sub

In cldInventory's Load event:
Call Me.Parent.SetInventoryFilter

In cldInventoryItems' Current event:
Static fNotFirstTime As Boolean
If fNotFirstTime Then
Call Me.Parent.SetInventoryFilter
Else
fNotFirstTime = True
End If


--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Still works but still generates the error on load...

Any other ideas?

Ernst.

Hi Ernst

I don't know why this is happening, but the fact that it occurs only when
the subform's SourceObject is being loaded suggests a timing problem. I
think the code must be running before the SourceObject load is complete,
so
your subform control cldMain does not *yet* have a Form property.

I suggest you try referring to cldInventory only in terms of its parent
form. In other words, change these lines:
Forms!frmStartup!cldMain.Form!cldInventory.Form.Filter = sFilter
Forms!frmStartup!cldMain.Form!cldInventory.Form.FilterOn = True
to:
Me.Parent!cldInventory.Form.Filter = sFilter
Me.Parent!cldInventory.Form.FilterOn = True

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hey,

I have a main form 'frmStartup' with a subform 'cldMain' that I change
the
recordsource of via code depending on which command button the user
chooses...

cldMain when the Orders button is chosen changes the source to
'frmInventorybyOrders' which has two sub forms on it -
cldInventoryItems
and cldInventory.

In the on Current event of the InventoryItems form I run code that
requeries the Inventory form but applies a filter as well... The code
runs
fine when the form is loaded and changes but when it is opened for the
first
time I recieve an error...

The error is: You entered an expression that has an invalid reference
to
the
property Form/Report.

Here is the code

Dim sStart As String
Dim sStop As String
sStart = Format(Me.Parent![txtStart] - 1, "\#mm/dd/yyyy\#")
sStop = Format(Me.Parent![txtStop] + 1, "\#mm/dd/yyyy\#")

On Error Resume Next
sFilter = "tblInventory.InvDate > " & sStart & " AND
tblInventory.InvDate
<
" & sStop

If Err <> 0 Then
GoTo Form_Current_Exit
Else
On Error GoTo Form_Current_Err
Forms!frmStartup!cldMain.Form!cldInventory.Form.Filter = sFilter
Forms!frmStartup!cldMain.Form!cldInventory.Form.FilterOn = True
Me.Parent![cldInventory].Requery
End If

Form_Current_Exit:
Exit Sub
Form_Current_Err:
MsgBox Error$
Resume Form_Current_Exit

Forms!frmStartup!cldMain.Form!cldInventory.Form.Filter = sFilter is the
line
that causes the error...

I just don't get it.. ANy help would be great...

Thanks,
Ernst.