open form and assign record souce

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

I want to click a button on the switchboard and have it
open a form and assign the recordsource, which will be
based on the selection that is made.
 
On open of the form place code to check the value of the control on your
first form and respond accordingly:

Private Sub Form_Open(Cancel As Integer)
Dim str As String
str = Forms!YourFirstForm.Form.YourControl
Select Case str
Case "whatever"
Me.Recordsource = "qry1"
Me.Requery
Case "whateverelse"
Me.Recordsource = "qry2"
Me.Requery
End Select
 
This won't work - Recordsource needs to be a valid SQL string.

Try
Me.Recordsource = "SELECT * FROM qry1"
 
Norm, I'm not sure, but I've tested the code below and it works like a champ
on my machine A2K & WXP Pro.
 
Though what you say may be correct the information below came from the help
file and I've used it many times In my apps. If you have some documentation
as to the contrary please let me know so I can investigate. Thanks!

The RecordSource property setting can be a table name, a query name, or an
SQL statement.

RecordSource Property Example
The following example sets a form's RecordSource property to the Customers
table:
Forms!frmCustomers.RecordSource = "Customers"
 
ok I am having a problem with assigning the str. I am not
sure what the myControl part should be. I have tried to
assign it the names of the buttons, option1, option2, etc
and the name that the switchbord has for it. This is what
I have so far:

Dim str As String
str = Forms!SwitchBoard.Form.[myControl?]
MsgBox str
'MsgBox Forms!SwitchBoard.Form.ActiveControl
Select Case str
Case "option1"
Me.RecordSource = "Active"
Me.Requery
Case "everyone"
Me.RecordSource = "All"
Me.Requery
End Select

Also what should come after the case. Should it just be
the name of the myControl or should it be something
like "myControl = ..." or should it be the whole str.
 
Dan, what kind of control on your switchboard are you using to
select/determine the recordsource for the form that you are opening? If
it's a combobox then myControl is the name of your combobox control on your
switchboard. If it's an option group then this would be the name of your
option group control on your switchboard. If it is an option group keep in
mind that the return value of the option group is a long integer. For
example if you had 3 choices in your option group and assuming you kept the
default values access gives when you place/select the options for the option
group, then the first item you added to the group would be = 1, the second =
2, etc. You can view the option value by selecting the option button in the
option group and viewing the properties wind. So, having said that and if
your control is an option group, and pretend this option group is called
myOptionGroup and it is located on frmSwitchboard then you could use
something like this on the On Open event of the form being opened.

Dim int As Long
int = Forms!SwitchBoard.Form.[myOptionGroup]
Select Case int
Case 1
Me.RecordSource = "Active"
Me.Requery
Case 2
Me.RecordSource = "All"
Me.Requery
Case Else
MsgBox "You didn't select anything"
End Select




Reggie

----------
Dan said:
ok I am having a problem with assigning the str. I am not
sure what the myControl part should be. I have tried to
assign it the names of the buttons, option1, option2, etc
and the name that the switchbord has for it. This is what
I have so far:

Dim str As String
str = Forms!SwitchBoard.Form.[myControl?]
MsgBox str
'MsgBox Forms!SwitchBoard.Form.ActiveControl
Select Case str
Case "option1"
Me.RecordSource = "Active"
Me.Requery
Case "everyone"
Me.RecordSource = "All"
Me.Requery
End Select

Also what should come after the case. Should it just be
the name of the myControl or should it be something
like "myControl = ..." or should it be the whole str.
-----Original Message-----
Though what you say may be correct the information below came from the help
file and I've used it many times In my apps. If you have some documentation
as to the contrary please let me know so I can investigate. Thanks!

The RecordSource property setting can be a table name, a query name, or an
SQL statement.

RecordSource Property Example
The following example sets a form's RecordSource property to the Customers
table:
Forms!frmCustomers.RecordSource = "Customers"

--
Reggie

----------
it works like a
champ of the control on
your


.
Click to expand...
Click to expand...
 
I believe that they are just command buttons. I want it
so that if they click the top button it will do one thing
and if they click the next button down it will do
something different and so on.

-----Original Message-----
Dan, what kind of control on your switchboard are you using to
select/determine the recordsource for the form that you are opening? If
it's a combobox then myControl is the name of your combobox control on your
switchboard. If it's an option group then this would be the name of your
option group control on your switchboard. If it is an option group keep in
mind that the return value of the option group is a long integer. For
example if you had 3 choices in your option group and assuming you kept the
default values access gives when you place/select the options for the option
group, then the first item you added to the group would be = 1, the second =
2, etc. You can view the option value by selecting the option button in the
option group and viewing the properties wind. So, having said that and if
your control is an option group, and pretend this option group is called
myOptionGroup and it is located on frmSwitchboard then you could use
something like this on the On Open event of the form being opened.

Dim int As Long
int = Forms!SwitchBoard.Form.[myOptionGroup]
Select Case int
Case 1
Me.RecordSource = "Active"
Me.Requery
Case 2
Me.RecordSource = "All"
Me.Requery
Case Else
MsgBox "You didn't select anything"
End Select




Reggie

----------
ok I am having a problem with assigning the str. I am not
sure what the myControl part should be. I have tried to
assign it the names of the buttons, option1, option2, etc
and the name that the switchbord has for it. This is what
I have so far:

Dim str As String
str = Forms!SwitchBoard.Form.[myControl?]
MsgBox str
'MsgBox Forms!SwitchBoard.Form.ActiveControl
Select Case str
Case "option1"
Me.RecordSource = "Active"
Me.Requery
Case "everyone"
Me.RecordSource = "All"
Me.Requery
End Select

Also what should come after the case. Should it just be
the name of the myControl or should it be something
like "myControl = ..." or should it be the whole str.
-----Original Message-----
Though what you say may be correct the information
Click to expand...
below
came from the help
file and I've used it many times In my apps. If you have some documentation
as to the contrary please let me know so I can investigate. Thanks!

The RecordSource property setting can be a table
Click to expand...
name, a
query name, or an
SQL statement.

RecordSource Property Example
The following example sets a form's RecordSource property to the Customers
table:
Forms!frmCustomers.RecordSource = "Customers"
Click to expand...
and
it works like a
champ
on my machine A2K & WXP Pro.

--
Reggie

----------
This won't work - Recordsource needs to be a valid SQL string.

Try
Me.Recordsource = "SELECT * FROM qry1"



On Tue, 20 Jul 2004 03:35:59 -0700, "Reggie"

On open of the form place code to check the value of the control on
your
first form and respond accordingly:

Private Sub Form_Open(Cancel As Integer)
Dim str As String
str = Forms!YourFirstForm.Form.YourControl
Select Case str
Case "whatever"
Me.Recordsource = "qry1"
Me.Requery
Case "whateverelse"
Me.Recordsource = "qry2"
Me.Requery
End Select





.
Click to expand...
Click to expand...


.
Click to expand...
 
I just use the switchboard manager if that makes any
difference

-----Original Message-----
I believe that they are just command buttons. I want it
so that if they click the top button it will do one thing
and if they click the next button down it will do
something different and so on.

-----Original Message-----
Dan, what kind of control on your switchboard are you using to
select/determine the recordsource for the form that you are opening? If
it's a combobox then myControl is the name of your combobox control on your
switchboard. If it's an option group then this would be the name of your
option group control on your switchboard. If it is an option group keep in
mind that the return value of the option group is a
Click to expand...
long
integer. For
example if you had 3 choices in your option group and assuming you kept the
default values access gives when you place/select the options for the option
group, then the first item you added to the group would be = 1, the second =
2, etc. You can view the option value by selecting the option button in the
option group and viewing the properties wind. So, having said that and if
your control is an option group, and pretend this
Click to expand...
option
group is called
myOptionGroup and it is located on frmSwitchboard then you could use
something like this on the On Open event of the form being opened.

Dim int As Long
int = Forms!SwitchBoard.Form.[myOptionGroup]
Select Case int
Case 1
Me.RecordSource = "Active"
Me.Requery
Case 2
Me.RecordSource = "All"
Me.Requery
Case Else
MsgBox "You didn't select anything"
End Select




Reggie

----------
ok I am having a problem with assigning the str. I am not
sure what the myControl part should be. I have tried to
assign it the names of the buttons, option1, option2, etc
and the name that the switchbord has for it. This is what
I have so far:

Dim str As String
str = Forms!SwitchBoard.Form.[myControl?]
MsgBox str
'MsgBox Forms!SwitchBoard.Form.ActiveControl
Select Case str
Case "option1"
Me.RecordSource = "Active"
Me.Requery
Case "everyone"
Me.RecordSource = "All"
Me.Requery
End Select

Also what should come after the case. Should it just be
the name of the myControl or should it be something
like "myControl = ..." or should it be the whole str.

-----Original Message-----
Though what you say may be correct the information below
came from the help
file and I've used it many times In my apps. If you
have some documentation
as to the contrary please let me know so I can
investigate. Thanks!

The RecordSource property setting can be a table name, a
query name, or an
SQL statement.

RecordSource Property Example
The following example sets a form's RecordSource
property to the Customers
table:
Forms!frmCustomers.RecordSource = "Customers"

--
Reggie

----------
message
Norm, I'm not sure, but I've tested the code below and
it works like a
champ
on my machine A2K & WXP Pro.

--
Reggie

----------
This won't work - Recordsource needs to be a valid
SQL string.

Try
Me.Recordsource = "SELECT * FROM qry1"



On Tue, 20 Jul 2004 03:35:59 -0700, "Reggie"

On open of the form place code to check the value
of the control on
your
first form and respond accordingly:

Private Sub Form_Open(Cancel As Integer)
Dim str As String
str = Forms!YourFirstForm.Form.YourControl
Select Case str
Case "whatever"
Me.Recordsource = "qry1"
Me.Requery
Case "whateverelse"
Me.Recordsource = "qry2"
Me.Requery
End Select





.
Click to expand...


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