Here is what I have for each combobox: - Private Sub CmbLineSearch_AfterUpdate()
-
-
DoCmd.ApplyFilter , "[LineNoID] = " & Str(Nz([Screen].[ActiveControl], 0))
-
-
End Sub
- Private Sub cmbSearchDate_AfterUpdate()
-
-
DoCmd.ApplyFilter , "[LineDate] = " & "#" & Format([Screen].[ActiveControl], "mm/dd/yyyy") & "#"
-
-
End Sub
- Private Sub cmbShiftSearch_AfterUpdate()
-
-
DoCmd.ApplyFilter , "[Shift] = " & Str(Nz([Screen].[ActiveControl], 0))
-
-
End Sub
How would I do it so I can filter using any combination of the three or two or just the one using a command button?
I found this on the net and modified for my needs but i get an error: - Private Sub ApplyFilter_Click()
-
-
Dim strFilter As String
-
-
strFilter = ""
-
-
' see if there is data in combo box CmbLineSearch, if so add it to the filter
-
If Me!cmbSearchDate & vbNullStr <> vbNullStr Then
-
strFilter = strFilter & " AND [LineDate] = " & Me.cmbSearchDate
-
End If
-
-
If Me!CmbLineSearch & vbNullStr <> vbNullStr Then
-
-
strFilter = strFilter & " AND [LineNoID] = #" & Me.CmbLineSearch & "#"
-
-
End If
-
-
If Me!cmbShiftSearch & vbNullStr <> vbNullStr Then
-
-
strFilter = strFilter & " AND [Shift] = #" & Me.cmbShiftSearch & "#"
-
-
End If
-
-
If strFilter <> "" Then
-
-
' trim off leading "AND"
-
-
Me.Filter = Mid(strFilter, 4)
-
Me.FilterOn = True
-
-
Else
-
-
Me.Filter = ""
-
Me.FilterOn = False
-
-
End If
-
-
End Sub
The Error is Variable not defined
It highlights vbNullstr
The site I got it from did not have it called out so I have no clue what it's for or if I even need it.
Thanks for any and all advise
I know the problem - this is the challenge of working long distance.
Here (notice lines 13 and 20): - Private Sub ApplyFilter_Click()
-
Dim strFilter As String
-
-
strFilter = ""
-
If Not IsNull(Me.cmbSearchDate) Then
-
strFilter = _
-
" AND [LineDate] = " & _
-
"#" & Format(Me.cmbSearchDate, "yyyy-mm-dd") & "#"
-
End If
-
-
If Not IsNull(Me.CmbLineSearch) Then
-
strFilter = _
-
strFilter & _
-
" AND [LineNoID] = " & _
-
Me.CmbLineSearch
-
End If
-
-
If Not IsNull(Me.cmbShiftSearch) Then
-
strFilter = _
-
strFilter & _
-
" AND [Shift] = " & _
-
Me.cmbShiftSearch
-
End If
-
-
Debug.Print Mid(strFilter, 6)
-
-
With Me.Form
-
.Filter = Mid(strFilter, 6)
-
.FilterOn = (Not strFilter = "")
-
End With
-
-
End Sub
13 2091
DJ,
This is another case of code (at least the source where you got it) over complexifying things just to make it less simple.
Your If...Thens should be: - If Not IsNull(Me!cmbSearchDate) Then
And so on. This could be tricky, because it looks like you are using a combo box, and many times combo boxes have a default value of 0 (at least if you build them that way) and it looks even more like you have very strange values being assigned to Combo Boxes (dates, etc.). Combo boxes usually refer to an index, which also make creating filters much easier.
I think this will get you started in the right direction, but there are certainly more things that may need to be addressed than just this solution.
Hope this hepps.
These comboboxes are unbound and is only pulling data from what is entered. I have used the individual filters with no issues at this point, but you never know. Thanks for the direction, I will try this as soon as I can, if I have have any questions I will let you know.
DJRhino1175:
These comboboxes are unbound and is only pulling data from what is entered.
Got it--but from a design perspective, I think you may be missing something with how these combo boxes get their data. This is an aside issue, but one of hte goals of this forum is not "just give solutions" but to guide toward better design. Hope that makes sense.
- Private Sub ApplyFilter_Click()
-
-
If Not IsNull(Me!cmbSearchDate) Then
-
DoCmd.ApplyFilter , "[LineDate] = " & "#" & Format([Screen].[ActiveControl], "mm/dd/yyyy") & "#"
-
End If
-
-
If Not IsNull(Me!CmbLineSearch) Then
-
DoCmd.ApplyFilter , "[LineNoID] = " & Str(Nz([Screen].[ActiveControl], 0))
-
End If
-
-
If Not IsNull(Me!cmbShiftSearch) Then
-
DoCmd.ApplyFilter , "[Shift] = " & Str(Nz([Screen].[ActiveControl], 0))
-
-
End If
-
-
End Sub
It throughs an error on the apply filter section. I know that code works as I was able to do them individually. So I think I have it structured wrong.
I " ' " the code for the comboboxes to perform them as a group per the above code.
The error I get is Run-time error 438 "Object doesn't support this property or method."
Twinny, I understand what your saying. I followed a procedure posted by microsoft on how to filter per a combobox. - SELECT [QryLine Calc].LineDate FROM [QryLine Calc] GROUP BY [QryLine Calc].LineDate ORDER BY [QryLine Calc].LineDate;
Here is what I have for a row source for one of the combo's.
I prefer to filter a form is to use the Form's Filter and FilterOn Settings, as it is more explicit (but you could use the ApplyFilter Method as well):
This should get you more closer: - Private Sub ApplyFilter_Click()
-
Dim strFilter As String
-
-
strFilter = ""
-
If Not IsNull(Me.cmbSearchDate) Then
-
strFilter = _
-
" AND [LineDate] = " & _
-
"#" & Format(Me.cmbSearchDate, "yyyy-mm-dd") & "#"
-
End If
-
-
If Not IsNull(Me.CmbLineSearch) Then
-
strFilter = _
-
" AND [LineNoID] = " & _
-
Me.CmbLineSearch
-
End If
-
-
If Not IsNull(Me.cmbShiftSearch) Then
-
strFilter = _
-
" AND [Shift] = " & _
-
Me.cmbShiftSearch
-
End If
-
-
Debug.Print Mid(strFilter, 6)
-
-
With Me.Form
-
.Filter = Mid(strFilter, 6)
-
.FilterOn = (Not strFilter = "")
-
End With
-
-
End Sub
Again, I don't know if you are dealing with strings, numbers or all dates in your Combo Boxes--So this code might not work just yet. However, since we are building a String, I've added a line to print the string in your immediate window, which will hepp in troubleshooting (always a good practice).
Hope this hepps!
Twinny,
2 combo boxes are numbers and one combo box is dates.
cmbSearchDate is a date field
cmbLinesearch is a number
cmbShiftSearch is a number - Private Sub ApplyFilter_Click()
-
-
Dim strFilter As String
-
-
If Not IsNull(Me!cmbSearchDate) Then
-
-
strFilter = " AND [LineDate] = ""#" & _
-
Format(Me.cmbSearchDate, "mm/dd/yyyy") & "#"
-
-
End If
-
-
If Not IsNull(Me.CmbLineSearch) Then
-
-
strFilter = " AND [LineNoID]" _
-
= "Me.CmbLineSearch"
-
-
End If
-
-
If Not IsNull(Me.cmbShiftSearch) Then
-
-
strFilter = " AND [Shift]" _
-
= "Me.cmbShiftSearch"
-
-
End If
-
-
Debug.Print Mid(strFilter, 6)
-
-
With Me.Form
-
.Filter = Mid(strFilter, 6)
-
.FilterOn = (Not strFilter = "")
-
End With
-
-
End Sub
Code compiles but nothing happens when I click the button. I did a compact and repair and tried again and nothing happens.
I corrected my code to include the ampersands (&)--which should be something you look for during your own troubleshooting and fixed the date format to the internationally recognized date format. It's impossible for me to troubleshoot this code from long distance. However, the quotes around the arguments should have indicated something to you when you saw it.
Please copy and paste the code above and try again.
Sorry Twinny, I do not know enough to know when something needs an & or " or ' in it to even trouble shoot for it.
The code as it is now works almost. I think it needs to be looped, but that's just a guess. If I put just the Date it finds it, if I just put the lineno I want it works and so on. But if I put all 3 in it only searches the last one.
So I'm going to search the loop procedure for something like this. I have a couple of DB's that have loops in them, so I'll look at them to see how its structured.
I found an Allen Brown example I'm going to try tomorrow. Just looking at it we are close, just need to add some and's after each string, but I'm not 100% clear on it at this point.
Here's the link: http://allenbrowne.com/ser-62code.html
I know the problem - this is the challenge of working long distance.
Here (notice lines 13 and 20): - Private Sub ApplyFilter_Click()
-
Dim strFilter As String
-
-
strFilter = ""
-
If Not IsNull(Me.cmbSearchDate) Then
-
strFilter = _
-
" AND [LineDate] = " & _
-
"#" & Format(Me.cmbSearchDate, "yyyy-mm-dd") & "#"
-
End If
-
-
If Not IsNull(Me.CmbLineSearch) Then
-
strFilter = _
-
strFilter & _
-
" AND [LineNoID] = " & _
-
Me.CmbLineSearch
-
End If
-
-
If Not IsNull(Me.cmbShiftSearch) Then
-
strFilter = _
-
strFilter & _
-
" AND [Shift] = " & _
-
Me.cmbShiftSearch
-
End If
-
-
Debug.Print Mid(strFilter, 6)
-
-
With Me.Form
-
.Filter = Mid(strFilter, 6)
-
.FilterOn = (Not strFilter = "")
-
End With
-
-
End Sub
That work perfectly. I appreciate all your help. Now I need to go debug another issue I'm having.
Thanks again you are so awesome for helping me.
Anytime, DJ!
Glad we could be of service.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: visionstate |
last post by:
Hi there,
I am working on a form that uses 3 text boxes and 3 combo boxes.
When any data is entered into any of these, I click a command button
and this requeries a sub query in the form and...
|
by: Shawn Yates |
last post by:
It has been a while since I have done anything on MS Access and I seem
to be a bit rusty. I hope someone could help me solve my issue. I have
a form that has multiple combo boxes on it. Each box...
|
by: JC21 |
last post by:
Hi guys,
I was wondering if someone could give me some insight on this. On a form I would like to have 3 combo boxes which can be used as filters. I would like the information to be displayed on a...
|
by: SPOILED36 |
last post by:
I am building a database to track attendance. I have one main form with multiple subforms. Within one of the subforms name sfrDailyAttendance, I also have cascading combo boxes (cboCategory and...
|
by: micarl |
last post by:
How would i print a report based on criteria selected from several
Combo Boxes as well as multiple Multi Select List Boxes, that are
located on the same form?
I can get one Multi List Box, just...
|
by: sarvmardan |
last post by:
how to open a report in access containing filtered data using two combo boxes on form and a button to click.
let i have two fields and other is .
two comboboxes are combo11 and combo9.
plz post...
|
by: jss787 |
last post by:
Let me start by saying this is my first database. The problem I am having is I have 7 combo boxes on a form that are dependent on each other and only two of them are diplaying unique values. The rest...
|
by: wirejp |
last post by:
I am using Microsoft Access 2010.I am trying to perform a search in a form (this form is a continuous form) using two combo boxes: a combo box called ClientID and another combo box called...
|
by: laht0028 |
last post by:
Hello,
I'm attempting to create a form where the multiple combo boxes are cleared when I proceed to the next record. I have applied the below code and it works to clear one of the combo boxes but...
|
by: rfstott |
last post by:
This database is used to track TV series that are being watched or have been watched.
I have multiple combos on a tab form to to use as filter options. After making selection in the verious combo...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |