VBA coding help & Form Linking

[rwrig]

I have a form called "Search Issues' and a Subform within call "Browse All Issues". Browse All Issues Record source is a table that contains all the data called Issues. I'm trying to create search features within "Search Issues" where I can select multiple criteria from List box and when I click Search Browse all issues filter on the criteria I selected. I currently I the following code:
Private Sub Search_Click()

On erorr GoTo errr

Me.Search.Form.RecordSource = "SELECT * From Browse_All_IssuesSubform " & BuildFilter

Me.Search.Form.Requery
Exit Sub
errr:
MsgBox Err.Description
End Sub

Private Function BuildFilter() As Variant

Dim strWhere As String

strWhere = IIf(Len(Me.AssignedTo & "") <> 0, "([AssignedTo] Like ""*" &
Me.AssignedTo & "*"") AND", "") & _

IIf(Len(Me.OpenedBy & "") <> 0, "([OpenedBy] Like ""*" & Me.OpenedBy & "*"") AND", "") & _

IIf(Len(Me.Status & "") <> 0, "([Status] Like ""*" & Me.Status & "*"") AND", "") & _

IIf(Len(Me.Category & "") <> 0, "([Category] Like ""*" & Me.Category & "*"") AND", "") & _

IIf(Len(Me.Priority & "") <> 0, "([Priority] Like ""*" & Me.Priority & "*"") AND", "") & _

IIf(Len(Me.OpenedDateFrom & "") <> 0, "([EnteredOn] >= #" & Format(Me.OpenedDateFrom, "mm/dd/yyyy") & "#) AND", "") & _

IIf(Len(Me.DueDateFrom & "") <> 0, "([EnteredOn] <= #" & Format(Me.DueDateFrom, "mm/dd/yyyy") & "#) AND", "")

If Len(strWhere & "") = 0
Then
MsgBox "No criteria", vbInformation, "Nothing to do."

Else

Me.Filter = Left(strWhere, Len(strWhere & "") - 4)

Me.FilterOn = True

Me.Requery

End If

BuildFilter = strWhere
End Function
How can I get his to work? When I run the event I get the message "Compile Error" : Method or data member not found.

 

[Cronk]

It would help if you had given a hint as to which line is giving the compile error. In the meantime, two things you might consider

Me.Filter = Left(strWhere, Len(strWhere & "") - 4)

Why do you want to filter the parent form?

Me.Search.Form.RecordSource = "SELECT * From Browse_All_IssuesSubform " & BuildFilter

Did you try putting a break point in and checking what BuildFilter actually contains? You might find a WHERE missing.

 

[rwrig]

Cronk how do I do that?

 

[BlueIshDan]

Could you please try explaining again what is is that your program is being designed to accomplish?

 

[rwrig]

BlueIshDan if you look at the snap shot I will like to use the drop down criteria to filter the records below by clicking search.

 

[BlueIshDan]

The below is a gridview subform?

 

[rwrig]

No the below is another sub form of the table

 

[BlueIshDan]

ok, could you please list the following for me.

Table being queried along with field names.
name of your controls that represent the fields
Name of subform

also whether or not you want to use LIKE '*" value "*'" OR absolute =

 

[rwrig]

I'm trying to query the Form Browse_All_Issues with record source is "Issues Table". I attached a pic of the browse_all_issues records.

I have a for Search Issues which has the list drop down buttons and the detail section is "Browse_All_Issues, I will like to use the drop down in Search Issues to Filter the Browse All Issues table.

 

[BlueIshDan]

I know this. I requested that information so that I could write you a dynamic sub or function that will update your subform's recordsource by generating a new query for its recordsource value.

 

[BlueIshDan]

also, make sure that your from & to values are date selector controls.

 

[rwrig]

So what info do you need again?

 

[BlueIshDan]

this is what i need

ok, could you please list the following for me.

**** Table being queried along with field names.
**** name of your controls that represent the fields
**** Name of subform

also whether or not you want to use LIKE '*" value "*'" OR absolute =

 

[BlueIshDan]

also if you could right click on that query design window and click SQL then send me what your current query is that would be great

 

[rwrig]

Bare with me BlueIshDan I'm a bit of a novice when it comes to access, When you say table being queried? I created a form (Browse All Issues) that takes all fields from the table (Issues) except for one (Edit Field is unique). So when you say the table being queried is it the Issues table or my Form? Preferably I will like for the form to show the queried results because the edit link steps into another form which creates the records in my table. Is it possible to filter by form?

 

[rwrig]

Name of Controls? Are you talking about the ones on my form if that's the case the names are all the same as the labels if you look at my code above.

 

[BlueIshDan]

So.
Your subform name is: Browse All Issues
Your table name is: Issues

You could also convert the form type to continuous.

With this you can add a footer and header to the form.

In the header you can put your filter selection controls.

In the detail you can add your text boxes that fill in with the field values of every record return from the forms recordsource.

In the footer you can display statistics & other required information.

 

[BlueIshDan]

Also, please supply my with a list of names of controls & table field names. It makes it a lot easier.

 

[rwrig]

So I should get rid of the additional form and make browse all issues form the main search form?

 

[BlueIshDan]

[Form Header] - Contains your current main form's controls that select filters.
- Also display column header labels above the text boxes below.
[Form Detail] - Contains text boxes with their Data-Control Source values set as a selected field.

With this you only have to work with one form but also have to generate a query through code.