The error is telling you that there is something wrong with the string that you are using in the dcount function.
You're missing the closing quote on the string... see my correction below.
You've stumbled upon one of my pet peeves by building the criteria string within the command -
and it's not your fault because that's how a majority of examples show how to use the command.
Instead I suggest that you build the string first and then use the string in the command. Why you might ask, because you can then check how the string is actually resolving; thus, making troubleshooting the code so much easier as most of the time the issue is with something missing or not resolving properly/as expected within your string.
So to use your code:
- Private Sub txtSubscriptionStartDate_AfterUpdate()
-
DIM strSQLCID as string
-
DIM strSQLJN as string
-
Dim lngCountCustID As Long
-
Dim lngCountJournalName Long
-
'
-
strSQLCID = "cust_id= " & Me.cust_id) > 0
-
lngCountCustID = DCount("cust_id", "Transaction", strSQLCID)
-
'> debug.print strSQLCid
-
'
-
strSQLJN = "[Journal_name]='" & Me![Journal_name] & "'") > 0
-
'> debug.print strSQLJN
-
'
-
lngCountJournalName = DCount("[Journal_name]", "Transaction",strSQLJN )
-
'
-
' If lngCountCustID > 0 And lngCountJournalName > 0 Then
-
MsgBox "Name Is Already In Database!"
-
Cancel = True
-
Forms!TransactionDetails!txtSubscriptionCustomerName.SetFocus
-
End If
-
End Sub
-
Now I used strSQLCID and strSQLJN here for clarity; however, I would normally just recycle a single string varible.
Note the '> Debug.Print.... lines. Uncomment and remove the ">" when the code is ran, you can press <Ctrl><G> and see how these strings are resolving.
I also pull the dcount() out of the IF..Then structure, once again, this is to make troubleshooting easier as now you can debug.print the value if needed to see what is happening within the count.
I know that many will complain about the extra code and processor times etc... we're no longe in the CBM-Vic20 days where 5K-RAM and a ultra-slow processor was the norm. These extra lines are hardly a blip in the processor and it is (IMHO) better to write code that is well commented and easy to troubleshoot that to compound things.
(^_^)
2
