Access 2003 DCount - finding prior records with like description

[keusch]

New to VBA excuse my ignorance - I've tried to solve the problem with DCount; however, this may not be correct solution - I'm open to suggestions:
Problem:
I have a form that Queries from table DATA_PERMIT sorting by WELLNAME, Agency etc. The current form only selects Agency = "ST". For reporting puporses, a display flag notes those records to be displayed in reports. A message needs remind users to uncheck prior flags for Type records "NP" or "RE". There may be many other [Type] which can have multiple displays but between NP and RE only one record can be marke with [Display]=true.


Private Sub Type_AfterUpdate()
If (DCount("[TYPE]", "DATA_PERMIT", [WELLNAME] = Me![WELLNAME] And Me![Agency] = "ST" And (Me![Type] = "NP" Or Me![Type] = "RE")) > 1) Then
MsgBox "Uncheck PRIOR RECORD Display Flag for New Permit or Refile"
Else
End If
--
The message does not appear for types other than NP or RE but the message appears anytime NP or RE is entered even if it is the only entry.
I don't know enough about VBA and can't read fast enough.
Can anyone help.
Much appreciated

 

[snyperx3]

You need quotes around your criteria

If (DCount("[TYPE]", "DATA_PERMIT", "[WELLNAME] = '" & Me![WELLNAME] & "' And [Agency] = 'ST' And ([Type] = 'NP' Or [Type] = 'RE')") > 1) Then

-Pete

 

[keusch]

Thanks Pete - but still no luck - Here's an example of the form-
When the user enters data for line 3 (if Type = RE or NP), message should generate and does not.

WELLNAME Agency Type Status Display Submit
AP 33-9-695 ST NP APP -1 7/13/2005
AP 33-9-695 ST SUN APP -1 7/15/2005
AP 33-9-695 ST RE -1
After reading your message I changed the dcount to wellname - still no luck - any suggestions?
If (DCount("[WELLNAME]", "DATA_PERMIT", "[WELLNAME] = '" & Me![WELLNAME] & "' And [Agency] = 'ST' And ([Type] = 'NP' Or [Type] = 'RE')") > 1) Then
Thanks

 

[Zion7]

DCount only returns valid values,no null values.
So even if there is a record for certain criteria, but WellName is null, DCount will not include it in the count.
Always use an obligatory field, usually Primary key,
where your guaranteed a value, regardless of the criteria.

Pete, aren't you surprised keusch's first code, even worked at all?

"...And Me![Agency] = "ST" And (Me![Type] = "NP" Or Me![Type] = "RE")) ...???

But anyhow, you definately gave him the right syntax.

Is it possible, criterion Me![WELLNAME], is not being populated?

Just a stretch,
...And [Agency] = 'ST' And [Type] <> 'SUN'....

 

[snyperx3]

Zion: Yes I am surprised that his original code worked at all.

keusch: Is there a control on your form that contains wellname? Is this a text box? If so what is the control name?

-Pete

 

[keusch]

Thanks for both your help -- Zion7, could have done without your sarcasm. Anyway, [WELLNAME] was populated, and there were no null values. The problem was that display = true was not part of the criteria. Here is the final code.
Private Sub Type_AfterUpdate()

Me.Refresh

If (DCount("[TYPE]", "DATA_PERMIT", "[WELLNAME] = '" & Me![WELLNAME] & "' And [Agency] = 'ST' AND [Display] = true And ([Type] = 'NP' Or [Type] = 'RE')") > 1) Then

MsgBox "Uncheck PRIOR RECORD Display Flag for New Permit or Refile"
Else
End If

End Sub

 

[Zion7]

keusch, no sarcasm intended. I was serious.
If your original code did work, it made me question what I knew about DCount(), that's why I wanted to run it past Pete.

Either way, I'm glad it's working!