Could someone help me modify this code?

[Emery]

Hi. I have an button that opens a report when clicked. Here is the code associated with the button:

Private Sub Preview_Med_Expense_rpt_Click()
DoCmd.OpenReport "rptMedExpense", acPreview, , IIf(Me.FilterOn = True, Me.Filter, "")
Me.FilterOn = False

End Sub

It works fine, but I need it to do two more things:

In the report:
Sort the records (A-Z) in the report by a field called "MedDateInitial"

In the current form:
Stay in the current record after all this is executed. Right now after I click the button, it automatically goes back to the first record. I need it to stay in the current record.

I'm hoping this will just be a couple more lines in the code. If anyone could modify the code so it will do this, I'd really appreciate it.

thanks for any help,

-Emery

 

[Kiwiman]

Howzit

You can apply sorting in the design view of your report.

Me.FilterOn = False

I believe this part is taking any filter you have on the form, so if you rem it out it should stay on the current record.

 

[Emery]

Thanks Kiwiman. That does keep it on the current record, but breaks something else. So it looks like that is a necessary piece of code.

So, is there maybe an additional piece of code that I can add after "Me.FilterOn = False" executes, that will bring me back to the previous record I was on?

thanks,

-Emery

 

[Kiwiman]

Howzit

try

Private Sub Preview_Med_Expense_rpt_Click()
Dim intRecord as integer

intRecord = Me.CurrentRecord

DoCmd.OpenReport "rptMedExpense", acPreview, , IIf(Me.FilterOn = True, Me.Filter, "")
Me.FilterOn = False

DoCmd.GoToRecord , , acGoTo, intRecord

End Sub

 

[Emery]

Thanks Kiwiman. Well, it still goes back to the first record after I hit the preview button. Any other ideas?

thanks for all your help so far.

-Emery

 

[Kiwiman]

Howzit

Try below - I got this from a MSTEF example database. I think the problem was taht when the form was filtered it only had one record. therfore went straight to the first record.

This way we record the actual Primary Key value, and tehn go back to it when finished.

Where it says yourprimaryfield - change with your controlname \ fieldname

Private Sub Preview_Med_Expense_rpt_Click()

Dim rs As Object
Dim intRecord as integer

intrecord = me.yourprimaryfield


DoCmd.OpenReport "rptMedExpense", acPreview, , IIf(Me.FilterOn = True, Me.Filter, "")
Me.FilterOn = False

Set rs = Me.Recordset.Clone
    rs.FindFirst "[YourPrimaryField] = " & intRecord
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

 

[Emery]

Thanks Kiwiman, this works like a champ. I appreciate all your help!

-Emery