"The value you entered isn't valid for this field" message

[sotchris]

Can you PLEASE give me a hand here!

I'm relatively new in VBA programming.
I use a combobox which uses an SQL qry string (strSQL) as a RowSource to select fields from various tables. The strSQL is itself selected according to the value of a option group with values 1, 2 and 3 using "Select Case .." . The first two choices (1 & 2) populate the combobox with numbers from a certain table. Choice no 3 populates it with strings that look something like "1/2/33" (not dates) from another table.
While the programm works fine with the numbers (options 1 & 2) I keep getting the above message every time I try to select something from option 3.
There's no incompatibility in data types - as far as I can tell - and the InputMask and Format properties are left blank.
Funny thing is that the programm worked fine until the day before!
Any hint how to overcome this?

Thanks in advance!

:banghead:

[OTWarrior]

sounds like option 3 is set for numbers or dates and is trying to read a string value.

would you be able to post your sql qry code?

btw: why are you using 3 fields to populate one combobox?

[sotchris]

Thanks OTWarrior for answering!
As a pass time I've been trying to rig a simple VBA program for my hobby (which is wargaming).
Here's how it works (or supposed to) :
When you select the firer you have a choice of three types of target, depending on the value of optTargetKind option group, which sets the SQL qry to the cboTargetID combobox accordingly (see code)
The funny thing is that the Format, ControlSource and InputMask properties are blank.

----------------------------------------------------------------
Private Sub cboTargetID_GotFocus()
Dim strSQLT As String

Me!cboTargetID.ColumnCount = 2
Me!cboTargetID.ColumnWidths = "1.4 cm;2.5 cm;"
Me!cboTargetMove.Enabled = True

Select Case TargetKind
Case 1 'Tanks
strSQLT = "SELECT tblCombatVehicles.[IDnumber], tblCombatVehicles.[Designation]" _
& "FROM tblCombatVehicles WHERE tblCombatVehicles.[Nationality] = '" & strTargetSide & "'" _
& "AND tblCombatVehicles.[Killed] = False AND tblCombatVehicles.[Type]<>'?? ???/??';"
Case 2 'A/T guns
strSQLT = "SELECT tblCombatVehicles.[IDnumber], tblCombatVehicles.[Designation]" _
& "FROM tblCombatVehicles WHERE tblCombatVehicles.[Nationality] = '" & strTargetSide & "'" _
& "AND tblCombatVehicles.[Killed] = False AND tblCombatVehicles.[Type]='?? ???/??';"
Me!cboTargetMove.Enabled = False
TargetMove = 0
Case 3 'Infantry
Me!cboTargetID.ColumnCount = 1
strSQLT = "SELECT tblCombatSquad.[SquadID], tblCombatSquad.[SquadID] FROM tblCombatSquad " _
& "WHERE tblCombatSquad.[Nationality] = '" & strTargetSide & "';"
End Select

Me!cboTargetID.RowSourceType = "Table/Query"
Me!cboTargetID.RowSource = strSQLT
Me!cboTargetID.Requery
End Sub
--------------------------------------------------------------

Options 1 & 2 (TargetKind=1 or 2) searches for the IDNumber field in one table and option 3 (TargetKind=3) searches SquadID in another table.
(The "Swedish" is actually Greek!)
I've bypassed the problem rather easily but it's still puzzling me and I'd appreciate it if you could give me a clue.

Thanks again for taking the time !
sotchris

[DarkSprout]

Is Case 3: breaking the Validation Rule for the Rowsource?
what does the Rowsource string look like
1: "1/2/33";"2/3/44";"3/4/55"
2: 1;2;3
Are your switching between Strings and Integers?
Check the bound column refelts the number of columns within the SQL.

[sotchris]

There is no Validation Rule (blank). All three values are asigned to string variables.
Option3 SQL string is :

strSQLT = "SELECT tblCombatSquad.[SquadAA], tblCombatSquad.[SquadID] FROM tblCombatSquad " _
& "WHERE tblCombatSquad.[Nationality] = '" & strTargetSide & "';"

Thanks