Problem with Dlookup function used in VBA DoCmd.RunSQL method 2

[Flo79]

The DoCmd.RunSQL method below keeps returning me the error "Criteria Type Mismatch". I believe that this error can be traced to the Dlookup fnction that I use inside my SQL code. I am new to writing VBA modules in Access (and to using VBA in general. Is there an obvious mistake someone can point out? Any tips for using this kind of code in VBA?

Your help is greatly appreciated!

Rgards,

Flo 79

'My Code
-----------------------------------------------------------

On Error GoTo Delete_Categories2_Err
Dim ID_New As Long
Dim ID_Old As Long
ID_New = Me.ID_New.Value
ID_Old = Me.ID_Old.Value



DoCmd.SetWarnings False
If (Eval("'" & ID_New & "' is not null")) Then


DoCmd.RunSQL "UPDATE Item_SubCategories INNER JOIN Transactions ON Item_SubCategories.SubCategoryID = Transactions.SubCategoryID SET Transactions.SubCategoryID = '" DLookup("[SubCategoryID]", "Item_SubCategories", "[CategoryID]='" & ID_New & "' AND [SubCategoryName]='<Unfiled>'") & "'" & _
"WHERE (((Transactions.SubCategoryID)= '" & DLookup("[SubCategoryID]", "Item_SubCategories", "[CategoryID]='" & ID_Old & "' AND [SubCategoryName]='<Unfiled>'") & "')));"

End If

 

[RickSpr]

I think you just need to put an ampersand (&) before the first DLookup.

If that doesn't work, I'd suggest creating some variables to hold the DLookup'ed values, so you can get the function calls out of the SQL statement. They make it really hard to follow. If you replace them with variables, the syntax would be easy to follow, and you might just see the problem yourself.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[PHV]

And for criteria on numeric fields get rid of the single quotes surrounding the tested numeric value.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RickSpr]

Looking at it again, I realize that without the ampersand, you'll get a VBA syntax error, so the code won't even run. So I looked a little harder.

The problem is probably in the expression:
(Eval("'" & ID_New & "' is not null"))
This expression will always evaluate to True, because even if ID_New is Null, the expression being Evaluated will become
'' is not null
which is always True. Since this is so, you're always attempting the RunSQL, even if ID_New is Null, and that's probably causing your error. Change your statement to:
If Not IsNull(ID_New) Then

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Flo79]

Thank you RickSpr for your help!

I changed my code based on your suggestions. I think I am getting there. However, I am still gettin gthe message: "Invalid use of Null"

Here is my new code:

rivate Sub Command31_Click()
On Error GoTo Delete_Categories2_Err
Dim ID_New As Long
Dim ID_Old As Long
Dim Unfiled_New As Long
Dim Unfiled_Old As Long
ID_New = Me.ID_New.Value
ID_Old = Me.ID_Old.Value
Unfiled_New = DLookup("[SubCategoryID]", "Item_SubCategories", "[CategoryID]='" & ID_New & "' AND [SubCategoryName]='<Unfiled>'")
Unfiled_Old = DLookup("[SubCategoryID]", "Item_SubCategories", "[CategoryID]='" & ID_Old & "' AND [SubCategoryName]='<Unfiled>'")


DoCmd.SetWarnings False
If Not IsNull("& ID_New &") Then

' Consolidate <Unfiled>
'-----------------------------------------------------------------------------------------
DoCmd.RunSQL "UPDATE Item_SubCategories INNER JOIN Transactions ON Item_SubCategories.SubCategoryID = Transactions.SubCategoryID" & _
"SET Transactions.SubCategoryID = " & Unfiled_New & "" & _
"WHERE (((Transactions.SubCategoryID)= " & Unfiled_Old & ")));"

' Re-Classify Sub_Categories
'-----------------------------------------------------------------------------------------
DoCmd.OpenQuery "TEST_3_Change", acViewNormal, acEdit

' Delete Category
'-----------------------------------------------------------------------------------------
DoCmd.RunCommand acCmdDeleteRecord

DoCmd.Close acForm, "Classifications_Categories_Delete"
DoCmd.SelectObject acForm, "Classifications", False
DoCmd.ShowAllRecords

End If
If IsNull("& ID_New &") Then
Beep
MsgBox "Please choose a new classification for all transactions that are associated with the category you are about to delete!", vbExclamation, ""
End If


Delete_Categories2_Exit:
Exit Sub

Delete_Categories2_Err:
MsgBox Error$
Resume Delete_Categories2_Exit

End Sub

 

[RickSpr]

Once again, on looking more carefully, I see something I missed before. If ID_New is Null, you can't assign it to a Long variable (if you try, you get that message). I imagine you're getting the error on the statement
ID_New = Me.ID_New.Value

Try reorganizing your code so that you don't do anything with ID_New if it's Null. Just Beep and display the MsgBox. In other words, move the If Not IsNull(...) up after the Dim's.

And why are you using [red](" & ID_New & ")[/red]? I gave you the correct syntax, why didn't you use it? Again, this expression will never be Null, because it's a string consisting of a space, an ampersand, a space, the word 'ID_New' (not the variable and not its value, just a word), a space, an ampersand, and a space.

One more thing. I notice you're doing SetWarnings False, but you're not doing a corresponding SetWarnings True. You should turn warnings back on again, without fail, after you turn them off.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein