Main form and subform

Very good Ms Tina,

Thank you for the information as I followed your suggestions, the error is
no longer visible. Although, I am not achieving the end results I am trying
to achieve. When I select RATES, PLANS, CO-PAY or ACCUMULATIONS from the
[WorkCategory] combo box drop down and then attempt to make a selection from
the [ClaimMonitor] field, also a combo box, a message box I created to save
or discard appears. I select no and a new record appears. Any ideas why?

Also when I make any other selection from the [WorkCategory] combo box I
receive a Runtime ‘2164’ (You can’t disable a control while it has the
focus).

All I would like to be able to do is; if I select rates, plans, co-pay or
accumulations from the [WorkCategory] combo box, then the [ClaimMonitor]
field is enabled and I can make a selection from the combo drop down. If I
make any other select from the [WorkCategory] field then the [ClaimMonitor]
field stays disabled or even grayed out (currently it is not grayed out).

Thank you in advance, any assistance you can offer.

Below code as per your suggestions

Private Sub WorkCategory_AfterUpdate()
Select Case Me.WorkCategory.Column(1)
Case "RATES", "PLANS", "CO-PAY", "ACCUMULATIONS"
Me!RoutingSub.Form!ClaimsMonitor.Enabled = True

Case Else
Me!RoutingSub.Form!ClaimsMonitor.Enabled = False

End Select

End Sub

"tina" wrote:

did you verify the name of the subform control *within the mainform*? a
subform is a form that is "housed" or "contained" in a control inside of
another form. that "container" control is the subform control within the
mainform. in your code, you need to make sure you're referring to the name
of the container control - NOT the name of the subform form object as it
shows in the database window. to get the correct name: open the mainform in
Design view. within the mainform, click *once* on the subform to select it.
in the Properties box, click on the Other tab and look at the Name property.
that's the name of the subform control within the mainform. for example,
let's say the above steps showed the subform control name as ChildMonitor.
assuming that the code is running in the mainform, the reference would be

Me!ChildMonitor.Form!ClaimMonitoring.Enabled = True

hth


"Beeyen" wrote in message
...
Good Day Gentlemen,

I have tried the solution below, which makes perfect sense, but I continue
to received a runtime error 438 (Object doesn't support this property or
method). The code is included below. Any thought of what I might be
doing
wrong?

Mainform = frmWorkload Information Tracker
Mainform field = WorkCategory
Subform = frmRouting
Subform field = ClaimMonitoring

Private Sub WorkCategory_AfterUpdate()
Select Case Me.WorkCategory.Column(2)
Case "RATES", "PLANS", "CO-PAY", "ACCUMULATIONS"
Me!frmRouting.Form![ClaimMonitoring].Enabled = True

Case Else
Me!frmRouting.Form![ClaimMonitoring].Enabled = False

End Select

End Sub

Thank you

"John... Visio MVP" wrote:

John, thanks for the assist.

John... Visio MVP
"John W. Vinson" wrote in message
...
On Sun, 12 Apr 2009 09:21:01 -0700, Beeyen

wrote:

Good Day John
Thanks for the assistance, the commas worked. Now I received another
Run-time 2465, The mainform can't find the field 'Claims Monitoring'
referred
to in your expression.

Could it be that the coding does not reference that the 'Claims
Monitoring'
field located in a Tab Subform called Routing?


Yes. A Subform is another form; Me! refers to the form on which the
code
exists (the main form in this case). The syntax for referencing a
subform
is
peculiar: you need to use the name *of the Subform control* (which may
be
different than the name of the Form object within that control). Try

Case "RATES", "PLANS", "CO-PAY", "ACCUMULATIONS"
Me!Routing.Form![Claims Monitoring].Enabled = True
Case Else
Me!Routing.Form![Claims Monitoring].Enabled = False

This means "look in the Subform control on this form (Me) named
Routing;
look
at the Form object within that Subform control (Form!); find the
control
named
[Claims Monitoring] and enable/disable it.
--

John W. Vinson [MVP]

comments inline.

"Beeyen" wrote in message
news
Very good Ms Tina,

Thank you for the information as I followed your suggestions, the error is
no longer visible. Although, I am not achieving the end results I am
trying
to achieve. When I select RATES, PLANS, CO-PAY or ACCUMULATIONS from the
[WorkCategory] combo box drop down and then attempt to make a selection
from
the [ClaimMonitor] field, also a combo box, a message box I created to
save
or discard appears. I select no and a new record appears. Any ideas why?

well, not really. you say you created the message box. what is the complete
code that includes the messagebox call? and where is the code running, in
the mainform or in the subform? and is the WorkCategory combobox control
bound to a field in the mainform's RecordSource? or is it unbound?

also, keep in mind that when the mainform is bound to a table (or a query
based on a table), any changes you make to a bound control in the mainform
will be saved automatically when you move into the subform. and when the
subform is bound to table or query, any changes you make to a bound control
in the subform will be saved automatically when you move back into the
mainform.

hth


Also when I make any other selection from the [WorkCategory] combo box I
receive a Runtime '2164' (You can't disable a control while it has the
focus).

All I would like to be able to do is; if I select rates, plans, co-pay or
accumulations from the [WorkCategory] combo box, then the [ClaimMonitor]
field is enabled and I can make a selection from the combo drop down. If I
make any other select from the [WorkCategory] field then the
[ClaimMonitor]
field stays disabled or even grayed out (currently it is not grayed out).

Thank you in advance, any assistance you can offer.

Below code as per your suggestions

Private Sub WorkCategory_AfterUpdate()
Select Case Me.WorkCategory.Column(1)
Case "RATES", "PLANS", "CO-PAY", "ACCUMULATIONS"
Me!RoutingSub.Form!ClaimsMonitor.Enabled = True

Case Else
Me!RoutingSub.Form!ClaimsMonitor.Enabled = False

End Select

End Sub

"tina" wrote:

did you verify the name of the subform control *within the mainform*? a
subform is a form that is "housed" or "contained" in a control inside of
another form. that "container" control is the subform control within the
mainform. in your code, you need to make sure you're referring to the
name
of the container control - NOT the name of the subform form object as it
shows in the database window. to get the correct name: open the
mainform in
Design view. within the mainform, click *once* on the subform to select
it.
in the Properties box, click on the Other tab and look at the Name
property.
that's the name of the subform control within the mainform. for example,
let's say the above steps showed the subform control name as
ChildMonitor.
assuming that the code is running in the mainform, the reference would
be

Me!ChildMonitor.Form!ClaimMonitoring.Enabled = True

hth


"Beeyen" wrote in message
...
Good Day Gentlemen,

I have tried the solution below, which makes perfect sense, but I
continue
to received a runtime error 438 (Object doesn't support this property
or
method). The code is included below. Any thought of what I might be
doing
wrong?

Mainform = frmWorkload Information Tracker
Mainform field = WorkCategory
Subform = frmRouting
Subform field = ClaimMonitoring

Private Sub WorkCategory_AfterUpdate()
Select Case Me.WorkCategory.Column(2)
Case "RATES", "PLANS", "CO-PAY", "ACCUMULATIONS"
Me!frmRouting.Form![ClaimMonitoring].Enabled = True

Case Else
Me!frmRouting.Form![ClaimMonitoring].Enabled = False

End Select

End Sub

Thank you

"John... Visio MVP" wrote:

John, thanks for the assist.

John... Visio MVP
"John W. Vinson" wrote in
message
...
On Sun, 12 Apr 2009 09:21:01 -0700, Beeyen

wrote:

Good Day John
Thanks for the assistance, the commas worked. Now I received
another
Run-time 2465, The mainform can't find the field 'Claims
Monitoring'
referred
to in your expression.

Could it be that the coding does not reference that the 'Claims
Monitoring'
field located in a Tab Subform called Routing?


Yes. A Subform is another form; Me! refers to the form on which
the
code
exists (the main form in this case). The syntax for referencing a
subform
is
peculiar: you need to use the name *of the Subform control* (which
may
be
different than the name of the Form object within that control).
Try

Case "RATES", "PLANS", "CO-PAY", "ACCUMULATIONS"
Me!Routing.Form![Claims Monitoring].Enabled = True
Case Else
Me!Routing.Form![Claims Monitoring].Enabled = False

This means "look in the Subform control on this form (Me) named
Routing;
look
at the Form object within that Subform control (Form!); find the
control
named
[Claims Monitoring] and enable/disable it.
--

John W. Vinson [MVP]

Good Day Ms Tina

You have asked a number of good questions so I thought I would respond with
the following information and hope it helps (let me know):

Thanks

On the MainForm
Combo Box = WorkCategory
Data
Control Source = WorkCategory
Row Source Type = Table/Query
Row Source = SELECT tblWorkCategory.WorkCategoryID,
tblWorkCategory.WorkCategoryName FROM tblWorkCategory ORDER BY
tblWorkCategory.WorkCategoryName WITH OWNERACCESS OPTION;
Bound Column = 2
Other
Name = WorkCategory

Event Procedures
Private Sub WorkCategory_AfterUpdate()
Select Case Me.WorkCategory.Column(2)
Case "RATES", "PLANS", "CO-PAY", "ACCUMULATIONS"
Me!RoutingSub.Form!ClaimsMonitor.Enabled = False

Case Else
Me!RoutingSub.Form!ClaimsMonitor.Enabled = True
End Select

End Sub
*****************************************
On the SubForm = Tab(Routing)
Combo Box = ClaimMonitoring
Data
Control Source = ClaimMonitoring
Row Source Type = Table/Query
Row Source = SELECT tblclaimmonitor.ClaimsMonitorID,
tblclaimmonitor.LastName & ", " & [FirstName] AS Expr1,
tblclaimmonitor.FirstName FROM tblclaimmonitor ORDER BY
tblclaimmonitor.LastName & ", " & [FirstName];
Bound Column = 2
Other = ClaimMonitor
*****************************************
The message comes from the following:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
strMsg = "Workload Tracker Data has changed."
strMsg = strMsg & "@Would you like to save the changes?"
strMsg = strMsg & "@Click Yes to Save or No to Discard changes and exit
out."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?") = vbYes Then
'do nothing
Else
DoCmd.RunCommand acCmdUndo

End If
End Sub


"tina" wrote:

comments inline.

"Beeyen" wrote in message
news
Very good Ms Tina,

Thank you for the information as I followed your suggestions, the error is
no longer visible. Although, I am not achieving the end results I am
trying
to achieve. When I select RATES, PLANS, CO-PAY or ACCUMULATIONS from the
[WorkCategory] combo box drop down and then attempt to make a selection
from
the [ClaimMonitor] field, also a combo box, a message box I created to
save
or discard appears. I select no and a new record appears. Any ideas why?

well, not really. you say you created the message box. what is the complete
code that includes the messagebox call? and where is the code running, in
the mainform or in the subform? and is the WorkCategory combobox control
bound to a field in the mainform's RecordSource? or is it unbound?

also, keep in mind that when the mainform is bound to a table (or a query
based on a table), any changes you make to a bound control in the mainform
will be saved automatically when you move into the subform. and when the
subform is bound to table or query, any changes you make to a bound control
in the subform will be saved automatically when you move back into the
mainform.

hth


Also when I make any other selection from the [WorkCategory] combo box I
receive a Runtime '2164' (You can't disable a control while it has the
focus).

All I would like to be able to do is; if I select rates, plans, co-pay or
accumulations from the [WorkCategory] combo box, then the [ClaimMonitor]
field is enabled and I can make a selection from the combo drop down. If I
make any other select from the [WorkCategory] field then the
[ClaimMonitor]
field stays disabled or even grayed out (currently it is not grayed out).

Thank you in advance, any assistance you can offer.

Below code as per your suggestions

Private Sub WorkCategory_AfterUpdate()
Select Case Me.WorkCategory.Column(1)
Case "RATES", "PLANS", "CO-PAY", "ACCUMULATIONS"
Me!RoutingSub.Form!ClaimsMonitor.Enabled = True

Case Else
Me!RoutingSub.Form!ClaimsMonitor.Enabled = False

End Select

End Sub

"tina" wrote:

did you verify the name of the subform control *within the mainform*? a
subform is a form that is "housed" or "contained" in a control inside of
another form. that "container" control is the subform control within the
mainform. in your code, you need to make sure you're referring to the
name
of the container control - NOT the name of the subform form object as it
shows in the database window. to get the correct name: open the
mainform in
Design view. within the mainform, click *once* on the subform to select
it.
in the Properties box, click on the Other tab and look at the Name
property.
that's the name of the subform control within the mainform. for example,
let's say the above steps showed the subform control name as
ChildMonitor.
assuming that the code is running in the mainform, the reference would
be

Me!ChildMonitor.Form!ClaimMonitoring.Enabled = True

hth


"Beeyen" wrote in message
...
Good Day Gentlemen,

I have tried the solution below, which makes perfect sense, but I
continue
to received a runtime error 438 (Object doesn't support this property
or
method). The code is included below. Any thought of what I might be
doing
wrong?

Mainform = frmWorkload Information Tracker
Mainform field = WorkCategory
Subform = frmRouting
Subform field = ClaimMonitoring

Private Sub WorkCategory_AfterUpdate()
Select Case Me.WorkCategory.Column(2)
Case "RATES", "PLANS", "CO-PAY", "ACCUMULATIONS"
Me!frmRouting.Form![ClaimMonitoring].Enabled = True

Case Else
Me!frmRouting.Form![ClaimMonitoring].Enabled = False

End Select

End Sub

Thank you

"John... Visio MVP" wrote:

John, thanks for the assist.

John... Visio MVP
"John W. Vinson" wrote in
message
...
On Sun, 12 Apr 2009 09:21:01 -0700, Beeyen

wrote:

Good Day John
Thanks for the assistance, the commas worked. Now I received
another
Run-time 2465, The mainform can't find the field 'Claims
Monitoring'
referred
to in your expression.

Could it be that the coding does not reference that the 'Claims
Monitoring'
field located in a Tab Subform called Routing?


Yes. A Subform is another form; Me! refers to the form on which
the
code
exists (the main form in this case). The syntax for referencing a
subform
is
peculiar: you need to use the name *of the Subform control* (which
may
be
different than the name of the Form object within that control).
Try

Case "RATES", "PLANS", "CO-PAY", "ACCUMULATIONS"
Me!Routing.Form![Claims Monitoring].Enabled = True
Case Else
Me!Routing.Form![Claims Monitoring].Enabled = False

This means "look in the Subform control on this form (Me) named
Routing;
look
at the Form object within that Subform control (Form!); find the
control
named
[Claims Monitoring] and enable/disable it.
--

John W. Vinson [MVP]

looks to me like the problem is in the BoundColumn property of the combobox
controls on both the mainform and the subform.


Row Source = SELECT tblWorkCategory.WorkCategoryID,
tblWorkCategory.WorkCategoryName FROM tblWorkCategory ORDER BY
tblWorkCategory.WorkCategoryName WITH OWNERACCESS OPTION;
Bound Column = 2

presumably field WorkCategoryID is the primary key of tblWorkCategory, and
it is probably a Number data type (or Autonumber). yet you have the bound
column of the combobox control set to column 2, which is the
WorkCategoryName field in the RowSource. so you're trying to save a text
value (the work category name) into a Number field (WorkCategory, in the
mainform's RecordSource).

Row Source = SELECT tblclaimmonitor.ClaimsMonitorID,
tblclaimmonitor.LastName & ", " & [FirstName] AS Expr1,
tblclaimmonitor.FirstName FROM tblclaimmonitor ORDER BY
tblclaimmonitor.LastName & ", " & [FirstName];
Bound Column = 2

here again, i presume that field ClaimsMonitorID is the primary key of
tblclaimmonitor, probably a Number data type (or Autonumber). but, again,
the BoundColumn is 2, which is the "Last Name, FirstName" of the RowSource.
so you're trying to save a person's name into a number field
(ClaimMonitoring, in the subform's RecordSource).

this is a common newbie mistake with combobox controls. i'm guessing that
you changed the BoundColumn property from 1 (the default) to 2, because you
were seeing the primary key number values in the droplist, rather than text.
combobox controls that are bound to foreign key fields cannot be manipulated
that way. so, in BOTH combobox controls. do the following: change the
BoundColumn back to 1. then make sure the ColumnCount property is set to 2.
then change the ColumnWidths property to

0"; 1"

you can make the second column wider or narrower than 1 inch, but make sure
the first column is zero (0) inches.

so now each combobox control will *display* the text values you want to see,
but will *store* the appropriate number values in their underlying foreign
key fields.

hth


"Beeyen" wrote in message
...
Good Day Ms Tina

You have asked a number of good questions so I thought I would respond
with
the following information and hope it helps (let me know):

Thanks

On the MainForm
Combo Box = WorkCategory
Data
Control Source = WorkCategory
Row Source Type = Table/Query
Row Source = SELECT tblWorkCategory.WorkCategoryID,
tblWorkCategory.WorkCategoryName FROM tblWorkCategory ORDER BY
tblWorkCategory.WorkCategoryName WITH OWNERACCESS OPTION;
Bound Column = 2
Other
Name = WorkCategory

Event Procedures
Private Sub WorkCategory_AfterUpdate()
Select Case Me.WorkCategory.Column(2)
Case "RATES", "PLANS", "CO-PAY", "ACCUMULATIONS"
Me!RoutingSub.Form!ClaimsMonitor.Enabled = False

Case Else
Me!RoutingSub.Form!ClaimsMonitor.Enabled = True
End Select

End Sub
*****************************************
On the SubForm = Tab(Routing)
Combo Box = ClaimMonitoring
Data
Control Source = ClaimMonitoring
Row Source Type = Table/Query
Row Source = SELECT tblclaimmonitor.ClaimsMonitorID,
tblclaimmonitor.LastName & ", " & [FirstName] AS Expr1,
tblclaimmonitor.FirstName FROM tblclaimmonitor ORDER BY
tblclaimmonitor.LastName & ", " & [FirstName];
Bound Column = 2
Other = ClaimMonitor
*****************************************
The message comes from the following:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
strMsg = "Workload Tracker Data has changed."
strMsg = strMsg & "@Would you like to save the changes?"
strMsg = strMsg & "@Click Yes to Save or No to Discard changes and
exit
out."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?") = vbYes
Then
'do nothing
Else
DoCmd.RunCommand acCmdUndo

End If
End Sub


"tina" wrote:

comments inline.

"Beeyen" wrote in message
news
Very good Ms Tina,

Thank you for the information as I followed your suggestions, the
error is
no longer visible. Although, I am not achieving the end results I am
trying
to achieve. When I select RATES, PLANS, CO-PAY or ACCUMULATIONS from
the
[WorkCategory] combo box drop down and then attempt to make a
selection
from
the [ClaimMonitor] field, also a combo box, a message box I created to
save
or discard appears. I select no and a new record appears. Any ideas
why?

well, not really. you say you created the message box. what is the
complete
code that includes the messagebox call? and where is the code running,
in
the mainform or in the subform? and is the WorkCategory combobox control
bound to a field in the mainform's RecordSource? or is it unbound?

also, keep in mind that when the mainform is bound to a table (or a
query
based on a table), any changes you make to a bound control in the
mainform
will be saved automatically when you move into the subform. and when the
subform is bound to table or query, any changes you make to a bound
control
in the subform will be saved automatically when you move back into the
mainform.

hth


Also when I make any other selection from the [WorkCategory] combo box
I
receive a Runtime '2164' (You can't disable a control while it has the
focus).

All I would like to be able to do is; if I select rates, plans, co-pay
or
accumulations from the [WorkCategory] combo box, then the
[ClaimMonitor]
field is enabled and I can make a selection from the combo drop down.
If I
make any other select from the [WorkCategory] field then the
[ClaimMonitor]
field stays disabled or even grayed out (currently it is not grayed
out).

Thank you in advance, any assistance you can offer.

Below code as per your suggestions

Private Sub WorkCategory_AfterUpdate()
Select Case Me.WorkCategory.Column(1)
Case "RATES", "PLANS", "CO-PAY", "ACCUMULATIONS"
Me!RoutingSub.Form!ClaimsMonitor.Enabled = True

Case Else
Me!RoutingSub.Form!ClaimsMonitor.Enabled = False

End Select

End Sub

"tina" wrote:

did you verify the name of the subform control *within the
mainform*? a
subform is a form that is "housed" or "contained" in a control
inside of
another form. that "container" control is the subform control within
the
mainform. in your code, you need to make sure you're referring to
the
name
of the container control - NOT the name of the subform form object
as it
shows in the database window. to get the correct name: open the
mainform in
Design view. within the mainform, click *once* on the subform to
select
it.
in the Properties box, click on the Other tab and look at the Name
property.
that's the name of the subform control within the mainform. for
example,
let's say the above steps showed the subform control name as
ChildMonitor.
assuming that the code is running in the mainform, the reference
would
be

Me!ChildMonitor.Form!ClaimMonitoring.Enabled = True

hth


"Beeyen" wrote in message
...
Good Day Gentlemen,

I have tried the solution below, which makes perfect sense, but I
continue
to received a runtime error 438 (Object doesn't support this
property
or
method). The code is included below. Any thought of what I might
be
doing
wrong?

Mainform = frmWorkload Information Tracker
Mainform field = WorkCategory
Subform = frmRouting
Subform field = ClaimMonitoring

Private Sub WorkCategory_AfterUpdate()
Select Case Me.WorkCategory.Column(2)
Case "RATES", "PLANS", "CO-PAY", "ACCUMULATIONS"
Me!frmRouting.Form![ClaimMonitoring].Enabled = True

Case Else
Me!frmRouting.Form![ClaimMonitoring].Enabled = False

End Select

End Sub

Thank you

"John... Visio MVP" wrote:

John, thanks for the assist.

John... Visio MVP
"John W. Vinson" wrote in
message
...
On Sun, 12 Apr 2009 09:21:01 -0700, Beeyen

wrote:

Good Day John
Thanks for the assistance, the commas worked. Now I received
another
Run-time 2465, The mainform can't find the field 'Claims
Monitoring'
referred
to in your expression.

Could it be that the coding does not reference that the
'Claims
Monitoring'
field located in a Tab Subform called Routing?


Yes. A Subform is another form; Me! refers to the form on
which
the
code
exists (the main form in this case). The syntax for
referencing a
subform
is
peculiar: you need to use the name *of the Subform control*
(which
may
be
different than the name of the Form object within that
control).
Try

Case "RATES", "PLANS", "CO-PAY", "ACCUMULATIONS"
Me!Routing.Form![Claims Monitoring].Enabled = True
Case Else
Me!Routing.Form![Claims Monitoring].Enabled = False

This means "look in the Subform control on this form (Me)
named
Routing;
look
at the Form object within that Subform control (Form!); find
the
control
named
[Claims Monitoring] and enable/disable it.
--

John W. Vinson [MVP]