Synchronized Combo Box Not Working

  • Thread starter Thread starter ScottMsp
  • Start date Start date
S

ScottMsp

Hello,

I have been working on this issue for hours and have spent many days trying
to read the board to solve my issue, but I cannot, which is why I am posting
this question.

I cannot get my drop down boxes to work.

I am simply trying to use two drop down boxes and limit the second drop down
box based on a selection I made in the first drop down box. Here is what I
have so far:

Form Name: fBenchmarkJobDetails
RecordSource: tBenchmarkJobDetails
Fields in tBenchmarkJobDetails
JobCode
SvyName_ID
SvyEffectiveDate
SvyJobCode
And so on…

First Drop Down Box
Control Source: SvyName_ID
Control Box Name: cboSvyName_ID
RowSource: SELECT [tSurveyNamesMaster].[SvyName_ID] FROM
[tSurveyNamesMaster] ORDER BY [SvyName_ID];

AfterUpdateEvent
Private Sub cboSvyName_ID_AfterUpdate()

Me.cboSvyEffectiveDate = Null
Me.cboSvyEffectiveDate.Requery
Me.cboSvyEffectiveDate.ItemData (0)

End Sub

Second Drop Down Box
Control Source: SvyEffectiveDate
Control Box Name: cboSvyEffectiveDate
Row Source: SELECT [tSurveynamesMaster].[SvyName_ID],
[tSurveyNamesMaster].[SvyEffectiveDate] FROM [tSurveyNamesMaster] WHERE
((([tSurveyNamesMaster].[SvyName_ID])=Forms!fBenchmarkJobDetails.Form!cboSvyName_ID));



Thanks in advance for your help.
 
Scott -

The second combo box's recordsource is set on load, and isn't automatically
refreshed when an item in the first combo box is selected. To fix this, in
the AfterUpdate event of the first combo box, you need to put code to requery
the data for the second combo box:

Me.cboSvyEffectiveDate.Requery

That should fix it.
 
Daryl,

I think I have that information in my original post. This is what I have in
the AfterUpdate section of the first combo box:

Private Sub cboSvyName_ID_AfterUpdate()

Me.cboSvyEffectiveDate = Null
Me.cboSvyEffectiveDate.Requery
Me.cboSvyEffectiveDate.ItemData (0)

End Sub

Do I need something different? The error message I get when trying to run
this in Access 2007 is:

Error Message
The expression After Update you entered as the event property setting
produced the following error: A problem occurred while Microsoft Office
Access was communicating with the OLE Server or Active X Control.
*The expression may not result in the name of a macro, the name of a
user-defined function or [Event Procedure]
*There may have been an error evaluating the function, event or macro

Thanks in advance.

Daryl S said:
Scott -

The second combo box's recordsource is set on load, and isn't automatically
refreshed when an item in the first combo box is selected. To fix this, in
the AfterUpdate event of the first combo box, you need to put code to requery
the data for the second combo box:

Me.cboSvyEffectiveDate.Requery

That should fix it.
--
Daryl S


ScottMsp said:
Hello,

I have been working on this issue for hours and have spent many days trying
to read the board to solve my issue, but I cannot, which is why I am posting
this question.

I cannot get my drop down boxes to work.

I am simply trying to use two drop down boxes and limit the second drop down
box based on a selection I made in the first drop down box. Here is what I
have so far:

Form Name: fBenchmarkJobDetails
RecordSource: tBenchmarkJobDetails
Fields in tBenchmarkJobDetails
JobCode
SvyName_ID
SvyEffectiveDate
SvyJobCode
And so on…

First Drop Down Box
Control Source: SvyName_ID
Control Box Name: cboSvyName_ID
RowSource: SELECT [tSurveyNamesMaster].[SvyName_ID] FROM
[tSurveyNamesMaster] ORDER BY [SvyName_ID];

AfterUpdateEvent
Private Sub cboSvyName_ID_AfterUpdate()

Me.cboSvyEffectiveDate = Null
Me.cboSvyEffectiveDate.Requery
Me.cboSvyEffectiveDate.ItemData (0)

End Sub

Second Drop Down Box
Control Source: SvyEffectiveDate
Control Box Name: cboSvyEffectiveDate
Row Source: SELECT [tSurveynamesMaster].[SvyName_ID],
[tSurveyNamesMaster].[SvyEffectiveDate] FROM [tSurveyNamesMaster] WHERE
((([tSurveyNamesMaster].[SvyName_ID])=Forms!fBenchmarkJobDetails.Form!cboSvyName_ID));



Thanks in advance for your help.
Click to expand...
Click to expand...
 
Scott -

What are you trying to do with the "Me.cboSvyEffectiveDate.ItemData (0)"
line? The ItemData property is read-only. Are you getting any compile or
run-time errors? Or are you using Access 2007 that may have something new
not in my Access 2003 database?

Also, what is in your second combo box (you only say it is not working)?
Are you getting too many records, no records, the wrong records?

--
Daryl S


ScottMsp said:
Daryl,

I think I have that information in my original post. This is what I have in
the AfterUpdate section of the first combo box:

Private Sub cboSvyName_ID_AfterUpdate()

Me.cboSvyEffectiveDate = Null
Me.cboSvyEffectiveDate.Requery
Me.cboSvyEffectiveDate.ItemData (0)

End Sub

Do I need something different? The error message I get when trying to run
this in Access 2007 is:

Error Message
The expression After Update you entered as the event property setting
produced the following error: A problem occurred while Microsoft Office
Access was communicating with the OLE Server or Active X Control.
*The expression may not result in the name of a macro, the name of a
user-defined function or [Event Procedure]
*There may have been an error evaluating the function, event or macro

Thanks in advance.

Daryl S said:
Scott -

The second combo box's recordsource is set on load, and isn't automatically
refreshed when an item in the first combo box is selected. To fix this, in
the AfterUpdate event of the first combo box, you need to put code to requery
the data for the second combo box:

Me.cboSvyEffectiveDate.Requery

That should fix it.
--
Daryl S


ScottMsp said:
Hello,

I have been working on this issue for hours and have spent many days trying
to read the board to solve my issue, but I cannot, which is why I am posting
this question.

I cannot get my drop down boxes to work.

I am simply trying to use two drop down boxes and limit the second drop down
box based on a selection I made in the first drop down box. Here is what I
have so far:

Form Name: fBenchmarkJobDetails
RecordSource: tBenchmarkJobDetails
Fields in tBenchmarkJobDetails
JobCode
SvyName_ID
SvyEffectiveDate
SvyJobCode
And so on…

First Drop Down Box
Control Source: SvyName_ID
Control Box Name: cboSvyName_ID
RowSource: SELECT [tSurveyNamesMaster].[SvyName_ID] FROM
[tSurveyNamesMaster] ORDER BY [SvyName_ID];

AfterUpdateEvent
Private Sub cboSvyName_ID_AfterUpdate()

Me.cboSvyEffectiveDate = Null
Me.cboSvyEffectiveDate.Requery
Me.cboSvyEffectiveDate.ItemData (0)

End Sub

Second Drop Down Box
Control Source: SvyEffectiveDate
Control Box Name: cboSvyEffectiveDate
Row Source: SELECT [tSurveynamesMaster].[SvyName_ID],
[tSurveyNamesMaster].[SvyEffectiveDate] FROM [tSurveyNamesMaster] WHERE
((([tSurveyNamesMaster].[SvyName_ID])=Forms!fBenchmarkJobDetails.Form!cboSvyName_ID));



Thanks in advance for your help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top