Noob To VB

  • Thread starter Thread starter JBurlison
  • Start date Start date
J

JBurlison

Ok im Reallllllllllyyyyy new to programming in general and in trying to get a
search working that as 7 different criteria on a form, all criteria are
selected via dropdown and are unbound. i want to open a multiple items form
based on a query that i made and apply the 7 criteria and filter the form
from there i would like the ability to print the search Via a report i have
made. now thusfar i have this and it dose not work at all and i am really
need help lol:


Private Sub Command28_Click()

DoCmd.OpenForm "Main Inventory Advanced Search"

If IsNull([Forms]![Advanced Inventorysearch Beta]![User]) Or "" Then


Else

DoCmd.ApplyFilter 'cannot figure this function out for the life of me.

End If



End Sub

Now the form i want to apply the filter to is "Advanced inventory Search"
and the form the Critera is on is "Advanced Inventorysearch Beta". tried
doint this via macro but someone told me i cant because i would not be able
to print the results on the report i want but even after i figure out the
whole "apply Filter" thing with multiple criteria couldent i just point the
reports fields to this form or would i have to save the search as a quary
somehow?
 
Here is an example from one of my apps:

Private Function SetFilters() As String
Dim strFilter As String 'Used by Form Filtering procedures

On Error GoTo SetFilters_Error

With Me

'Build The Filter String
If .cboPriority <> "(All)" Then
strFilter = "[InitPriority] = " & .cboPriority
End If

If .cboOrigDate <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([OrigReleaseTarget],
""yyyy-mm"") = """ & _
.cboOrigDate & """"
End If

If .cboCurrDate <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([CurrentReleaseTarget],
""yyyy-mm"") = """ & _
.cboCurrDate & """"
End If

If .cboInitStatus <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitStatus] = " & .cboInitStatus
End If

If .cboInitType <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitType] = " & .cboInitType
End If

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[CcID] = " & Me.cboCenter

If Len(strFilter) > 0 Then
.subInitiative.Form.Filter = strFilter
.subInitiative.Form.FilterOn = True
.subInitiative.Form.Requery
Else
.subInitiative.Form.FilterOn = False
End If

End With 'Me

SetFilters = strFilter

SetFilters_Exit:
On Error GoTo 0
Exit Function

SetFilters_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure SetFilters of VBA Document Form_frmStartForm"
GoTo SetFilters_Exit

End Function

*****************
Here is the AddAnd function it calls:

Private Function AddAnd(strFilterString) As String
On Error GoTo AddAnd_Error

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If


AddAnd_Exit:

Exit Function
On Error GoTo 0

AddAnd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure AddAnd of VBA Document Form_frmStartForm"
GoTo AddAnd_Exit

End Function

*****************
Here is an example of a rowsource for a combo that includes the (All) option:

SELECT 0 As ID, "(All)" As Dummy FROM dbo_Initiative UNION SELECT StatusID,
StatusDescr FROM dbo_ProjectStatus;

Now, the only other thing is that in the After Update event of the combos:

=SetFilters()

Oh, you may want to clear the filters with a command button:

Private Sub cmdClearFilters_Click()
With Me
.cboPriority = "(All)"
.cboOrigDate = "(All)"
.cboCurrDate = "(All)"
.cboInitStatus = 0
.cboInitType = 0
.cboCenter = DLookup("[DefaultCenter]", "tblClientVersion")
.subInitiative.Form.FilterOn = False
.subInitiative.Form.Requery
End With

End Sub

--
Dave Hargis, Microsoft Access MVP


JBurlison said:
Ok im Reallllllllllyyyyy new to programming in general and in trying to get a
search working that as 7 different criteria on a form, all criteria are
selected via dropdown and are unbound. i want to open a multiple items form
based on a query that i made and apply the 7 criteria and filter the form
from there i would like the ability to print the search Via a report i have
made. now thusfar i have this and it dose not work at all and i am really
need help lol:


Private Sub Command28_Click()

DoCmd.OpenForm "Main Inventory Advanced Search"

If IsNull([Forms]![Advanced Inventorysearch Beta]![User]) Or "" Then


Else

DoCmd.ApplyFilter 'cannot figure this function out for the life of me.

End If



End Sub

Now the form i want to apply the filter to is "Advanced inventory Search"
and the form the Critera is on is "Advanced Inventorysearch Beta". tried
doint this via macro but someone told me i cant because i would not be able
to print the results on the report i want but even after i figure out the
whole "apply Filter" thing with multiple criteria couldent i just point the
reports fields to this form or would i have to save the search as a quary
somehow?
Click to expand...
 
Humm i have 07' and its not letting me do strFilter = AddAnd(strFilter) and
umm i downt quite understand the "all" part of thisIf .cboOrigDate <> "(All)"
Then. what is the "all" Checking? also <> i do not know what it means lol.
and the .cboOrigDate is just pointing to your combo box. right? sorry i am
just trying to break this do to understand it better im really new to
programming.

----------------------------------------------------------------------
Dim strFilter As String 'Used by Form Filtering procedures

On Error GoTo SetFilters_Error 'this reffering to the error string at the
bottom

With Me 'no idea what this dose

'Build The Filter String
If .cboPriority <> "(All)" Then
strFilter = "[InitPriority] = " & .cboPriority 'i understand this you are
adding an argument to you strFilter variable
End If

If .cboOrigDate <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([OrigReleaseTarget],
""yyyy-mm"") = """ & _ 'this comes up red i deleted it probablty nothing i
need
..cboOrigDate & """"
End If

If .cboCurrDate <> "(All)" Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "Format([CurrentReleaseTarget],
""yyyy-mm"") = """ & _
..cboCurrDate & """"
End If

If .cboInitStatus <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitStatus] = " & .cboInitStatus
End If

If .cboInitType <> 0 Then
strFilter = AddAnd(strFilter)
strFilter = strFilter & "[InitType] = " & .cboInitType
End If

strFilter = AddAnd(strFilter)
strFilter = strFilter & "[CcID] = " & Me.cboCenter

If Len(strFilter) > 0 Then
..subInitiative.Form.Filter = strFilter 'this function dose not work at all
in 07 but this line sets the filter to the strFilter variable.
..subInitiative.Form.FilterOn = True 'turns the filter on
..subInitiative.Form.Requery 'initates the filter
Else
..subInitiative.Form.FilterOn = False
End If

End With 'Me

SetFilters = strFilter

SetFilters_Exit:
On Error GoTo 0
Exit Function

SetFilters_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure SetFilters of VBA Document Form_frmStartForm"
GoTo SetFilters_Exit
 
Back
Top