Passing a parameter to a combo box.

[eckert1961]

Hello,

I currently have a student membership database with a main Members form. When it opens it prompts me for a parameter which determines if only active, inactive or all members will be displayed. Once the form opens I use a combo box to search through all of the records. Currently this combo box lists all of the members names regardless of the parameter. To address this I set the same parameter query for the combo box. This works fine but it means that I have to enter the same parameter twice. What I would like to do is to have the 1st parameter, when the form opens, pass to the combo box. Any pointers would be greatly appreciated.

Also, if anyone knows of a more elegant way to accomplish this other than using a parameter query please feel free to pass this along.

Thanks,
Chris

PS: I am running Access 2003.

 

[MStef]

Hello Chris!

Look at "DemoQryFormA2000.mdb", I think it can help you with some modifications.

 

[eckert1961]

Additional Assistance

Hello MStef,

Your attached database helped immensely. I now have a combo box named ViewActive that lets me selective Active=Yes, No or when I double-click it all records are displayed.

When I select Yes then I only see active students when I use the record navigation to scroll through the records.

I only have one item that I would like to clean up. On the same form I have another Combo Box that displays the students names. If I select the pull down list all names are displayed whether or not I've selected the Active=Yes. The following is the AfterUpdate Code that I have linked to the Members combo box.

Private Sub cboMembers_AfterUpdate()
Dim rst As Recordset
Const QUOTE = """"

Set rst = Me.RecordsetClone
rst.Sort = ("[LastName]")
rst.FindFirst "([FirstName]& "" "" & [LastName] )= " & QUOTE & Me!cboMembers & QUOTE
If rst.NoMatch Then
MsgBox "No match was found. Student not in database!"
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close
End Sub

Can you or someone else note what needs to be changed so that only the names of the Active or Inactive members are displayed in this combo box depending on what is selected from the ViewActive combo box?

Thanks,
Chris

 

[Wulf13]

Let me say...

..this forum is a wonderful place and you guys are full of awesome information. Now that I have groveled to the great gods of Access, let me get to the heart of my problem.

My database is very similiar to this one but I would like to incorporate a LIST BOX that also updates and displays only those records with the status I selected in the COMBO BOX.

Is this possible?