DCOUNT query not working 1

[crisis2007]

I have 2 fields on a form ("StartDate" and "Watch" and a command button that runs code to check in a query so I can prevent a duplicate entry. It does not always work and I can not figure it out. It will work with some dates and some it will not work. Here is the code behind the command button:

Private Sub Command26_Click()

Dim stDocName As String
Dim stLinkCriteria As String
Dim numActivity As Integer

numActivity = 0
numActivity = DCount("*", "Q_ActivityDupCheck3")
If numActivity > 0 Then
MsgBox "Activity Report for date " & Me.StartDate & " Watch " & Me.Watch & " already exists."

Form![Watch] = Null
End If
End Sub

My Query is as follows:
SELECT T_Activity.ActivityDate, T_Activity.Watch
FROM T_Activity
WHERE (((T_Activity.ActivityDate)=[Forms]![F_NewDateActivityAdd]![StartDate]) AND ((T_Activity.Watch)=[Forms]![F_NewDateActivityAdd]![Watch]))
ORDER BY T_Activity.ActivityDate DESC , T_Activity.Watch;

I appreciate any help.

 

[TheAceMan1]

How are ya crisis2007 . . .

[ol][li]You don't validate that both fields have data. Nor do you validate that [blue]StartDate[/blue] is an access accepted date.[/li]
[li]Your code doesn't prevent saving a record. It just notifies the user of duplicates.[/li][/ol]
A much better place for your code would be the [blue]forms BeforeUpdate[/blue] event. Here you could use the [blue]Cancel[/blue] property to prevent saving and leave the user where they are:

[blue]    If numActivity > 0 Then
        MsgBox "Activity Report for date " & Me.StartDate & " Watch " & Me.Watch & " already exists."
        
        Form![Watch] = Null
        [purple][b]Cancel = True[/b][/purple]
     End If[/blue]

To correct your date problem, in query design view of [blue]Q_ActivityDupCheck3[/blue], change the criteria for [blue]ActivityDate[/blue] to:

[blue]Format([Forms]![F_NewDateActivityAdd]![StartDate], "yyyy, mm, dd")[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[crisis2007]

Thanks for answering! I am still having a problem with the query recognizing what is there. I changed the criteria as you suggested but still will only work on occasion with only certain dates - just as it had before your suggestion. When I remove the criteria, the query does work properly, displaying all the dates that were previously inputted.Any other suggestions? I am really stumped on this one.

 

[crisis2007]

Oh - I figured out the problem, I think.... I did not place in an input mask for my data input form. There are several workstations where data is entered. Some were entering it mm,dd,yyy and others were mm,dd,yy, hh:mm. It appears that my query would only pull out the dates with the certain mm,dd,yyyy formats and not recognize the others. I have to do further testing but so far, this seems to be my problem. But thanks TheAceMan1! I would not have looked at that had you not mentioned the formatting issue in your reply! But let me know if this does not look like my problem in your expertise.

 

[TheAceMan1]

crisis2007 . . .

Including the change in criteria I gave you:

[blue]Change: numActivity = DCount("*", "Q_ActivityDupCheck3")
To    : numActivity = DCount("[[purple][B][I]PrimaryKeyName[/I][/B][/purple]]", "Q_ActivityDupCheck3")[/blue]

Where [purple]PrimaryKeyName[/purple] is that of table [blue]T_Activity[/blue].

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[crisis2007]

Thanks! It is working fine now. I appreciate your assistance! I learned something once again.