Getting filtered data displayed on Sub-Form

[CuppyCakes]

Hi

So I have never coded a day in my life up until about last week when deciding that there was no way to get around it to make the data base I am working on to function the way I want it to, so please bare with me. I'm Uber Noob.

Pretty much what I have so far is my form with all of my unbound text boxes where I want to input the data that I want to filter my results by. And originally just had a couple like functions and a query that a ran and produced the data on the sub-form below the unbound text boxes. But you have to have each of the different criteria (unbound txt boxes) filled out for that to work and I don't want the user to have to enter in all of them if they don't want to. So, trying to make a long story short, I attempted to write a code that would filter my data for me and still produce said data on the sub-form. But not only does my code not do anything (I'm a lame code writer) but I don't know where to begin to make my data appear on my sub-form.

I am officially lost and have found these forums amazingly helpful and thought maybe someone might know how to help :/

Here is my code (thought this might be useful lol)( yes, that is actually what I named my btn)

Private Sub btnVBAttempt_Click()
Public Function getWhere() As String
Dim strProject As String
Dim strAssignment As String
Dim strWorkWeek As String
Dim frm As Access.Form
Set frm = Forms("frmSearch")

'get Project
If Not Trim(frm.txtProject & " ") = "" Then
strProject = "([Project] = '" & frm.txtProject & "') OR "
End If

'get Assignment
If Not Trim(frm.txtAssignment & " ") = "" Then
strAssignment = "([Assignment] = '" & frm.txtAssignment & "') OR "
End If

'getWorkWeek
If IsWorkWeek(frm.txtMinWorkWeek) And IsWorkWeek(frm.txtMaxWorkWeek) Then
strWorkWeek = "([WorkWeek] Between #" & frm.txtMinWorkWeek & "# AND #" & frm.txtMaxWorkWeek & "#) OR "
End If

'Add together
getWhere = strProject & strAssignment & strCompleted & strWorkWeek

'Remove the hanging OR
getWhere = Left(getWhere, Len(getWhere) - 4)
End Function
End Sub

pretty much any advice at this point would be useful.

Thanks!

CuppyCakes

 

[dhookom]

I'm not sure why you have a function wrapped in a sub. I usually create a sub or function to build a where string like:

Public Function getWhere() As String
  Dim strWhere as String

  Dim frm As Access.Form
  Set frm = Forms("frmSearch")

  strWhere = "1=1 "
  'get Project
  If Not IsNull(frm.txtProject)  Then
    strWhere = StrWhere & " AND [Project] = '" & frm.txtProject & "' "
  End If

  'get Assignment
  If Not IsNull(frm.txtAssignment) Then
    strWhere = strWhere & " AND [Assignment] = '" & frm.txtAssignment & "' "
  End If

  'getWorkWeek
  If Not IsNull(frm.txtMinWorkWeek) THen 
    strWhere = strWhere &  " And [WorkWeek] >= #" & frm.txtMinWorkWeek & "# "
  End If

  If Not IsNull(frm.txtMaxWorkWeek) Then
    strWhere = strWhere & " AND [WorkWeek] <= #" & frm.txtMaxWorkWeek & "# " 
  End IF

  getWhere = strWhere
End Function

Duane
Hook'D on Access
MS Access MVP

 

[CuppyCakes]

Well I thought I needed it inside my OnClick action to make it do it when I clicked. But again, I have no idea what I am doing.

When I run this function I am getting an 'application-defined or object-defined error' on...

strWhere = StrWhere & " AND [Project] = '" & frm.txtProject & "' "

 

[CuppyCakes]

Well I thought I needed it inside my OnClick action to make it do it when I clicked. But again, I have no idea what I am doing.

When I run this function I am getting an 'application-defined or object-defined error' on...

strWhere = StrWhere & " AND [Project] = '" & frm.txtProject & "' "

Thank you so much!
CuppyCakes

 

[dhookom]

Do you have a text box named txtProjects?
You need to understand at least the basics and post back what your environment is.



Duane
Hook'D on Access
MS Access MVP

 

[CuppyCakes]

Of course I have a text box named txtProject. On my form (frmSearch) in MS Access.

I do understand the BASICS... but that is it. Hence why I am looking for help. And you are probably going to throw the towel in with this one but... I'm not 100% sure what you are asking for 'environment'.

MS Access is what I am trying to get it to work in. And when I am in VB I am just running the code as a module and debugging it (attempting, unsuccessfully obviously).

Sorry :/ I'm sure that's not the info you were looking for.
CC

 

[dhookom]

Apparently you are attempting to set a filter on a subform based on the controls that a user has entered a value into. If this is the case, you would probably have a button (maybe btnVBAttempt) that you could call the code:

Private Sub btnVBAttempt_Click()
    Me.[Your Subform control].Form.Filter  = _
      getWhere()
    Me.[Your Subform control].Form.FilterOn = True
End If

Duane
Hook'D on Access
MS Access MVP

 

[CuppyCakes]

You are awesome! Thank you!

It is officially limping along. Now my only problem is when I go to filter it, and have entered a project name to filter by in the txtproject box, it throws a window asking me to enter a parameter value for Project instead of pulling it from the txt box. But I think I can play around with the code a little bit and straighten it out.

Thank you again so much for your help!


CC