DCount Expression Syntax

[burrina]

What am I missing something here?

Code: Select all

If (DCount("*", "TCheckNo", "EmployeeID=" & Me.EmployeeID & "ChkPD Is Null") > 0) Or (DCount("*", "tblMiscChecks", "EmplID=" & Me.EmployeeID & "mPaid Is Null") > 0) Then   'Can't change employee type if unpaid checks.

RESOLVED:

Thanks,

[HansV]

You need an operator between the two conditions, I assume AND:

If (DCount("*", "TCheckNo", "EmployeeID=" & Me.EmployeeID & " AND ChkPD Is Null") > 0) Or (DCount("*", "tblMiscChecks", "EmplID=" & Me.EmployeeID & " AND mPaid Is Null") > 0) Then

[burrina]

Thanks. But for some reason I am not getting the result I expect. I can still change/edit the record. If the code runs successfully? Then
Me.EmpOrCon.Enabled = False
I should not be able to change this, but I can?

tblEmployees is not linked to TCheckNo except by Master/Child on other form with a subform is why!
This code is run on frmEmployees and there is no link between tblEmployee EmployeeID and TCheckNo EmpID that equals the same EmployeeID in tblMiscChecks
Example:
tblMiscChecks EmpID FK 15
TCheckNo EmployeeID FK 8
tblEmployees EmployeeID PK 8

[HansV]

Try this:

Code: Select all

    MsgBox "First DCount = " & DCount("*", "TCheckNo", "EmployeeID=" & Me.EmployeeID & " AND ChkPD Is Null") & vbCrLf & _
        "Second DCount = " & DCount("*", "tblMiscChecks", "EmplID=" & Me.EmployeeID & " AND mPaid Is Null")
    If (DCount("*", "TCheckNo", "EmployeeID=" & Me.EmployeeID & " AND ChkPD Is Null") > 0) Or _
            (DCount("*", "tblMiscChecks", "EmplID=" & Me.EmployeeID & " AND mPaid Is Null") > 0) Then
        Me.EmpOrCon.Enabled = False
    End If

Does the message box provide a clue?

[burrina]

Hans, see my edit above and hope you can understand it.

[HansV]

I'm completely lost.

[burrina]

Here is a screenshot of the Master/Child relationship but not linked by ID in tables.

[burrina]

I think I need a where statement to link the EmployeeID in TCheckNo to the EmpID in frmMiscellaneousChecks

[HansV]

Is frmMiscellaneousChecks a separate form? Or is it a subform?

[burrina]

It is a subform on frmEmployees

[HansV]

But EmpID on frmMiscellaneousChecks is not the same as EmployeeID on frmEmployees?

[burrina]

Yes, it is. tblMiscChecks and tblEmployees are OK. TCheckNo and tblEmployees are NOT. They are linked by Master/Child ONLY via Form/Subform on another form.

[HansV]

How about

DCount("*", "TCheckNo", "EmployeeID=" & Forms!frmHourlyPerJobContractorPayChecks!EmployeeID & " AND ChkPD Is Null")

[burrina]

If DCount("*", "TCheckNo", "EmployeeID=" & [frmMiscellaneousChecks].[Form]!EmpID & " AND ChkPD Is Null") Or (DCount("*", "tblMiscChecks", "EmpID=" & Me.EmployeeID & " AND mPaid Is Null") > 0) Then

But, it does nos not work?

[HansV]

Without further information, I have no idea.

[burrina]

Thanks for trying.

[burrina]

I resolved this although not by employee but is sufficient.
Thanks,

Code: Select all

If (DCount("*", "TCheckNo", "ChkPD =0") > 0) Or (DCount("*", "tblMiscChecks", "mPaid =0") > 0) Then