best way to use same form with different criteria

[John]

Hello I'm building an App that uses the switchboard functions of access. I
have a form that can be opened from the switchboard. This forms recordset is
a query with the criteria on one field set to "is not null". I would like to
have a second button on the switchboard that would open the same form, but
change the query's criteria to "is null". How would be the best way to
accomplish this?

v/r
john

 

[Klatuu]

Can't do it from the Switchboard. It is not smart enough.

 

[KARL DEWEY]

Dave could you not read which command button was selected?

 

[John]

I was thinking something along the lines of using the open event on the
actual form and only passing a param from the switchboard.

 

[Klatuu]

I don't think so.
The proble is that using the SwitchBoard manager, the only thing you can do
to open a form is select OpenForm, and a Form Name.
The form being opened would have no way of knowing which switchboard button
was pressed.

I thought about running a Macro, but there is no option in a macro to send
an OpenArgs to a form.

The problem is Macros and the Switchboard are "crutches" for people who
don't know VBA. Certainly they are valuable tools, but have some sever
limitations.

The only other thing I can think of would be to use a Run Code command, and
write two small functions that pass an OpenArg value to the form in the
OpenForm method, but if the OP is familiar with using VBA, that would be of
no value to him.

 

[KARL DEWEY]

The form being opened would have no way of knowing which switchboard button
was pressed.
I was thinking of criteria for the query instead.

 

[Klatuu]

KARL, you got me to thinkin' and schemin' and I came up with an idea.

Here is what I found:

Private Sub Form_Load()
Dim ctl As Control
Set ctl = Screen.ActiveControl
MsgBox ctl.Name
End Sub

As the button that opens this form is the Option 1 button on the
switchboard, it returns "Option1"

How clever!

So, OP, here is what you can do. First, open the switchboard form in design
view. Not the Switchboard Manager, but select forms, and open the
switchboard form in design view. Now open the Properties dialog and find the
name of the two buttons that open the form. In the example below

Private Sub Form_Load()
Dim ctl As Control

Set ctl = Screen.ActiveControl
If ctl.Name = "Option6" Then 'IsNull Selected
Me.Filter = "[CriteriaField] Is Null"
Else 'Is Not Null Selected
Me.Filter = "[CriteriaField] Is Not Null"
End If

Me.FilterOn = True
Set ctl = Noting
End Sub

Wow! between us, KARL, we is almost genius level <g>
Thanks for the proding.

Hey, John, if you haven't given up and you have any questions about this,
feel free to post back.

 

[KARL DEWEY]

One potential problem I see with this is that if you change the Switchboard
Items table data so that a differnt button is used you will have lost it.
--
KARL DEWEY
Build a little - Test a little

KARL, you got me to thinkin' and schemin' and I came up with an idea.

Here is what I found:

Private Sub Form_Load()
Dim ctl As Control
Set ctl = Screen.ActiveControl
MsgBox ctl.Name
End Sub

As the button that opens this form is the Option 1 button on the
switchboard, it returns "Option1"

How clever!

So, OP, here is what you can do. First, open the switchboard form in design
view. Not the Switchboard Manager, but select forms, and open the
switchboard form in design view. Now open the Properties dialog and find the
name of the two buttons that open the form. In the example below

Private Sub Form_Load()
Dim ctl As Control

Set ctl = Screen.ActiveControl
If ctl.Name = "Option6" Then 'IsNull Selected
Me.Filter = "[CriteriaField] Is Null"
Else 'Is Not Null Selected
Me.Filter = "[CriteriaField] Is Not Null"
End If

Me.FilterOn = True
Set ctl = Noting
End Sub

Wow! between us, KARL, we is almost genius level <g>
Thanks for the proding.

Hey, John, if you haven't given up and you have any questions about this,
feel free to post back.
--
Dave Hargis, Microsoft Access MVP

was pressed.
I was thinking of criteria for the query instead.

 

[Klatuu]

There you go slamming reality into a perfectly beautiful concept!

Right, but as long as the OP is aware of that, he can deal with it.
--
Dave Hargis, Microsoft Access MVP

One potential problem I see with this is that if you change the Switchboard
Items table data so that a differnt button is used you will have lost it.
--
KARL DEWEY
Build a little - Test a little

KARL, you got me to thinkin' and schemin' and I came up with an idea.

Here is what I found:

Private Sub Form_Load()
Dim ctl As Control
Set ctl = Screen.ActiveControl
MsgBox ctl.Name
End Sub

As the button that opens this form is the Option 1 button on the
switchboard, it returns "Option1"

How clever!

So, OP, here is what you can do. First, open the switchboard form in design
view. Not the Switchboard Manager, but select forms, and open the
switchboard form in design view. Now open the Properties dialog and find the
name of the two buttons that open the form. In the example below

Private Sub Form_Load()
Dim ctl As Control

Set ctl = Screen.ActiveControl
If ctl.Name = "Option6" Then 'IsNull Selected
Me.Filter = "[CriteriaField] Is Null"
Else 'Is Not Null Selected
Me.Filter = "[CriteriaField] Is Not Null"
End If

Me.FilterOn = True
Set ctl = Noting
End Sub

Wow! between us, KARL, we is almost genius level <g>
Thanks for the proding.

Hey, John, if you haven't given up and you have any questions about this,
feel free to post back.
--
Dave Hargis, Microsoft Access MVP

The form being opened would have no way of knowing which switchboard button
was pressed.
I was thinking of criteria for the query instead.
--
KARL DEWEY
Build a little - Test a little


:

I don't think so.
The proble is that using the SwitchBoard manager, the only thing you can do
to open a form is select OpenForm, and a Form Name.
The form being opened would have no way of knowing which switchboard button
was pressed.

I thought about running a Macro, but there is no option in a macro to send
an OpenArgs to a form.

The problem is Macros and the Switchboard are "crutches" for people who
don't know VBA. Certainly they are valuable tools, but have some sever
limitations.

The only other thing I can think of would be to use a Run Code command, and
write two small functions that pass an OpenArg value to the form in the
OpenForm method, but if the OP is familiar with using VBA, that would be of
no value to him.
--
Dave Hargis, Microsoft Access MVP


:

Dave could you not read which command button was selected?
--
KARL DEWEY
Build a little - Test a little


:

Can't do it from the Switchboard. It is not smart enough.
--
Dave Hargis, Microsoft Access MVP


:

Hello I'm building an App that uses the switchboard functions of access. I
have a form that can be opened from the switchboard. This forms recordset is
a query with the criteria on one field set to "is not null". I would like to
have a second button on the switchboard that would open the same form, but
change the query's criteria to "is null". How would be the best way to
accomplish this?

v/r
john

 

[John]

Thanks Klatuu, But Now that I have add the code to the On load event. I'm
getting an error "Run-time error '2474':" The expression you entered requires
the control to be in the active window.
Here is the code I have entered:
Private Sub Form_Load()
Dim ctl As Control

Set ctl = Screen.ActiveControl
If ctl.Name = "Option1" Then 'IsNull Selected
Me.Filter = "[Ccloseddate] Is Null"
Else 'Is Not Null Selected
Me.Filter = "[Ccloseddate] Is Not Null"
End If
Me.FilterOn = True
Set ctl = Noting

End Sub

I'm not sure I understand how this would work or if I did it correctly! How
would the Me.Filter change the criteria of the underlying query the form is
based on? I placed this code on the Load event of the Switchboard Not the
actual form that I would like to change the criteria on. Should I also place
some code in the actual form itself? Thanks for your help

KARL, you got me to thinkin' and schemin' and I came up with an idea.

Here is what I found:

Private Sub Form_Load()
Dim ctl As Control
Set ctl = Screen.ActiveControl
MsgBox ctl.Name
End Sub

As the button that opens this form is the Option 1 button on the
switchboard, it returns "Option1"

How clever!

So, OP, here is what you can do. First, open the switchboard form in design
view. Not the Switchboard Manager, but select forms, and open the
switchboard form in design view. Now open the Properties dialog and find the
name of the two buttons that open the form. In the example below

Private Sub Form_Load()
Dim ctl As Control

Set ctl = Screen.ActiveControl
If ctl.Name = "Option6" Then 'IsNull Selected
Me.Filter = "[CriteriaField] Is Null"
Else 'Is Not Null Selected
Me.Filter = "[CriteriaField] Is Not Null"
End If

Me.FilterOn = True
Set ctl = Noting
End Sub

Wow! between us, KARL, we is almost genius level <g>
Thanks for the proding.

Hey, John, if you haven't given up and you have any questions about this,
feel free to post back.
--
Dave Hargis, Microsoft Access MVP

was pressed.
I was thinking of criteria for the query instead.

 

[Klatuu]

What version of Access are you using?
I tested this in 2003 and it worked for me.
--
Dave Hargis, Microsoft Access MVP

Thanks Klatuu, But Now that I have add the code to the On load event. I'm
getting an error "Run-time error '2474':" The expression you entered requires
the control to be in the active window.
Here is the code I have entered:
Private Sub Form_Load()
Dim ctl As Control

Set ctl = Screen.ActiveControl
If ctl.Name = "Option1" Then 'IsNull Selected
Me.Filter = "[Ccloseddate] Is Null"
Else 'Is Not Null Selected
Me.Filter = "[Ccloseddate] Is Not Null"
End If
Me.FilterOn = True
Set ctl = Noting

End Sub

I'm not sure I understand how this would work or if I did it correctly! How
would the Me.Filter change the criteria of the underlying query the form is
based on? I placed this code on the Load event of the Switchboard Not the
actual form that I would like to change the criteria on. Should I also place
some code in the actual form itself? Thanks for your help

KARL, you got me to thinkin' and schemin' and I came up with an idea.

Here is what I found:

Private Sub Form_Load()
Dim ctl As Control
Set ctl = Screen.ActiveControl
MsgBox ctl.Name
End Sub

As the button that opens this form is the Option 1 button on the
switchboard, it returns "Option1"

How clever!

So, OP, here is what you can do. First, open the switchboard form in design
view. Not the Switchboard Manager, but select forms, and open the
switchboard form in design view. Now open the Properties dialog and find the
name of the two buttons that open the form. In the example below

Private Sub Form_Load()
Dim ctl As Control

Set ctl = Screen.ActiveControl
If ctl.Name = "Option6" Then 'IsNull Selected
Me.Filter = "[CriteriaField] Is Null"
Else 'Is Not Null Selected
Me.Filter = "[CriteriaField] Is Not Null"
End If

Me.FilterOn = True
Set ctl = Noting
End Sub

Wow! between us, KARL, we is almost genius level <g>
Thanks for the proding.

Hey, John, if you haven't given up and you have any questions about this,
feel free to post back.
--
Dave Hargis, Microsoft Access MVP

The form being opened would have no way of knowing which switchboard button
was pressed.
I was thinking of criteria for the query instead.
--
KARL DEWEY
Build a little - Test a little


:

I don't think so.
The proble is that using the SwitchBoard manager, the only thing you can do
to open a form is select OpenForm, and a Form Name.
The form being opened would have no way of knowing which switchboard button
was pressed.

I thought about running a Macro, but there is no option in a macro to send
an OpenArgs to a form.

The problem is Macros and the Switchboard are "crutches" for people who
don't know VBA. Certainly they are valuable tools, but have some sever
limitations.

The only other thing I can think of would be to use a Run Code command, and
write two small functions that pass an OpenArg value to the form in the
OpenForm method, but if the OP is familiar with using VBA, that would be of
no value to him.
--
Dave Hargis, Microsoft Access MVP


:

Dave could you not read which command button was selected?
--
KARL DEWEY
Build a little - Test a little


:

Can't do it from the Switchboard. It is not smart enough.
--
Dave Hargis, Microsoft Access MVP


:

Hello I'm building an App that uses the switchboard functions of access. I
have a form that can be opened from the switchboard. This forms recordset is
a query with the criteria on one field set to "is not null". I would like to
have a second button on the switchboard that would open the same form, but
change the query's criteria to "is null". How would be the best way to
accomplish this?

v/r
john

 

[John]

2000, but I'm getting ready to upgrade to 2007! Can you explain how it would
change the actual forms underlying query. Or am I just way of base

v/r
john

What version of Access are you using?
I tested this in 2003 and it worked for me.
--
Dave Hargis, Microsoft Access MVP

Thanks Klatuu, But Now that I have add the code to the On load event. I'm
getting an error "Run-time error '2474':" The expression you entered requires
the control to be in the active window.
Here is the code I have entered:
Private Sub Form_Load()
Dim ctl As Control

Set ctl = Screen.ActiveControl
If ctl.Name = "Option1" Then 'IsNull Selected
Me.Filter = "[Ccloseddate] Is Null"
Else 'Is Not Null Selected
Me.Filter = "[Ccloseddate] Is Not Null"
End If
Me.FilterOn = True
Set ctl = Noting

End Sub

I'm not sure I understand how this would work or if I did it correctly! How
would the Me.Filter change the criteria of the underlying query the form is
based on? I placed this code on the Load event of the Switchboard Not the
actual form that I would like to change the criteria on. Should I also place
some code in the actual form itself? Thanks for your help

KARL, you got me to thinkin' and schemin' and I came up with an idea.

Here is what I found:

Private Sub Form_Load()
Dim ctl As Control
Set ctl = Screen.ActiveControl
MsgBox ctl.Name
End Sub

As the button that opens this form is the Option 1 button on the
switchboard, it returns "Option1"

How clever!

So, OP, here is what you can do. First, open the switchboard form in design
view. Not the Switchboard Manager, but select forms, and open the
switchboard form in design view. Now open the Properties dialog and find the
name of the two buttons that open the form. In the example below

Private Sub Form_Load()
Dim ctl As Control

Set ctl = Screen.ActiveControl
If ctl.Name = "Option6" Then 'IsNull Selected
Me.Filter = "[CriteriaField] Is Null"
Else 'Is Not Null Selected
Me.Filter = "[CriteriaField] Is Not Null"
End If

Me.FilterOn = True
Set ctl = Noting
End Sub

Wow! between us, KARL, we is almost genius level <g>
Thanks for the proding.

Hey, John, if you haven't given up and you have any questions about this,
feel free to post back.
--
Dave Hargis, Microsoft Access MVP


:

The form being opened would have no way of knowing which switchboard button
was pressed.
I was thinking of criteria for the query instead.
--
KARL DEWEY
Build a little - Test a little


:

I don't think so.
The proble is that using the SwitchBoard manager, the only thing you can do
to open a form is select OpenForm, and a Form Name.
The form being opened would have no way of knowing which switchboard button
was pressed.

I thought about running a Macro, but there is no option in a macro to send
an OpenArgs to a form.

The problem is Macros and the Switchboard are "crutches" for people who
don't know VBA. Certainly they are valuable tools, but have some sever
limitations.

The only other thing I can think of would be to use a Run Code command, and
write two small functions that pass an OpenArg value to the form in the
OpenForm method, but if the OP is familiar with using VBA, that would be of
no value to him.
--
Dave Hargis, Microsoft Access MVP


:

Dave could you not read which command button was selected?
--
KARL DEWEY
Build a little - Test a little


:

Can't do it from the Switchboard. It is not smart enough.
--
Dave Hargis, Microsoft Access MVP


:

Hello I'm building an App that uses the switchboard functions of access. I
have a form that can be opened from the switchboard. This forms recordset is
a query with the criteria on one field set to "is not null". I would like to
have a second button on the switchboard that would open the same form, but
change the query's criteria to "is null". How would be the best way to
accomplish this?

v/r
john

 

[Klatuu]

It wouldn't change the query, it set the form's filter which basically does
the same thing. It has been a while since I used 2000, so I don't know if it
will work in that version and I don't have a copy so I can't test it.

I find it interesting there is that difference. As you probably read, I was
thinking there wouldn't be a way to do it, but KARL got me to thinking and I
experimented with it until I got it to work with the code I posted.

--
Dave Hargis, Microsoft Access MVP

2000, but I'm getting ready to upgrade to 2007! Can you explain how it would
change the actual forms underlying query. Or am I just way of base

v/r
john

What version of Access are you using?
I tested this in 2003 and it worked for me.
--
Dave Hargis, Microsoft Access MVP

Thanks Klatuu, But Now that I have add the code to the On load event. I'm
getting an error "Run-time error '2474':" The expression you entered requires
the control to be in the active window.
Here is the code I have entered:
Private Sub Form_Load()
Dim ctl As Control

Set ctl = Screen.ActiveControl
If ctl.Name = "Option1" Then 'IsNull Selected
Me.Filter = "[Ccloseddate] Is Null"
Else 'Is Not Null Selected
Me.Filter = "[Ccloseddate] Is Not Null"
End If
Me.FilterOn = True
Set ctl = Noting

End Sub

I'm not sure I understand how this would work or if I did it correctly! How
would the Me.Filter change the criteria of the underlying query the form is
based on? I placed this code on the Load event of the Switchboard Not the
actual form that I would like to change the criteria on. Should I also place
some code in the actual form itself? Thanks for your help


:

KARL, you got me to thinkin' and schemin' and I came up with an idea.

Here is what I found:

Private Sub Form_Load()
Dim ctl As Control
Set ctl = Screen.ActiveControl
MsgBox ctl.Name
End Sub

As the button that opens this form is the Option 1 button on the
switchboard, it returns "Option1"

How clever!

So, OP, here is what you can do. First, open the switchboard form in design
view. Not the Switchboard Manager, but select forms, and open the
switchboard form in design view. Now open the Properties dialog and find the
name of the two buttons that open the form. In the example below

Private Sub Form_Load()
Dim ctl As Control

Set ctl = Screen.ActiveControl
If ctl.Name = "Option6" Then 'IsNull Selected
Me.Filter = "[CriteriaField] Is Null"
Else 'Is Not Null Selected
Me.Filter = "[CriteriaField] Is Not Null"
End If

Me.FilterOn = True
Set ctl = Noting
End Sub

Wow! between us, KARL, we is almost genius level <g>
Thanks for the proding.

Hey, John, if you haven't given up and you have any questions about this,
feel free to post back.
--
Dave Hargis, Microsoft Access MVP


:

The form being opened would have no way of knowing which switchboard button
was pressed.
I was thinking of criteria for the query instead.
--
KARL DEWEY
Build a little - Test a little


:

I don't think so.
The proble is that using the SwitchBoard manager, the only thing you can do
to open a form is select OpenForm, and a Form Name.
The form being opened would have no way of knowing which switchboard button
was pressed.

I thought about running a Macro, but there is no option in a macro to send
an OpenArgs to a form.

The problem is Macros and the Switchboard are "crutches" for people who
don't know VBA. Certainly they are valuable tools, but have some sever
limitations.

The only other thing I can think of would be to use a Run Code command, and
write two small functions that pass an OpenArg value to the form in the
OpenForm method, but if the OP is familiar with using VBA, that would be of
no value to him.
--
Dave Hargis, Microsoft Access MVP


:

Dave could you not read which command button was selected?
--
KARL DEWEY
Build a little - Test a little


:

Can't do it from the Switchboard. It is not smart enough.
--
Dave Hargis, Microsoft Access MVP


:

Hello I'm building an App that uses the switchboard functions of access. I
have a form that can be opened from the switchboard. This forms recordset is
a query with the criteria on one field set to "is not null". I would like to
have a second button on the switchboard that would open the same form, but
change the query's criteria to "is null". How would be the best way to
accomplish this?

v/r
john

 

[John]

Am I correct in that you did not add any code to the actual form, Just the
switchboard form?

It wouldn't change the query, it set the form's filter which basically does
the same thing. It has been a while since I used 2000, so I don't know if it
will work in that version and I don't have a copy so I can't test it.

I find it interesting there is that difference. As you probably read, I was
thinking there wouldn't be a way to do it, but KARL got me to thinking and I
experimented with it until I got it to work with the code I posted.

--
Dave Hargis, Microsoft Access MVP

2000, but I'm getting ready to upgrade to 2007! Can you explain how it would
change the actual forms underlying query. Or am I just way of base

v/r
john

What version of Access are you using?
I tested this in 2003 and it worked for me.
--
Dave Hargis, Microsoft Access MVP


:

Thanks Klatuu, But Now that I have add the code to the On load event. I'm
getting an error "Run-time error '2474':" The expression you entered requires
the control to be in the active window.
Here is the code I have entered:
Private Sub Form_Load()
Dim ctl As Control

Set ctl = Screen.ActiveControl
If ctl.Name = "Option1" Then 'IsNull Selected
Me.Filter = "[Ccloseddate] Is Null"
Else 'Is Not Null Selected
Me.Filter = "[Ccloseddate] Is Not Null"
End If
Me.FilterOn = True
Set ctl = Noting

End Sub

I'm not sure I understand how this would work or if I did it correctly! How
would the Me.Filter change the criteria of the underlying query the form is
based on? I placed this code on the Load event of the Switchboard Not the
actual form that I would like to change the criteria on. Should I also place
some code in the actual form itself? Thanks for your help


:

KARL, you got me to thinkin' and schemin' and I came up with an idea.

Here is what I found:

Private Sub Form_Load()
Dim ctl As Control
Set ctl = Screen.ActiveControl
MsgBox ctl.Name
End Sub

As the button that opens this form is the Option 1 button on the
switchboard, it returns "Option1"

How clever!

So, OP, here is what you can do. First, open the switchboard form in design
view. Not the Switchboard Manager, but select forms, and open the
switchboard form in design view. Now open the Properties dialog and find the
name of the two buttons that open the form. In the example below

Private Sub Form_Load()
Dim ctl As Control

Set ctl = Screen.ActiveControl
If ctl.Name = "Option6" Then 'IsNull Selected
Me.Filter = "[CriteriaField] Is Null"
Else 'Is Not Null Selected
Me.Filter = "[CriteriaField] Is Not Null"
End If

Me.FilterOn = True
Set ctl = Noting
End Sub

Wow! between us, KARL, we is almost genius level <g>
Thanks for the proding.

Hey, John, if you haven't given up and you have any questions about this,
feel free to post back.
--
Dave Hargis, Microsoft Access MVP


:

The form being opened would have no way of knowing which switchboard button
was pressed.
I was thinking of criteria for the query instead.
--
KARL DEWEY
Build a little - Test a little


:

I don't think so.
The proble is that using the SwitchBoard manager, the only thing you can do
to open a form is select OpenForm, and a Form Name.
The form being opened would have no way of knowing which switchboard button
was pressed.

I thought about running a Macro, but there is no option in a macro to send
an OpenArgs to a form.

The problem is Macros and the Switchboard are "crutches" for people who
don't know VBA. Certainly they are valuable tools, but have some sever
limitations.

The only other thing I can think of would be to use a Run Code command, and
write two small functions that pass an OpenArg value to the form in the
OpenForm method, but if the OP is familiar with using VBA, that would be of
no value to him.
--
Dave Hargis, Microsoft Access MVP


:

Dave could you not read which command button was selected?
--
KARL DEWEY
Build a little - Test a little


:

Can't do it from the Switchboard. It is not smart enough.
--
Dave Hargis, Microsoft Access MVP


:

Hello I'm building an App that uses the switchboard functions of access. I
have a form that can be opened from the switchboard. This forms recordset is
a query with the criteria on one field set to "is not null". I would like to
have a second button on the switchboard that would open the same form, but
change the query's criteria to "is null". How would be the best way to
accomplish this?

v/r
john

 

[Klatuu]

No, I put the code in the form I open from the Switchboard, not in the
switchboard itself. Maybe that is the problem.
--
Dave Hargis, Microsoft Access MVP

Am I correct in that you did not add any code to the actual form, Just the
switchboard form?

It wouldn't change the query, it set the form's filter which basically does
the same thing. It has been a while since I used 2000, so I don't know if it
will work in that version and I don't have a copy so I can't test it.

I find it interesting there is that difference. As you probably read, I was
thinking there wouldn't be a way to do it, but KARL got me to thinking and I
experimented with it until I got it to work with the code I posted.

--
Dave Hargis, Microsoft Access MVP

2000, but I'm getting ready to upgrade to 2007! Can you explain how it would
change the actual forms underlying query. Or am I just way of base

v/r
john

:

What version of Access are you using?
I tested this in 2003 and it worked for me.
--
Dave Hargis, Microsoft Access MVP


:

Thanks Klatuu, But Now that I have add the code to the On load event. I'm
getting an error "Run-time error '2474':" The expression you entered requires
the control to be in the active window.
Here is the code I have entered:
Private Sub Form_Load()
Dim ctl As Control

Set ctl = Screen.ActiveControl
If ctl.Name = "Option1" Then 'IsNull Selected
Me.Filter = "[Ccloseddate] Is Null"
Else 'Is Not Null Selected
Me.Filter = "[Ccloseddate] Is Not Null"
End If
Me.FilterOn = True
Set ctl = Noting

End Sub

I'm not sure I understand how this would work or if I did it correctly! How
would the Me.Filter change the criteria of the underlying query the form is
based on? I placed this code on the Load event of the Switchboard Not the
actual form that I would like to change the criteria on. Should I also place
some code in the actual form itself? Thanks for your help


:

KARL, you got me to thinkin' and schemin' and I came up with an idea.

Here is what I found:

Private Sub Form_Load()
Dim ctl As Control
Set ctl = Screen.ActiveControl
MsgBox ctl.Name
End Sub

As the button that opens this form is the Option 1 button on the
switchboard, it returns "Option1"

How clever!

So, OP, here is what you can do. First, open the switchboard form in design
view. Not the Switchboard Manager, but select forms, and open the
switchboard form in design view. Now open the Properties dialog and find the
name of the two buttons that open the form. In the example below

Private Sub Form_Load()
Dim ctl As Control

Set ctl = Screen.ActiveControl
If ctl.Name = "Option6" Then 'IsNull Selected
Me.Filter = "[CriteriaField] Is Null"
Else 'Is Not Null Selected
Me.Filter = "[CriteriaField] Is Not Null"
End If

Me.FilterOn = True
Set ctl = Noting
End Sub

Wow! between us, KARL, we is almost genius level <g>
Thanks for the proding.

Hey, John, if you haven't given up and you have any questions about this,
feel free to post back.
--
Dave Hargis, Microsoft Access MVP


:

The form being opened would have no way of knowing which switchboard button
was pressed.
I was thinking of criteria for the query instead.
--
KARL DEWEY
Build a little - Test a little


:

I don't think so.
The proble is that using the SwitchBoard manager, the only thing you can do
to open a form is select OpenForm, and a Form Name.
The form being opened would have no way of knowing which switchboard button
was pressed.

I thought about running a Macro, but there is no option in a macro to send
an OpenArgs to a form.

The problem is Macros and the Switchboard are "crutches" for people who
don't know VBA. Certainly they are valuable tools, but have some sever
limitations.

The only other thing I can think of would be to use a Run Code command, and
write two small functions that pass an OpenArg value to the form in the
OpenForm method, but if the OP is familiar with using VBA, that would be of
no value to him.
--
Dave Hargis, Microsoft Access MVP


:

Dave could you not read which command button was selected?
--
KARL DEWEY
Build a little - Test a little


:

Can't do it from the Switchboard. It is not smart enough.
--
Dave Hargis, Microsoft Access MVP


:

Hello I'm building an App that uses the switchboard functions of access. I
have a form that can be opened from the switchboard. This forms recordset is
a query with the criteria on one field set to "is not null". I would like to
have a second button on the switchboard that would open the same form, but
change the query's criteria to "is null". How would be the best way to
accomplish this?

v/r
john

 

[John]

Thanks Klatuu, I put the code in the actual form and it worked. With one
exception. I still got an error on this line Set ctl = Noting, but I just
commented out that line and all worked. Is that line important? I think it
may be a spelling error "Nothing" but not sure and don't care if I can get
by without it. Thanks again for all your help. I'm slowwwly learning a
little about VBA! You have been great!!!

No, I put the code in the form I open from the Switchboard, not in the
switchboard itself. Maybe that is the problem.
--
Dave Hargis, Microsoft Access MVP

Am I correct in that you did not add any code to the actual form, Just the
switchboard form?

It wouldn't change the query, it set the form's filter which basically does
the same thing. It has been a while since I used 2000, so I don't know if it
will work in that version and I don't have a copy so I can't test it.

I find it interesting there is that difference. As you probably read, I was
thinking there wouldn't be a way to do it, but KARL got me to thinking and I
experimented with it until I got it to work with the code I posted.

--
Dave Hargis, Microsoft Access MVP


:

2000, but I'm getting ready to upgrade to 2007! Can you explain how it would
change the actual forms underlying query. Or am I just way of base

v/r
john

:

What version of Access are you using?
I tested this in 2003 and it worked for me.
--
Dave Hargis, Microsoft Access MVP


:

Thanks Klatuu, But Now that I have add the code to the On load event. I'm
getting an error "Run-time error '2474':" The expression you entered requires
the control to be in the active window.
Here is the code I have entered:
Private Sub Form_Load()
Dim ctl As Control

Set ctl = Screen.ActiveControl
If ctl.Name = "Option1" Then 'IsNull Selected
Me.Filter = "[Ccloseddate] Is Null"
Else 'Is Not Null Selected
Me.Filter = "[Ccloseddate] Is Not Null"
End If
Me.FilterOn = True
Set ctl = Noting

End Sub

I'm not sure I understand how this would work or if I did it correctly! How
would the Me.Filter change the criteria of the underlying query the form is
based on? I placed this code on the Load event of the Switchboard Not the
actual form that I would like to change the criteria on. Should I also place
some code in the actual form itself? Thanks for your help


:

KARL, you got me to thinkin' and schemin' and I came up with an idea.

Here is what I found:

Private Sub Form_Load()
Dim ctl As Control
Set ctl = Screen.ActiveControl
MsgBox ctl.Name
End Sub

As the button that opens this form is the Option 1 button on the
switchboard, it returns "Option1"

How clever!

So, OP, here is what you can do. First, open the switchboard form in design
view. Not the Switchboard Manager, but select forms, and open the
switchboard form in design view. Now open the Properties dialog and find the
name of the two buttons that open the form. In the example below

Private Sub Form_Load()
Dim ctl As Control

Set ctl = Screen.ActiveControl
If ctl.Name = "Option6" Then 'IsNull Selected
Me.Filter = "[CriteriaField] Is Null"
Else 'Is Not Null Selected
Me.Filter = "[CriteriaField] Is Not Null"
End If

Me.FilterOn = True
Set ctl = Noting
End Sub

Wow! between us, KARL, we is almost genius level <g>
Thanks for the proding.

Hey, John, if you haven't given up and you have any questions about this,
feel free to post back.
--
Dave Hargis, Microsoft Access MVP


:

The form being opened would have no way of knowing which switchboard button
was pressed.
I was thinking of criteria for the query instead.
--
KARL DEWEY
Build a little - Test a little


:

I don't think so.
The proble is that using the SwitchBoard manager, the only thing you can do
to open a form is select OpenForm, and a Form Name.
The form being opened would have no way of knowing which switchboard button
was pressed.

I thought about running a Macro, but there is no option in a macro to send
an OpenArgs to a form.

The problem is Macros and the Switchboard are "crutches" for people who
don't know VBA. Certainly they are valuable tools, but have some sever
limitations.

The only other thing I can think of would be to use a Run Code command, and
write two small functions that pass an OpenArg value to the form in the
OpenForm method, but if the OP is familiar with using VBA, that would be of
no value to him.
--
Dave Hargis, Microsoft Access MVP


:

Dave could you not read which command button was selected?
--
KARL DEWEY
Build a little - Test a little


:

Can't do it from the Switchboard. It is not smart enough.
--
Dave Hargis, Microsoft Access MVP


:

Hello I'm building an App that uses the switchboard functions of access. I
have a form that can be opened from the switchboard. This forms recordset is
a query with the criteria on one field set to "is not null". I would like to
have a second button on the switchboard that would open the same form, but
change the query's criteria to "is null". How would be the best way to
accomplish this?

v/r
john

 

[Klatuu]

typo on my part, it should be Nothing

It is not required, but a good practice to set all object references to
Nothing when you are done with them.

Glad it worked.
I was really excited about figuring that out.
We both learned something!
--
Dave Hargis, Microsoft Access MVP

Thanks Klatuu, I put the code in the actual form and it worked. With one
exception. I still got an error on this line Set ctl = Noting, but I just
commented out that line and all worked. Is that line important? I think it
may be a spelling error "Nothing" but not sure and don't care if I can get
by without it. Thanks again for all your help. I'm slowwwly learning a
little about VBA! You have been great!!!

No, I put the code in the form I open from the Switchboard, not in the
switchboard itself. Maybe that is the problem.
--
Dave Hargis, Microsoft Access MVP

Am I correct in that you did not add any code to the actual form, Just the
switchboard form?

:

It wouldn't change the query, it set the form's filter which basically does
the same thing. It has been a while since I used 2000, so I don't know if it
will work in that version and I don't have a copy so I can't test it.

I find it interesting there is that difference. As you probably read, I was
thinking there wouldn't be a way to do it, but KARL got me to thinking and I
experimented with it until I got it to work with the code I posted.

--
Dave Hargis, Microsoft Access MVP


:

2000, but I'm getting ready to upgrade to 2007! Can you explain how it would
change the actual forms underlying query. Or am I just way of base

v/r
john

:

What version of Access are you using?
I tested this in 2003 and it worked for me.
--
Dave Hargis, Microsoft Access MVP


:

Thanks Klatuu, But Now that I have add the code to the On load event. I'm
getting an error "Run-time error '2474':" The expression you entered requires
the control to be in the active window.
Here is the code I have entered:
Private Sub Form_Load()
Dim ctl As Control

Set ctl = Screen.ActiveControl
If ctl.Name = "Option1" Then 'IsNull Selected
Me.Filter = "[Ccloseddate] Is Null"
Else 'Is Not Null Selected
Me.Filter = "[Ccloseddate] Is Not Null"
End If
Me.FilterOn = True
Set ctl = Noting

End Sub

I'm not sure I understand how this would work or if I did it correctly! How
would the Me.Filter change the criteria of the underlying query the form is
based on? I placed this code on the Load event of the Switchboard Not the
actual form that I would like to change the criteria on. Should I also place
some code in the actual form itself? Thanks for your help


:

KARL, you got me to thinkin' and schemin' and I came up with an idea.

Here is what I found:

Private Sub Form_Load()
Dim ctl As Control
Set ctl = Screen.ActiveControl
MsgBox ctl.Name
End Sub

As the button that opens this form is the Option 1 button on the
switchboard, it returns "Option1"

How clever!

So, OP, here is what you can do. First, open the switchboard form in design
view. Not the Switchboard Manager, but select forms, and open the
switchboard form in design view. Now open the Properties dialog and find the
name of the two buttons that open the form. In the example below

Private Sub Form_Load()
Dim ctl As Control

Set ctl = Screen.ActiveControl
If ctl.Name = "Option6" Then 'IsNull Selected
Me.Filter = "[CriteriaField] Is Null"
Else 'Is Not Null Selected
Me.Filter = "[CriteriaField] Is Not Null"
End If

Me.FilterOn = True
Set ctl = Noting
End Sub

Wow! between us, KARL, we is almost genius level <g>
Thanks for the proding.

Hey, John, if you haven't given up and you have any questions about this,
feel free to post back.
--
Dave Hargis, Microsoft Access MVP


:

The form being opened would have no way of knowing which switchboard button
was pressed.
I was thinking of criteria for the query instead.
--
KARL DEWEY
Build a little - Test a little


:

I don't think so.
The proble is that using the SwitchBoard manager, the only thing you can do
to open a form is select OpenForm, and a Form Name.
The form being opened would have no way of knowing which switchboard button
was pressed.

I thought about running a Macro, but there is no option in a macro to send
an OpenArgs to a form.

The problem is Macros and the Switchboard are "crutches" for people who
don't know VBA. Certainly they are valuable tools, but have some sever
limitations.

The only other thing I can think of would be to use a Run Code command, and
write two small functions that pass an OpenArg value to the form in the
OpenForm method, but if the OP is familiar with using VBA, that would be of
no value to him.
--
Dave Hargis, Microsoft Access MVP


:

Dave could you not read which command button was selected?
--
KARL DEWEY
Build a little - Test a little


:

Can't do it from the Switchboard. It is not smart enough.
--
Dave Hargis, Microsoft Access MVP


:

Hello I'm building an App that uses the switchboard functions of access. I
have a form that can be opened from the switchboard. This forms recordset is
a query with the criteria on one field set to "is not null". I would like to
have a second button on the switchboard that would open the same form, but
change the query's criteria to "is null". How would be the best way to
accomplish this?

v/r
john

 

[Laura Finn - Fly By Night]

I have a similar switchboard issue -- my initial solution was to save the
form with a new name i.e MainForm & FormCriteria1. It works, but is there
anyway to associate the controls and subforms with the new form without
having to change all of the form name references.? Can I use a wildcard for
the form name so it will work on both MainForm and FormCriteria1??

Here's a general description of my database ------ My main form has several
tabbed pages each with 1 - 3 subforms and one of more command buttons. A
command button located on a tabbed page is set to open a form using an
embedded macro for the onclick as follows =
[ProjectID]=[Forms]![MainForm]![ProjectID]. I am using Access2007 (recently
converted from 2003).

Hope this makes sense, this is my first post to any newsgroup and my
database knowledge is minimal at best. The info I've gleaned from this group
has been a huge help ---

 

[Klatuu]

No, you can't use wild cards when referencing controls and subforms. They
must be explicit.

Can you provide a bit more description. What is it that is different about
your two forms?
--
Dave Hargis, Microsoft Access MVP

I have a similar switchboard issue -- my initial solution was to save the
form with a new name i.e MainForm & FormCriteria1. It works, but is there
anyway to associate the controls and subforms with the new form without
having to change all of the form name references.? Can I use a wildcard for
the form name so it will work on both MainForm and FormCriteria1??

Here's a general description of my database ------ My main form has several
tabbed pages each with 1 - 3 subforms and one of more command buttons. A
command button located on a tabbed page is set to open a form using an
embedded macro for the onclick as follows =
[ProjectID]=[Forms]![MainForm]![ProjectID]. I am using Access2007 (recently
converted from 2003).

Hope this makes sense, this is my first post to any newsgroup and my
database knowledge is minimal at best. The info I've gleaned from this group
has been a huge help ---

--
Laura S Finn
Fly By Night, Inc
www.flybynightinc.org

Hello I'm building an App that uses the switchboard functions of access. I
have a form that can be opened from the switchboard. This forms recordset is
a query with the criteria on one field set to "is not null". I would like to
have a second button on the switchboard that would open the same form, but
change the query's criteria to "is null". How would be the best way to
accomplish this?

v/r
john

 

[Laura Finn - Fly By Night]

Hi Dave,
Basically, I can do every thing from the MainForm, the filtered versions
allow me to focus easily on the data for a particular page (tab) when the
form is opened from the switchboard. Since I'm a biologist & not a database
person I thought it might be easier than filtering the form everytime.
My database is fairly complex & remains a work in progress that I continue
to tweak. I'd love to have a 'real' database person take a look at it & help
me work out the bugs; but so far no takers. The scary thing is that as I
learn more about database design, I realize it could certainly be greatly
simplified & is probably dirtier than I even know. -- ^^0^^
Thanks for you help,

Laura S Finn
Fly By Night, Inc
www.flybynightinc.org

No, you can't use wild cards when referencing controls and subforms. They
must be explicit.

Can you provide a bit more description. What is it that is different about
your two forms?
--
Dave Hargis, Microsoft Access MVP

I have a similar switchboard issue -- my initial solution was to save the
form with a new name i.e MainForm & FormCriteria1. It works, but is there
anyway to associate the controls and subforms with the new form without
having to change all of the form name references.? Can I use a wildcard for
the form name so it will work on both MainForm and FormCriteria1??

Here's a general description of my database ------ My main form has several
tabbed pages each with 1 - 3 subforms and one of more command buttons. A
command button located on a tabbed page is set to open a form using an
embedded macro for the onclick as follows =
[ProjectID]=[Forms]![MainForm]![ProjectID]. I am using Access2007 (recently
converted from 2003).

Hope this makes sense, this is my first post to any newsgroup and my
database knowledge is minimal at best. The info I've gleaned from this group
has been a huge help ---

--
Laura S Finn
Fly By Night, Inc
www.flybynightinc.org

Hello I'm building an App that uses the switchboard functions of access. I
have a form that can be opened from the switchboard. This forms recordset is
a query with the criteria on one field set to "is not null". I would like to
have a second button on the switchboard that would open the same form, but
change the query's criteria to "is null". How would be the best way to
accomplish this?

v/r
john