address a different form in code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In an afterupdate routine, I have the following code which works well:
Me.subtypecbx = Null
Me.subtypecbx.Requery

I understand this code refers to the combobox on the same form. However, I
want to mimic this with a reference to a combobox on another subform. How
would I re-write this code to apply to a combobox "reason" on the form,
"reasonform"?

Thanks in advance
Papa
 
Papa said:
In an afterupdate routine, I have the following code which works well:
Me.subtypecbx = Null
Me.subtypecbx.Requery

I understand this code refers to the combobox on the same form. However, I
want to mimic this with a reference to a combobox on another subform. How
would I re-write this code to apply to a combobox "reason" on the form,
"reasonform"?
Click to expand...


Use a full reference to an independent form:
Forms!formname.controlname

to a subform on an independent form:
Forms!formname.subformcontrolname.Form.controlname

to a subform on the same form:
Me.subformcontrolname.Form.controlname
 
I am confused.
In the examples, what would the difference be between "subformcontrolname"
and "controlname". I am wanting to address one control that is on a seperate
subform.
Does your answer mean I refer to the control twice?
 
The form object that you are using as a subform is displayed
within a subform control. I.e. it takes a subform control
that contains the form object for this whole subform concept
to work. I know that sounds confusing when you're not
familiar with what's going on here. Another way to note the
difference between a form and a subform control is to note
that there is a subform control in the toolbox toolbar. If
you use that you will have a place holder for a form, but it
won't do anything until you specify a form object in the
subform control's SourceObject property. All of your form
objects are available under the Forms tab in the Database
window.

Assuming your head is spinning slowly enough to proceed ;-)

In the reference: Me.subformcontrolname.Form.controlname

Me refers to the form that contains that line of code.

subformcontrolname refers to the container control on the
main form.

Form refers to the form object displayed in the subform
control.

controlname refers to the control on subform (the form
object returned by the Form property).
 
I think I am getting a grip on the head spinning - maybe.

In my effort to apply your guidance I came up with the following code -
which did not work.
Forms!Subtype.Subtype.Form.cbxSubtype = Null
What I thought I was saying in this was to insert a null value in the
"cbxsubtype" combobox on the "subtype" subform that has been inserted into
the "subtype" source object in the "Data" Form.

I think it is probably important to note that this code is inserted in the
afterupdate event for a combobox on a DIFFERENT subform ("Type") on the
"Data" Form.

Am I getting close?




Marshall Barton said:
The form object that you are using as a subform is displayed
within a subform control. I.e. it takes a subform control
that contains the form object for this whole subform concept
to work. I know that sounds confusing when you're not
familiar with what's going on here. Another way to note the
difference between a form and a subform control is to note
that there is a subform control in the toolbox toolbar. If
you use that you will have a place holder for a form, but it
won't do anything until you specify a form object in the
subform control's SourceObject property. All of your form
objects are available under the Forms tab in the Database
window.

Assuming your head is spinning slowly enough to proceed ;-)

In the reference: Me.subformcontrolname.Form.controlname

Me refers to the form that contains that line of code.

subformcontrolname refers to the container control on the
main form.

Form refers to the form object displayed in the subform
control.

controlname refers to the control on subform (the form
object returned by the Form property).
--
Marsh
MVP [MS Access]



Papa said:
I am confused.
In the examples, what would the difference be between "subformcontrolname"
and "controlname". I am wanting to address one control that is on a seperate
subform.
Does your answer mean I refer to the control twice?
Click to expand...
Click to expand...
 
Papa said:
In my effort to apply your guidance I came up with the following code -
which did not work.
Forms!Subtype.Subtype.Form.cbxSubtype = Null
What I thought I was saying in this was to insert a null value in the
"cbxsubtype" combobox on the "subtype" subform that has been inserted into
the "subtype" source object in the "Data" Form.

I think it is probably important to note that this code is inserted in the
afterupdate event for a combobox on a DIFFERENT subform ("Type") on the
"Data" Form.
Click to expand...


Closer, but a little ways to go yet ;-)

No matter where you are you can always use a full reference.
In this case:
Forms!Data.Subtype.Form.cbxSubtype = Null

The bit about this being from one subform to another subform
in the same main form is important in the case where you
want to make the reference independent of the main form
name:
Me.Parent.Subtype.Form.cbxSubtype = Null

Just keep in mind that the above uses Subtype as the name of
the subform **control**, which may be different than the
name of the source object it is displaying. The source
object is **not** used in the reference. One reason for
these seeming redundant names is that it is possible to make
the subform display different form objects simply by setting
the subform control's SourceObject property and have the
rest of the code continue to work (as long as the cbxSubtype
control name is the same in both forms).
 
One might hope that I would get this at some point, but until then - I
perservere.

Based on your last input, I have modified my afterupdate code to be:
Private Sub Typecombobox_AfterUpdate()

Forms!orpsdata.Form1.Form.subtypecombobox = Null
Forms!orpsdata.Form1.Form.subtypecombobox.Requery

End Sub
This combobox is in the subform, Form1.
I am able to change my selection in the combobox, but then I get an inputbox
asking for me to enter a parameter value for Forms!form1!typecombobox
Why is it asking me for this when I just selected something?
 
This is a new issue. I think the prompt may be coming from
a query that uses Forms!form1!typecombobox in its criteria.
Probably the subtypecombobox's RowSource query.

The problem is that this reference does not take the
form/subform arrangement into account. It needs to be like
the reference to the other combo box:
Forms!orpsdata.Form1.Form.typecombobox
 
Marsh,
That got rid of the input box, however the subtypecombobox is nolonger
populated. It was populated as long as I kept entering the proper answers
into the input box.

I am totally stumped.

Papa

Marshall Barton said:
This is a new issue. I think the prompt may be coming from
a query that uses Forms!form1!typecombobox in its criteria.
Probably the subtypecombobox's RowSource query.

The problem is that this reference does not take the
form/subform arrangement into account. It needs to be like
the reference to the other combo box:
Forms!orpsdata.Form1.Form.typecombobox
--
Marsh
MVP [MS Access]


Papa said:
One might hope that I would get this at some point, but until then - I
perservere.

Based on your last input, I have modified my afterupdate code to be:
Private Sub Typecombobox_AfterUpdate()

Forms!orpsdata.Form1.Form.subtypecombobox = Null
Forms!orpsdata.Form1.Form.subtypecombobox.Requery

End Sub
This combobox is in the subform, Form1.
I am able to change my selection in the combobox, but then I get an inputbox
asking for me to enter a parameter value for Forms!form1!typecombobox
Why is it asking me for this when I just selected something?
Click to expand...
Click to expand...
 
It's is not clear what you mean by "populated". If you mean
the value is not displayed, then that's what I would expect
since you just set the value to Null.

OTOH, if you mean the combo box has nothing in its drop down
list, then I would suspect that the typecombobox does not
have its BoundColumn property set correctly. Either that or
you've selected a value that doesn't have any matching items
in the subtype combo box.
 
I think the issue lies in your other hand. The drop down has nothing in it.
When I reformatted the query criteria to include
Forms!orpsdata.Form1.Form.typecombobox
That took care of the input box appearing but then the dropdown no longer
had any choices. When I changed the critera back to
[Forms]![orpsdata].[Form1].[typecombobox]
the input box reappears, but my dropdowns have choices again.

Currently (with inputboxes showing up)
The rowsource for the type combobox is:
SELECT [Zlookup: Type].TypeID, [Zlookup: Type].Type FROM [Zlookup: Type]
ORDER BY [Zlookup: Type].TypeID;

The rowsource for the subtype combobox is:
SELECT [Zlookup: Subtype].SubtypeID, [Zlookup: Subtype].Subtype, [Zlookup:
Subtype].TypeID FROM [Zlookup: Subtype] WHERE ((([Zlookup:
Subtype].TypeID)=[Forms]![orpsdata].[Form1].[typecombobox]));

I have tried selecting different bound columns, etc and can not seem to get
it to work.
 
It looks like the typecombobox needs to have its BoundColumn
property set to 1 and its ColumnCount set to 2

Also, quit fooling around with the criteria. If you have
anything that Access can't find it will prompt you for a
value. You could just as well use:
[this is not what I want]
and you would get the same result as you keep getting with
the incomplete combo box reference. Assuming the form and
control names are correct, it should be:
Forms!orpsdata.Form1.FORM.typecombobox

If the list is still empty after all this, then double check
the value in the TypeID value in the [Zlookup: Type] table
is really what you think it is.
--
Marsh
MVP [MS Access]


Papa said:
I think the issue lies in your other hand. The drop down has nothing in it.
When I reformatted the query criteria to include
Forms!orpsdata.Form1.Form.typecombobox
That took care of the input box appearing but then the dropdown no longer
had any choices. When I changed the critera back to
[Forms]![orpsdata].[Form1].[typecombobox]
the input box reappears, but my dropdowns have choices again.

Currently (with inputboxes showing up)
The rowsource for the type combobox is:
SELECT [Zlookup: Type].TypeID, [Zlookup: Type].Type FROM [Zlookup: Type]
ORDER BY [Zlookup: Type].TypeID;

The rowsource for the subtype combobox is:
SELECT [Zlookup: Subtype].SubtypeID, [Zlookup: Subtype].Subtype, [Zlookup:
Subtype].TypeID FROM [Zlookup: Subtype] WHERE ((([Zlookup:
Subtype].TypeID)=[Forms]![orpsdata].[Form1].[typecombobox]));

I have tried selecting different bound columns, etc and can not seem to get
it to work.


Marshall Barton said:
It's is not clear what you mean by "populated". If you mean
the value is not displayed, then that's what I would expect
since you just set the value to Null.

OTOH, if you mean the combo box has nothing in its drop down
list, then I would suspect that the typecombobox does not
have its BoundColumn property set correctly. Either that or
you've selected a value that doesn't have any matching items
in the subtype combo box.
Click to expand...
Click to expand...
 
Okay Marsh,
I will quit fooling with the criteria.
I changed the criteria to read just as you have it below. I also changed
the bound columns as you suggested. The lookup tables are correct I am
assuming based on the fact that the dropdown lists are properly populated
(when they seem to work).
However, step by step this is what is happening.
When I open the orpsdata form, I get an input box asking for input for
Forms!orpsdata.frmtype.form.typecombobox. I hit cancel. This form does not
have any events identified.
As expected after changing the bound columns (per your last comments) the
previous slections from the dropdowns (that are stored in tblTypeSubType) do
not appear.
If I try to reenter data in the comboboxes, I can select from the
typecombobox. Then afterupdate should fire. I get another input box. If I
enter the value that should be automatically considered the dropdown for
subtype works. But only if I enter it in the input box.
However, now when I try to close it I get an error that says You cannot add
or change a record because a related recordd is required in a table 'zlookup:
type'.
I haven't seen this before.

You are probably getting as frustrated with me as I am with Access. But I
promise I won't dork with anything else unless you tell me to.

Thanks for your persistence.

Papa


Marshall Barton said:
It looks like the typecombobox needs to have its BoundColumn
property set to 1 and its ColumnCount set to 2

Also, quit fooling around with the criteria. If you have
anything that Access can't find it will prompt you for a
value. You could just as well use:
[this is not what I want]
and you would get the same result as you keep getting with
the incomplete combo box reference. Assuming the form and
control names are correct, it should be:
Forms!orpsdata.Form1.FORM.typecombobox

If the list is still empty after all this, then double check
the value in the TypeID value in the [Zlookup: Type] table
is really what you think it is.
--
Marsh
MVP [MS Access]


Papa said:
I think the issue lies in your other hand. The drop down has nothing in it.
When I reformatted the query criteria to include
Forms!orpsdata.Form1.Form.typecombobox
That took care of the input box appearing but then the dropdown no longer
had any choices. When I changed the critera back to
[Forms]![orpsdata].[Form1].[typecombobox]
the input box reappears, but my dropdowns have choices again.

Currently (with inputboxes showing up)
The rowsource for the type combobox is:
SELECT [Zlookup: Type].TypeID, [Zlookup: Type].Type FROM [Zlookup: Type]
ORDER BY [Zlookup: Type].TypeID;

The rowsource for the subtype combobox is:
SELECT [Zlookup: Subtype].SubtypeID, [Zlookup: Subtype].Subtype, [Zlookup:
Subtype].TypeID FROM [Zlookup: Subtype] WHERE ((([Zlookup:
Subtype].TypeID)=[Forms]![orpsdata].[Form1].[typecombobox]));

I have tried selecting different bound columns, etc and can not seem to get
it to work.


Marshall Barton said:
It's is not clear what you mean by "populated". If you mean
the value is not displayed, then that's what I would expect
since you just set the value to Null.

OTOH, if you mean the combo box has nothing in its drop down
list, then I would suspect that the typecombobox does not
have its BoundColumn property set correctly. Either that or
you've selected a value that doesn't have any matching items
in the subtype combo box.


Papa Jonah wrote:
That got rid of the input box, however the subtypecombobox is nolonger
populated. It was populated as long as I kept entering the proper answers
into the input box.


:
This is a new issue. I think the prompt may be coming from
a query that uses Forms!form1!typecombobox in its criteria.
Probably the subtypecombobox's RowSource query.

The problem is that this reference does not take the
form/subform arrangement into account. It needs to be like
the reference to the other combo box:
Forms!orpsdata.Form1.Form.typecombobox


Papa Jonah wrote:
One might hope that I would get this at some point, but until then - I
perservere.

Based on your last input, I have modified my afterupdate code to be:
Private Sub Typecombobox_AfterUpdate()

Forms!orpsdata.Form1.Form.subtypecombobox = Null
Forms!orpsdata.Form1.Form.subtypecombobox.Requery

End Sub
This combobox is in the subform, Form1.
I am able to change my selection in the combobox, but then I get an
Click to expand...
Click to expand...
Click to expand...
 
Yeah, frustration can be an obstacle on the path to an
answer. The main issue we're are struggling with is that
our communications are not conveying the information we need
to proceed.

I need to see the exact properties of the two combo boxes:
Name:
ControlSource:
RowSource (If the RowSource is a query name, a copy
of the query's SQL or if it's a table name the
list of fields in the table):
ColumnCount:
BoundColumn:
Also include any event procedure's VBA. (I think there is
only the one for the typecombobox AfterUpdate event.)

If at anytime in our investigations, you change any of these
settings, post the new settings so we can stay in sync.
Also be sure to use Copy/Paste for the long settings to
avoid any typos confusing things any further than they are
already.

Whenever you get an unexpected prompt, please post the exact
prompt string. Since the prompt string is the thing that
Access can not resolve, this an important clue. In the case
of being prompted for
Forms!orpsdata.frmtype.form.typecombobox
when the form opens, I think that implies that one of those
names in not spelled correctly. Be sure to use the subform
**control** Name property, not the name of the form object
it is displaying (the SourceObject property). The first
prompt may be caused by the timing of opening the subform vs
the main form, so let's not go into that now. The prompt
when you select something in the type combo box is the one I
most concerned with now.

Why you can't save a record is a separate issue that may or
may not involve the combo boxes, so let's put that on hold
too.
--
Marsh
MVP [MS Access]


Papa said:
I will quit fooling with the criteria.
I changed the criteria to read just as you have it below. I also changed
the bound columns as you suggested. The lookup tables are correct I am
assuming based on the fact that the dropdown lists are properly populated
(when they seem to work).
However, step by step this is what is happening.
When I open the orpsdata form, I get an input box asking for input for
Forms!orpsdata.frmtype.form.typecombobox. I hit cancel. This form does not
have any events identified.
As expected after changing the bound columns (per your last comments) the
previous slections from the dropdowns (that are stored in tblTypeSubType) do
not appear.
If I try to reenter data in the comboboxes, I can select from the
typecombobox. Then afterupdate should fire. I get another input box. If I
enter the value that should be automatically considered the dropdown for
subtype works. But only if I enter it in the input box.

However, now when I try to close it I get an error that says You cannot add
or change a record because a related recordd is required in a table 'zlookup:
type'.
I haven't seen this before.

You are probably getting as frustrated with me as I am with Access. But I
promise I won't dork with anything else unless you tell me to.


Marshall Barton said:
It looks like the typecombobox needs to have its BoundColumn
property set to 1 and its ColumnCount set to 2

Also, quit fooling around with the criteria. If you have
anything that Access can't find it will prompt you for a
value. You could just as well use:
[this is not what I want]
and you would get the same result as you keep getting with
the incomplete combo box reference. Assuming the form and
control names are correct, it should be:
Forms!orpsdata.Form1.FORM.typecombobox

If the list is still empty after all this, then double check
the value in the TypeID value in the [Zlookup: Type] table
is really what you think it is.


Papa said:
I think the issue lies in your other hand. The drop down has nothing in it.
When I reformatted the query criteria to include
Forms!orpsdata.Form1.Form.typecombobox
That took care of the input box appearing but then the dropdown no longer
had any choices. When I changed the critera back to
[Forms]![orpsdata].[Form1].[typecombobox]
the input box reappears, but my dropdowns have choices again.

Currently (with inputboxes showing up)
The rowsource for the type combobox is:
SELECT [Zlookup: Type].TypeID, [Zlookup: Type].Type FROM [Zlookup: Type]
ORDER BY [Zlookup: Type].TypeID;

The rowsource for the subtype combobox is:
SELECT [Zlookup: Subtype].SubtypeID, [Zlookup: Subtype].Subtype, [Zlookup:
Subtype].TypeID FROM [Zlookup: Subtype] WHERE ((([Zlookup:
Subtype].TypeID)=[Forms]![orpsdata].[Form1].[typecombobox]));

I have tried selecting different bound columns, etc and can not seem to get
it to work.


:
It's is not clear what you mean by "populated". If you mean
the value is not displayed, then that's what I would expect
since you just set the value to Null.

OTOH, if you mean the combo box has nothing in its drop down
list, then I would suspect that the typecombobox does not
have its BoundColumn property set correctly. Either that or
you've selected a value that doesn't have any matching items
in the subtype combo box.


Papa Jonah wrote:
That got rid of the input box, however the subtypecombobox is nolonger
populated. It was populated as long as I kept entering the proper answers
into the input box.


:
This is a new issue. I think the prompt may be coming from
a query that uses Forms!form1!typecombobox in its criteria.
Probably the subtypecombobox's RowSource query.

The problem is that this reference does not take the
form/subform arrangement into account. It needs to be like
the reference to the other combo box:
Forms!orpsdata.Form1.Form.typecombobox


Papa Jonah wrote:
One might hope that I would get this at some point, but until then - I
perservere.

Based on your last input, I have modified my afterupdate code to be:
Private Sub Typecombobox_AfterUpdate()

Forms!orpsdata.Form1.Form.subtypecombobox = Null
Forms!orpsdata.Form1.Form.subtypecombobox.Requery

End Sub
This combobox is in the subform, Form1.
I am able to change my selection in the combobox, but then I get an
Click to expand...
Click to expand...
Click to expand...
 
Back
Top