473,320 Members | 2,048 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Popup form for filtering form

Hi All,

I am hoping someone out there will be kind enough to find out where my
code is going wrong. The current code is inefficiant but hopefully it
will convey the data I require to be filtered.

Basically I have a popup form which has 6 optional controls to filter
records in another form. The code below does not work. Can anyone
suggest some correctons or alternatives.

Thanks,

Nathan

Private Sub Search_Click()
Dim varFilter1 As String
Dim varFilter2 As String
Dim varFilter3 As String
Dim varFilter4 As String
Dim varFilter5 As String
Dim varFilter6 As String

varFilter1 = "[LoadsheetNo] = #" & Forms!frmSearch![Filter1] & "#"
varFilter2 = "[CarrierConnote] = #" & Forms!frmSearch![Filter2] & "#"
varFilter3 = "[Date] = #" & Forms!frmSearch![Filter3] & "#"
varFilter4 = "[CarrierName] = Forms!frmSearch![Filter4]"
varFilter5 = "[SendingStoreNo] = #" & Forms!frmSearch![Filter5] & "#"
varFilter6 = "[ReceivingStoreNo] = #" & Forms!frmSearch![Filter6] &
"#"

Dim strSQL As String, intCounter As Integer
'Build SQL String

If Application.CurrentProject.AllForms("frmLoadsheets Search").IsLoaded
Then
DoCmd.Close acForm, "frmLoadsheetsSearch"
DoCmd.OpenForm "frmLoadsheetsSearch", acViewNormal
For intCounter = 1 To 6
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & Chr(34) & "Filter" & intCounter & Chr(34) &
" And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Forms![frmLoadsheetsSearch].Filter = "[LoadsheetNo] = #" &
Forms!frmSearch![Filter1] & "#"
Forms![frmLoadsheetsSearch].FilterOn = True
GoTo Endcode
End If
Else

DoCmd.OpenForm "frmLoadsheetsSearch", acViewNormal

For intCounter = 1 To 6
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & Chr(34) & "Filter" & intCounter & Chr(34) & " And "

End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Forms![frmLoadsheetsSearch].Filter = strSQL
Forms![frmLoadsheetsSearch].FilterOn = True
End If
End If
Endcode:
End Sub
Nov 12 '05 #1
7 2627
Hi Nathan

To filter Date/Time fields, you need the # as the delimiter.
To filter Text fields, use " as the delimiter.
To filter Number fields, use no delimiter.

Test if each of the controls is Null before including them in the filter
string.

(Note that if you do have a field called "Date", Access may misunderstand
it, since Date in VBA refers to the system date.)

Private Sub Search_Click()
Dim strFilter As String
Dim lngLen As Long
Dim strFormName As String

If Not IsNull(Me.Filter1) Then 'Number-field example
strFilter = strFilter & "([LoadsheetNo] = " & Me.Filter1 & ") AND "
End If
If Not IsNull(Me.Filter2) Then 'Text-field example
strFilter = strFilter & "[CarrierConnote] = """ & Me.Filter2 & """)
AND "
End If
If Not IsNull(Me.Filter3) Then 'Date/Time example
strFilter = strFilter & "[Date] = #" & Format(Me.Filter3,
"mm/dd/yyyy") & "#) AND "
End If
'etc.

lngLen = Len(strFilter) - 5
If lngLen <= 0 Then
MsgBox "No criteria"
Else
strFilter = Left$(strFilter, lngLen)
strFormName = "frmLoadsheetsSearch"
DoCmd.OpenForm strFormName, acViewNormal
With Forms(strFormName)
.Filter = strFilter
.FilterOn = True
End With
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nathan Bloomfield" <na**************@hotmail.com> wrote in message
news:4b**************************@posting.google.c om...

I am hoping someone out there will be kind enough to find out where my
code is going wrong. The current code is inefficiant but hopefully it
will convey the data I require to be filtered.

Basically I have a popup form which has 6 optional controls to filter
records in another form. The code below does not work. Can anyone
suggest some correctons or alternatives.

Thanks,

Nathan

Private Sub Search_Click()
Dim varFilter1 As String
Dim varFilter2 As String
Dim varFilter3 As String
Dim varFilter4 As String
Dim varFilter5 As String
Dim varFilter6 As String

varFilter1 = "[LoadsheetNo] = #" & Forms!frmSearch![Filter1] & "#"
varFilter2 = "[CarrierConnote] = #" & Forms!frmSearch![Filter2] & "#"
varFilter3 = "[Date] = #" & Forms!frmSearch![Filter3] & "#"
varFilter4 = "[CarrierName] = Forms!frmSearch![Filter4]"
varFilter5 = "[SendingStoreNo] = #" & Forms!frmSearch![Filter5] & "#"
varFilter6 = "[ReceivingStoreNo] = #" & Forms!frmSearch![Filter6] &
"#"

Dim strSQL As String, intCounter As Integer
'Build SQL String

If Application.CurrentProject.AllForms("frmLoadsheets Search").IsLoaded
Then
DoCmd.Close acForm, "frmLoadsheetsSearch"
DoCmd.OpenForm "frmLoadsheetsSearch", acViewNormal
For intCounter = 1 To 6
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & Chr(34) & "Filter" & intCounter & Chr(34) &
" And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Forms![frmLoadsheetsSearch].Filter = "[LoadsheetNo] = #" &
Forms!frmSearch![Filter1] & "#"
Forms![frmLoadsheetsSearch].FilterOn = True
GoTo Endcode
End If
Else

DoCmd.OpenForm "frmLoadsheetsSearch", acViewNormal

For intCounter = 1 To 6
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & Chr(34) & "Filter" & intCounter & Chr(34) & " And "

End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Forms![frmLoadsheetsSearch].Filter = strSQL
Forms![frmLoadsheetsSearch].FilterOn = True
End If
End If
Endcode:
End Sub

Nov 12 '05 #2
See comments/questions throughout...

"Nathan Bloomfield" <na**************@hotmail.com> wrote in message
news:4b**************************@posting.google.c om...
Hi All, Private Sub Search_Click()
Dim varFilter1 As String
Dim varFilter2 As String
Dim varFilter3 As String
Dim varFilter4 As String
Dim varFilter5 As String
Dim varFilter6 As String

varFilter1 = "[LoadsheetNo] = #" & Forms!frmSearch![Filter1] & "#"
varFilter2 = "[CarrierConnote] = #" & Forms!frmSearch![Filter2] & "#"
varFilter3 = "[Date] = #" & Forms!frmSearch![Filter3] & "#"
varFilter4 = "[CarrierName] = Forms!frmSearch![Filter4]"
varFilter5 = "[SendingStoreNo] = #" & Forms!frmSearch![Filter5] & "#"
varFilter6 = "[ReceivingStoreNo] = #" & Forms!frmSearch![Filter6] &
"#"

You seem to be treating all but one of the above as dates, although only one
field name indicates it would be a date.
Only surround dates with #. Strings need to be enclosed with ' or ". Numbers
are not enclosed.
Dim strSQL As String, intCounter As Integer
'Build SQL String

If Application.CurrentProject.AllForms("frmLoadsheets Search").IsLoaded
Then
DoCmd.Close acForm, "frmLoadsheetsSearch"
DoCmd.OpenForm "frmLoadsheetsSearch", acViewNormal
Why close the form, then re-open it?


For intCounter = 1 To 6
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & Chr(34) & "Filter" & intCounter & Chr(34) &
" And "
End If
Next

If you are refering to a control name when using Me("Filter" & intCounter)
then you should use
Me.Controls("Filter" & intCounter). Also you should (I feel it's good
practise even if it's not required)
to include the counter or object variable in the Next statement ie. Next
intCounter
If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Forms![frmLoadsheetsSearch].Filter = "[LoadsheetNo] = #" &
Forms!frmSearch![Filter1] & "#"
Forms![frmLoadsheetsSearch].FilterOn = True
GoTo Endcode
End If
Else

Why start over? At the begining you tested to see if the form was open, then
you closed it then
reopened it. Instead of enclosing all of the above within the first If
statement, only deal with
wether or not the form is open. Once open, all the rest need only be writen
once.

DoCmd.OpenForm "frmLoadsheetsSearch", acViewNormal

For intCounter = 1 To 6
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & Chr(34) & "Filter" & intCounter & Chr(34) & " And "

End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Forms![frmLoadsheetsSearch].Filter = strSQL
Forms![frmLoadsheetsSearch].FilterOn = True
End If
End If
Endcode:
End Sub


What are all the varFilters for? You haven't used them anywhere.

Mike Storr
www.veraccess.com

Nov 12 '05 #3
Hi Allen,

Thanks very much for the help. The code below looks promising but I
have come across a minor(hopefully) hiccup.

The number fields work but the text fields come up with "type mismatch"
error". I have checked all the fields on the table and they are
definately text.

The date field comes up with a "you cant assign a value to this object"
error.

Thanks again for your assistance,

Nathan

Private Sub Search_Click()
Dim strFilter As String
Dim lngLen As Long
Dim strFormName As String

If Not IsNull(Me.Filter1) Then 'Number-field example
strFilter = strFilter & "([LoadsheetNo] = " & Me.Filter1 & ")
AND "
End If
If Not IsNull(Me.Filter2) Then 'Number-field example
strFilter = strFilter & "([CarrierConnote] = " & Me.Filter2 & ")
AND "
End If
If Not IsNull(Me.Filter3) Then 'Date/Time example
strFilter = strFilter & "[LoadsheetDate] = #" &
Format(Me.Filter3, "dd/mm/yyyy") & "#) AND "
End If
If Not IsNull(Me.Filter4) Then 'Text-field example
strFilter = strFilter & "[CarrierName] = """ & Me.Filter4 &
""")" And ""
End If
If Not IsNull(Me.Filter5) Then 'Number-field example
strFilter = strFilter & "([SendingStoreNo] = " & Me.Filter5 & ")
AND "
End If
If Not IsNull(Me.Filter6) Then 'Number-field example
strFilter = strFilter & "([ReceivingStoreNo] = " & Me.Filter6 &
") AND "
End If

lngLen = Len(strFilter) - 5
If lngLen <= 0 Then
MsgBox "No criteria"
Else
strFilter = Left$(strFilter, lngLen)
strFormName = "frmLoadsheetsSearch"
DoCmd.OpenForm strFormName, acViewNormal
With Forms(strFormName)
.Filter = strFilter
.FilterOn = True
End With
End If

End Sub

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #4
Just to add:

The LoadsheetNo & CarrierConnote are both text fields in the
tblLoadsheets which is the source of qryLoadsheets which is the record
source of frmLoadsheetsSearch
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #5
Hi Nathan,

In your test field assignments, you either need to use four
double-apostrophes at each end, or use a single apostrophe within two
doubles - e.g.
strFilter = strFilter & "[CarrierName] = " & """" & Me.Filter4 & """" & "
And "
or
strFilter = strFilter & "[CarrierName] = '" & Me.Filter4 & "' And "

In either case once Jet has a squizz at """" or "'" it'll know you mean a
string delimiter.
"""" translates into ", and "'" into ' either of which works fine with Jet.

BTW look out for redundant or extra brackets creeping in.

Try setting a breakpoint after the final clean-up of the string, then check
the variable using the Immediate Window (CTRL+G, then type "?strFilter" -
without the apostrophes - and press ENTER. You'll soon see it it's OK. You
can always open up a new query in design mode, change to SQL view, the copy
and paste the Immediate Window output into it, add "SELECT * FROM
<yourformrecordsource> WHERE " in front of the filter string and see what
happens when you change to regular design or preview view. It'll bitch at
you if you've forgotten a space or left a bracket in by mistake.

Enough apostrophes already! You'll soon get the hang of it - some of the
rest of us have, and we're not all scary gurus!

By the way, you could also stick your filter string into the DoCmd command
as the fourth argument:
DoCmd.OpenForm "frmWhatever", , , strFilter
or
DoCmd.OpenForm FormName:= "frmWhatever", WhereCondition:=strFilter
if you prefer, so your filter string works just like an extra WHERE clause
on the forms RecordSource. Easy, huh?

Good luck

Andrew
"Nathan Bloomfield" <na***************@hortmail.corm> wrote in message
news:40*********************@news.frii.net...
Hi Allen,

Thanks very much for the help. The code below looks promising but I
have come across a minor(hopefully) hiccup.

The number fields work but the text fields come up with "type mismatch"
error". I have checked all the fields on the table and they are
definately text.

The date field comes up with a "you cant assign a value to this object"
error.

Thanks again for your assistance,

Nathan

Private Sub Search_Click()
Dim strFilter As String
Dim lngLen As Long
Dim strFormName As String

If Not IsNull(Me.Filter1) Then 'Number-field example
strFilter = strFilter & "([LoadsheetNo] = " & Me.Filter1 & ")
AND "
End If
If Not IsNull(Me.Filter2) Then 'Number-field example
strFilter = strFilter & "([CarrierConnote] = " & Me.Filter2 & ")
AND "
End If
If Not IsNull(Me.Filter3) Then 'Date/Time example
strFilter = strFilter & "[LoadsheetDate] = #" &
Format(Me.Filter3, "dd/mm/yyyy") & "#) AND "
End If
If Not IsNull(Me.Filter4) Then 'Text-field example
strFilter = strFilter & "[CarrierName] = """ & Me.Filter4 &
""")" And ""
End If
If Not IsNull(Me.Filter5) Then 'Number-field example
strFilter = strFilter & "([SendingStoreNo] = " & Me.Filter5 & ")
AND "
End If
If Not IsNull(Me.Filter6) Then 'Number-field example
strFilter = strFilter & "([ReceivingStoreNo] = " & Me.Filter6 &
") AND "
End If

lngLen = Len(strFilter) - 5
If lngLen <= 0 Then
MsgBox "No criteria"
Else
strFilter = Left$(strFilter, lngLen)
strFormName = "frmLoadsheetsSearch"
DoCmd.OpenForm strFormName, acViewNormal
With Forms(strFormName)
.Filter = strFilter
.FilterOn = True
End With
End If

End Sub

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #6
Nathan, nothing is being assigned to any object until the string is assigned
to the Filter property of the form. Is that where the problem occurs? If so,
add:
Debug.Print strFilter
so you can see what's wrong in the Immediate Window (Ctrl+G).

If things are going wrong before that, you must have a naming conflict, or
invalid syntax.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nathan Bloomfield" <na***************@hortmail.corm> wrote in message
news:40*********************@news.frii.net...

Thanks very much for the help. The code below looks promising but I
have come across a minor(hopefully) hiccup.

The number fields work but the text fields come up with "type mismatch"
error". I have checked all the fields on the table and they are
definately text.

The date field comes up with a "you cant assign a value to this object"
error.

Thanks again for your assistance,

Nathan

Private Sub Search_Click()
Dim strFilter As String
Dim lngLen As Long
Dim strFormName As String

If Not IsNull(Me.Filter1) Then 'Number-field example
strFilter = strFilter & "([LoadsheetNo] = " & Me.Filter1 & ")
AND "
End If
If Not IsNull(Me.Filter2) Then 'Number-field example
strFilter = strFilter & "([CarrierConnote] = " & Me.Filter2 & ")
AND "
End If
If Not IsNull(Me.Filter3) Then 'Date/Time example
strFilter = strFilter & "[LoadsheetDate] = #" &
Format(Me.Filter3, "dd/mm/yyyy") & "#) AND "
End If
If Not IsNull(Me.Filter4) Then 'Text-field example
strFilter = strFilter & "[CarrierName] = """ & Me.Filter4 &
""")" And ""
End If
If Not IsNull(Me.Filter5) Then 'Number-field example
strFilter = strFilter & "([SendingStoreNo] = " & Me.Filter5 & ")
AND "
End If
If Not IsNull(Me.Filter6) Then 'Number-field example
strFilter = strFilter & "([ReceivingStoreNo] = " & Me.Filter6 &
") AND "
End If

lngLen = Len(strFilter) - 5
If lngLen <= 0 Then
MsgBox "No criteria"
Else
strFilter = Left$(strFilter, lngLen)
strFormName = "frmLoadsheetsSearch"
DoCmd.OpenForm strFormName, acViewNormal
With Forms(strFormName)
.Filter = strFilter
.FilterOn = True
End With
End If

End Sub

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #7
Hello Allen, Andrew & Mike:

Thanks for your help. By piecing together your suggestions I have
come away with more than originally intended. I have in the past
neglected to utilise the Immediate Window which is an invaluable tool.
And I now understand the use of apostrophes for the varying field
types. As an amateur programmer I would be lost without this kind of
assistance.

Regards,

Nathan
Melbourne, Australia
The final code:

Private Sub Search_Click()
'Thanks to Allen Browne for the code below
Dim strFilter As String
Dim lngLen As Long
Dim strFormName As String

If Not IsNull(Me.Filter1) Then 'Text-field example
strFilter = strFilter & "[LoadsheetNo] =" & """" & Me.Filter1
& """" & " And "
End If
If Not IsNull(Me.Filter2) Then 'Text-field example
strFilter = strFilter & "[CarrierConnote] =" & """" &
Me.Filter2 & """" & " And "
End If
If Not IsNull(Me.Filter3) Then 'Date/Time example
strFilter = strFilter & "[LoadsheetDate] = #" &
Format(Me.Filter3, "mm/dd/yyyy") & "# AND "
End If
If Not IsNull(Me.Filter4) Then 'Text-field example
strFilter = strFilter & "[CarrierName] =" & """" & Me.Filter4
& """" & " And "
End If
If Not IsNull(Me.Filter5) Then 'Number-field example
strFilter = strFilter & "([SendingStoreNo] = " & Me.Filter5 &
") AND "
End If
If Not IsNull(Me.Filter6) Then 'Number-field example
strFilter = strFilter & "([ReceivingStoreNo] = " & Me.Filter6
& ") AND "
End If

lngLen = Len(strFilter) - 5
If lngLen <= 0 Then
MsgBox "No criteria"
Else
strFilter = Left$(strFilter, lngLen)
strFormName = "frmLoadsheetsSearch"
DoCmd.OpenForm strFormName, acViewNormal
With Forms(strFormName)
Debug.Print strFilter
.Filter = strFilter
.FilterOn = True
End With
End If

End Sub
Nov 12 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Davey | last post by:
I have a website which has a popup window (this only opens when the user chooses to open it). In the popup window I have a <select> control which lists a selection of "classes". Each class has a...
3
by: Jason | last post by:
I am trying to filter records in a primary form based on records in related tables. The data in the related tables is being displayed in the primary form through subforms. To be more specific, I...
0
by: CSDunn | last post by:
Hello, I have a problem with field filtering between an Access 2000 Project form (the application is called CELDT), and the report that shows the results of the filter. Both the form and the...
5
by: Richard | last post by:
Hi, I have a form that take some time to load due to many comboboxes and at least 8 subforms. When I filter or sort the main form I get an error message and then Access shuts down. They ask if...
18
by: Colin McGuire | last post by:
Hi - this was posted last weekend and unfortunately not resolved. The solutions that were posted almost worked but after another 5 days of working on the code everynight, I am not further ahead....
4
by: Macbane | last post by:
Hi, I have a 'main' form called frmIssues which has a subform control (named linkIssuesDrug) containing the subform sfrmLink_Issues_Drugs. A control button on the main form opens a pop-up form...
3
by: cmo | last post by:
Well I hope I this isn't too nebulous of a problem. The problem I currently have is this: I have a button in a form that opens up a javascript/css poup that has an input field and two ahref links...
5
by: Thelma Roslyn Lubkin | last post by:
I am still having trouble trying to use a popup form to allow user to set filters for the main form. The main form is based on a single table. The popup contains 5 listboxes, so the user can...
11
by: V S Rawat | last post by:
using Javascript, I am opening a web-based url in a popup window. MyWin1=Window.Open(url, "mywindow") There is a form (form1) in the url in that popup window, I need to submit that form. ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.