DCount Problems

[Howlsta]

I'm trying to use the Dcount method to check records in a tblStaffModules for a particular modulecode. If the DCount is zero I want the code to run otherwise I will get it to show a msg. The code assigns a member of staff to the module they selected in the cbo. It worked fine before i put the DCount in (maybe someone can spot something untoward here). I just want to query the user if they try to assign a second member of staff to giving the module.
Any ideas?

Private Sub cmdAddStaffToMod_Click()

Dim rst As ADODB.Recordset

If IsNull(Me.CboModule) Or _
IsNull(Me.CboStaff) Then

MsgBox ("A Module and lecturer Must be Selected!"), (vbExclamation), _
("Empty Field(s)")

Else
If DCount("[ModuleCode]", "tblStaffModules", [ModuleCode] = Me.CboModule) = 0 Then


Set rst = New ADODB.Recordset

With rst
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open "Select * from tblStaffModules"
.AddNew
!StaffID = Me.CboStaff
!ModuleCode = Me.CboModule
.Update
End With

Me.lboStaffMods.Requery

End If
End If

End Sub

 

[D-Fresh]

The problem is the syntax of your dcount statement... The where clause at the end has to be in Quotes...

DCount("[ModuleCode]","tblStaffModules","[ModuleCode]='" & Me.CboModule & "'")


If CboModule is a numeric value, then just take out the two tick(') casting marks. Hope this helps.

Doug

 

[jatfill]

if ModuleCode is a numeric field, change the DCount line to read


If DCount("[ModuleCode]", "tblStaffModules", "[ModuleCode] = " & Me.CboModule) = 0

If ModuleCode is a text/alpha field, use this:

If DCount("[ModuleCode]", "tblStaffModules", "[ModuleCode] = " & "'" & Me.CboModule & "'") = 0

 

[Howlsta]

Doug and Jatfill,

Thanks for your help, it seems to work fine now.