combo box error

  • Thread starter Thread starter Khang
  • Start date Start date
K

Khang

I have converted an Access 97 database to XP.

I have the following error, "The value you entered isn't
valid for this field," when I click on a combo box to
choose a list of tables so that I can create a plot from
the fields in the selected table. The combo box has a Row
Source Type of 'Table/Query' and a Row Source based on a
query.

I have double checked the Data Types and Field Size of
the fields in the tables and there are no differences in
the 97 and XP version. I have tried debugging the code
with no success. I even tried to replace the combox box
in XP. I have exhausted all my options and I am desperate.

Is there something that I have missed? Please help me.
Thanks.

Khang
 
How many columns are in the combo's RowSource query/statement/table?
Which one is the Bound column of the query?
Does the Column Count include the Bound column?
What is the data type and size of the bound column, compared to the field
nominated in its Control Source?

If the Bound column refers to a calculated field in a query, can you
explicitly typecast the data in this field, e.g. CLng(), CVDate(), ...?
 
Hi Allen, thanks for your help! Actually, I am using a
code to generate a query to populate the combo box with 1
column only(the combo box is filled with a list of tables
you can select). For a given selected table, another
combo box is filled using the code with a list of
available fields that can be plotted on the ms chart. The
error, 'The value you entered isn't valid for this
field,' immediately occurred once the user click on the
first combo box. There are different data types in the
selected fields(second combo box). However, this error
has never occured in Access 97, but it happened once I
have converted to XP.

I would appreciate further help from you. Thanks!

-Khang
 
So the first combo box selects a table name (string value), and its
AfterUpdate applies that in some way to the 2nd combo. Is it the AfterUpdate
code that generates the error? If so, you may need to post the code.
 
Thanks for your continuing support. Yes, it fails at the
AfterUpdate() sub-routine toward the last second line of
the code. But when I comment out the line, it still fail
with the same error message. Here is the code:

;;------------------------------------

Private Sub cboSourceTable_AfterUpdate()
'
' Determine start and finish dates of chosen table and
' set "Start" and "Finish" tabs appropriately. Also
update
' project code and field combo boxes.
'
Dim i As Long 'Counter
Dim qry As String 'SQL query string
Dim tblsource As String 'Name of source table

fldX = "Date_Time"
msgtitle = "Source Table: Determine Start & Finish
Dates"


' CONFIRM THAT "Date_Time" FIELD EXISTS
tblsource = cboSourceTable.Value
If tblsource = "Choose Me First" Then End
cboSourceY1.RowSourceType = "Field List"
cboSourceY1.RowSource = tblsource

For i = 0 To cboSourceY1.ListCount
If cboSourceY1.ItemData(i) = fldX Then GoTo 100
Next i
MsgBox "There is no '" & fldX & "' field in this
table." & _
Chr(10) & Chr(10) & _
"Please select another table.", , msgtitle
End


' DETERMINE AND SET START & FINISH DATES
' Determine start date of table
100 tblsource = cboSourceTable.Value
qry = "SELECT Min(" & tblsource & "." & fldX & ") AS
MinOfDate_Time " & _
"FROM " & tblsource & ";"
cboSourceY1.RowSourceType = "Table/Query"
cboSourceY1.RowSource = qry

' Set start date on display
timeS = cboSourceY1.ItemData(0)
txtYrS.Value = Year(timeS)
cboMonS.Value = Format(timeS, "mmm")
txtDayS.Value = Day(timeS)

' Determine finish date of table
tblsource = cboSourceTable.Value
qry = "SELECT Max(" & tblsource & "." & fldX & ") AS
MaxOfDate_Time " & _
"FROM " & tblsource & ";"
cboSourceY1.RowSource = qry

' Set finish date on display (adding 1 day to include
last day of data)
timeF = cboSourceY1.ItemData(0)
timeF = timeF + 1
txtYrF.Value = Year(timeF)
cboMonF.Value = Format(timeF, "mmm")
txtDayF.Value = Day(timeF)


' UPDATE COMBO BOXES
' Update project code combo box
cboProjCode.RowSourceType = "Table/Query"
qryplot = "SELECT " & tblsource & ".Project_Code" & _
" FROM " & tblsource & _
" GROUP BY " & tblsource & ".Project_Code;"
cboProjCode.RowSource = qryplot
cboProjCode.Value = cboProjCode.ItemData(0)

' Reset field selections
cboSourceY1.Value = "Choose Me Second" 'IT FAILS
HERE!
cboSourceY2.Value = "None"

End Sub
;;------------------------------------

Thanks again,

-Khang
 
If the LimitToList property of cboSourceY1 is Yes, attempting to set it to
the string "Choose Me Second" will fail.
 
Back
Top