Filtered Contact Info report from form

[Neil Greenough]

I have a list of contacts, each falling within 6 specific 'areas' and their
respective 'neighbourhoods.' So, for example,

1 Joe Bloggs Brooklyn NewYork
2 Jeff Riggs Kansas Texas

There are only six areas though (New York and Texas being areas)

Now, from my tblContacts, I want to be able to create a form that offers me
two drop down menus, the first with the 6 specific areas listed, and the
second with the respective neighbourhoods for that area. Then, when I select
the 'go' button, I want a report to open listing the contact info for all
those who fall within that area and/or neighbourhood.

In the form, a neighbourhood does not have to be selected and all contacts
within the neighbourhoods within the area selected will be listed in the
report. However, an 'area' must be selected.

Step-by-step guide if possible please

Thanks

 

[Allen Browne]

Use the AfterUpdate event of the Area box to load the Neighbourhood's
RowSource with just places in that area. Details in:
Limit content of combo/list boxes
at:
http://www.mvps.org/access/forms/frm0028.htm

Then use the Click event of your Go button to build the WhereCondition
string. Something like this:

Private Sub cmdGo_Click()
Dim strWhere As String
Dim strMsg As String

If IsNull(Me.Area) Then
strMsg = "Select an area"
Else
If IsNull(Me.Neighbourhood) Then
strWhere = "[Neighbourhood] = """ & Me.Neighbourhood & """"
Else
strWhere = "[Area] = """ & Me.Area & """"
End If
End If
If Len(strMsg) > 0 Then
MsgBox "Select an area"
Me.Area.SetFocus
Else
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End If
End Sub


Note: If the bound column of the combos refers to a Number field, lose the
extra quotes, e.g.:
strWhere = "[Area] = " & Me.Area

 

[zhangxiaoli]

fsdgas./jf