Filter a form

E

Ernst Guckel

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.
 
G

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
 
E

Ernst Guckel

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

Any other ideas?

Ernst.

Graham Mandeno said:
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

Ernst Guckel said:
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.
Click to expand...
Click to expand...
 
G

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

Ernst Guckel said:
Still works but still generates the error on load...

Any other ideas?

Ernst.

Graham Mandeno said:
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

Ernst Guckel said:
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.
Click to expand...
Click to expand...
Click to expand...
 
E

Ernst Guckel

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.


Graham Mandeno said:
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

Ernst Guckel said:
Still works but still generates the error on load...

Any other ideas?

Ernst.

Graham Mandeno said:
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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple Error Trapping for 1 Event 3
Requery error when using a MS Access 2k designed db app with 2k3 & 0
Form/Subform/Command Button to Open Form - Type Mismatch Error 2
Filter based on date range and a field value 6
Form filter problem 3
Changing RecordSource Causes A Reload of Sub Form Destroying References 12
Northwind Order Sample sync subs 0
Vis Basic Question 3

Top