On Form_Load() check if select query is null

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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???
 
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.
 
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
 
I figured it out...it need the closing ).

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