Open form on filter

  • Thread starter Thread starter LeAnne
  • Start date Start date
L

LeAnne

I've posted several variations of this same question to the Forms group with
no success; I'm hoping someone here can assist.

I have an unbound form in a Stream Cleanups database which contains, among
other controls:

- A combobox based on a query, which enables the user to select a name from
a dropdown list of volunteer organization names;

- A multi-column listbox linked to the combobox, which displays a list of
cleanup events based on the organization selected in the cbo;

- And a button ("View") which is intended to open a filtered popup form in
Datasheet View (CleanupDetails) when the user selects an event in the listbox
and clicks the ctrl (the procedure also fires if the user doubleclicks an
event in the listbox).

The cbo and listbox seem to be working ok, but the procedure stalls when it
gets to the DoCmd.OpenForm step to open & filter the popup. The error popup
says "Runtime error '2501': The OpenForm action was canceled." I'm not sure
if the problem lies in that particular line of code, in the way I set up my
global variable (gstrWhereCleanup) meant to hold the filter "criteria," or in
the way I set up the On Filter and Apply Filter properties in the popup.
Could someone help me sort it all out?

Here's the code for the OnClick event of the View button in the Cleanups form:

Private Sub cmdSome_Click()
Dim strWhere As String, varItem As Variant
' If no items selected, then nothing to do
If Me!lstCleanups.ItemsSelected.Count = 0 Then Exit Sub
' Loop through the items selected collection
For Each varItem In Me!lstCleanups.ItemsSelected
' Grab the EventID for each selected item
strWhere = strWhere & Chr$(34) & Me!lstCleanups.Column(0, varItem) &
Chr$(34) & ","
Next varItem
' Throw away the extra comma on the "IN" string
strWhere = Left$(strWhere, Len(strWhere) - 1)
' Open the cleanups form filtered on the selected events
gstrWhereCleanup = "[EventID] IN (" & strWhere & ")"
DoCmd.OpenForm "frmCleanupDetails", acFormDS, , gstrWhereCleanup

End Sub

And in the CleanupDetails form:

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
' Filter has been applied - save it in the global filter string
gstrWhereCleanup = Me.Filter
If IsNothing(Me.Filter) Or ApplyType = acShowAllRecords Then
' Nothing in the filter, so set up like they clicked Show All
gstrWhereCleanup = ""
End If

End Sub

Private Sub Form_Filter(Cancel As Integer, FilterType As Integer)
' User about to build a filter, so clear previous
Me.Filter = ""
gstrWhereCleanup = ""
End Sub

Thanks in advance,

LeAnne
 
Hi LeAnne,

I threw together a small sub to test your code. This is what was returned
for gstrWhereCleanup:

[ClientID] IN ("2","3","4")

My "[ClientID]" field is defined in the table as a Long. But the "Where"
string has quotes around the numbers - which caused it to bomb with the same
error: "The OpenForm action was canceled".

Try using this line

strWhere = strWhere & Me!lstCleanups.Column(0, varItem) & ","

instead of :

strWhere = strWhere & Chr$(34) & Me!lstCleanups.Column(0, varItem) &
Chr$(34) & ","


(assuming that "[EventID]" is a number.)

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


LeAnne said:
I've posted several variations of this same question to the Forms group with
no success; I'm hoping someone here can assist.

I have an unbound form in a Stream Cleanups database which contains, among
other controls:

- A combobox based on a query, which enables the user to select a name from
a dropdown list of volunteer organization names;

- A multi-column listbox linked to the combobox, which displays a list of
cleanup events based on the organization selected in the cbo;

- And a button ("View") which is intended to open a filtered popup form in
Datasheet View (CleanupDetails) when the user selects an event in the listbox
and clicks the ctrl (the procedure also fires if the user doubleclicks an
event in the listbox).

The cbo and listbox seem to be working ok, but the procedure stalls when it
gets to the DoCmd.OpenForm step to open & filter the popup. The error popup
says "Runtime error '2501': The OpenForm action was canceled." I'm not sure
if the problem lies in that particular line of code, in the way I set up my
global variable (gstrWhereCleanup) meant to hold the filter "criteria," or in
the way I set up the On Filter and Apply Filter properties in the popup.
Could someone help me sort it all out?

Here's the code for the OnClick event of the View button in the Cleanups form:

Private Sub cmdSome_Click()
Dim strWhere As String, varItem As Variant
' If no items selected, then nothing to do
If Me!lstCleanups.ItemsSelected.Count = 0 Then Exit Sub
' Loop through the items selected collection
For Each varItem In Me!lstCleanups.ItemsSelected
' Grab the EventID for each selected item
strWhere = strWhere & Chr$(34) & Me!lstCleanups.Column(0, varItem) &
Chr$(34) & ","
Next varItem
' Throw away the extra comma on the "IN" string
strWhere = Left$(strWhere, Len(strWhere) - 1)
' Open the cleanups form filtered on the selected events
gstrWhereCleanup = "[EventID] IN (" & strWhere & ")"
DoCmd.OpenForm "frmCleanupDetails", acFormDS, , gstrWhereCleanup

End Sub

And in the CleanupDetails form:

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
' Filter has been applied - save it in the global filter string
gstrWhereCleanup = Me.Filter
If IsNothing(Me.Filter) Or ApplyType = acShowAllRecords Then
' Nothing in the filter, so set up like they clicked Show All
gstrWhereCleanup = ""
End If

End Sub

Private Sub Form_Filter(Cancel As Integer, FilterType As Integer)
' User about to build a filter, so clear previous
Me.Filter = ""
gstrWhereCleanup = ""
End Sub

Thanks in advance,

LeAnne
Click to expand...
 
OMG! Of course EventID is a number...can't believe I overlooked that. Steve,
you da MAN. It works beautifully! Thankyouthankyouthankyou!!!

Cheers,

LeAnne

Steve Sanford said:
Hi LeAnne,

I threw together a small sub to test your code. This is what was returned
for gstrWhereCleanup:

[ClientID] IN ("2","3","4")

My "[ClientID]" field is defined in the table as a Long. But the "Where"
string has quotes around the numbers - which caused it to bomb with the same
error: "The OpenForm action was canceled".

Try using this line

strWhere = strWhere & Me!lstCleanups.Column(0, varItem) & ","

instead of :

strWhere = strWhere & Chr$(34) & Me!lstCleanups.Column(0, varItem) &
Chr$(34) & ","


(assuming that "[EventID]" is a number.)

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


LeAnne said:
I've posted several variations of this same question to the Forms group with
no success; I'm hoping someone here can assist.

I have an unbound form in a Stream Cleanups database which contains, among
other controls:

- A combobox based on a query, which enables the user to select a name from
a dropdown list of volunteer organization names;

- A multi-column listbox linked to the combobox, which displays a list of
cleanup events based on the organization selected in the cbo;

- And a button ("View") which is intended to open a filtered popup form in
Datasheet View (CleanupDetails) when the user selects an event in the listbox
and clicks the ctrl (the procedure also fires if the user doubleclicks an
event in the listbox).

The cbo and listbox seem to be working ok, but the procedure stalls when it
gets to the DoCmd.OpenForm step to open & filter the popup. The error popup
says "Runtime error '2501': The OpenForm action was canceled." I'm not sure
if the problem lies in that particular line of code, in the way I set up my
global variable (gstrWhereCleanup) meant to hold the filter "criteria," or in
the way I set up the On Filter and Apply Filter properties in the popup.
Could someone help me sort it all out?

Here's the code for the OnClick event of the View button in the Cleanups form:

Private Sub cmdSome_Click()
Dim strWhere As String, varItem As Variant
' If no items selected, then nothing to do
If Me!lstCleanups.ItemsSelected.Count = 0 Then Exit Sub
' Loop through the items selected collection
For Each varItem In Me!lstCleanups.ItemsSelected
' Grab the EventID for each selected item
strWhere = strWhere & Chr$(34) & Me!lstCleanups.Column(0, varItem) &
Chr$(34) & ","
Next varItem
' Throw away the extra comma on the "IN" string
strWhere = Left$(strWhere, Len(strWhere) - 1)
' Open the cleanups form filtered on the selected events
gstrWhereCleanup = "[EventID] IN (" & strWhere & ")"
DoCmd.OpenForm "frmCleanupDetails", acFormDS, , gstrWhereCleanup

End Sub

And in the CleanupDetails form:

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
' Filter has been applied - save it in the global filter string
gstrWhereCleanup = Me.Filter
If IsNothing(Me.Filter) Or ApplyType = acShowAllRecords Then
' Nothing in the filter, so set up like they clicked Show All
gstrWhereCleanup = ""
End If

End Sub

Private Sub Form_Filter(Cancel As Integer, FilterType As Integer)
' User about to build a filter, so clear previous
Me.Filter = ""
gstrWhereCleanup = ""
End Sub

Thanks in advance,

LeAnne
Click to expand...
Click to expand...
 
You're welcome.... :D
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


LeAnne said:
OMG! Of course EventID is a number...can't believe I overlooked that. Steve,
you da MAN. It works beautifully! Thankyouthankyouthankyou!!!

Cheers,

LeAnne

Steve Sanford said:
Hi LeAnne,

I threw together a small sub to test your code. This is what was returned
for gstrWhereCleanup:

[ClientID] IN ("2","3","4")

My "[ClientID]" field is defined in the table as a Long. But the "Where"
string has quotes around the numbers - which caused it to bomb with the same
error: "The OpenForm action was canceled".

Try using this line

strWhere = strWhere & Me!lstCleanups.Column(0, varItem) & ","

instead of :

strWhere = strWhere & Chr$(34) & Me!lstCleanups.Column(0, varItem) &
Chr$(34) & ","


(assuming that "[EventID]" is a number.)

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


LeAnne said:
I've posted several variations of this same question to the Forms group with
no success; I'm hoping someone here can assist.

I have an unbound form in a Stream Cleanups database which contains, among
other controls:

- A combobox based on a query, which enables the user to select a name from
a dropdown list of volunteer organization names;

- A multi-column listbox linked to the combobox, which displays a list of
cleanup events based on the organization selected in the cbo;

- And a button ("View") which is intended to open a filtered popup form in
Datasheet View (CleanupDetails) when the user selects an event in the listbox
and clicks the ctrl (the procedure also fires if the user doubleclicks an
event in the listbox).

The cbo and listbox seem to be working ok, but the procedure stalls when it
gets to the DoCmd.OpenForm step to open & filter the popup. The error popup
says "Runtime error '2501': The OpenForm action was canceled." I'm not sure
if the problem lies in that particular line of code, in the way I set up my
global variable (gstrWhereCleanup) meant to hold the filter "criteria," or in
the way I set up the On Filter and Apply Filter properties in the popup.
Could someone help me sort it all out?

Here's the code for the OnClick event of the View button in the Cleanups form:

Private Sub cmdSome_Click()
Dim strWhere As String, varItem As Variant
' If no items selected, then nothing to do
If Me!lstCleanups.ItemsSelected.Count = 0 Then Exit Sub
' Loop through the items selected collection
For Each varItem In Me!lstCleanups.ItemsSelected
' Grab the EventID for each selected item
strWhere = strWhere & Chr$(34) & Me!lstCleanups.Column(0, varItem) &
Chr$(34) & ","
Next varItem
' Throw away the extra comma on the "IN" string
strWhere = Left$(strWhere, Len(strWhere) - 1)
' Open the cleanups form filtered on the selected events
gstrWhereCleanup = "[EventID] IN (" & strWhere & ")"
DoCmd.OpenForm "frmCleanupDetails", acFormDS, , gstrWhereCleanup

End Sub

And in the CleanupDetails form:

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
' Filter has been applied - save it in the global filter string
gstrWhereCleanup = Me.Filter
If IsNothing(Me.Filter) Or ApplyType = acShowAllRecords Then
' Nothing in the filter, so set up like they clicked Show All
gstrWhereCleanup = ""
End If

End Sub

Private Sub Form_Filter(Cancel As Integer, FilterType As Integer)
' User about to build a filter, so clear previous
Me.Filter = ""
gstrWhereCleanup = ""
End Sub

Thanks in advance,

LeAnne
Click to expand...
Click to expand...
Click to expand...
 
Back
Top