Combo box error after conversion to 2000

[JSchumacher]

Hi,

I recently converted a db from 97 to 2k. I have a combo box with the RowSource modified in the code as different option groups are changed. One of the statements returns a Short Date and another returns the Year using a DatePart function to find it. The combo box worked fine in 97, but now when the RowSource is changed and a selection is made on the new data, an error comes up. "The value you entered isn't valid for this field. You may have entered text in a numeric field or a number that is larger than the FieldSize setting permits." It doesn't matter if I go from Year to Date or from Date to year. It will work the first time I use it, but once the RowSource is changed, it won't. I tried using Format instead of DatePart and using CInt on the date, but still have the same error. Any ideas on what the problem is?

Thanks!

Judie

 

[JSchumacher]

I'm still unable to get this to work - any ideas anyone???

Thanks.

 

[JeremyNYC]

Judie,

Can you post the code you're using to alter the rowsource? That might help.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[JSchumacher]

Here is the code that modifies the RowSource. At the end of the code I Null out the value of the Combo box and set two other Combo boxes to have the same RowSource.


Private Sub fraReportType_AfterUpdate()
Select Case fraReportType
Case 1, 2
lblPeriodCurrent.Caption = "Individual Month:"
Select Case fraPortfolio
Case 1, 2
cboCurrentMonth.RowSource = "SELECT tblMonthlyData.MEDate FROM tblMonthlyData WHERE (((tblMonthlyData.TotalFiles) Is Not Null) AND ((tblMonthlyData.PortID)=" & fraPortfolio & &quot) GROUP BY tblMonthlyData.MEDate ORDER BY tblMonthlyData.MEDate DESC;"
Case 3
cboCurrentMonth.RowSource = "SELECT ..." SIMILAR CODE
End Select

Case 3
lblPeriodCurrent.Caption = "Individual Year:"
Select Case fraPortfolio
Case 1, 2
cboCurrentMonth.RowSource = "SELECT DatePart(""yyyy"",[MEDate]) AS Year FROM tblMonthlyData WHERE (((tblMonthlyData.NewBks) Is Not Null)AND ((tblMonthlyData.PortID)=" & fraPortfolio & &quot) GROUP BY DatePart(""yyyy"",[MEDate]) ORDER BY DatePart(""yyyy"",[MEDate]) DESC;"
Case 3
cboCurrentMonth.RowSource = "SELECT ..." SIMILAR CODE
End Select
End Select
End Sub

Thanks for taking a look.

Judie

 

[JSchumacher]

In trying to figure this out, I'm checking out various properties. I notice that the ColTyp on the combo box I'm using doesn't change in Access 97 (where this works), but in 2000 it does. On load it is 0, then when the RowSource is set the first time it becomes an 8 when fraReportType is 1 or 2 and a 3 when fraReportType is a 3. In subsequent settings of the RowSource, the ColType does NOT change.

What is a ColType and could this be the source of my problem?

 

[paradiso]

This is precisely the error I've been fussing with today. Casting the date values to strings in the rowsource query does not help. Nor does requery. Nor does placing a dummy query in the rowsource property in the form's design mode.

Has anyone found an answer to this?

paradiso