Query to retrieve RefNumber from current record NOT last saved record

[ollyb303]

Hi,

I have a problem I need some help with.

In my Access 2000 database (used for logging complaints to my company), I'm using the following code to send an email to our finance dept when a refund is requested:

Private Sub Save_Record_Click()
On Error GoTo Err_Save_Record_Click
 
Dim strTo As String
Dim strCC As String
Dim strSubject As String
Dim strBody As String
Dim FirstFile As String
 
 
    If RefundStatus = "sent" Then
        MsgBox "THIS REFUND HAS ALREADY BEEN SENT - LOG NOT SAVED"
        Exit Sub
    Else
        End If
 
    If Confirmation = -1 Then
        strCC = "[e-mail]"
    Else:
        strCC = ""
    End If
        strTo = "[e-mail]"
        strSubject = "Refund"
        strBody = "Refund for authorisation"
        FirstFile = "h:\financereport.rtf"
 
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 
 
 
    If Refund_Amount > 0 Then
 
        If fIsAppRunning = False Then
            MsgBox "PLEASE LOG ON TO LOTUS NOTES BEFORE TRYING TO SEND REFUND REQUEST" & Chr$(10) & "                                              *REFUND NOT SENT*"
            Exit Sub
        Else
        End If
 
 
        DoCmd.OutputTo acOutputReport, "FinanceReport", acFormatRTF, "H:\financereport.rtf"
 
        SendNotesMail strTo, strCC, strSubject, strBody, FirstFile
        RefundStatus.SetFocus
        RefundStatus.Text = "sent"
        DoCmd.GoToRecord , , acNext
        MsgBox "REFUND REQUEST HAS BEEN SENT TO FINANCE" & Chr$(10) & "              * LOG SAVED, THANK YOU *"

The finance report is created using a query where the details are retrieved using "MaxOfRefNumber" where RefNumber is an autonumber. This is causing problems when a second user starts logging a complaint after the first user, but saves before the first does, or when a user starts logging a complaint, partially completes the form, then has to log a second call and go back the original record. In these cases the details from the wrong record are being retrieved for the finance report.

Crunch time: Is there a way I can retrieve the details for the CURRENT record, rather than the record with the highest reference number?

Any help gratefully received.

Many thanks,

Olly

[Rabbit]

Rather than using the max reference number, have the query/report use the current reference number from your form. It can be called using the format Forms!FormName!ControlName

[ollyb303]

Thanks Rabbit,

Where do I do this? I'm not used to referencing forms in queries/reports.

Cheers,

Olly

[ollyb303]

In fact, don't worry I have worked out myself that I can create a new report and use this syntax directly on the report (which makes my query redundant!)

Thanks for your help rabbit, much appreciated!

[Rabbit]

Not a problem, good luck.