SubForms vs Forms

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

Guest

Is there things the Forms can do that a SubForm can not.

For Example, I cannot get a subform to set a value to null on a requery in
the subform using a macro. Also I cannot get a subform to automatically
enter information into certain fields in the subform based on another field
in the subform like I can with a form. Is there a help file or list of
things somewhere that can show me what can and cannot be done in forms vs
subforms.

Another note of interest. I can get the subforms to run as forms correctly
but they will not do the same thing as a subform. It is quite frustrating.

Any help on this would greatly be appreciated.
 
You should be able to set a control to Null or any other value in a subform,
UNLESS you're trying to set the field that links the subform to the main
form. It will necessarily have to match the value in the main form. Perhaps
you're not using the right syntax in referring to a control on a subform.
For example to set a control named txtQty on a subform named OrderDetails to
Null, the VBA statement is:

Me![OrderDetails].Form![txtQty] = Null

HTH
Sprinks
 
It does this correctly when I use the form as a standalone. When I use it as
a subform it does not work.

Sprinks said:
You should be able to set a control to Null or any other value in a subform,
UNLESS you're trying to set the field that links the subform to the main
form. It will necessarily have to match the value in the main form. Perhaps
you're not using the right syntax in referring to a control on a subform.
For example to set a control named txtQty on a subform named OrderDetails to
Null, the VBA statement is:

Me![OrderDetails].Form![txtQty] = Null

HTH
Sprinks

James Kendall said:
Is there things the Forms can do that a SubForm can not.

For Example, I cannot get a subform to set a value to null on a requery in
the subform using a macro. Also I cannot get a subform to automatically
enter information into certain fields in the subform based on another field
in the subform like I can with a form. Is there a help file or list of
things somewhere that can show me what can and cannot be done in forms vs
subforms.

Another note of interest. I can get the subforms to run as forms correctly
but they will not do the same thing as a subform. It is quite frustrating.

Any help on this would greatly be appreciated.
Click to expand...
Click to expand...
 
James,

Firstly, assuming you're using the correct syntax in each case (see below),
the only reason I can think of why it wouldn't allow you to set its value in
the subform is because that field is linked to the main form. For example,
an Orders main form and OrderDetails subform would be linked by the
OrderNumber--each detail record would need the Ordernumber to associate the
details with the order. Perhaps you're attempting to do a similar thing.
Please post the values of the subform controls Link Master Fields and Link
Child Fields properties, the name of the control in question, and the
control's Control Source property.

Reference on Standalone Form Me![txtQty]
Reference on Subform Named sbfDetail Me![sbfDetail].Form![txtQty]

Sprinks

James Kendall said:
It does this correctly when I use the form as a standalone. When I use it as
a subform it does not work.

Sprinks said:
You should be able to set a control to Null or any other value in a subform,
UNLESS you're trying to set the field that links the subform to the main
form. It will necessarily have to match the value in the main form. Perhaps
you're not using the right syntax in referring to a control on a subform.
For example to set a control named txtQty on a subform named OrderDetails to
Null, the VBA statement is:

Me![OrderDetails].Form![txtQty] = Null

HTH
Sprinks

James Kendall said:
Is there things the Forms can do that a SubForm can not.

For Example, I cannot get a subform to set a value to null on a requery in
the subform using a macro. Also I cannot get a subform to automatically
enter information into certain fields in the subform based on another field
in the subform like I can with a form. Is there a help file or list of
things somewhere that can show me what can and cannot be done in forms vs
subforms.

Another note of interest. I can get the subforms to run as forms correctly
but they will not do the same thing as a subform. It is quite frustrating.

Any help on this would greatly be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Ok here goes...I hope I got everything.

Main forms Name - [F: Error Packaging Audit]
Sub form Name - [F: Error Tracking] This subform has the fields [Station of
Error] and [Operation]
Link Master Fields - [ID]
Link Child Fields - [Link to Packaging Audit]

Field combo box used for initial selection - [Station of Error] using SELECT
Operations.Station FROM Operations GROUP BY Operations.Station ORDER BY
Operations.Station;
Second combo box is [Operation] which gets its values query [Q: Operation
Query] using the macro below.
On [Station of Error] selection it runs macro - [M: Requery] which has a
SetValue line with
Item box - [Forms]![F: Error Tracking]![Operation] and the
Expression box - Null

Error Received when running as a subform says "Microsoft Access can't find
the form 'F: Error Tracking' Referred to in the macro expression or the
visual basic code." Along with other explainations. If I run this as a main
form it works but I need it as a subform.

I tried to enter Me![F: Operation].Form![F: Error Tracking] in the Item box
but it gave me an error titled "The object doesn't contain the Automation
object 'Me.'" Along with other explainations.

If I have to run something in visual basic or something else I am less
familiar with any of that and how to get to it.

Thanks for trying to help.
Windows NT
Office 97

Sprinks said:
James,

Firstly, assuming you're using the correct syntax in each case (see below),
the only reason I can think of why it wouldn't allow you to set its value in
the subform is because that field is linked to the main form. For example,
an Orders main form and OrderDetails subform would be linked by the
OrderNumber--each detail record would need the Ordernumber to associate the
details with the order. Perhaps you're attempting to do a similar thing.
Please post the values of the subform controls Link Master Fields and Link
Child Fields properties, the name of the control in question, and the
control's Control Source property.

Reference on Standalone Form Me![txtQty]
Reference on Subform Named sbfDetail Me![sbfDetail].Form![txtQty]

Sprinks

James Kendall said:
It does this correctly when I use the form as a standalone. When I use it as
a subform it does not work.

Sprinks said:
You should be able to set a control to Null or any other value in a subform,
UNLESS you're trying to set the field that links the subform to the main
form. It will necessarily have to match the value in the main form. Perhaps
you're not using the right syntax in referring to a control on a subform.
For example to set a control named txtQty on a subform named OrderDetails to
Null, the VBA statement is:

Me![OrderDetails].Form![txtQty] = Null

HTH
Sprinks

:

Is there things the Forms can do that a SubForm can not.

For Example, I cannot get a subform to set a value to null on a requery in
the subform using a macro. Also I cannot get a subform to automatically
enter information into certain fields in the subform based on another field
in the subform like I can with a form. Is there a help file or list of
things somewhere that can show me what can and cannot be done in forms vs
subforms.

Another note of interest. I can get the subforms to run as forms correctly
but they will not do the same thing as a subform. It is quite frustrating.

Any help on this would greatly be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
James,

I believe the problem is referring to the control on the subform.

Access couldn't find the "form 'F: Error Tracking' Referred to in the macro
expression" because it's not loaded as a form; inserting it the main form
made it a *control*, albeit a special type, namely, a subform control.

Your Me! statement didn't work because the syntax was incorrect. It follows
a topdown progression--first you get to the subform control, then add .Form
to get to *its* set of controls, and then name the specific control you're
after on the subform.

Forms![TheNameofYourSubform].Form![TheNameoftheSubform'sControl]

or, in your case,

Forms![F: Error Packaging Audit]![F: Error Tracking].Form![YourControlName]

Me![F:Error Tracking].Form![YourControlName] would be the substitute using
the Me! shortcut. I don't know, however, if Me! is supported in macros.

To further complicate things, TheNameofYourSubform above refers to the
subform contro's name in the main form. This may be different than the name
you gave the form when you created it, although by default, when you insert a
subform, the subform control inherits the form's name.

I encourage you to take the VBA plunge, and away from macros. While there
is a learning curve to understand VBA objects, events, properties, and
methods, once you learn them, VBA is much more flexible, easy to document,
and allows you do error checking. If a macro crashes, the application
terminates unceremoniously.

Hope that solves it.
Sprinks



James Kendall said:
Ok here goes...I hope I got everything.

Main forms Name - [F: Error Packaging Audit]
Sub form Name - [F: Error Tracking] This subform has the fields [Station of
Error] and [Operation]
Link Master Fields - [ID]
Link Child Fields - [Link to Packaging Audit]

Field combo box used for initial selection - [Station of Error] using SELECT
Operations.Station FROM Operations GROUP BY Operations.Station ORDER BY
Operations.Station;
Second combo box is [Operation] which gets its values query [Q: Operation
Query] using the macro below.
On [Station of Error] selection it runs macro - [M: Requery] which has a
SetValue line with
Item box - [Forms]![F: Error Tracking]![Operation] and the
Expression box - Null

Error Received when running as a subform says "Microsoft Access can't find
the form 'F: Error Tracking' Referred to in the macro expression or the
visual basic code." Along with other explainations. If I run this as a main
form it works but I need it as a subform.

I tried to enter Me![F: Operation].Form![F: Error Tracking] in the Item box
but it gave me an error titled "The object doesn't contain the Automation
object 'Me.'" Along with other explainations.

If I have to run something in visual basic or something else I am less
familiar with any of that and how to get to it.

Thanks for trying to help.
Windows NT
Office 97

Sprinks said:
James,

Firstly, assuming you're using the correct syntax in each case (see below),
the only reason I can think of why it wouldn't allow you to set its value in
the subform is because that field is linked to the main form. For example,
an Orders main form and OrderDetails subform would be linked by the
OrderNumber--each detail record would need the Ordernumber to associate the
details with the order. Perhaps you're attempting to do a similar thing.
Please post the values of the subform controls Link Master Fields and Link
Child Fields properties, the name of the control in question, and the
control's Control Source property.

Reference on Standalone Form Me![txtQty]
Reference on Subform Named sbfDetail Me![sbfDetail].Form![txtQty]

Sprinks

James Kendall said:
It does this correctly when I use the form as a standalone. When I use it as
a subform it does not work.

:

You should be able to set a control to Null or any other value in a subform,
UNLESS you're trying to set the field that links the subform to the main
form. It will necessarily have to match the value in the main form. Perhaps
you're not using the right syntax in referring to a control on a subform.
For example to set a control named txtQty on a subform named OrderDetails to
Null, the VBA statement is:

Me![OrderDetails].Form![txtQty] = Null

HTH
Sprinks

:

Is there things the Forms can do that a SubForm can not.

For Example, I cannot get a subform to set a value to null on a requery in
the subform using a macro. Also I cannot get a subform to automatically
enter information into certain fields in the subform based on another field
in the subform like I can with a form. Is there a help file or list of
things somewhere that can show me what can and cannot be done in forms vs
subforms.

Another note of interest. I can get the subforms to run as forms correctly
but they will not do the same thing as a subform. It is quite frustrating.

Any help on this would greatly be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Thanks it works! Yippee! I also had to change a query to the Forms![F:
Error Packaging Audit]![F: Error Tracking].Form![YourControlName] style. Now
that you explained it so well I actually understand it. This was one of my
gaps in learning because I am self-taught with books and access help menus.
This explaination also will help me solve another issue I have had with
another database using a subform. Thanks Sprinks, you knocked two birds down
with one stone. And yes I intend to take a class or two for the VB type
information but it will have to wait 'till next year.

Sprinks said:
James,

I believe the problem is referring to the control on the subform.

Access couldn't find the "form 'F: Error Tracking' Referred to in the macro
expression" because it's not loaded as a form; inserting it the main form
made it a *control*, albeit a special type, namely, a subform control.

Your Me! statement didn't work because the syntax was incorrect. It follows
a topdown progression--first you get to the subform control, then add .Form
to get to *its* set of controls, and then name the specific control you're
after on the subform.

Forms![TheNameofYourSubform].Form![TheNameoftheSubform'sControl]

or, in your case,

Forms![F: Error Packaging Audit]![F: Error Tracking].Form![YourControlName]

Me![F:Error Tracking].Form![YourControlName] would be the substitute using
the Me! shortcut. I don't know, however, if Me! is supported in macros.

To further complicate things, TheNameofYourSubform above refers to the
subform contro's name in the main form. This may be different than the name
you gave the form when you created it, although by default, when you insert a
subform, the subform control inherits the form's name.

I encourage you to take the VBA plunge, and away from macros. While there
is a learning curve to understand VBA objects, events, properties, and
methods, once you learn them, VBA is much more flexible, easy to document,
and allows you do error checking. If a macro crashes, the application
terminates unceremoniously.

Hope that solves it.
Sprinks



James Kendall said:
Ok here goes...I hope I got everything.

Main forms Name - [F: Error Packaging Audit]
Sub form Name - [F: Error Tracking] This subform has the fields [Station of
Error] and [Operation]
Link Master Fields - [ID]
Link Child Fields - [Link to Packaging Audit]

Field combo box used for initial selection - [Station of Error] using SELECT
Operations.Station FROM Operations GROUP BY Operations.Station ORDER BY
Operations.Station;
Second combo box is [Operation] which gets its values query [Q: Operation
Query] using the macro below.
On [Station of Error] selection it runs macro - [M: Requery] which has a
SetValue line with
Item box - [Forms]![F: Error Tracking]![Operation] and the
Expression box - Null

Error Received when running as a subform says "Microsoft Access can't find
the form 'F: Error Tracking' Referred to in the macro expression or the
visual basic code." Along with other explainations. If I run this as a main
form it works but I need it as a subform.

I tried to enter Me![F: Operation].Form![F: Error Tracking] in the Item box
but it gave me an error titled "The object doesn't contain the Automation
object 'Me.'" Along with other explainations.

If I have to run something in visual basic or something else I am less
familiar with any of that and how to get to it.

Thanks for trying to help.
Windows NT
Office 97

Sprinks said:
James,

Firstly, assuming you're using the correct syntax in each case (see below),
the only reason I can think of why it wouldn't allow you to set its value in
the subform is because that field is linked to the main form. For example,
an Orders main form and OrderDetails subform would be linked by the
OrderNumber--each detail record would need the Ordernumber to associate the
details with the order. Perhaps you're attempting to do a similar thing.
Please post the values of the subform controls Link Master Fields and Link
Child Fields properties, the name of the control in question, and the
control's Control Source property.

Reference on Standalone Form Me![txtQty]
Reference on Subform Named sbfDetail Me![sbfDetail].Form![txtQty]

Sprinks

:

It does this correctly when I use the form as a standalone. When I use it as
a subform it does not work.

:

You should be able to set a control to Null or any other value in a subform,
UNLESS you're trying to set the field that links the subform to the main
form. It will necessarily have to match the value in the main form. Perhaps
you're not using the right syntax in referring to a control on a subform.
For example to set a control named txtQty on a subform named OrderDetails to
Null, the VBA statement is:

Me![OrderDetails].Form![txtQty] = Null

HTH
Sprinks

:

Is there things the Forms can do that a SubForm can not.

For Example, I cannot get a subform to set a value to null on a requery in
the subform using a macro. Also I cannot get a subform to automatically
enter information into certain fields in the subform based on another field
in the subform like I can with a form. Is there a help file or list of
things somewhere that can show me what can and cannot be done in forms vs
subforms.

Another note of interest. I can get the subforms to run as forms correctly
but they will not do the same thing as a subform. It is quite frustrating.

Any help on this would greatly be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Glad it's working. And you're welcome; my pleasure.

I also wanted to mention 2 ways to ease into VBA. The first is to enable
wizards in form design view (View, Toolbox, then toggle on the button with
the wand and stars), and then insert various command buttons. The wizard
will guide you through various choices, and creates the code that gets
executed when the button is pressed. The code is stored as an "Event"
procedure. Events are the real power of Access VBA, because you can write
code specific to any event. To see the code the wizard created, click on the
button in design view and press <Ctrl-G>, which loads VBA and the Immediate
window.

The second way is to look at the extensive VBA Help, which can only be
accessed from the VB window. You can look at and see examples of all the
methods and properties of various objects, etc., and it's a handy reference.

Best regards.
Sprinks

James Kendall said:
Thanks it works! Yippee! I also had to change a query to the Forms![F:
Error Packaging Audit]![F: Error Tracking].Form![YourControlName] style. Now
that you explained it so well I actually understand it. This was one of my
gaps in learning because I am self-taught with books and access help menus.
This explaination also will help me solve another issue I have had with
another database using a subform. Thanks Sprinks, you knocked two birds down
with one stone. And yes I intend to take a class or two for the VB type
information but it will have to wait 'till next year.

Sprinks said:
James,

I believe the problem is referring to the control on the subform.

Access couldn't find the "form 'F: Error Tracking' Referred to in the macro
expression" because it's not loaded as a form; inserting it the main form
made it a *control*, albeit a special type, namely, a subform control.

Your Me! statement didn't work because the syntax was incorrect. It follows
a topdown progression--first you get to the subform control, then add .Form
to get to *its* set of controls, and then name the specific control you're
after on the subform.

Forms![TheNameofYourSubform].Form![TheNameoftheSubform'sControl]

or, in your case,

Forms![F: Error Packaging Audit]![F: Error Tracking].Form![YourControlName]

Me![F:Error Tracking].Form![YourControlName] would be the substitute using
the Me! shortcut. I don't know, however, if Me! is supported in macros.

To further complicate things, TheNameofYourSubform above refers to the
subform contro's name in the main form. This may be different than the name
you gave the form when you created it, although by default, when you insert a
subform, the subform control inherits the form's name.

I encourage you to take the VBA plunge, and away from macros. While there
is a learning curve to understand VBA objects, events, properties, and
methods, once you learn them, VBA is much more flexible, easy to document,
and allows you do error checking. If a macro crashes, the application
terminates unceremoniously.

Hope that solves it.
Sprinks



James Kendall said:
Ok here goes...I hope I got everything.

Main forms Name - [F: Error Packaging Audit]
Sub form Name - [F: Error Tracking] This subform has the fields [Station of
Error] and [Operation]
Link Master Fields - [ID]
Link Child Fields - [Link to Packaging Audit]

Field combo box used for initial selection - [Station of Error] using SELECT
Operations.Station FROM Operations GROUP BY Operations.Station ORDER BY
Operations.Station;
Second combo box is [Operation] which gets its values query [Q: Operation
Query] using the macro below.
On [Station of Error] selection it runs macro - [M: Requery] which has a
SetValue line with
Item box - [Forms]![F: Error Tracking]![Operation] and the
Expression box - Null

Error Received when running as a subform says "Microsoft Access can't find
the form 'F: Error Tracking' Referred to in the macro expression or the
visual basic code." Along with other explainations. If I run this as a main
form it works but I need it as a subform.

I tried to enter Me![F: Operation].Form![F: Error Tracking] in the Item box
but it gave me an error titled "The object doesn't contain the Automation
object 'Me.'" Along with other explainations.

If I have to run something in visual basic or something else I am less
familiar with any of that and how to get to it.

Thanks for trying to help.
Windows NT
Office 97

:

James,

Firstly, assuming you're using the correct syntax in each case (see below),
the only reason I can think of why it wouldn't allow you to set its value in
the subform is because that field is linked to the main form. For example,
an Orders main form and OrderDetails subform would be linked by the
OrderNumber--each detail record would need the Ordernumber to associate the
details with the order. Perhaps you're attempting to do a similar thing.
Please post the values of the subform controls Link Master Fields and Link
Child Fields properties, the name of the control in question, and the
control's Control Source property.

Reference on Standalone Form Me![txtQty]
Reference on Subform Named sbfDetail Me![sbfDetail].Form![txtQty]

Sprinks

:

It does this correctly when I use the form as a standalone. When I use it as
a subform it does not work.

:

You should be able to set a control to Null or any other value in a subform,
UNLESS you're trying to set the field that links the subform to the main
form. It will necessarily have to match the value in the main form. Perhaps
you're not using the right syntax in referring to a control on a subform.
For example to set a control named txtQty on a subform named OrderDetails to
Null, the VBA statement is:

Me![OrderDetails].Form![txtQty] = Null

HTH
Sprinks

:

Is there things the Forms can do that a SubForm can not.

For Example, I cannot get a subform to set a value to null on a requery in
the subform using a macro. Also I cannot get a subform to automatically
enter information into certain fields in the subform based on another field
in the subform like I can with a form. Is there a help file or list of
things somewhere that can show me what can and cannot be done in forms vs
subforms.

Another note of interest. I can get the subforms to run as forms correctly
but they will not do the same thing as a subform. It is quite frustrating.

Any help on this would greatly be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top