Connecting commands on form

  • Thread starter Thread starter Evan
  • Start date Start date
E

Evan

I've created a form with a drop-down list, command button and subform and
would like to connect the three together. So that when name1 is selected
from the dropdown list and a command button "Go" is clicked name1's subform
opens as the subform in the form or if name2 is selected and the "go" button
is clicked then name2's subform will appear as the subform. Is this
possible? Can anyone help? I'm assuming that I need to use the
DoCmd:OpenSubForm and then use an If ElseIf Statement.
 
Evan it is even easier than that. When you select a name from an unbound
combobox, you can have the form move to that record, and the subform will as
well.

Two things that make it very easy. When you add the dropdown list, use 2
columns, the ID (primary key) column, and the Name. If you have wizards
enabled (the magic wand in the toolbox) a dialog will come up to walk you
through the process.

Second, when you add a subform control, from the toolbox, a wizard will walk
you through the process and automatically link the form and subform.
 
I wish this were so easy. I've created a form with a combo box of names
linked to a table of names & NameID's (primary key). I've also created the
command button: "Go" using: Form Operations > Open form > specific form >
open form/show all records. The specific form opens as a separate form. I
need the form named in the combo box to open as a subform in the form. How
do I do that?
 
I wrote code so that when a name in a combo box is clicked on its
corrosponding form opens but as a separate window. Is it possible to get the
form to open as a subform of the form on which the combo box is located?
 
By changing the Source Object property of the subform control. But it still
needs to be linked to the main form, or the recordsource has to use the
combobox as a criteria:

Sub cboNameID_AfterUpdate()
Me.NameOfSubformControl.SourceObject = "SubFormName"
Me.NameOfSubformControl.Form.RecordSource = "Select * From tblWhatever
Where NameID =" & Me.cboNameID
End Sub

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
 
I need a bit more help. When I click on a name in the comboBox I get "sub or
function not defined." Is the record set code used for the main form
causing a problem? Also wasn't sure what tbl Whatever referred to? I've
substituted the names of the cbo, subformControl, and NameID and put the
following code in the Module: Combobox AfterUpdate:

Private Sub Combo7_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CountryID] = " & Str(Nz(Me![Combo7], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me.BRSch.SourceObject = "SubFormName"
Me.BRSch.Form.RecordSource = "Select * From tblWhatever"
Where NameID = " & Me.BRSch"

End Sub

When I click on a name in the comboBox I get "sub or function not defined."
Is the record set code used for the main form causing a problem? Also
wasn't sure what tbl Whatever referred to?
 
You may not have a reference to DAO. Open any code window and uncheck the
reference to ActiveX Data Objects (ADO) and check the one marked:

Microsoft DAO 3.6 Object Library

If that still doesn't help, set a breakpoint at:

Set rs = Me.Recordset.Clone

and step through the code (F8) to find the error.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Evan said:
I need a bit more help. When I click on a name in the comboBox I get "sub
or
function not defined." Is the record set code used for the main form
causing a problem? Also wasn't sure what tbl Whatever referred to? I've
substituted the names of the cbo, subformControl, and NameID and put the
following code in the Module: Combobox AfterUpdate:

Private Sub Combo7_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CountryID] = " & Str(Nz(Me![Combo7], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me.BRSch.SourceObject = "SubFormName"
Me.BRSch.Form.RecordSource = "Select * From tblWhatever"
Where NameID = " & Me.BRSch"

End Sub

When I click on a name in the comboBox I get "sub or function not
defined."
Is the record set code used for the main form causing a problem? Also
wasn't sure what tbl Whatever referred to?

Arvin Meyer said:
By changing the Source Object property of the subform control. But it
still
needs to be linked to the main form, or the recordsource has to use the
combobox as a criteria:

Sub cboNameID_AfterUpdate()
Me.NameOfSubformControl.SourceObject = "SubFormName"
Me.NameOfSubformControl.Form.RecordSource = "Select * From
tblWhatever
Where NameID =" & Me.cboNameID
End Sub

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
Click to expand...
Click to expand...
 
The error occurs at the "Where NameID=" line. I'm not sure what the
terms: NameID and " & Me.cboNameID" refer to and think that I may be
substituting the wrong control names in my code.

Me.NameOfSubformControl.SourceObject = "SubFormName"
Me.NameOfSubformControl.Form.RecordSource = "Select * From tblWhatever "
Where NameID = " & Me.cboNameID"

Arvin Meyer said:
You may not have a reference to DAO. Open any code window and uncheck the
reference to ActiveX Data Objects (ADO) and check the one marked:

Microsoft DAO 3.6 Object Library

If that still doesn't help, set a breakpoint at:

Set rs = Me.Recordset.Clone

and step through the code (F8) to find the error.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Evan said:
I need a bit more help. When I click on a name in the comboBox I get "sub
or
function not defined." Is the record set code used for the main form
causing a problem? Also wasn't sure what tbl Whatever referred to? I've
substituted the names of the cbo, subformControl, and NameID and put the
following code in the Module: Combobox AfterUpdate:

Private Sub Combo7_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CountryID] = " & Str(Nz(Me![Combo7], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me.BRSch.SourceObject = "SubFormName"
Me.BRSch.Form.RecordSource = "Select * From tblWhatever"
Where NameID = " & Me.BRSch"

End Sub

When I click on a name in the comboBox I get "sub or function not
defined."
Is the record set code used for the main form causing a problem? Also
wasn't sure what tbl Whatever referred to?

Arvin Meyer said:
By changing the Source Object property of the subform control. But it
still
needs to be linked to the main form, or the recordsource has to use the
combobox as a criteria:

Sub cboNameID_AfterUpdate()
Me.NameOfSubformControl.SourceObject = "SubFormName"
Me.NameOfSubformControl.Form.RecordSource = "Select * From
tblWhatever
Where NameID =" & Me.cboNameID
End Sub

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


I wrote code so that when a name in a combo box is clicked on its
corrosponding form opens but as a separate window. Is it possible to
get
the
form to open as a subform of the form on which the combo box is
located?

:

I wish this were so easy. I've created a form with a combo box of
names
linked to a table of names & NameID's (primary key). I've also
created
the
command button: "Go" using: Form Operations > Open form > specific
form

open form/show all records. The specific form opens as a separate
form.
I
need the form named in the combo box to open as a subform in the form.
How
do I do that?

:

Evan it is even easier than that. When you select a name from an
unbound
combobox, you can have the form move to that record, and the subform
will as
well.

Two things that make it very easy. When you add the dropdown list,
use
2
columns, the ID (primary key) column, and the Name. If you have
wizards
enabled (the magic wand in the toolbox) a dialog will come up to
walk
you
through the process.

Second, when you add a subform control, from the toolbox, a wizard
will
walk
you through the process and automatically link the form and subform.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


I've created a form with a drop-down list, command button and
subform
and
would like to connect the three together. So that when name1 is
selected
from the dropdown list and a command button "Go" is clicked
name1's
subform
opens as the subform in the form or if name2 is selected and the
"go"
button
is clicked then name2's subform will appear as the subform. Is
this
possible? Can anyone help? I'm assuming that I need to use the
DoCmd:OpenSubForm and then use an If ElseIf Statement.
Click to expand...
Click to expand...
Click to expand...
 
This code:

Me.NameOfSubformControl.SourceObject = "SubFormName"
Me.NameOfSubformControl.Form.RecordSource = "Select * From tblWhatever "
Where NameID = " & Me.cboNameID"

Should look like this:

Me.NameOfSubformControl.SourceObject = "SubFormName"
Me.NameOfSubformControl.Form.RecordSource = "Select * From tblWhatever _
Where NameID = " & Me.cboNameID

or like this:

Me.NameOfSubformControl.SourceObject = "SubFormName"

Me.NameOfSubformControl.Form.RecordSource = "Select * From tblWhatever
Where NameID = " & Me.cboNameID

Where the second line is all on 1 single line.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Evan said:
The error occurs at the "Where NameID=" line. I'm not sure what the
terms: NameID and " & Me.cboNameID" refer to and think that I may
be
substituting the wrong control names in my code.

Me.NameOfSubformControl.SourceObject = "SubFormName"
Me.NameOfSubformControl.Form.RecordSource = "Select * From tblWhatever
"
Where NameID = " & Me.cboNameID"

Arvin Meyer said:
You may not have a reference to DAO. Open any code window and uncheck the
reference to ActiveX Data Objects (ADO) and check the one marked:

Microsoft DAO 3.6 Object Library

If that still doesn't help, set a breakpoint at:

Set rs = Me.Recordset.Clone

and step through the code (F8) to find the error.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Evan said:
I need a bit more help. When I click on a name in the comboBox I get
"sub
or
function not defined." Is the record set code used for the main form
causing a problem? Also wasn't sure what tbl Whatever referred to?
I've
substituted the names of the cbo, subformControl, and NameID and put
the
following code in the Module: Combobox AfterUpdate:

Private Sub Combo7_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CountryID] = " & Str(Nz(Me![Combo7], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me.BRSch.SourceObject = "SubFormName"
Me.BRSch.Form.RecordSource = "Select * From tblWhatever"
Where NameID = " & Me.BRSch"

End Sub

When I click on a name in the comboBox I get "sub or function not
defined."
Is the record set code used for the main form causing a problem? Also
wasn't sure what tbl Whatever referred to?

:

By changing the Source Object property of the subform control. But it
still
needs to be linked to the main form, or the recordsource has to use
the
combobox as a criteria:

Sub cboNameID_AfterUpdate()
Me.NameOfSubformControl.SourceObject = "SubFormName"
Me.NameOfSubformControl.Form.RecordSource = "Select * From
tblWhatever
Where NameID =" & Me.cboNameID
End Sub

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


I wrote code so that when a name in a combo box is clicked on its
corrosponding form opens but as a separate window. Is it possible
to
get
the
form to open as a subform of the form on which the combo box is
located?

:

I wish this were so easy. I've created a form with a combo box of
names
linked to a table of names & NameID's (primary key). I've also
created
the
command button: "Go" using: Form Operations > Open form > specific
form

open form/show all records. The specific form opens as a separate
form.
I
need the form named in the combo box to open as a subform in the
form.
How
do I do that?

:

Evan it is even easier than that. When you select a name from an
unbound
combobox, you can have the form move to that record, and the
subform
will as
well.

Two things that make it very easy. When you add the dropdown
list,
use
2
columns, the ID (primary key) column, and the Name. If you have
wizards
enabled (the magic wand in the toolbox) a dialog will come up to
walk
you
through the process.

Second, when you add a subform control, from the toolbox, a
wizard
will
walk
you through the process and automatically link the form and
subform.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


I've created a form with a drop-down list, command button and
subform
and
would like to connect the three together. So that when name1
is
selected
from the dropdown list and a command button "Go" is clicked
name1's
subform
opens as the subform in the form or if name2 is selected and
the
"go"
button
is clicked then name2's subform will appear as the subform. Is
this
possible? Can anyone help? I'm assuming that I need to use
the
DoCmd:OpenSubForm and then use an If ElseIf Statement.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top