Filter records in a form based on a combo box entry

[piovesan]

Hi all;

I am really new to filters and I am not even sure if that is what I need to do here, but here it is, really simple I am sure....

I just want to be able to use a combo box as a lookup for a field (which I have done), but I only want to be able to scroll through the records on the form that meet that look up critieria.

So, I have a main form that looks like this:

SELECT DISTINCT LandParcels.MAINCLASSIFICATION, LandParcels.ASSET_ID, LandGroup_Attributes.GROUP_ID, LandParcels.ASSET_TYPE, LandParcels.PID, LandParcels.OWNER, LandParcels.ACQ_DATE, LandParcels.ACQ_TYPE, LandGroup_Attributes.LANDNAME, Parks.PARK_NAME
FROM ((LandParcels RIGHT JOIN LandGroup_Attributes ON LandParcels.GROUP_ID = LandGroup_Attributes.GROUP_ID) LEFT JOIN Parkland_Attributes ON LandParcels.GROUP_ID = Parkland_Attributes.GROUP_ID) LEFT JOIN Parks ON Parkland_Attributes.PARK_ID = Parks.PARK_ID
ORDER BY LandParcels.MAINCLASSIFICATION, LandParcels.ASSET_ID;

and the combo box look up:
SELECT [LAND QUERY].PID
FROM [LAND QUERY]
ORDER BY [LAND QUERY].PID;


This DOES look up the PID that I put in, but I can also then scroll through the over 9000 other records using the navigation buttons. I don't want to get rid of the navigation buttons, because sometimes there are two or three records with the same PID and I do want to be able to scroll through those.

Can someone help?
THanks!!
piovest

 

[PHV]

Set the Filter and FilterOn properties of the form in the AfterUpdate event procedure of the combo.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[piovesan]

This is what is in the AfterUpdate Event Procedure for the combo:

Option Compare Database

Private Sub Combo16_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PID] = '" & Me![Combo16] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I am not sure what you mean with the "Filter and Filter On" properties of the form though..... By that, do you mean the Filter field after record source in the properties? What do I put in there?

 

[nim180]

Hey piovesan how are ya....

Well if you dont want to scroll through the whole 9000 records why dont you use a textbox instead and then use that textbox to populate a listbox with all the info on your PID. Also if there is alot of Duplications of PID appearing in the combobox then follow this thread to remove them from the combobox that should cut your 9000 records down.

thread702-1464486

If you want to use a textbox instead of a combobox just say so and ill run you through the steps.

Nim

 

[PHV]

I suggested this:

Private Sub Combo16_AfterUpdate()
    Me.Filter = "[PID] = '" & Me![Combo16] & "'"
    Me.FilterOn = True
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[piovesan]

Thank You PHV!! This works! Sorry, I am new with codes and wasn't sure how to do what you originally suggested, so thanks for being patient!
piovesan