Data Type Incorrect - Value Comes From Combo Box

[Fattire]

I am trying to make sure that two objects' values in a form (text box and a combo box) don't already exist in the DB when they make changes to whatever values are already in there hit the cmd btn to do the commit/edit.

Says I have a wrong data type on field varThemeID, which comes from the 1st column from the forms only combo box, column(0).

Field that varThemeID is updating is a Double data type in the table and is declared as such in the code.

normally, varThemeID = 1202842120

Dim varThemeID As Variant

varThemeID = Nz(Me.cbo_ThemeUpdate.Column(0), "0")

    If Not IsNull(DLookup("[ServicesID]", "[tbl_Services]", "[ServicesID] = '" & Me.OpenArgs & "'" And "[ThemeID] = '" & varThemeID & "'")) Then
    Me.txt_EditConfirm = "Nothing to change or already exists in database"
    Me.txt_ServicesNameEdit.SetFocus
    Else

I've run the code in a query window and I get the data I need, not sure what else to do, any help would be appreciated.

 

[ck1999]

if varthemeID = 1202842120 then you should try

If Not IsNull(DLookup("[ServicesID]", "[tbl_Services]", "[ServicesID] = '" & Me.OpenArgs & "'" And "[ThemeID] = " & varThemeID))

You only need '" & _____ & "'" if the variable is a string

ck1999

 

[Fattire]

Thank you for the help. I tried that before and I got the Type mismatch error still

I've tried:

Dim varThemeID As Double
Dim varThemeID As Variant
Dim varThemeID As String

"[ThemeID] = " & varThemeID)
"[ThemeID] = " & [varThemeID])
"[ThemeID] = '" & Nz(Me.cbo_ThemeUpdate.Column(0), "0")))
"[ThemeID] = '" & [varThemeID] & "'")
"[ThemeID] = '" & varThemeID & "'")

I even typed in the actual value, that didn't work, same error as typed below.

"[ThemeID] = 1202842120")

What's interesting is that this update statement works just fine with the variable, it just won't work in a Dlookup or a With/Do While statement:

sqlstr2 = sqlstr2 & " UPDATE tbl_Services "
sqlstr2 = sqlstr2 & " SET tbl_Services.ThemeID = " & varThemeID
sqlstr2 = sqlstr2 & " WHERE (((tbl_Services.ServicesID)= " & Me.OpenArgs & "));"
rec2.Open sqlstr2, con1

The combo box's RowSource is a SQL statement, but that's never been a problem before, a number is a number.

Any other idea's?

 

[RivetHed]

I notice that your Nz is setting null values to "0" rather than 0 if you're searching on an integer field this would cause a type mismatch.

 

[ck1999]

One more shot

If Not IsNull(DLookup("[ServicesID]", "[tbl_Services]", "[ServicesID] = '" & Me.OpenArgs & "' And [ThemeID] = " & varThemeID))



ck1999

 

[Fattire]

Changed the Nz statement, thanks RivetHead.

Tried it CK1999

.....no luck.....