find a match in table w/SQL

  • Thread starter Thread starter Wally
  • Start date Start date
W

Wally

Hello again y'all,
I need some help again. It's been awhile but I'm back
feeling helpless as ever.

Using Access 2000
In a forms After_Update event I need to look at the data
in another table in the same database to see if the value
just typed in a control is in that table. I have tried
several things but to no avail.

Table with data is called "Holidays"
the code is...
Dim strSQL As String
Dim rst As Recordset
Dim CountAll As Integer

Set db = CurrentDb()

strSQL = "SELECT Count(*) as CountAll from Holidays_
Where KeyDate =#" & IncreemntedDate & "#"

' If a record is found then Countall will be 1
Response = MsgBox("Found " & CountAll & " records",
vbOKOnly, "ALERT")

I have tried various statements between strSQL def and
MsgBox like... rst.Open strSQL
but nothing seems to work.
The code as above is missing any statement that runs the
query string. This is where I need help, well maybe more
but that's why I asking for help.

I have query I created in design view called "QryCount"
that maybe I could use instead of the strSQL.
This code looks like this...
SELECT Count(*) AS CountAll
FROM HOLIDAYS
WHERE (((HOLIDAYS.KeyDate)=[IncrementedDate]));

[IncrementedDate] is a Variable created in the
After_Update event procedure.

Also, there is never going to be alot of data in this
table so maybe just looping thru each record in the table
till amatch is found might be a simpler way, but I still
want to make this work for the learning experience is
invaluable. I'm rambling now.

Any direction anyone can give me would be invaluable to
my learning process.

Thanks,
Wally
 
if dcount ("*", "Holidays", "KeyDate =#" & IncreemntedDate & "#") > 0 Then
MsgBox("Found " & CountAll & " records", vbOKOnly, "ALERT")
else
MsgBox("Found No Records", vbOKOnly, "ALERT")
End if
 
Back
Top