Opening a filtered form from a custom switchboard

[angi35]

Hi - In Access 2000, I'm trying to create a switchboard so users can open a certain form with different filters. I thought I would use an option group with toggle buttons. I suppose it could be just a series of command buttons instead. Either way, I can't figure out the code to get Access to both open a form and filter it at the same time.

Part of my trouble is also that the terms I need to filter on are multiple words, and I'm really confused about how to correctly refer to things with multiple words that are separated by spaces when I'm working in VB -- when to use [], or _, or " ", etc.

The form is called BID LIST.
The filter is on a control called Status2. The options are:
1. Bid Accepted
2. Submitted Budgetary Bid
3. No Bid
4. Bid Rejected
5. Bid in Process
6. Bid Submitted
7. ...and of course, 'no filter' is another option. (In other words, just open the form).

I tried making just one command button to open the "Bid Submitted" version of the form. Using the command button wizard, I first created the button to open the BID LIST form. This is the code I got:

Private Sub Command13_Click ()
On Error GoTo Err_Command13_C lick

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "BID LIST"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command13_ Click:

Exit Sub

Err_Command13_C lick:

MsgBox Err.Description
Resume Exit_Command13_ Click

End Sub

Then I had the code I've used in the past to create filtering option buttons within a form:

BID_List.Filter = "Status2 = Bid Submitted"
BID_List.Filter On = True

Of course, I don't know how to deal with the two words Bid Submitted that are already within a quotation-marked statement... ??

And I couldn't figure out where to insert these two lines in the larger mess of code for opening the form. I tried:

Private Sub Command13_Click ()
On Error GoTo Err_Command13_C lick

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "BID LIST"
DoCmd.OpenForm stDocName, , , stLinkCriteria

BID_List.Filter = "Status2 = [Bid Submitted]"
BID_List.Filter On = True

Exit_Command13_ Click:

Exit Sub

Err_Command13_C lick:

MsgBox Err.Description
Resume Exit_Command13_ Click

End Sub

...but I got an error message when I tried out the button ("Object Required"). I have no idea whether the error has to do with the wording, placement, or punctuation of the code I inserted.

Any ideas?

Angi

[ADezii]

Hi - In Access 2000, I'm trying to create a switchboard so users can open a certain form with different filters. I thought I would use an option group with toggle buttons. I suppose it could be just a series of command buttons instead. Either way, I can't figure out the code to get Access to both open a form and filter it at the same time.

Part of my trouble is also that the terms I need to filter on are multiple words, and I'm really confused about how to correctly refer to things with multiple words that are separated by spaces when I'm working in VB -- when to use [], or _, or " ", etc.

The form is called BID LIST.
The filter is on a control called Status2. The options are:
1. Bid Accepted
2. Submitted Budgetary Bid
3. No Bid
4. Bid Rejected
5. Bid in Process
6. Bid Submitted
7. ...and of course, 'no filter' is another option. (In other words, just open the form).

I tried making just one command button to open the "Bid Submitted" version of the form. Using the command button wizard, I first created the button to open the BID LIST form. This is the code I got:

Private Sub Command13_Click ()
On Error GoTo Err_Command13_C lick

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "BID LIST"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command13_ Click:

Exit Sub

Err_Command13_C lick:

MsgBox Err.Description
Resume Exit_Command13_ Click

End Sub

Then I had the code I've used in the past to create filtering option buttons within a form:

BID_List.Filter = "Status2 = Bid Submitted"
BID_List.Filter On = True

Of course, I don't know how to deal with the two words Bid Submitted that are already within a quotation-marked statement... ??

And I couldn't figure out where to insert these two lines in the larger mess of code for opening the form. I tried:

Private Sub Command13_Click ()
On Error GoTo Err_Command13_C lick

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "BID LIST"
DoCmd.OpenForm stDocName, , , stLinkCriteria

BID_List.Filter = "Status2 = [Bid Submitted]"
BID_List.Filter On = True

Exit_Command13_ Click:

Exit Sub

Err_Command13_C lick:

MsgBox Err.Description
Resume Exit_Command13_ Click

End Sub

...but I got an error message when I tried out the button ("Object Required"). I have no idea whether the error has to do with the wording, placement, or punctuation of the code I inserted.

Any ideas?

Angi

BID_List.Filter = "[Status2] = 'Bid Submitted'"
BID_List.FilterOn = True

[angi35]

BID_List.Filter = "[Status2] = 'Bid Submitted'"
BID_List.FilterOn = True

Thanks, but I'm still having the error problem. My form is called BID LIST, but I noticed that when I type BID_LIST.Filter , the program insists on automatically changing LIST to List. Maybe this is the problem. Is there any way to make it stop changing this?

Angi

[ADezii]

Thanks, but I'm still having the error problem. My form is called BID LIST, but I noticed that when I type BID_LIST.Filter , the program insists on automatically changing LIST to List. Maybe this is the problem. Is there any way to make it stop changing this?

Angi

I'll try to duplicate the problem and get back to you.

[ADezii]

Thanks, but I'm still having the error problem. My form is called BID LIST, but I noticed that when I type BID_LIST.Filter , the program insists on automatically changing LIST to List. Maybe this is the problem. Is there any way to make it stop changing this?

Angi

If your Form is called BID LIST, then:
Forms![BID List].Filter = "[Status2] = 'Bid Submitted'"
Forms![BID List].FilterOn = True
             OR
Me.Filter = "[Status2] = 'Bid Submitted'"
Me.FilterOn = True

[angi35]

If your Form is called BID LIST, then:
Forms![BID List].Filter = "[Status2] = 'Bid Submitted'"
Forms![BID List].FilterOn = True
             OR
Me.Filter = "[Status2] = 'Bid Submitted'"
Me.FilterOn = True

Thanks for your help! That did it.

Patty

[ADezii]

Thanks for your help! That did it.

Patty

You're welcome, Patty.

[mshmyob]

From Angi to Patty - neat trick (lol)

You're welcome, Patty.

[ADezii]

From Angi to Patty - neat trick (lol)

After almost 3,000 Posts, the names sort of all blend in together. (LOL).

[angi35]

After almost 3,000 Posts, the names sort of all blend in together. (LOL).

Oh well... I'll answer to both.

Angi, aka Patty