On Form_Load() check if select query is null

L

Leslie

I have a form based on a select query using two tables. There are three
field in each table that must match or else I would like to send the user a
message. I am confussed on the sql part.

Private Sub Form_Load(Cancel As Integer)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim lngRecords As Long

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT ALLPolicies.CustomerNo,
ALLPolicies.Name, ALLPolicies.Carrier, ALLPolicies.EffDate,
ALLPolicies.AnnualPremium, ALLPolicies.PolicyNo, ALLPolicies.Agent
FROM WorkTable INNER JOIN ALLPolicies ON (WorkTable.CustomerNo =
ALLPolicies.CustomerNo) AND (WorkTable.ApplicationNo =
ALLPolicies.ApplicationNo) AND (WorkTable.Carrier = ALLPolicies.Carrier);

lngRecords = rst.Fields
If lngRecords = 0 Then
MsgBox "No records for this customer; application; carrier exist, please
try again!."
End If
End Sub


How can I use the count on the sql stmt???
 
O

Ofer Cohen

You can use the EOF (End Of File) to check if the recordset doesn't return
any record

If rst.Eof Then
MsgBox "No records for this customer; application; carrier exist, please
try again!."
End If

But I don't understand how the output from the recordset going to be
different, you are checking it on the form load event.
 
L

Leslie

Here is my code but there is something in the sql wrong that I'm not seeing.


Private Sub Form_Open(Cancel As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT ALLPolicies.CustomerNo,
ALLPolicies.Name, " & _
"ALLPolicies.Carrier, ALLPolicies.EffDate, ALLPolicies.AnnualPremium, "
& _
"ALLPolicies.PolicyNo, ALLPolicies.Agent FROM WorkTable " & _
"INNER JOIN ALLPolicies ON (WorkTable.CustomerNo =
ALLPolicies.CustomerNo) " & _
"AND (WorkTable.ApplicationNo = ALLPolicies.ApplicationNo) " & _
"AND (WorkTable.Carrier = ALLPolicies.Carrier);"

If rst.EOF Then
MsgBox "You have entered either the wrong Customer Number OR
Application OR Carrier, Please Try Again!"
End If

rst.Close

End If

End Sub
 
L

Leslie

I figured it out...it need the closing ).

Thank you for your help on the EOF...it worked great.
 
Top