Dcount not working?

[csdrex87]

So i've created the checker to make sure that the user entered a name that matches with a form. The problem is that when i use the Dcount function no matter what the user enters it is considered false (0). There is a table with all of the form names in it and what Dcount is supposed to do is look in that table and if it is there return a 1 if it is not return a 0 and then goes through an if statement to set the case equal to the user access or to 5 (which is what happens if there is no form name). Here is the segment of code that has the problem.

Dim intCaseN As Integer
Dim iCheck As Long

CaseN = Forms!formUserLoginAccess!txtUSL

iCheck = DCount("[FormName]", "tblPromptForm", "FormName = '" & Me.[FormName] & "'")

If iCheck <> 0 Then
Else:
CaseN = 5
End If

MsgBox iCheck, vbOKOnly (is always 0 when i test)
MsgBox CaseN, vbOKOnly (is always 5 when i test)

 

[pbaldy]

The syntax looks correct, and the fact that it returns zero implies that the value in the textbox isn't what you expect. Have you set a breakpoint and seen what Me.[FormName] is returning as the code runs?

 

[csdrex87]

Ah! gotcha. FormName was actually returning the current Form's Name not the name in the box named FormName. I renamed the field in all of the related tables and what not and it appears to be working. Thanks for the suggestion

 

[pbaldy]

No problemo; I was thinking Access might get confused between the two. Here's a pretty comprehensive list of potential problem names:

http://allenbrowne.com/AppIssueBadWord.html