Pls Help, Double booking problem. (1 Viewer)

[pbaldy]

The before update event of that subform, since that's where you're adding new records.

Let's ignore the >0 or >1 thing for now. You have it correct with >0.

 

[unclejoe]

Because the Dcount output value is always "-1". Meaning, the IfThen will always be True.

UncleJoe:
Why do you believe the 1st DCount() will always be > 0?

 

[pbaldy]

Because the Dcount output value is always "-1". Meaning, the IfThen will always be True.

Maybe we're not understanding what you're trying to say, but a DCount will count the records the match the criteria. If there are none, it will return 0. Otherwise it will return the number of records found. I don't think it can ever return -1. The OP is using it correctly, testing for >0. If it returns >0, then there's a duplicate to what's being tested; if not, then there is no duplicate.

 

[unclejoe]

Yes, Paul, you are right.

Accord to the code "Dcount(.....) > 0", where the OP's intentions of searching for a empty record.

But the IfThen statement reads the "Dcount" as "-1". This is where the confusion lies.

The more appropriate code would be:

Dim Cnt as Long

Cnt = DCount( "*", "Table", "WhereCondition")

If Cnt = 0 Then
'do nothing
Else
MsgBox "Dulplicate Record"
End If

Maybe we're not understanding what you're trying to say, but a DCount will count the records the match the criteria. If there are none, it will return 0. Otherwise it will return the number of records found. I don't think it can ever return -1. The OP is using it correctly, testing for >0. If it returns >0, then there's a duplicate to what's being tested; if not, then there is no duplicate.

 

[pbaldy]

I'm going to respectfully disagree. I've used tests like:

If DCount(...) > 0 Then

many times, and it works flawlessly. The DCount will not be evaluated as "-1"; it will be evaluated for whatever count it returns, and that value will be combined with the > 0 part, and THAT will be evaluated as True (-1) or False (0) for the If/Then test. In your example, there's no functional difference between

Cnt = DCount( "*", "Table", "WhereCondition")
If Cnt = 0 Then

and

If DCount( "*", "Table", "WhereCondition") = 0 Then

The results of both will be the same.

 

[unclejoe]

Yes, you are right again.

With this code "If DCount( "*", "Table", "WhereCondition") = 0 Then". You may get away with it.

But I'll never take any chances. I'll also test on my end. The "=0" did not work on my end because I have added a same record "Jeremy" with date and time matching. It skip the IfThen line.

I'm going to respectfully disagree. I've used tests like:

If DCount(...) > 0 Then

many times, and it works flawlessly. The DCount will not be evaluated as "-1"; it will be evaluated for whatever count it returns, and that value will be combined with the > 0 part, and THAT will be evaluated as True (-1) or False (0) for the If/Then test. In your example, there's no functional difference between

Cnt = DCount( "*", "Table", "WhereCondition")
If Cnt = 0 Then

and

If DCount( "*", "Table", "WhereCondition") = 0 Then

The results of both will be the same.

 

[Martin Griffiths]

well it looks like i have opened a can of worms here, but i happy to say that it now works perfectly.

my two pennenth worth on the >0 >1 issue now that i have it working in practice is quite simple.

>0 will work if i am creating a new record, but not if i am amended a record,
however the >1 will let me amend, but not let me create a duplicate..

well i say not let me.. i mean inform me that a double booking has taken place.

therefore >1 wins ( on this occasion)

i thank you all again for your help

 

[RuralGuy]

Glad you got it working Martin. Thanks for posting back with your success.

 

[unclejoe]

I was hoping that none of veterans would reply on this and that you’ll give me the input regarding the “>0” on why did you need this. I was wrong on both counts! The purpose was to check whether you understood this.

Since the outcome of the DCount is rated “Low” to “Serious”, I’ll try to explain it anyway for the benefit of others whom might encounter this.

Base on the assumption of the OP’s event for checking an existing record.

1. With a DCount(…..) > 0, the value is “1”. Therefore, the IfThen equation is “True”. A wrong message shows up. But not if there is no duplicate.

Something like this would be more appropriate.

 If DCount(…) > 0 Then
msgbox “show duplicate”
Else
‘do nothing here because no duplicate
End If

2. With a DCount(….) = 0, although the DCount value is “1”, the IfThen is now “False”. Where the OP message supposed the show a message duplicate. However, if there is no duplicate, the message will not show up. Still not what the OP intended. Although the DCount value is “0” is correct if there was no duplicate.

Something like this would be more appropriate.

 If DCount(…) = 0 Then
‘Do nothing here because no duplicate
Else
MsgBox “show duplicate”
End If

Although Paul was right, he’s getting away with it. (still confused yet?)

To clear things up, the following formula will be use to demonstrate that the Dcount became a mathematical equation.

Given: (duplicate record)
X = dcount(..) = 0
X = 1 = 0
X = False

VBA
X = If Dcount() = 0 Then
X = if 1=0 then
X = if false then

Given: (no duplicate record)
X = dcount() = 0
X = 0 = 0
X = True

VBA
X = if dcount = 0 then
X = if 0 = 0 then
X = if True Then

Because DCount is an undeclared variable in the IfThen statement, VBA evaluate it as a mathematical equation first before the IfThen.

Where as a declared variable will be evaluate as a “True” or “False” in the IfThen statement.

Example.

Dim cnt as Integer

cnt = Dcount(“*”, “Table”. “WhereCondition”)

If cnt = 0 Then
Msgbox “no duplicate exist”
else
msgbox “duplicate exist”
end if

Is there a way to proof that the “Dcount” is a Boolean variable?

Take the codes above and change the IfThen statements by inserting a “Not” in front of “Dcount” like..

If Not Dcount(..) = 0 Then

This will reversed the equation of the IfThen statement.

Or just simply remove the math equation

If Not DCount(…) Then
Or
If Dcount(..) Then

You need reverse the output value to the IfThen statement according to what you wish for.

Since both of these are Boolean variable and you’re looking for a “True” or “False” condition, there is no need for the math equation. (Note that there are certain cases where you don’t need “Not” because in a Dcount output = 0, the IfThen statement is treated as “False”. If Dcount output is 1 or more. It is “True”. Note that I’m not referring to the mathematical equation output.)

well it looks like i have opened a can of worms here, but i happy to say that it now works perfectly.
my two pennenth worth on the >0 >1 issue now that i have it working in practice is quite simple.
>0 will work if i am creating a new record, but not if i am amended a record,
however the >1 will let me amend, but not let me create a duplicate..
well i say not let me.. i mean inform me that a double booking has taken place.
therefore >1 wins ( on this occasion)
i thank you all again for your help