Filter for form Access 2000

[Jan]

Hi,
There are 2 problems I am having with filtering records...one is as follows.
I really wanted to do this on my own but I can't get the code correct (been
trying for about 2 weeks).

I have a form that is opened from an input box that displays a list of
customers that have a last name that begins with a string such as Hill*.
The list shows only only a few fields and shows them in continuous form
view. The user can scroll down and select a specific Customer and from
there I want to open another form in Form view that displays all of the
customer information for the customer record that the user selects with the
mouse. I have no problem displaying one record but the user must be able
to move forward and backward in the filtered recordset beginning with the
original record selected.

In other words, I want to open a form with a filtered recordset and make the
current record from the first form the displayed record in the second form.
Below is 2 examples of VBA code I've used. The program is Access 2000 (DAO)

#1 - opens the second form with the desired filter but displays the first
record in the filtered recordset as the current record not the selected
record.
Private Sub cmdOpenCustomers_Click()
dim rst as Recordset
dim stDocName as String
dim stLinkCriteria as String

set rst = Me.RecordsetClone
stDocName = "frmCustomers"
stLinkCriteria = Me.Filter

DoCmd.OpenForm stDocName, , , stLinkCriteria
rst.FindFirst ("[CustomerID =" & Me![CustomerID])
End Sub

#2 - opens the second form and makes the selected customer the only record.
Private Sub cmdOpenCustomers_Click()
dim rst as Recordset
dim stDocName as String
dim stLinkCriteria as String

set rst = Me.RecordsetClone
stDocName = "frmCustomers"
stLinkCriteria = ("[CustomerID =" & Me![CustomerID])

DoCmd.OpenForm stDocName, , Me.Filter, stLinkCriteria
End Sub

The second form (frmCustomers) is also used independently.
All help is greatly appreciated.
Jan

 

[Erez]

hi
if i got it right you need to:

Private Sub cmdOpenCustomers_Click()

DoCmd.OpenForm "frmCustomers", , , , , , customerID
(that's sending the customerID from the list-type form as
the "OpenArgs" argument)

End Sub

Now, in the Open event of the Single form, check to see if
there's an OpenArgs value (if there isnt, the form was
opened independantly)

Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
Dim RS As DAO.Recordset
Set RS = Me.RecordsetClone
RS.FindFirst "CustomerID = " & Me.OpenArgs
If Not RS.NoMatch Then
Me.Bookmark = RS.Bookmark
End If
End If
end sub

good luck
Erez.

 

[Jan]

Hi Erez,
Thanks for your help and that works. Not exactly the way I'd like it to but
it's the first success I've had. The problem is, it opens the second form
with all records (over 4000) instead of just a small number (the original
filter). I'm opening both forms as a snapshot and there could be as many as
8 users at a time. Do you know how well this would work over a network.
Will it slow it down? Or will opening the form as a snapshot take care of
that. The users are not permitted to edit any fields except one. The plan
was to temporarily open a small form with just the matching field in it for
editing.

The other option I thought of was to use an input box to open the second
form first in datasheet view (filtered), then permit the users to jump to
form view to look at the full record. (and back to datasheet view).

What do you think?

Thanks.
Jan