Type Mismatch - SQL Syntax in VBA Code - UGH!!

[cariengon]

I'm using the SQL from a query that is the query that feeds the form where this VBA code is stored. With the help of JeremyNYC, I built a recordset query and I'm attempting to mirror the SQL from the original query, so I'm sure to get the same results as what has been pulled in the recordset that populates the form. I can't get past the strSQL statement that I built due to a Type Mismatch. After researching and trying different ways of adjusting the syntax, I just can't seem to get it right. I think the problem lies in the [BU] and [Trans Amt], as both of these are numeric fields. The rest are all text.

Would someone review my Code? One set of eyes looking at the same thing can sometimes drive you nuts!! And if also, tell me if the rest of the code is on the right track?

Here's my code:

****************************
'Remind the user to enter the JE detail if is a JDE Journal Entry clearing item.
'Must be a loop to check all records...

If Me.chk_JDE_JE = True Then

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb
strSQL = "Select [100_MAIN_tbl].[Status],[100_MAIN_tbl].[Comment] FROM [100_MAIN_tbl] WHERE " & _
"((([100_MAIN_tbl].[BU])=([Forms].[5000_ManualSettle_Select_frm].[txtBU]) Or" & _
"Nz([Forms].[5000_ManualSettle_Select_frm].[txtBU],'""') Is Not Null) And" & _
"((([100_MAIN_tbl].[Contract ID]) Like '" * "' & [Forms].[5000_ManualSettle_Select_frm].[txtContractID] & '" * "') Or" & _
"Nz([Forms].[5000_ManualSettle_Select_frm].[txtContractID],'""')='""') And" & _
"((([100_MAIN_tbl].[PO Num]) Like '" * "' & [Forms].[5000_ManualSettle_Select_frm].[txtPONum] & '" * "') Or" & _
"Nz([Forms].[5000_ManualSettle_Select_frm].[txtPONum],'""')='""') And" & _
"((([100_MAIN_tbl].[Trans Key]) Like '" * "' & [Forms].[5000_ManualSettle_Select_frm].[txtTransKey] & '" * "') Or" & _
"Nz([Forms].[5000_ManualSettle_Select_frm].[txtTransKey],'""')='""') And" & _
"((([100_MAIN_tbl].[Pmt Ref Num]) Like '" * "' & [Forms].[5000_ManualSettle_Select_frm].[txtPmtRefNum] & '" * "') Or" & _
"Nz([Forms].[5000_ManualSettle_Select_frm].[txtPmtRefNum],'""')='""') And" & _
"((([100_MAIN_tbl].[Inv Num]) Like '" * "' & [Forms].[5000_ManualSettle_Select_frm].[txtInvNum] & '" * "') Or" & _
"Nz([Forms].[5000_ManualSettle_Select_frm].[txtInvNum],'""')='""') And" & _
"(((Abs([100_MAIN_tbl].[Trans Amt])) Like '" * "' & [Forms].[5000_ManualSettle_Select_frm].[txtAbsAmt] & '" * "') Or" & _
"Nz([Forms].[5000_ManualSettle_Select_frm].[txtAbsAmt],'""')='""'));"
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
Do Until rst.EOF
If rst![Status] = "JDE JE Adj" And _
IsNull(rst![Comment]) Or _
rst![Comment] = "" Then
MsgBox "You did not enter any 'Details' regarding the JD Edwards Journal Entry." & vbCrLf & vbCrLf & _
"You cannot save these changes until you document the JD Edwards Journal Entry detail.", vbOKOnly, "No Comments?"
Exit Sub
End If
Loop

rst.Close
Set rst = Nothing
db.Close
Set db = Nothing

End If

********************

Thanks so much!
Carie

 

[cariengon]

I think I solved part of my problem using a rst.RecordSetClone. This duplicated the recordset that feeds the form...

Now, I'm stuck in the loop! I added a rst.MoveNext, but it doesn't recognize the "Comments made" in the comment field unless I completely move to a different record.

What do I add to my code that would commit the changes to the record without forcing the user to 'click' into a different record. Plus, I don't see how this would work if you only had one record returned....

Here's my code:

If Me.chk_JDE_JE = True Then
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
Do Until rst.EOF
If IsNull(rst![Comment]) Or _
rst![Comment] = "" And _
rst![Status] = "JDE JE Adj" Then
MsgBox "You did not enter any 'Details' regarding the JD Edwards Journal Entry." & vbCrLf & vbCrLf & _
"You cannot save these changes until you document the JD Edwards Journal Entry detail.", vbOKOnly, "No Comments?"
Exit Sub
End If
rst.MoveNext
Loop
rst.Close
'Set rst = Nothing 'removed because cleared out the rst during testing...
End If

 

[cariengon]

NEVERMIND - I finally got it to work!!

I simply used Me.Refresh at the beginning of my code. This took the current record out of Edit mode!

Whew!!

Thanks anyway...
Carie