Add New Record From Form Access 2010

Question

I am using a form to create invoices ("frmInvoices"). Since people sometimes send prepayments for next month invoice, I record three fields; "Prepayment Amount", "Prepayment Month", "Prepayment Year" on "frmInvoices". I am looking to place this data on the 'tblPrapayment' table as well.

The kicker. For my system to work, I need to make sure that when the invoice form is saved and a prepayment value has been entered, that month and year are entered as well.

I have placed this code in my Microsoft Access Class Objects, not a module. I only need this to work on the "frmInvoices" form. I don't get any errors. But nothing really happens either. If you know an easier way to do this I am open to it as well.

Private Sub Add_Prepayment_Save()
DoCmd.Save ("frmInvoices")
If [Rec'd_Prepayment] = "$0.00" Then
DoCmd.Save ("frmInvoices")
End If
If [Rec'd_Prepayments] <> "$0.00" And [Prepayment_Month] = "" Or [Prepayment_Year] = "" Then
    MsgBox "Please Update Prepayment Month And/Or Prepayment Year"
End If
If [Rec'd_Prepayments] <> "0.00" And [Prepayment_Month] <> "" Or [Prepayment_Year] <> "" Then
    Dim RecSet As Recordset
    Set RecSet = CurrentDb.OpenRecordset("tblPrePayments")
    RecSet.AddNew
        RecSet![AccountID] = "AccountID"
        RecSet![Prepayment_Month] = "Billing_Month"
        RecSet![Prepayment_Year] = "Billing_Year"
        RecSet![Rec'd_Prepayment] = "Prepayment1"
    RecSet.Update
End If
End Sub

Have you confirmed whether the code runs at all? If not, set a breakpoint on the first DoCmd.Save and step through the code one line at a time with F8. — HansUp
@HansUp I don't believe the code is running. I set a breakpoint on the DoCmd.Save and tried to step through, but nothing happened. I couldn't any line to highlight yellow to step through. I tried adding in 'acForm' in DoCmd.Save acForm, "frmInvoices" and that didn't help either. — bjk
Smandoli's suggestion looks promising to me. If that's not what you're looking for, tell us about how you call Add_Prepayment_Save() ... what is supposed to cause that code to be executed? — HansUp
@HansUp I haven't tried out Smandoli's suggestion yet, but I will. I am looking for this to be executed when the form is saved. — bjk
You have some If criteria with multiple clauses. Suggest you use parenthesis to avoid unexpected order of processing for operators (And/Or). — Smandoli

Smandoli Smandoli · Accepted Answer · 2013-12-01T03:51:32

Try adding this to your form module:

Private Sub Form_AfterUpdate()
    MsgBox "Time to validate the form!"  ''reassurance, temp
    Call Add_Prepayment_Save
End Sub

You will probably want to make Add_Prepayment_Save a function that returns True or False. If the user needs to work more on the form, the function returns false and you handle it from there. I usually have to fiddle around a bit -- for example, calling your validation from Form_Close or Form_LostFocus may work better than Form_AfterUpdate().

Add New Record From Form Access 2010

1 Answers