Form creating a new record when filter fails

  • Thread starter Thread starter lantel
  • Start date Start date
L

lantel

Guys can anyone help me out with my problem:

Basically I have a form that displays records from a CUSTOMER table
based on this query:

SELECT CUSTOMER.Customer_ID, CUSTOMER.First_Name, CUSTOMER.Last_Name,
CUSTOMER.Phone_Nb, CUSTOMER.House_Nb, CUSTOMER.Apt_Nb, CUSTOMER.Street,
CUSTOMER.City, CUSTOMER.Country, CUSTOMER.Postal_Code,
CUSTOMER.Customer_Info, CUSTOMER.Status, CUSTOMER.Manager_ID
FROM CUSTOMER
WHERE CUSTOMER.Status="p";

Now if I open the form, and there are no records which have their
status fields set to "p", then the form creates a new record in the
customer table.

I want to avoid this. Is there any code I can put in on_current, or
something that when I open the form, and there are no records which
have their status fields to "p" (ie. the query returns 0 records), that
it would simply display a msgbox and quit the form?

I've been trying different things, but can't get anything that works.

Thanks guys,
appreciated.
 
How do you open the form? Is it through a command button on a
switchboard or another form? If yes then it's quite easy... precede the
DoCmd.OpenForm with something like:

If DCount("*", "CUSTOMER", "[Status] = 'p') = 0 Then
MsgBox "No customers found"
Exit Sub
End If
'then the rest as it was:
'DoCmd.OpenForm etc.
so the form won't open at all.

If you are opening the form directly (not good practice), then set the
form's Allow Additions property to No, so it won't create the new
record, and use the following code in its On Open event:

Private Sub Form_Open(Cancel As Integer)
If Me.Recordset.RecordCount = 0 Then
MsgBox "No customers found"
Cancel = True
End If
End Sub

HTH,
Nikos
 
in the Form Load event put this

if Me.NewRecord = True then
msgbox "No data"
docmd.Close
end if
 
Dennis,

This will close the form, OK, but the new record is created in the table
nonetheless, which is not desired... or, the user gets error messages if
a field won't accept a null value.

Nikos
 
Back
Top