Open form on filter

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
 
S

Steve Sanford

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

LeAnne

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

Steve Sanford

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

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

VBA filter not working in form 2
Run select query from VBA code where criteria comes from a multi select ListBox 2
filter report from listbox without having to select any 7
Filter Report based on forms filter 2
multiselect list box code for filter form 11
select child records from list box to filter report 1
Two list boxes not working 2
Create Report On the Fly 1

Top