VBA record filtering and filterOn property

[Guest]

Hi all,

I wanted to add a "print form" button to a form. Basically, instead of
using a report based on the form, I just wanted to be able to print the form.
To that end, I added the following code to the click event of a command
button:
_________________

If Me.NewRecord Then
MsgBox ("You cannot print a blank record!")
Exit Sub
End If

DoCmd.ApplyFilter , "ID = " & Me.ID
DoCmd.PrintOut
Me.FilterOn = False
Me.Requery

End Sub
________________

Because I display the Access navigation buttons on my form, I had to filter
the form or it would print all the records out. I'd guess that one of the
benefits of making your own navigation buttons is that possibly that wouldn't
be neccessary, but I don't know and it doesn't matter here.

Two questions:
1. Is using that Me.FilterOn = False give me the same results as if I hit
the 'Remove Filter' button on the Filter/Sort toolbar?

2. When the form requeries, it puts the current record (the one that
printed) as the first record in the recordset such that it's 1 of x records
down by the navigation buttons. That seems a bit confusing for a user that
may be using the navigation buttons to scroll between a few records....after
the print the records have lost their relative location (or at least that one
has).

Is there a way to requery the form such that the default order is restored
and the printed record is the current record after the requery (read, instead
of being record 1 of x where x is the total, the record would be y of x where
y is the records previous relative location before the form was filtered by
the vba code)?

Tahnks for any feedback,
CW

 

[Guest]

Hi CW

1. Yes turning off the filter will show all records

2. I would store the current record position before the requery, then go to
that record afterwards, like this:

lngCurrent = Me.CurrentRecord
Me.Requery
If Not lngCurrent = Me.CurrentRecord Then
DoCmd.GoToRecord , , , lngCurrent - 1
End If

This will take you back to the record you were on before, as long as the
sort order is the same and no new records were added in the meantime. If you
think that might happen I recommend using FindRecord instead, and store the
ID field instead of the current record number.

Hope that helps some!

RealJC

 

[Guest]

Thanks JC,

I'll give that code a whirl. Since the sort order is being changed to put
the record I print in the front, I'll have to try the second suggestion.

Thanks again,
CW

 

[Guest]

Thanks again, JC,

This is the solution that I put together. I don't know if it's "good"
coding, but it seems to function as intended:

Private Sub cmdPrint_Click()
Dim intMyID As Integer
intMyID = Me.txtID

On Error GoTo cmdPrint_Click_Error

If Me.NewRecord Then
MsgBox ("You cannot print a blank record!")
Exit Sub
End If

DoCmd.ApplyFilter , "ID = " & Me.ID
DoCmd.PrintOut
Me.FilterOn = False

DoCmd.FindRecord intMyID, , , acSearchAll, , acAll, True

Exit_cmdPrint_Click:
Exit Sub

cmdPrint_Click_Error:
Call ErrorLog(Err.Description, Err.Number, Me.Name)

End Sub

Anyway, thanks for the 'heads up' on the findrecord method.

CW