Automatic Data Entry

[coolcatkelso]

Hiya

I have 2 forms - Workorders by Customers and Payments

The workorder by customer is a brief summery of previous workorders, contains current account balance etc

The payment form is as it is.. Contains the Amount to be paid, payment method and WorkorderID (Related)

I have a field on the Workorder by Customer form that should display the amount been paid. Before this displays any numbers, obvisouly you need to create a workorder..

Once the workorder has been created, You should see the amount due,, This works perfectly

Problem:

In order for me to display the unpaid accounts and the current account balance on the Summery page, I have to goto the payments form and enter ?0.00 as the amount paid. Here is the code behind that form

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
    If Not IsLoaded("Workorders by Customer") Then
        MsgBox "Open the Payments form using the Payments button on the Workorders by Customer form."
        Cancel = True
    End If
End Sub
Private Sub Form_Activate()
On Error GoTo Err_Form_Activate
    Me.Requery
Exit_Form_Activate:
    Exit Sub
Err_Form_Activate:
    MsgBox Err.Description
    Resume Exit_Form_Activate
End Sub
Private Sub Form_BeforeInsert(Cancel As Integer)
On Error GoTo Err_Form_BeforeInsert
    Me![PaymentDate] = Date
    Me![PaymentAmount] = [Forms]![Workorders by Customer]![Workorders by Customer Subform].Form![Amount Due]
Exit_Form_BeforeInsert:
    Exit Sub
Err_Form_BeforeInsert:
    MsgBox Err.Description
    Resume Exit_Form_BeforeInsert
End Sub

Private Sub PaymentMethodID_NotInList(NewData As String, Response As Integer)
    MsgBox "Double-click this field to add an entry to the list."
    Response = acDataErrContinue
End Sub
Private Sub PaymentMethodID_AfterUpdate()
On Error GoTo Err_PaymentMethodID_AfterUpdate
    If Not IsNull(Me![PaymentMethodID]) Then
        If Me![PaymentMethodID].Column(2) = True Then
            Me![PaymentMethod] = [Forms]![Workorders by Customer]![FirstName] & " " & [Forms]![Workorders by Customer]![LastName]
        End If
    End If
Exit_PaymentMethodID_AfterUpdate:
    Exit Sub
Err_PaymentMethodID_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_PaymentMethodID_AfterUpdate
End Sub
Private Sub PaymentMethodID_DblClick(Cancel As Integer)
On Error GoTo Err_PaymentMethodID_DblClick
    Dim lngPaymentMethodID As Long
    If IsNull(Me![PaymentMethodID]) Then
        Me![PaymentMethodID].Text = ""
    Else
        lngPaymentMethodID = Me![PaymentMethodID]
        Me![PaymentMethodID] = Null
    End If
    DoCmd.OpenForm "Payment Methods", , , , , acDialog, "GotoNew"
    Me![PaymentMethodID].Requery
    If lngPaymentMethodID <> 0 Then Me![PaymentMethodID] = lngPaymentMethodID
Exit_PaymentMethodID_DblClick:
    Exit Sub
Err_PaymentMethodID_DblClick:
    MsgBox Err.Description
    Resume Exit_PaymentMethodID_DblClick
End Sub
Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

    DoCmd.Close
Exit_Command11_Click:
    Exit Sub
Err_Command11_Click:
    MsgBox Err.Description
    Resume Exit_Command11_Click
    
End Sub

Is there a way I can do this automattically? For example

Let;s say we just created a new workorder, I click on Print Invoice and Print Workorder (Tradesman Copy) then Close the workorder... ONCLOSE something like this

Payment Form

WorkorderID: 192
PaymentMethod: Other
PaymentDate: 10 Dec 2009
PaymentAmount: ?0.00

Is this poss?

Cheers CCK
________
MOTORCYCLE TIRES

 

[MarkK]

There are bunch of ways to solve this problem. You can design the table such that the default value of the field is zero. You can run code anywhere, when the form is open, to update the value in the form...

Forms("Payments").AmountPaid = 0

But I presume you need to set this to zero to avoid an 'Invalid use of Null' error. If this is so you might also want look up the Nz() function, which makes working with Null values much easier.

 

[boblarson]

Actually, the most likely problem is that you really need an outer join to display all workorders and display those with payments (but it will also display those without as a null). If you use an Inner Join then it will only return workorders that have at least one payment.