Mark as items as Paid

[CraigBFG]

I'm looking for the best method to mark an invoice as paid.

What I've come up with is as follows - comments?

1. Query invoices between dates
2. Form produces list of invoices with DatePaid = Null
3. Question - Are you sure you want to pay?
4. Yes - Query - Update DatePaid=Now() where InvDate = user selection.
5. Print list of invoices paid.

Voila - does this make sense??

 

[Mile-O]

Update Query:

UPDATE MyTable
SET DatePaid = Date()
WHERE DatePaid Is Null AND InvoiceDate Between StartDate() And EndDate();

If MsgBox("Update records?", vbQuestion + vbYesNo, "Update..") = vbYes Then
    With DoCmd
        .SetWarnings False
        .OpenQuery "MyUpdateQuery"
        .SetWarnings True
    End With
End If

 

[CraigBFG]

Tried to run this, think I've got something mixed up.

The message box works great, but when I run the pay query, I get an undefined function StartDate.

Is the best way to handle dates within a seperate unbound dialog form which opens the listing form/query?

I think that access can't find where the original StartDate comes from

 

[Mile-O]

That's only because I didn't know where you were getting your data range from.

i.e

[From Code Module]

UPDATE MyTable
SET DatePaid = Date()
WHERE DatePaid Is Null AND InvoiceDate Between StartDate() And EndDate();


[From Parameters]

UPDATE MyTable
SET DatePaid = Date()
WHERE DatePaid Is Null AND InvoiceDate Between [Enter Start Date] And [Enter End Date];

[From Form]

UPDATE MyTable
SET DatePaid = Date()
WHERE DatePaid Is Null AND InvoiceDate Between [Forms]![MyForm]![txtStartDate] And [Forms]![MyForm]![txtEndDate];

 

[Rich]

Don't you record monies received?

 

[CraigBFG]

As i typed it, I realised my mistake.

Between [Forms]![fPL_Pay_Date]![StartDate] And [Forms]![fPL_Pay_Date]![EndDate].

But now, to be really clever, can I include a form field within the message box?

If MsgBox("Pay these invoices - totalling [formfield123] ?", vbQuestion + vbYesNo, "Confirm Payment...") = vbYes Then
With DoCmd
.SetWarnings False
.OpenQuery "qPL_Pay"
.SetWarnings True
End With

 

[Mile-O]

If MsgBox("Pay these invoices - totalling " & [formfield123] & "?", vbQuestion + vbYesNo, "Confirm Payment...") = vbYes Then
    With DoCmd
        .SetWarnings False
        .OpenQuery "qPL_Pay"
        .SetWarnings True
    End With
End If

 

[CraigBFG]

Your a genius matey..

On final, final thing..

Is there a way that I can close the now 2 open forms after the Pay query has run

If MsgBox("Pay these invoices totalling £" & Round([GrandTotal], 2) & "?", vbQuestion + vbYesNo, "Confirm Payment...") = vbYes Then
With DoCmd
.SetWarnings False
.OpenReport "rPL_Pay_List"
.OpenReport "rPL_Remittance"
.OpenQuery "qPL_Pay"
.SetWarnings True
End With

Close form (fPL_Pay_List) - form that this runs from
Close form (fPL_Pay_Date) - form where user enters date range

 

[Mile-O]

If MsgBox("Pay these invoices totalling £" & Round(Me.[GrandTotal], 2) & "?", vbQuestion + vbYesNo, "Confirm Payment...") = vbYes Then
    With DoCmd
        .OpenReport "rPL_Pay_List"
        .OpenReport "rPL_Remittance"
        .SetWarnings False
        .OpenQuery "qPL_Pay"
        .SetWarnings True
        .Close acForm, "fPL_Pay_Date"
        .Close acForm, Me.Name
    End With
End If

Have you considered Rich's question above?