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

 

[KevinC]

Hi Jan.

If I have got it right you wish to open a customer details form from a
filtered list of customers. this is the best way and neatest I have found to
do it. This presumes your primary key is a CustomerID field.

Create your two forms say frmCustomerSelect a continuos form showing a
records but just the customer name and first address line or town, make the
form pop and modal, and frmCustomerDetail a single record form showing a
customer details with the customer table as its recordsorce.

In the header of the frmCustomerSelect put your customerID field and a
textbox called txtSearch.

In the forms global decalarations section.

Public lngID as Long

In the afterupdate event of the txtSearch put something like the following
code.

Private Sub txtSearch_AfterUpdate()

Me.Filter = "CustomerName LIKE '" & txtSearch & "*'"arrResult

Me.FilterOn = True
If Me.Recordset.RecordCount <> 0 Then
Me.CustomerName.SetFocus
End If
End Sub

Typing Hill in the txtSearch box will list only companies with name
starting with Hill

In the CustomerName field textbox in the continuos detail section in the
onclick event put
Private CustomerName_Click()
lngID=me.CustomerID
Me.visible=false
End sub

Create a new module and a public function called GetCustomerID

Public Function GetCustomerID() As Long 'form returns 0 if cancelled or
not found
Const strDocName As String = "frmCustomerSelect"
DoCmd.OpenForm strDocName, , , , , acDialog
GetClientID = Forms(strDocName).lngID
DoCmd.Close acForm, strDocName
End Function

In the Load event of the frmCustomerDetail put the following
Me.filter = "CustomerID = " & GetCustomerID
Me.filterOn=True

To open and display a selected customer detail record all you have to do is
Docmd.OpenForm "frmCustomerDetail"

This automatically opens the customer selection form allows the user to
filter the list of customers and select one then continues to open the
customer detail form showing the selected customers details, very neat and
works well. You can also use the GetCustomerID function whanever you need
the user to select a customer for query's etc.

Happy coding.

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