DCount Error - Always 0 (1 Viewer)

[Jared]

I am quite sure I am missing something very basic, but I've been struggling with this all morning and am hoping I can find some help.

I have a form, frmPersonnelFlexible, for entering personnel records. It's largely based on a table called tblPersonnel, which has PersonnelID as a unique record identifier. I'm trying to write code for a textbox, txtLastName, on that form so that if a name is entered with a hyphen in it, the code will automatically enter up to three records into a separate table, tblAltNames, each connected to the current frmPersonnelFlexible's PersonnelID field.

The three records are intended to be the last name as it appears left of the hypen, the last name as it appears right of the hyphen, and the last name with a space rather than a hyphen, covering the various different ways the name might later be searched for.

I've written out all the code necessary to enter those records, as you'll see below, but I don't seem to be able to get the code working so that it checks before entering each record to make sure it doesn't already exist in the tblAltNames table.

The DCount function seems ideal for this, and I've been experimenting with it, but it appears to always equal zero (and thus triggers the code to add the record).

I've been looking over other threads, trying to be a good board denizen, not repeating answered questions and not wasting others' time, and in trying to follow the advice in several of them, I've been playing around with more basic (non-concatenated) criteria, single quotes, double quotes, use of chr(34) and a few other things this morning, but it either doesn't function or always equals zero.

What am I doing wrong?

Code:

If InStr(1, Me.txtLastName, "-") = 0 Then
Exit Sub
End If
If Me.txtLastName > A Then

TempVars.Add "strLeftName", Left(Me.txtLastName, (InStr(1, Me.txtLastName, "-") - 1))
TempVars.Add "strRightName", Right(Me.txtLastName, (Len(Me.txtLastName) - (InStr(1, Me.txtLastName, "-"))))
TempVars.Add "strHyphenLess", Replace(Me.txtLastName, "-", " ")
DoCmd.SetWarnings False

If DCount("[AltNameID]", "tblAltNames", "[PersonnelID] & [AltName] = [Forms]![frmPersonnelFlexible].[PersonnelID] & '[TempVars].[strLeftName]'") = 0 Then
DoCmd.RunSQL "INSERT INTO tblAltNames ( [PersonnelID], AltName ) SELECT [Forms]![frmPersonnelFlexible].[PersonnelID] AS Expr1, [TempVars]![strLeftName] AS Expr2;"
End If

If DCount("[AltNameID]", "tblAltNames", "[PersonnelID] & [AltName] = [Forms]![frmPersonnelFlexible].[PersonnelID] & '[TempVars].[strRightName]'") = 0 Then
DoCmd.RunSQL "INSERT INTO tblAltNames ( [PersonnelID], AltName ) SELECT [Forms]![frmPersonnelFlexible].[PersonnelID] AS Expr1, [TempVars]![strRightName] AS Expr2;"
End If

If DCount("[AltNameID]", "tblAltNames", "[PersonnelID] & [AltName] = [Forms]![frmPersonnelFlexible].[PersonnelID] & '[TempVars].[strHyphenLess]'") = 0 Then
DoCmd.RunSQL "INSERT INTO tblAltNames ( [PersonnelID], AltName ) SELECT [Forms]![frmPersonnelFlexible].[PersonnelID] AS Expr1, [TempVars]![strHyphenLess] AS Expr2;"
End If

DoCmd.SetWarnings True
TempVars.RemoveAll
End If

 

[DCrake]

If DCount("[AltNameID]", "tblAltNames", "[PersonnelID] & [AltName] = [Forms]![frmPersonnelFlexible].[PersonnelID] & '[TempVars].[strLeftName]'") = 0

If DCount("[AltNameID]", "tblAltNames", "[PersonnelID] & [AltName] = '" &[Forms]![frmPersonnelFlexible].[PersonnelID] & [TempVars].[strLeftName] & "'") = 0

Spot the difference

 

[Jared]

I do see the difference, but when I implement that code change, I get Run-time error 438: Object doesn't support this property or method.

I'm running Access 2007. Could this be a setting problem?

 

[DCrake]

I suspect it has to do the [TempVars].[strLeftName] try replaing with strLeftName

 

[boblarson]

The TempVars ALSO has to be outside of the quotes just like a form reference does.

 

[MarkK]

TempVars("strLeftName") 
' - or -
TempVars!strLeftName

 

[Jared]

DCrake - Changing the code to just strLeftName, strRightName, and strHyphenLess eliminated the runtime error, but put it back to always equalling zero.

Lagbolt - Simply using the bang instead of the period fixed it perfectly. When I was posting this, I figured it would end with a "d'oh!" moment. I owe you a hug, or possibly a beverage of your choice.

Thank all of you for your patience and help!