Add new record code

axsnub

Registered User.
Local time
Yesterday, 20:41
Joined
Apr 2, 2005
Messages
18
First off let me say there is some terrific info here on this site, great job! Also, let me state that I'm designing my first database so I'll likely be asking some really stupid questions.

I'm designing a database which will allow customer work orders to be entered. The main form is pretty big but it requires a lot of info. Eventually I want to add a search option that will allow the user to search for all records for a specific customer, or address.

Anyway, I had an Add Record button working but it was causing other problems so I had somebody help me out but now I have problems with what they added to the add record button. If some of the required fields are not filled out there is no action when the add record button is pushed. I was getting into an endless loop because the "Resume" command was in the code so I took it out. Can anybody help me with this please?

Here's the code on the Add Record button:

Private Sub addnew_Click()

On Error GoTo Err_addnew_Click

DoCmd.GoToRecord , , acNewRec

Exit_addnew_Click:

MsgBox "customer added", vbOKOnly, App.Title
Exit Sub

Err_addnew_Click:
MsgBox Err.Description
Exit_addnew_Click

End Sub
 
Make your code look as follows:
Code:
Private Sub addnew_Click()

On Error GoTo Err_addnew_Click

DoCmd.GoToRecord , , acNewRec
MsgBox "customer added", vbOKOnly, App.Title

Exit_addnew_Click:
   Exit Sub

Err_addnew_Click:
   MsgBox Err.Description
   Resume Exit_addnew_Click

End Sub
 
Thanks so much for the help RuralGuy! This did the trick but I do still get the message for a missing object even though all required fields have been entered. Is this normal for the code or am I missing something?

Thanks again for the help, I'll be calling on you all again later today. hehe

You guys rock!
 
Can you pinpoint where you are getting the "missing object" error? Is it when you push this AddNew button?
 
Sorry, I should have been clearer. It is when I push the AddNew button that I get this message. In order to be certain I have entered all required fields I have market them on the form so that I know what is needed for testing purposes. I do get the "Object Required" pop up but it is no longer an endless loop.
 
It sounds like you have some code elsewhere in the form that is constructed poorly. Do you have anything in the BeforeUpdate or AfterUpdate events of the Form?
 
RuralGuy said:
It sounds like you have some code elsewhere in the form that is constructed poorly. Do you have anything in the BeforeUpdate or AfterUpdate events of the Form?

nothing there at all. It's pretty basic seeing as I'm a basic user. Should there be something there?
 
Change this line:
MsgBox "customer added", vbOKOnly, App.Title

...to this:
MsgBox "customer added"
 
That did it RuralGuy!!! You're great!

Can I go a little further and ask if there's a simple way to have it so that if there is a required field missed, the pop-up tells the user what field it is?
 
It is all a manual process that you code into the BeforeUpdate event of the form. Here's a sample from one of my systems:


Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
'-- Make sure at least the minimum fields are completed
On Error GoTo Err_Form_BeforeUpdate

Dim MyMessage As String, ctlWithFocus As Control, Answer As Integer
MyMessage = ""

If Not Deleted Then
    If Nz(Me.optStorage, 0) = 0 Then
        MyMessage = "The Storage Requirements must be set!" & vbCrLf & vbCrLf & MyMessage
        Set ctlWithFocus = Me.optStorage
    End If
    If Me.txtUnitCase = 0 Then
        MyMessage = "The Units per case must be set!" & vbCrLf & vbCrLf & MyMessage
        Set ctlWithFocus = Me.txtUnitCase
    End If
    If (Me.cboUnits & "") = "" Then
        MyMessage = "The Measurement Unit must be set!" & vbCrLf & vbCrLf & MyMessage
        Set ctlWithFocus = Me.cboUnits
    End If
    If Me.cboCategory.Column(0) = 27 Then
        MyMessage = "The Expensing Category has not been set!" & vbCrLf & vbCrLf & MyMessage
        Set ctlWithFocus = Me.cboCategory
    End If
    If (Me.txtSupplierDesc & "") = "" Then
        MyMessage = "The Description has not been completed!" & vbCrLf & vbCrLf & MyMessage
        Set ctlWithFocus = Me.txtSupplierDesc
    End If
    If (Me.txtProdName & "") = "" Then
        MyMessage = "The Product Name has not been completed!" & vbCrLf & vbCrLf & MyMessage
        Set ctlWithFocus = Me.txtProdName
    End If
    If Len(MyMessage) <> 0 Then
        MyMessage = MyMessage & vbCrLf & vbCrLf & _
                    "Do you wish to DELETE this Product?"
        Answer = MsgBox(MyMessage, vbYesNo)
        If Answer = vbNo Then
            ctlWithFocus.SetFocus
            If (ctlWithFocus = Me.cboCategory) Or (ctlWithFocus = Me.cboUnits) Then
                ctlWithFocus.Dropdown
            End If
            Cancel = True
        Else
            Cancel = Not DeleteProduct()
        End If
    End If
Else
    Cancel = False
End If

If Not Cancel Then
    If Not IsNull(Me.OpenArgs) Then
        Forms(CallingForm).UpdateProductList
        Forms(CallingForm).Visible = True
    End If
End If

Exit_Form_BeforeUpdate:
    On Error Resume Next
    Set ctlWithFocus = Nothing
    Exit Sub

Err_Form_BeforeUpdate:
    Call LogError(Err.Number, Err.Description, "Form_Unload() in " & Me.Name)
    Resume Exit_Form_BeforeUpdate

End Sub
This showed the user everything that was missing and set the focus to one of the offending controls. There is no automatic feature in Access that will perform this function for you.
 
looks pretty complicated at first glance. Is this sort of how each one is broken down?

If Me.txtUnitCase = 0 Then
MyMessage = "The Units per case must be set!" & vbCrLf & vbCrLf & MyMessage
Set ctlWithFocus = Me.txtUnitCase
End If

"Me.txtUnitCase" is the field name?
do I have to set anything for this "vbCrLf"
"Set ctlWithFocus" sends the cursor to the offending field?
 
You've got it! The vbCrLf is a VBA constant for Carriage Return and Line Feed. It simply adds the CrLf to the message so additional text is on a different line.
 
I can't seem to get this working in my BeforeUpdate section but I'll keep working on it.
 

Users who are viewing this thread

Back
Top Bottom