Software

Ask something
Discussion Locked

Filtering data in Access 2000

0 helpful
2 replies

by lalitha_gs

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi,

Can anyone explain in detail, how a drop down list in access 2000 can be sorted and filtered, so that the data related to it is shown in the adjacent colums or rows?

Thank you,
Lalitha

2 replies

by sgt_shultz

well, for 125 points, here is what i offer (rdl):
(snipped from Access 2003 Help but I expect the idea to be exactly the same in Access 2000. i suggest: skim the article below, then you might want to search Help in your version of Access for Examples for Access 2000
---
Examples of synchronizing two forms programmatically
When you move between supplier records on a Suppliers form, you might want to see the products each supplier carries in a Product List form. You can do this either with a macro or with Microsoft Visual Basic code.

Using a macro

You can accomplish this by carrying out an OpenForm action in a macro that runs in response to the Supplier form's Current event. You can make the Product List form display records whose value in the SupplierID field in the underlying table matches the value in the SupplierID field on the Suppliers form. Set the action's Where Condition argument as follows:

[SupplierID]=Forms![Suppliers]![SupplierID]

by sgt_shultz

(continued from above)
Tip

If you don't always want the Product List form to open when you move between records on the Suppliers form, include a conditional expression in the macro that checks whether or not the Product List form is open.

This conditional expression uses the IsLoaded function to check whether the Product List form is open. The IsLoaded function is a sample Function procedure included with the Northwind sample database, and not a built-in Visual Basic function.

After creating the macro, set the Suppliers form's OnCurrent event property to the name of the macro.

Using Visual Basic code

When you move between supplier records on a Suppliers form, you might want to see the products each supplier carries in a Products form. In the Form_Current event procedure of the Suppliers form, set the FilterOn and Filter properties of the Products form:

Private Sub Form_Current()

' Declare and set a variable to store the WHERE
' clause that describes the records you want to
' display.
Dim strCond As String
strCond = "SupplierID = Forms!Suppliers!SupplierID"

' Use the IsLoaded function from the Northwind
' sample database to check whether the Products
' form is open, then set the properties.
If IsLoaded("Products") Then
Forms![Products].FilterOn = True
Forms![Products].Filter = strCond
End If

End Sub

This discussion is closed to new replies.