main form and sub form with combo box filter

[CRobledo]

I have a main form (Orchard input) with a table Sub form (orchard input form). What I would like to do is to have have a combo box in the main form that had certain criteria and based on what I select the sub form will filter to display any records that have that criteria in a specific field (UnboundOrchard) Ive tried a few things but I keep getting errors. Does anyone have any ideas on how to do this. I'm kinda new and running out of ideas.

Here is the criteria. its located in a table called Orchard index in a field called OrchardName

OrchardName
3 Bucks (JD Orchards)
Bandon Orchard
Big Red Ranch
Canyon
G3 Orchards
Graystone
Hi-Point
J&B
Predator H&H
Predator Ridge
Quarry
Red Bluff
Roll 'Em

[orange]

???? Can you try to describe this again? Forget the form and subform for the moment, just tell us in pain English or orchard talk,
what exactly are you trying to do. Describe as you would to an 8 yr old.

[CRobledo]

I would like for the combo box to be used to filter and show in the table only records for the values selected based on the unbound orchard field

[orange]

???I don't think we are communicating.
Using Form and subform
you would put a combo in the header of a form; rowsource of the combo is distinct Orchard (id or name..)
Select an orchard in the combo,
apply a filter to the recordsource of the subform,
requery the subform and only records matching your criteria should be displayed

Update: added subform to original post. It wasn't clear when re-reading my response..

[CRobledo]

how do I apply a filter to the rowsource of the form?

[orange]

In your case, it seems to me since you have the names of the orchards, you would have a combo on the mainform whose
rowsource was the names of the Orchards.

The subform contains all data from your table you need including orchard name. That is it's recordsource is - Let's assume for this post it is - SELECT * from MyOrchardTable

In the afterUpdate event of the combo (after you select the value you want) you would have code along my set up below.
The sql for your subform record source will get modified based on the combo value selected on the mainform.


This is from a sample I created for a different post which shows the general set up:

Private Sub txtFilter_AfterUpdate()
'
' This sample contain information about Animals and where they were seen (Lat/Long) and when.
' The subform is linked to mainform on AnimalID
' In my sample I used a textbox as the filter to allow entry of any part or no keyword/string by using the like operator

Dim sql As String

'This  is the sql that serves as recordsource for my subform

 sql = "SELECT aname, [AnimalLocs].[SightingDate], [AnimalLocs].[GPSLong], " _
       & "[AnimalLocs].[GPSLat] " _
       & " FROM [AnimalLocs] Inner join Animal  " _
       & " ON [AnimalLocs].[AnimalId] = Animal.animalid " _
       & " Where aname like '*" & Me.txtFilter & "*' "
 
' these next two lines are the guts of the filter, and the syntax  says
' on me (mainform) using the AnimalLocsSubform control, which is a Form, set its recordsource =
' 1 - sets the subform recordsource  
       Me.AnimalLocsSubform.Form.RecordSource = sql

' 2 - tells Access to requery the subform
      
      Me.AnimalLocsSubform.Form.Requery

End Sub

To repeat:
In your case, it seems to me since you have the names of the orchards, you would have a combo on the mainform whose
rowsource is the unique names of the Orchards.
eg: Select distinct OrchardName from myOrchardTable

The subform contains all data from your table you need including orchard name. That is it's recordsource is - Let's assume for this post it is - SELECT * from MyOrchardTable

So your
Private Sub cboFilter_AfterUpdate()
Dim sql As String
sql = "SELECT * from MyOrchardTable " _
& " where orchardname = '" & me.cboFilter &"' "

and your 2 critical statements would be similar to mine, but using your subform control name
and your name for the combo.

There is a youtube video by Steve Bishop --he shows a keyword search with Form/subform and filter.
He then requeries the sub form to use the textbox entry on the mainform as the new recordsource of the subform. You should watch this to get all the details.


Good luck with your project.

[lacigol]

Private Sub cboFilter_AfterUpdate()
Dim sql As String
sql = "SELECT * from MyOrchardTable " _
& " where orchardname = '" & me.cboFilter &"' "

Followed by:

Me.[orchard input form].RecordSource = sql

IMHO