cascading combos and sub forms

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

Guest

I have a cascading combo box on a subform. When I open the main form I get an
"enter parameter value" message.

Do I have to reference the main form as well the sub form?
 
Finally, a straight answer to a straight question.

Cheers!

KARL DEWEY said:
Yep! Like this --
[Forms]![MainFormName]![SubFormName]![ControlName]

scubadiver said:
I have a cascading combo box on a subform. When I open the main form I get an
"enter parameter value" message.

Do I have to reference the main form as well the sub form?
Click to expand...
Click to expand...
 
Well, not so simple really!

My database basically involves storing data on a weekly basis for each
employee, BUT for each date, an employee can work for more than one
department. So as I see it, I have a sub form (Dept) within a main form (age)
and then I am using that form as a sub-form within another form (employee).

Is this the right way to do it because I want to be sure that I have the
correct set of records for any particular week.

Dept and subdept have combo boxes. Within this form, using a single control
source works okay.

I then put the following in the control source:

[forms]![date]![dept]![dept]

it says "Control can't be edited; its bound to unknown field
'[forms]![date]![dept]![dept]'".

Cheers!

KARL DEWEY said:
Yep! Like this --
[Forms]![MainFormName]![SubFormName]![ControlName]

scubadiver said:
I have a cascading combo box on a subform. When I open the main form I get an
"enter parameter value" message.

Do I have to reference the main form as well the sub form?
Click to expand...
Click to expand...
 
My database basically involves storing data on a weekly basis for each
employee, BUT for each date, an employee can work for more than one
department.
How do you store this information? Do you have one record per week for the
employee? Or do you use multiple records per day?
I then put the following in the control source: [forms]![date]![dept]![dept]
Click to expand...
Click to expand...
Do you have a form named "date" and a subform named "dept" with a control
named "dept"?

scubadiver said:
Well, not so simple really!

My database basically involves storing data on a weekly basis for each
employee, BUT for each date, an employee can work for more than one
department. So as I see it, I have a sub form (Dept) within a main form (age)
and then I am using that form as a sub-form within another form (employee).

Is this the right way to do it because I want to be sure that I have the
correct set of records for any particular week.

Dept and subdept have combo boxes. Within this form, using a single control
source works okay.

I then put the following in the control source:

[forms]![date]![dept]![dept]

it says "Control can't be edited; its bound to unknown field
'[forms]![date]![dept]![dept]'".

Cheers!

KARL DEWEY said:
Yep! Like this --
[Forms]![MainFormName]![SubFormName]![ControlName]

scubadiver said:
I have a cascading combo box on a subform. When I open the main form I get an
"enter parameter value" message.

Do I have to reference the main form as well the sub form?
Click to expand...
Click to expand...
Click to expand...
 
At long last (and after losing a few brain cells) I am sure I have the form
the way I would like it.

As I say in my second reply, I have a form with a subform which, in turn, is
inserted into a "top level" form

1) The top level form is called "employees"
2) The subform within "employees" is called "records"
3) "Records" consists of a main form called "Date"
4) "Date" has a subform called "dept"

??????

My cascading combo box is in "dept" and I would like to be advised on the
controlsource code!

Cheers!

KARL DEWEY said:
Yep! Like this --
[Forms]![MainFormName]![SubFormName]![ControlName]

scubadiver said:
I have a cascading combo box on a subform. When I open the main form I get an
"enter parameter value" message.

Do I have to reference the main form as well the sub form?
Click to expand...
Click to expand...
 
I get a message saying "control can't be edited" ??

scubadiver said:
At long last (and after losing a few brain cells) I am sure I have the form
the way I would like it.

As I say in my second reply, I have a form with a subform which, in turn, is
inserted into a "top level" form

1) The top level form is called "employees"
2) The subform within "employees" is called "records"
3) "Records" consists of a main form called "Date"
4) "Date" has a subform called "dept"

??????

My cascading combo box is in "dept" and I would like to be advised on the
controlsource code!

Cheers!

KARL DEWEY said:
Yep! Like this --
[Forms]![MainFormName]![SubFormName]![ControlName]

scubadiver said:
I have a cascading combo box on a subform. When I open the main form I get an
"enter parameter value" message.

Do I have to reference the main form as well the sub form?
Click to expand...
Click to expand...
Click to expand...
 
Your post says you have four layers of forms-subforms. I thought Access only
could handle three.
Ok, use this then --
[Forms]![employees]![records]![Date]![dept]![dept]

By the way you should not use "date" as it is a reserved word in Access.

scubadiver said:
At long last (and after losing a few brain cells) I am sure I have the form
the way I would like it.

As I say in my second reply, I have a form with a subform which, in turn, is
inserted into a "top level" form

1) The top level form is called "employees"
2) The subform within "employees" is called "records"
3) "Records" consists of a main form called "Date"
4) "Date" has a subform called "dept"

??????

My cascading combo box is in "dept" and I would like to be advised on the
controlsource code!

Cheers!

KARL DEWEY said:
Yep! Like this --
[Forms]![MainFormName]![SubFormName]![ControlName]

scubadiver said:
I have a cascading combo box on a subform. When I open the main form I get an
"enter parameter value" message.

Do I have to reference the main form as well the sub form?
Click to expand...
Click to expand...
Click to expand...
 
Sorry, but no.

First of all, it's important to distinguish between the name of the control
that holds the subform and the name of the form that's being used as a
subform.

Let's assume you've got a form ParentForm and a form SubForm. If you drag
SubForm onto ParentForm to create the subform, the subform container will
usually be named SubForm (unless, of course, there's already a control named
SubForm on ParentForm). On the other hand, if you select the subform control
from the tool box, draw it on ParentForm and then populate the properties of
that control (either using the wizard or filling them in manually), the
subform control will be named something like Child0 (the number 0 will
change). When you're referring to subforms, you always need to use the name
of the control.

Since you're referring to a control on the parent form, you need to look at
the Form property of that control to look at controls on the subform.

That means you probably need:

[Forms]![employees]![records].Form![Date].Form![dept].Form![MyComboBox]

However, I don't understand what scubadiver means by point 3 ("Records"
consists of a main form called "Date"). Perhaps the subform control on the
main form ("employees") is named "records", and it uses "Date" as its form.
That would mean that you wouldn't need that reference to Date above (it's
what pointed to by the Form property of the records control):

[Forms]![employees]![records].Form![dept].Form![MyComboBox]

Provided you remember that when it's referring to Subform1 and Subform2,
it's talking about the subform control name,
http://www.mvps.org/access/forms/frm0031.htm at "The Access Web" provides a
good summary.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


KARL DEWEY said:
Your post says you have four layers of forms-subforms. I thought Access only
could handle three.
Ok, use this then --
[Forms]![employees]![records]![Date]![dept]![dept]

By the way you should not use "date" as it is a reserved word in Access.

scubadiver said:
At long last (and after losing a few brain cells) I am sure I have the form
the way I would like it.

As I say in my second reply, I have a form with a subform which, in turn, is
inserted into a "top level" form

1) The top level form is called "employees"
2) The subform within "employees" is called "records"
3) "Records" consists of a main form called "Date"
4) "Date" has a subform called "dept"

??????

My cascading combo box is in "dept" and I would like to be advised on the
controlsource code!

Cheers!

KARL DEWEY said:
Yep! Like this --
[Forms]![MainFormName]![SubFormName]![ControlName]

:

I have a cascading combo box on a subform. When I open the main form I get an
"enter parameter value" message.

Do I have to reference the main form as well the sub form?
Click to expand...
Click to expand...
Click to expand...
 
Hello,

It is rather confusing.

When I first tried a form, the "date" and "dept" were two separate sub forms
within one main form, but I wanted to be sure that each date had its own set
of department records for each employee.

So what I decided is that I would create a main form called "date" and
insert a sub-form called "dept", then I would incorporate the whole form into
a sub-form within another main form called "employees". So dept" is a
sub-sub-form!!

When I click on the "+"'s in the "employee" table it expands exactly the way
I want it to. Each employee has a weekly record and within each of these are
the list of departments the employee has worked and the number of hours.

Douglas J Steele said:
Sorry, but no.

First of all, it's important to distinguish between the name of the control
that holds the subform and the name of the form that's being used as a
subform.

Let's assume you've got a form ParentForm and a form SubForm. If you drag
SubForm onto ParentForm to create the subform, the subform container will
usually be named SubForm (unless, of course, there's already a control named
SubForm on ParentForm). On the other hand, if you select the subform control
from the tool box, draw it on ParentForm and then populate the properties of
that control (either using the wizard or filling them in manually), the
subform control will be named something like Child0 (the number 0 will
change). When you're referring to subforms, you always need to use the name
of the control.

Since you're referring to a control on the parent form, you need to look at
the Form property of that control to look at controls on the subform.

That means you probably need:

[Forms]![employees]![records].Form![Date].Form![dept].Form![MyComboBox]

However, I don't understand what scubadiver means by point 3 ("Records"
consists of a main form called "Date"). Perhaps the subform control on the
main form ("employees") is named "records", and it uses "Date" as its form.
That would mean that you wouldn't need that reference to Date above (it's
what pointed to by the Form property of the records control):

[Forms]![employees]![records].Form![dept].Form![MyComboBox]

Provided you remember that when it's referring to Subform1 and Subform2,
it's talking about the subform control name,
http://www.mvps.org/access/forms/frm0031.htm at "The Access Web" provides a
good summary.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


KARL DEWEY said:
Your post says you have four layers of forms-subforms. I thought Access only
could handle three.
Ok, use this then --
[Forms]![employees]![records]![Date]![dept]![dept]

By the way you should not use "date" as it is a reserved word in Access.

scubadiver said:
At long last (and after losing a few brain cells) I am sure I have the form
the way I would like it.

As I say in my second reply, I have a form with a subform which, in turn, is
inserted into a "top level" form

1) The top level form is called "employees"
2) The subform within "employees" is called "records"
3) "Records" consists of a main form called "Date"
4) "Date" has a subform called "dept"

??????

My cascading combo box is in "dept" and I would like to be advised on the
controlsource code!

Cheers!

:

Yep! Like this --
[Forms]![MainFormName]![SubFormName]![ControlName]

:

I have a cascading combo box on a subform. When I open the main form I get an
"enter parameter value" message.

Do I have to reference the main form as well the sub form?
Click to expand...
Click to expand...
Click to expand...
 
I am continuing to try and get the correct control source code.

When I type in the following:

=[Forms]![employees]![records].Form!date

The "dept" field inserts the date from the "date" main form but if I change
it to:

=[Forms]![employees]![records].[Form]![dept]

it doesn't work even though I have provided the row source that I would
like. But it does give the message "control can't be edited; its bound to the
expression..."

Douglas J Steele said:
Sorry, but no.

First of all, it's important to distinguish between the name of the control
that holds the subform and the name of the form that's being used as a
subform.

Let's assume you've got a form ParentForm and a form SubForm. If you drag
SubForm onto ParentForm to create the subform, the subform container will
usually be named SubForm (unless, of course, there's already a control named
SubForm on ParentForm). On the other hand, if you select the subform control
from the tool box, draw it on ParentForm and then populate the properties of
that control (either using the wizard or filling them in manually), the
subform control will be named something like Child0 (the number 0 will
change). When you're referring to subforms, you always need to use the name
of the control.

Since you're referring to a control on the parent form, you need to look at
the Form property of that control to look at controls on the subform.

That means you probably need:

[Forms]![employees]![records].Form![Date].Form![dept].Form![MyComboBox]

However, I don't understand what scubadiver means by point 3 ("Records"
consists of a main form called "Date"). Perhaps the subform control on the
main form ("employees") is named "records", and it uses "Date" as its form.
That would mean that you wouldn't need that reference to Date above (it's
what pointed to by the Form property of the records control):

[Forms]![employees]![records].Form![dept].Form![MyComboBox]

Provided you remember that when it's referring to Subform1 and Subform2,
it's talking about the subform control name,
http://www.mvps.org/access/forms/frm0031.htm at "The Access Web" provides a
good summary.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


KARL DEWEY said:
Your post says you have four layers of forms-subforms. I thought Access only
could handle three.
Ok, use this then --
[Forms]![employees]![records]![Date]![dept]![dept]

By the way you should not use "date" as it is a reserved word in Access.

scubadiver said:
At long last (and after losing a few brain cells) I am sure I have the form
the way I would like it.

As I say in my second reply, I have a form with a subform which, in turn, is
inserted into a "top level" form

1) The top level form is called "employees"
2) The subform within "employees" is called "records"
3) "Records" consists of a main form called "Date"
4) "Date" has a subform called "dept"

??????

My cascading combo box is in "dept" and I would like to be advised on the
controlsource code!

Cheers!

:

Yep! Like this --
[Forms]![MainFormName]![SubFormName]![ControlName]

:

I have a cascading combo box on a subform. When I open the main form I get an
"enter parameter value" message.

Do I have to reference the main form as well the sub form?
Click to expand...
Click to expand...
Click to expand...
 
If you're trying to display a field from the subform on another form using
that sort of ControlSource, then you will not be able to update the control
on the other form.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


scubadiver said:
I am continuing to try and get the correct control source code.

When I type in the following:

=[Forms]![employees]![records].Form!date

The "dept" field inserts the date from the "date" main form but if I
change
it to:

=[Forms]![employees]![records].[Form]![dept]

it doesn't work even though I have provided the row source that I would
like. But it does give the message "control can't be edited; its bound to
the
expression..."

Douglas J Steele said:
Sorry, but no.

First of all, it's important to distinguish between the name of the
control
that holds the subform and the name of the form that's being used as a
subform.

Let's assume you've got a form ParentForm and a form SubForm. If you drag
SubForm onto ParentForm to create the subform, the subform container will
usually be named SubForm (unless, of course, there's already a control
named
SubForm on ParentForm). On the other hand, if you select the subform
control
from the tool box, draw it on ParentForm and then populate the properties
of
that control (either using the wizard or filling them in manually), the
subform control will be named something like Child0 (the number 0 will
change). When you're referring to subforms, you always need to use the
name
of the control.

Since you're referring to a control on the parent form, you need to look
at
the Form property of that control to look at controls on the subform.

That means you probably need:

[Forms]![employees]![records].Form![Date].Form![dept].Form![MyComboBox]

However, I don't understand what scubadiver means by point 3 ("Records"
consists of a main form called "Date"). Perhaps the subform control on
the
main form ("employees") is named "records", and it uses "Date" as its
form.
That would mean that you wouldn't need that reference to Date above (it's
what pointed to by the Form property of the records control):

[Forms]![employees]![records].Form![dept].Form![MyComboBox]

Provided you remember that when it's referring to Subform1 and Subform2,
it's talking about the subform control name,
http://www.mvps.org/access/forms/frm0031.htm at "The Access Web" provides
a
good summary.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


KARL DEWEY said:
Your post says you have four layers of forms-subforms. I thought
Access only
could handle three.
Ok, use this then --
[Forms]![employees]![records]![Date]![dept]![dept]

By the way you should not use "date" as it is a reserved word in
Access.

:

At long last (and after losing a few brain cells) I am sure I have
the form
the way I would like it.

As I say in my second reply, I have a form with a subform which, in turn, is
inserted into a "top level" form

1) The top level form is called "employees"
2) The subform within "employees" is called "records"
3) "Records" consists of a main form called "Date"
4) "Date" has a subform called "dept"

??????

My cascading combo box is in "dept" and I would like to be advised on the
controlsource code!

Cheers!

:

Yep! Like this --
[Forms]![MainFormName]![SubFormName]![ControlName]

:

I have a cascading combo box on a subform. When I open the main
form I get an
"enter parameter value" message.

Do I have to reference the main form as well the sub form?
Click to expand...
Click to expand...
Click to expand...
 
Hello,

I have reverted back to having a main form with two subforms ("date" and
"dept").

I have typed the following in the controlsource and it doesn't work so I'm
not too sure what is going on:

=[forms]![employee]![dept].[form]![dept]

I do have two fields called "dept" in two different tables (many-to-1) and
that maybe the source of the problem (but then I have two fields called
"week" in two different tables)

These are the relationships I have at the moment (from L-to-R):

"Employee"

Payroll (PK: 1)
Employee
Work
Status
Rate

"Date"

Payroll (FK: Many)
Month
Week (PK: 1)

"Link" (this is used a 3rd junction for two "1-to-many" relationships)

Week (FK: Many)
Dept (PK: Many)

"Dept"

Dept (FK: 1)
Subdept
Costcentre
Standard
Timehalf
Double

Unless there is something I am missing?
 
For one thing, lose the [] around Form. You're talking about a property of
the control: putting the square brackets around it will make Access think
it's something else:

=[forms]![employee]![dept].form![dept]

It's been suggested to you several times that you not use Date as the name
for a form or control: Date is a reserved word, and using it for your own
purposes can lead to unexpected results.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


scubadiver said:
Hello,

I have reverted back to having a main form with two subforms ("date" and
"dept").

I have typed the following in the controlsource and it doesn't work so I'm
not too sure what is going on:

=[forms]![employee]![dept].[form]![dept]

I do have two fields called "dept" in two different tables (many-to-1) and
that maybe the source of the problem (but then I have two fields called
"week" in two different tables)

These are the relationships I have at the moment (from L-to-R):

"Employee"

Payroll (PK: 1)
Employee
Work
Status
Rate

"Date"

Payroll (FK: Many)
Month
Week (PK: 1)

"Link" (this is used a 3rd junction for two "1-to-many" relationships)

Week (FK: Many)
Dept (PK: Many)

"Dept"

Dept (FK: 1)
Subdept
Costcentre
Standard
Timehalf
Double

Unless there is something I am missing?

Douglas J. Steele said:
If you're trying to display a field from the subform on another form
using
that sort of ControlSource, then you will not be able to update the
control
on the other form.
Click to expand...
Click to expand...
 
Back
Top