Append from an unbound form.

[jevans]

Hello everybody,

I think this is a problem that has been asked anytimes, so what's one more time. I have an unbound form named tbl_AddCustomer with fields like Customer,Telephone#, and Address. What I'm trying to do is prevent entries into the customer table if either the customer or telephone fields remain blank. I have the code to append properly, but my if statement isn't working. I think I'm having trouble with Null values. Here's the code.

If Me.Customer.Value = Null Or Me.Business_.Value = Null Then
Beep
MsgBox "One or more of the required fields is empty.", vbCritical, "ERROR"
End If

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_Customer")

With rs
.AddNew
.Fields("BusinessCustomer") = 1
.Fields("Customer") = Me.Customer.Value
.Fields("Address") = Me.Address.Value
.Fields("City") = Me.City.Value
.Fields("Province") = Me.Province.Value
.Fields("Postal Code") = Me.Postal_Code.Value
.Fields("Business#") = Me.Business_.Value
.Fields("Fax#") = Me.Fax_.Value
.Fields("Pager#") = Me.Pager_.Value
.Fields("Email") = Me.Email.Value
.Update
End With

rs.Close

End Sub

Where am I going wrong?

 

[Pat Hartman]

Why are you making all this work for yourself by using an unbound form? You could have used a bound form and just performed the null edit in the BeforeUpdate event of the form. That would have handled both additions and changes in the same piece of code. Do you also have similar code that you are using for update? You'll need the same edit there also since there is nothing that prevents someone from deleting the value of a previously populated field.

Use the IsNull() function -
If IsNull(Me.Customer) Or IsNull(Me.Business_) = Null Then
Beep
MsgBox "One or more of the required fields is empty.", vbCritical, "ERROR"
Exit Sub
End If

Get rid of the .value at the end of each field and do something (like Exit Sub) to prevent the add from happening if one of the fields is null.

Why do some of the fields end with an underscore? That is pretty strange.

 

[jevans]

I forgot all about the beforeupdate event. I agree this approach is too time consuming and I'll try your method.

Thanks Pat

 

[jevans]

Hey Pat,

I'm going to use a bound form and the BeforeUpdate Event on the addition form. What is the code to prevent records with null values in the customer and business fields (text fields) from being entered. Also, if they are null return the user to the incorrect field for correction without losing the prevoiusly entered information.

Thanks again.

 

[Pat Hartman]

If IsNull(Me.Customer) Then
Beep
MsgBox "Customer is required.", vbCritical, "ERROR"
Cancel = True
Me.Customer.Setfocus
End If

If IsNull(Me.Business) Then
Beep
MsgBox "Business is required.", vbCritical, "ERROR"
Cancel = True
Me.Business.Setfocus
End If