Saving a Filter as a Query?

J

JBurlison

Registered User.
Local time
Today, 17:23
Joined
Mar 14, 2008
Messages
172
Saving a Filter as a Query in VB Via a button, Can it be done?




P.S. I Use 07.
 
JBurlison said:
Saving a Filter as a Query in VB Via a button, Can it be done?
Click to expand...
YES. I can be done. But I don't think you would want to code it.

If there is no other way to accomplish your goal, than you'll have to bring a heck of a lot more detail into this thread to get this done (if anybody is even willing to do it for you, that is)...
 
Ahhh Yes this is referring back to Here.

I want to set a Filter witch im still not totally clear on but getting there i understand setting a variable and whatnot this is roughly what the code looks like from what i have learned about VB thusfar. im sory if it looks bad or if there realllly obvious mistake it my first =D


Code: 
Private Sub Command28_Click()
    
Dim strFilter As String

If Len([Location] & "") = 0 Then
    Else
    strFilter = [Location]
End If

If Len([User] & "") = 0 Then
    Else
    strFilter = "[User] = " & strfilter
End If

If Len([Manufacturer] & "") = 0 Then
    Else
    strFilter = "[Manufacturer] = " & strfilter
End If

If Len([Model] & "") = 0 Then
    Else
    strFilter = "[Model] = " & strfilter
End If


If Len([Model Number] & "") = 0 Then
    Else
    strFilter = "[Model Number] = " & strfilter
End If

If Len([Anti Virus] & "") = 0 Then
    Else
    strFilter = "[Anti Virus] = " & strfilter
End If

If Len(strFilter) > 0 Then
DoCmd.ApplyFilter strFilter
Requery
DoCmd.Save acQuery, "IR"

Else

FilterOn = False
End If

DoCmd.OpenForm "Advanced Inventory Search"

This Dose not work BTW no surprise tho it is my first code its probably crude and horrid. i need help lol :D

Edit: Quick edit all the conditions are unbounded dropdowns i dont know if its relevant. and the Form is based off of a Query "Advanced Inventory Search", both being named the same One being a query the other one a view multiple items table.
 
Last edited:
I don't really understand what you're saying about the filter, but maybe you can start by reading a few of these comments:
Code: 
Private Sub Command28_Click()
    
Dim strFilter As String
  [COLOR="SeaGreen"][COLOR="Cyan"][COLOR="Indigo"]strFilter = ""[/COLOR][/COLOR][/COLOR] [color=red]<---- You may need this line to give the string variable an initial value[/color]

If Len([Location] & "") = 0 Then
    Else
    strFilter = [Location]
End If

If Len([User] & "") = 0 Then
    Else
    strFilter = "[User] = " & strfilter
End If

If Len([Manufacturer] & "") = 0 Then
    Else [color=red]<--- This is not needed in an IF statement if you're only checking one condition[/color]
    strFilter = "[Manufacturer] = " & strfilter
End If

If Len([Model] & "") = 0 Then
    Else [color=red]<--- This is not needed in an IF statement if you're only checking one condition[/color]
    strFilter = "[Model] = " & strfilter
End If


If Len([Model Number] & "") = 0 Then
    Else [color=red]<--- This is not needed in an IF statement if you're only checking one condition[/color]
    strFilter = "[Model Number] = " & strfilter
End If

If Len([Anti Virus] & "") = 0 Then
    Else
    strFilter = "[Anti Virus] = " & strfilter
End If

If Len(strFilter) > 0 Then
DoCmd.ApplyFilter strFilter
Requery
DoCmd.Save acQuery, "IR"

Else

[COLOR="Red"]Do you have an object to go with this filter command? --->[/COLOR]FilterOn = False
End If

DoCmd.OpenForm "Advanced Inventory Search"
It looks like you are trying to check for null values with this code:
Code: 
If Len([field]) & "" > 0
If that's what you're doing, change the code to this:
Code: 
If Isnull([field]
 
Last edited:
I took the other if out completely at the bottom, Should i change the Len() to


Code: 
If [Location] = "" Then
    Else
    strFilter = [Location]
End If

If [Manufacturer] = "" Then
   Else
    strFilter = "[Manufacturer] = " & strfilter
End If

or

Code: 
If IsNull([Location]) Or [Location] = "" Then
    Else
    strFilter = [Location]
End If

If IsNull([Location]) Or [Manufacturer] = "" Then
   Else
    strFilter = "[Manufacturer] = " & strfilter
End If



Code: 
Private Sub Command28_Click()
    
Dim strFilter As String

If Len([Location] & "") [COLOR="Red"]<---This will [U]always[/U] be greater than 0[/COLOR] = 0 Then
    Else
    strFilter = [Location]
End If

If Len([User] & "") [COLOR="Red"]<---This will [U]always[/U] be greater than 0[/COLOR] = 0 Then
    Else
    strFilter = "[User] = " & strfilter
End If

If Len([Manufacturer] & "") [COLOR="Red"]<---This will [U]always[/U] be greater than 0[/COLOR] = 0 Then
    Else
    strFilter = "[Manufacturer] = " & strfilter
End If

If Len([Model] & "") [COLOR="Red"]<---This will [U]always[/U] be greater than 0[/COLOR] = 0 Then
    Else
    strFilter = "[Model] = " & strfilter
End If


If Len([Model Number] & "") [COLOR="Red"]<---This will [U]always[/U] be greater than 0[/COLOR] = 0 Then
    Else
    strFilter = "[Model Number] = " & strfilter
End If

If Len([Anti Virus] & "") [COLOR="Red"]<---This will [U]always[/U] be greater than 0[/COLOR] = 0 Then
    Else
    strFilter = "[Anti Virus] = " & strfilter
End If

DoCmd.ApplyFilter strFilter
Requery
DoCmd.Save acQuery, "IR"


DoCmd.OpenForm "Advanced Inventory Search"
 
I changed my post. Please see it again for different notes...
 
Ahhhh Ic Ic so go with the IsNull, now (assuming that the filter works) saving the Filter as a Query at the end of the filter i put:

Code: 
DoCmd.ApplyFilter strFilter
Requery
DoCmd.Save acQuery, "IR"

that do command probably not right i was just winging it there lol. dose anyone know how to save it as a filter i need to use this at several different points in the database. or maybe there is a better way of going about it i don't think it can be done with macros or syntax, still very new to the programming part of access.
 
JBurlison said:
Ahhhh Ic Ic so go with the IsNull, now (assuming that the filter works) saving the Filter as a Query at the end of the filter i put:

Code: 
DoCmd.ApplyFilter strFilter
Requery
DoCmd.Save acQuery, "IR"

that do command probably not right i was just winging it there lol. dose anyone know how to save it as a filter i need to use this at several different points in the database. or maybe there is a better way of going about it i don't think it can be done with macros or syntax, still very new to the programming part of access.
Click to expand...
You are opening the "Advance Inventory Search" form after this. Is that the form you want to filter? Is that the form with the query as its source? Instead of trying to filter the form's source, which does not have the ability to hold code, why can't you just source the form with the source table of the query?? That's the best way to do it anyway...
 
yah i want to filter the advanced inventory search. and yes the query is its source. how would i go about setting that up how would i apply the filter?

Code: 
Private Sub Command28_Click()
    
Dim strFilter As String

If IsNull([Location]) Or [Location] = "" Then
    Else
    strFilter = [Location]
End If

If IsNull([Manufacturer]) Or [Manufacturer] = "" Then
   Else
    strFilter = "[Manufacturer] = " & strfilter
End If

If IsNull([User]) Or [User] = "" Then
   Else
    strFilter = "[User] = " & strfilter
End If

If IsNull([Model]) Or [Model] = "" Then
   Else
    strFilter = "[Model] = " & strfilter
End If

If IsNull([Model Number]) Or [Model Number] = "" Then
   Else
    strFilter = "[Model Number] = " & strfilter
End If

If IsNull([Anti Virus]) Or [Anti Virus] = "" Then
   Else
    strFilter = "[Anti Virus] = " & strfilter
End If

DoCmd.ApplyFilter strFilter
Requery
DoCmd.Save acQuery, "IR"


DoCmd.OpenForm "Advanced Inventory Search"
 
Just Tried the Filter umm i just realized that all the

Code: 
If IsNull([Location]) Or [Location] = "" Then
    Else
    strFilter = [Location]
End If

If IsNull([Manufacturer]) Or [Manufacturer] = "" Then
   Else
    strFilter = "[Manufacturer] = " & strfilter
End If

If IsNull([User]) Or [User] = "" Then
   Else
    strFilter = "[User] = " & strfilter
End If

If IsNull([Model]) Or [Model] = "" Then
   Else
    strFilter = "[Model] = " & strfilter
End If

If IsNull([Model Number]) Or [Model Number] = "" Then
   Else
    strFilter = "[Model Number] = " & strfilter
End If

If IsNull([Anti-Virus]) Or [Anti-Virus] = "" Then
   Else
    strFilter = "[Anti-Virus] = " & strfilter
End If

How dose the filter know what Column it is filtering? how do i fix this problem? the column names are athat of there respected string "i.e.
Code: 
 If IsNull([Anti-Virus]) Or [Anti-Virus] = "" Then
   Else
    strFilter = "[Anti-Virus] = " & strfilter
End If

So how do i fix this problem?
 
You must log in or register to reply here.

Similar threads

MattBaldry
Solved Form Filter in VBA
Replies
15
Views
879
MattBaldry
MattBaldry
C
Solved Using a query to filter a different query
Replies
7
Views
616
Gasman
Gasman
F
Solved Form with subform filtered by two cascading combo boxes and a checkbox
2
Replies
26
Views
1,480
Faoineag
F
C
Invalid use of Me.
Replies
8
Views
714
cheekybuddha
cheekybuddha
Gasman
Solved Tempvars not working in function?
Replies
5
Views
366
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom