Main form and subform

[Beeyen]

Good Day,

Let me try this one again; I have a master form with several Tabs and
Subforms. In the master form there is a field called Work Category (a combo
box) and in this field, among other listings I have rate, plans, co-pay and
accumulations. In one of my Tabs called Routing, one of my fields is called
Claims Monitoring

If rate, plans, co-pay or accumulation is selection in the Work Category
field of the Master form I would like to see the Claim Monitoring field
populated with which every the selection is, otherwise the field is
grayed-out.

Can this be done?

Thanks in advance

 

[Beeyen]

Good Day Steve,

Thank you for the assistance. You are correct with the parameters of the
combo box and I have entered the code as you suggested (as is below):

Private Sub Work_Category_AfterUpdate()
Select Case Me![Work Category].Column(2)
Case "rates" Or "plans" Or "co-pay" Or "accumulations"
Me![Claims Monitoring].Enabled = True
Case Else
Me![Claims Monitoring].Enabled = False
End Select

End Sub

Yet, I received a debugging runtime error 13, type mismatch message, which
stops at: Case "rates" Or "plans" Or "co-pay" Or "accumulations"

Any ideas?

I will assume that your Work Category combobox is based on a table that
looks like:
TblWorkCategory
WorkCategoryID
WorkCategory
and your combobox has its Bound Column property set to 1 and Column Width
property set to 0;1.5.

To do what you want ...........
1. Go to design view and set the Enabled property of Claim Monitoring
field to False
2. Put the following code in the AfterUpdate event of your combobox:
Select Case Me![Work Category].Column(2)
Case "Rate" Or "Plans" Or "Co-pay" Or "Accumulation"
Me![Claims Monitoring].Enabled = True
Case Else
Me![Claims Monitoring].Enabled = False
End Select

Steve
(e-mail address removed)

Good Day,

Let me try this one again; I have a master form with several Tabs and
Subforms. In the master form there is a field called Work Category (a
combo
box) and in this field, among other listings I have rate, plans, co-pay
and
accumulations. In one of my Tabs called Routing, one of my fields is
called
Claims Monitoring

If rate, plans, co-pay or accumulation is selection in the Work Category
field of the Master form I would like to see the Claim Monitoring field
populated with which every the selection is, otherwise the field is
grayed-out.

Can this be done?

Thanks in advance

 

[John... Visio MVP]

Two suggestions
1: replace the "or"s with commas so that the line is
Case "rates", "plans", "co-pay", "accumulations"
2: Avoid any offers by steve to help. He is a known troll who offers
questionable services for questionable fees

John... Visio MVP

Good Day Steve,

Thank you for the assistance. You are correct with the parameters of the
combo box and I have entered the code as you suggested (as is below):

Private Sub Work_Category_AfterUpdate()
Select Case Me![Work Category].Column(2)
Case "rates" Or "plans" Or "co-pay" Or "accumulations"
Me![Claims Monitoring].Enabled = True
Case Else
Me![Claims Monitoring].Enabled = False
End Select

End Sub

Yet, I received a debugging runtime error 13, type mismatch message, which
stops at: Case "rates" Or "plans" Or "co-pay" Or "accumulations"

Any ideas?

I will assume that your Work Category combobox is based on a table that
looks like:
TblWorkCategory
WorkCategoryID
WorkCategory
and your combobox has its Bound Column property set to 1 and Column Width
property set to 0;1.5.

To do what you want ...........
1. Go to design view and set the Enabled property of Claim Monitoring
field to False
2. Put the following code in the AfterUpdate event of your combobox:
Select Case Me![Work Category].Column(2)
Case "Rate" Or "Plans" Or "Co-pay" Or "Accumulation"
Me![Claims Monitoring].Enabled = True
Case Else
Me![Claims Monitoring].Enabled = False
End Select

Steve
(e-mail address removed)

Good Day,

Let me try this one again; I have a master form with several Tabs and
Subforms. In the master form there is a field called Work Category (a
combo
box) and in this field, among other listings I have rate, plans, co-pay
and
accumulations. In one of my Tabs called Routing, one of my fields is
called
Claims Monitoring

If rate, plans, co-pay or accumulation is selection in the Work
Category
field of the Master form I would like to see the Claim Monitoring field
populated with which every the selection is, otherwise the field is
grayed-out.

Can this be done?

Thanks in advance

 

[Beeyen]

Good Day John,

Thanks for the heads up. I am an Access medium beginner (self – taught) and
am working on a database with a number of conditions and I have lots of
questions. So it has been good to seek the assistance of the community. I
was thinking to myself with so much more corruption (mostly due to the
current economy) it is good to be able to raise a question and have so many
united for an answer. My second job is building computers; I do it for the
experience. I have always shared my knowledge and assistance daily; in return
knowing others are not being taken advantage of and can enjoy the technology
before them.

Thanks again

Two suggestions
1: replace the "or"s with commas so that the line is
Case "rates", "plans", "co-pay", "accumulations"
2: Avoid any offers by steve to help. He is a known troll who offers
questionable services for questionable fees

John... Visio MVP

Good Day Steve,

Thank you for the assistance. You are correct with the parameters of the
combo box and I have entered the code as you suggested (as is below):

Private Sub Work_Category_AfterUpdate()
Select Case Me![Work Category].Column(2)
Case "rates" Or "plans" Or "co-pay" Or "accumulations"
Me![Claims Monitoring].Enabled = True
Case Else
Me![Claims Monitoring].Enabled = False
End Select

End Sub

Yet, I received a debugging runtime error 13, type mismatch message, which
stops at: Case "rates" Or "plans" Or "co-pay" Or "accumulations"

Any ideas?

I will assume that your Work Category combobox is based on a table that
looks like:
TblWorkCategory
WorkCategoryID
WorkCategory
and your combobox has its Bound Column property set to 1 and Column Width
property set to 0;1.5.

To do what you want ...........
1. Go to design view and set the Enabled property of Claim Monitoring
field to False
2. Put the following code in the AfterUpdate event of your combobox:
Select Case Me![Work Category].Column(2)
Case "Rate" Or "Plans" Or "Co-pay" Or "Accumulation"
Me![Claims Monitoring].Enabled = True
Case Else
Me![Claims Monitoring].Enabled = False
End Select

Steve
(e-mail address removed)


Good Day,

Let me try this one again; I have a master form with several Tabs and
Subforms. In the master form there is a field called Work Category (a
combo
box) and in this field, among other listings I have rate, plans, co-pay
and
accumulations. In one of my Tabs called Routing, one of my fields is
called
Claims Monitoring

If rate, plans, co-pay or accumulation is selection in the Work
Category
field of the Master form I would like to see the Claim Monitoring field
populated with which every the selection is, otherwise the field is
grayed-out.

Can this be done?

Thanks in advance

 

[Beeyen]

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?

rivate Sub WorkCategory_AfterUpdate()
Select Case Me![WorkCategory].Column(2)
Case "RATES", "PLANS", "CO-PAY", "ACCUMULATIONS"
Me![Claims Monitoring].Enabled = True
Case Else
Me![Claims Monitoring].Enabled = False

Good Day John,

Thanks for the heads up. I am an Access medium beginner (self – taught) and
am working on a database with a number of conditions and I have lots of
questions. So it has been good to seek the assistance of the community. I
was thinking to myself with so much more corruption (mostly due to the
current economy) it is good to be able to raise a question and have so many
united for an answer. My second job is building computers; I do it for the
experience. I have always shared my knowledge and assistance daily; in return
knowing others are not being taken advantage of and can enjoy the technology
before them.

Thanks again

Two suggestions
1: replace the "or"s with commas so that the line is
Case "rates", "plans", "co-pay", "accumulations"
2: Avoid any offers by steve to help. He is a known troll who offers
questionable services for questionable fees

John... Visio MVP

Good Day Steve,

Thank you for the assistance. You are correct with the parameters of the
combo box and I have entered the code as you suggested (as is below):

Private Sub Work_Category_AfterUpdate()
Select Case Me![Work Category].Column(2)
Case "rates" Or "plans" Or "co-pay" Or "accumulations"
Me![Claims Monitoring].Enabled = True
Case Else
Me![Claims Monitoring].Enabled = False
End Select

End Sub

Yet, I received a debugging runtime error 13, type mismatch message, which
stops at: Case "rates" Or "plans" Or "co-pay" Or "accumulations"

Any ideas?

:

I will assume that your Work Category combobox is based on a table that
looks like:
TblWorkCategory
WorkCategoryID
WorkCategory
and your combobox has its Bound Column property set to 1 and Column Width
property set to 0;1.5.

To do what you want ...........
1. Go to design view and set the Enabled property of Claim Monitoring
field to False
2. Put the following code in the AfterUpdate event of your combobox:
Select Case Me![Work Category].Column(2)
Case "Rate" Or "Plans" Or "Co-pay" Or "Accumulation"
Me![Claims Monitoring].Enabled = True
Case Else
Me![Claims Monitoring].Enabled = False
End Select

Steve
(e-mail address removed)


Good Day,

Let me try this one again; I have a master form with several Tabs and
Subforms. In the master form there is a field called Work Category (a
combo
box) and in this field, among other listings I have rate, plans, co-pay
and
accumulations. In one of my Tabs called Routing, one of my fields is
called
Claims Monitoring

If rate, plans, co-pay or accumulation is selection in the Work
Category
field of the Master form I would like to see the Claim Monitoring field
populated with which every the selection is, otherwise the field is
grayed-out.

Can this be done?

Thanks in advance

 

[John W. Vinson]

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... Visio MVP]

John, thanks for the assist.

John... Visio MVP

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.

 

[Beeyen]

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, thanks for the assist.

John... Visio MVP

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.

 

[tina]

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

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, thanks for the assist.

John... Visio MVP

On Sun, 12 Apr 2009 09:21:01 -0700, Beeyen
<[email protected]>
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.

 

[Beeyen]

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

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

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, thanks for the assist.

John... Visio MVP
On Sun, 12 Apr 2009 09:21:01 -0700, Beeyen
<[email protected]>
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.

 

[tina]

comments inline.

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

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

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, thanks for the assist.

John... Visio MVP
On Sun, 12 Apr 2009 09:21:01 -0700, Beeyen
<[email protected]>
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.

 

[Beeyen]

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

comments inline.

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

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


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, thanks for the assist.

John... Visio MVP
On Sun, 12 Apr 2009 09:21:01 -0700, Beeyen
<[email protected]>
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.

 

[tina]

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

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

comments inline.

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

:

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


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, thanks for the assist.

John... Visio MVP
On Sun, 12 Apr 2009 09:21:01 -0700, Beeyen
<[email protected]>
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.