?
Solved

HOWTO: Create a search form

Posted on 2003-02-22
45
Medium Priority
?
11,638 Views
Last Modified: 2010-07-05
Experts, please don't answer this Q, this is being posted as a general tutorial question, but please feel free to add your comments and suggest any additions!

Q. We want to create a form for users to look up records in our table. Our table is called Customers and looks like this:
CusID (Autonumber field)
LastName (Text field)
FirstName (Text field)
Age (Number)
Address (Text Field)
Status (Text field, and it will only ever contain "Active", "Inactive" or "Prospect")

A. We have a form called SearchForm. It has 5 controls:
* Two combo boxes called cboFindFirstName and cboFindLastName. These show all the unique first names from the customers table, and their RowSource properties are set to SQL like this: SELECT DISTINCT FirstName FROM Customers. We want the search form to find all records matching what We choose from these combo boxes.

* A text box called txtFindAge. We want to find everyone older than the number we put in here.

* An text box called txtFindAddress. We want to find addresses that contain any text we put in here. So if we put in "19 East", we want it to find "19 East Road" and "19 East Avenue"

* A multiselect list box called lstFindStatus which shows "Active", "Inactive" or "Prospect". We want to find records matching all the statuses we pick from this box. So if we choose both Active and Inactive, we want to find both active and inactive customers.
If we leave any of these controls blank or we don't choose anything, we don't want it to search on that field.

One way of doing this is described here.

In order to achieve the above, we have to create a filtering string. We have a command button on the form, "Find records", called cmdFindRecords.

We need to put code in the OnClick event of this button. To do this, click on the command button in design view, and then click View->Properties on the menus. This brings up the Properties window. Click on the Event page, and click in the "On Click" row. A build button with "..." on it appears. Click on that button, and choose Code Builder.

A window with:
Private Sub cmdFindRecords_Click()

End Sub
appears. All our code goes in between those two lines.

First of all, we create a string variable to hold our filtering string, and set it to an empty string. We also need a variable to represent the items we've selected from the list box:
---
Dim strFilter As String
Dim varItem As Variant

strFilter=""
---

Now we check to see if the combo boxes are empty. If they aren't, we add them to the filter string:
---
If Not IsNull(Me!cboFindFirstName) Then strFilter = strFilter & "[FirstName]=" Chr(34) & Me!cboFindFirstName & Chr(34) & " AND "
If Not IsNull(Me!cboFindLastName) Then strFilter = strFilter & "[LastName]=" Chr(34) & Me!cboFindLastName & Chr(34) & " AND "
---
Using Me!cboFindFirstName and Me!cboFindLastName gives us the values we've chosen in those combo boxes. We first check to see if they are empty by using the IsNull function (which gives us back True if they are). Because FirstName and LastName are text fields, we put quote characters around them in the string (this is what Chr(34) is). We could use apostrophes (') but we might have names like O'Sullivan, which would cause the filter string to be wrong so we use quotes instead.

Now we check if anything has been entered into the txtAge text box.
---
If Not IsNull(Me!txtFindAge) Then strFilter = strFilter & "[Age]>" & Me!txtFindAge & " AND "
---
Because Age is a number field, we don't need to put apostrophes or quote marks around the value.

The next thing to check is the address. We want to match lines containing what we've typed not exact matches, so we have to use the Like operator which lets us use wildcards:
---
If Not IsNull(Me!txtFindAddress) Then strFilter = strFilter & "[Address] Like " Chr(34) & "*" & Me!txtFindAddress & "*" & Chr(34) & " AND "
---

The final thing to check is the multiselect list box. This is trickier because the user might have chosen more than one item. So we have to loop through the list box's items to see which ones have been selected:
---
if Me!lstFindStatus.ItemsSelected.Count>0 Then
   strFilter = strFilter & "("
   For Each varItem In Me!lstFindStatus.ItemsSelected
      strFilter = strFilter & "[Status]=" & Chr(34) & Me!txtFindStatus.ItemData(varItem) & Chr(34) & " OR "
   Next
   strFilter = Left$(strFilter, Len(strFilter)-4) & ") AND "
End If
---

We begin the above code checking to see if anything has been selected, We then loop through each selected item and add it to the filtering string. We use OR here rather than AND because we want to find records that have a status of say, Active OR have a status of Inactive. After the loop finishes, we have a stray OR at the end, so we take that off and put a close bracket on the end.

Finally, we have to check to see if our filter string has anything in it at all (because someone might have clicked on Find Records but not typed anything in or chosen anything from the combos and listbox. If they've chosen or typed anything, our filter string will have a stray AND, so we have to take that off.
---
If strFilter<>"" Then strFilter = Left$(strFilter,Len(strFilter)-5)
---
So if the user has chosen "Smith" in the last name combo, "Joe" in the first name combo, typed "20" in the age box, "Main" in the address box and chosen "Active" and "Inactive" from the list box, our filter string looks like this:
[FirstName]="Joe" AND [LastName]="Smith" AND [Age]>20 AND [Address] Like "*Main*" AND ([Status]="Active" OR [Status]="Inactive")

Now that we have our filtering string, what do we do with it ?

We can use it to open another form which shows all of the records from the Customers table. For example:
---
DoCmd.OpenForm "frmCustomerDetails", , ,strFilter
---
This opens frmCustomerDetails (which is based on the Customers table and would normally shows all customers) and applies the filter to only show records matching our criteria.

Or instead, we might have a subform inside our search form which shows all the customer details in a tabular format (a continuous or datasheet subform). This subform might be called frmSubCustomerDetails, and is held inside a subform control called subCustomers. We can use our filter string to set a  filter for the subform:
---
If strFilter="" Then
   Me!subCustomers.Form.FilterOn = False
Else
   Me!subCustomers.Form.Filter = strFilter
   Me!subCustomers.Form.FilterOn = True
End If
---
This checks to see if our filter is empty. If it is, we just switch any existing filter on our subform off. Otherwise, we set the subform's filter to our string, and then switch the filter on.

Our completed code looks like this:
===================================

Private Sub cmdFindRecords_Click()
Dim strFilter As String
Dim varItem As Variant

strFilter=""

If Not IsNull(Me!cboFindFirstName) Then strFilter = strFilter & "[FirstName]=" Chr(34) & Me!cboFindFirstName & Chr(34) & " AND "
If Not IsNull(Me!cboFindLastName) Then strFilter = strFilter & "[LastName]=" Chr(34) & Me!cboFindLastName & Chr(34) & " AND "
If Not IsNull(Me!txtFindAge) Then strFilter = strFilter & "[Age]>" & Me!txtFindAge & " AND "
If Not IsNull(Me!txtFindAddress) Then strFilter = strFilter & "[Address] Like " Chr(34) & "*" & Me!txtFindAddress & "*" & Chr(34) & " AND "
if Me!lstFindStatus.ItemsSelected.Count>0 Then
   strFilter = strFilter & "("
   For Each varItem In Me!lstFindStatus.ItemsSelected
      strFilter = strFilter & "[Status]=" & Chr(34) & Me!txtFindStatus.ItemData(varItem) & Chr(34) & " OR "
   Next
   strFilter = Left$(strFilter, Len(strFilter)-4) & ") AND "
End If

If strFilter<>"" Then strFilter = Left$(strFilter,Len(strFilter)-5)
DoCmd.OpenForm "frmCustomerDetails", , ,strFilter
End Sub
0
Comment
Question by:shanesuebsahakarn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 6
  • 5
  • +9
45 Comments
 
LVL 12

Expert Comment

by:nexusnation
ID: 7999684
you will one day find yourself on the Top 15. this is truly a great thing to do.

<copy> <open microsoft word> <paste> i think i'll snag this...

maybe you can help me on a similar problem. i would like to use a code like this, but i have multiple multiselect list boxes for the SAME field (states divided into regions).

I'll gladly hand you a bunch of points for this one...
0
 
LVL 41

Author Comment

by:shanesuebsahakarn
ID: 7999874
Nexusnation, can you post another Q outlining your question ? I want to keep this thread clear-ish as a reference for people looking for answers (Netminder should lock this Q soon).

Thanks :)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 8000218
Guess:
If Not IsNull(Me!cboFindFirstName) Then strFilter = strFilter & "[FirstName]=" Chr(37) & Me!cboFindFirstName & Chr(37) & " AND "

should read:

If Not IsNull(Me!cboFindFirstName) Then strFilter = strFilter & "[FirstName]=" Chr(34) & Me!cboFindFirstName & Chr(34) & " AND "

or

If Not IsNull(Me!cboFindFirstName) Then strFilter = strFilter & "[FirstName] like " Chr(37) & Me!cboFindFirstName & Chr(37) & " AND "

Also a bit strange to see the ( .. OR .. OR .. ) construction for the listbox where the IN ( .., .., .. ) option would be more efficient...


Basically I think having this many fields to type in for a user to do a selection is a bad idea.
As typing is involved, typo's will spoil the result.

When offering this type of form the next user request will be to have an "OR" possibility between the fields and finally you end up with rebuilding the criteria for a complete WHERE statement...

The right-click option from Access already offers this possibility on every field of a form. All needed is to teach the user howto use that option.
For the OR just teach the advanced user to use the FilterByForm.

I know arguing with you on these points is uselesss, so when Netminder locks this Q he has permission to remove my comment. Just wanted to point out the % error, probably a typo...

Nic;o)
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 53

Expert Comment

by:COBOLdinosaur
ID: 8000550
If I might offer a suggest, totally unrelated to how the code should be written.  I am not and Access guru so that leaves me without an opinion on how it should be done.

If there is value in putting up common questions to reduce the redundant questions, then it should only be questions where there is general agree among experts about what the correct answer is.  

If there is a disagreement here, then there are two possibilities here you can agree on a presentation to the user that most are comfortable with, or you can take this one done and find those things where there is agreement.  If there is no agreement on anything, then by definition there are no standard answers and therefore no question in the TA is ever redundant.

Cd&
0
 
LVL 54

Expert Comment

by:nico5038
ID: 8000741
Cd&,

Experts never agree, you should know :-)

That's why I did post to have my comment removed (after correcting the HOWTO) as my opinion is not relevant to the HOWTO and I already experienced shanesuebsahakarn has his way of development, answering Q's and cooperation with other experts and I have mine.

I also posted in http://www.experts-exchange.com/Databases/MS_Access/Q_20525288.html why I think this won't work. None the less I think samples shouldn't give syntax errors.

C U :-)

Nic;o)
0
 
LVL 41

Author Comment

by:shanesuebsahakarn
ID: 8001223
Thanks for pointing out the error in the posting. I should really not write these things when I'm tired.

As for the OR construct, I thnk this comes down to personal preference. I try to make a habit of avoiding INs, mainly because they are slow in large subselects, so I use ORs in this case.

As for arguing with me, Nico, I'm happy to discuss reasonable and constructive criticism. I don't react well to insults, particularly insidious, subtle and patronising ones.

I have always found this method to be more comfortable for users than use a filter by form, and more efficient (particularly on a network, where the form would have to retrieve every field from the table). For example, I have users who need to search a table of mobile phone (cellphone) users. The table actually contains a large amount of information, but they only ever need to search by user's name, phonenumber, and very occasionally, SIM number. The records are grouped into accounts on a subform, so in order to use Filter-By-Form to find a number, they would first need to know the account that the phone is on. With 20K phone records to look through, this is impossible. So when someone calls, the call-centre user simply asks for a phone number, types it in and clicks on a button to take him/her straight to the account/phone record.

I will amend this question to point out that it is *one* way of doing it.
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 8001490
Per request, points refunded and question closed.

Netminder
EE Admin
0
 
LVL 12

Expert Comment

by:nexusnation
ID: 8004161
I noticed a mistake in your code. please fix it immediately (or ask a mod to edit your post, as you cannot after it is a PAQ).

on the first three lines of the code, there is no "&" (no quotes) before the first "(Chr)34" (no quotes). I would fix it immediately.

Thanks!

Andrew
0
 
LVL 41

Author Comment

by:shanesuebsahakarn
ID: 8004206
Argh, I think I edited it out when I changed the Chr(37) to Chr(34)! I'll get it fixed!
0
 

Expert Comment

by:sihyer
ID: 8036637
I'm getting compile errors on chr portion of the code in the vb editor.  Any suggestions?  It gives the (charactar code as long) caption when typing in the code.

Also, this should work with split tables housing 'lookup to ???' columns, right?  Should i use the lookup column in this scenario for 'SELECT DISTINCT...'or use the split table reference?

Thanks in Adv.
Alland
0
 
LVL 41

Author Comment

by:shanesuebsahakarn
ID: 8036783
Alland, there's an error in the code - the two lines:
If Not IsNull(Me!cboFindFirstName) Then strFilter = strFilter & "[FirstName]=" Chr(34) & Me!cboFindFirstName & Chr(34) & " AND "
If Not IsNull(Me!cboFindLastName) Then strFilter = strFilter & "[LastName]=" Chr(34) & Me!cboFindLastName & Chr(34) & " AND "

should read:
If Not IsNull(Me!cboFindFirstName) Then strFilter = strFilter & "[FirstName]=" & Chr(34) & Me!cboFindFirstName & Chr(34) & " AND "
If Not IsNull(Me!cboFindLastName) Then strFilter = strFilter & "[LastName]=" & Chr(34) & Me!cboFindLastName & Chr(34) & " AND "

If I understand your second part correctly, you want to know if you should refer to the value that you see on screen, or the value that you store in the table, right ? When filtering, you should always filter or query on the value stored in the table, not the text given by a lookup field.
0
 

Expert Comment

by:wonderfulcow
ID: 8044198
I got an error on this line

If Not IsNull(Me!txtFindAddress) Then strFilter = strFilter & "[Address] Like " Chr(34) & "*" & Me!txtFindAddress & "*" & Chr(34) & " AND "
0
 

Expert Comment

by:wonderfulcow
ID: 8044329
oops, sorry, I found the error, there's the extra " in front of the Chr(34) - my bad.
0
 

Expert Comment

by:wonderfulcow
ID: 8044455
WHen I compile, it gives me a Compile error:
"Wrong number of arguments or invalid property assignment"

When I try to assign the button t open a report.

Here is my code

DoCmd.OpenReport "rptCustomers" , , , strFilter

How do i fix it so it opens a report with filtered information?
0
 

Expert Comment

by:wonderfulcow
ID: 8044696
Also, I applied the same method on this database search form I'm budilng, and I get the following error message:

Object doesn't support this property or method.

What could have caused this problem? If I followed and created the database based on your example?
0
 
LVL 41

Author Comment

by:shanesuebsahakarn
ID: 8044923
Ok, first of all don't panic, we need to check that strFilter is being built properly. Replace your DoCmd.OpenReport line with:
Debug.Print strFilter
Once it has finished, press CTRL+G, and you should see what strFilter is equal to. Post the result, and we can take it from there. Also, could you please open another question for your problem, I want to keep this thread fairly empty (otherwise it could go on for miles if I answer everyone's questions in one thread).
0
 
LVL 9

Expert Comment

by:mcallarse
ID: 8055256
Just a thought...

I'm not a huge fan of typing redundant code, so tend to program in a way that allows me to separate business logic from programming logic. In that vein, the following is an alternative to the multiple If...Then statements in use in the Search Form solution here.

The Search solution proposed is based on concatenating multiple selection expressions with a logical operator to create a WHERE clause. Each expression is made of three parts:

1. A Field;
2. An equality Operator (=, In, Is, Between,...); and
3. A comparison Value, or set of Values.

Put another way, the WHERE clause is a structure with multiple condition elements; each condition is a structure, with three elements. How can we use this to make our programming easier?

One option is to create a Condition table with at least three fields, corresponding to the three elements of an expression. This design allows you to create, delete and edit conditions pretty much on the fly: I add a field to my form, add a record to my table and don't have to worry about any code. The following example assumes that this table exists and is named tblCondition, with fields FieldName, Op and FormName, as described above, and a fourth field, FieldType that will allow us to format our Value in accordance with Jet SQL.

Keep in mind that this is quick-and-dirty, and as written works only with the basic equality operators (=,<,> and Like).

--

Dim db, rs ' as DAO Database and Recordset
Dim strWhere As String, strEnc As String
Dim myCtl As Control, myValue Ss String

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT FieldName, Op, FormName, FieldType FROM tblCondition")

With rs
    Do Until .EOF
        Set myCtl = Controls(!FormName)
        If Not IsNull(myCtl.Value) Then
            Select Case !fieldtype
            Case "Date":
                If Not IsDate(myCtl.Value) Then
                    MsgBox myCtl.Name & " is not a date!"
                    myCtl.SetFocus
                    GoTo fin
                End If
                strEnc = "#"
            Case "String":
                strEnc = """"
            Case "Number":
                If Not IsNumeric(myCtl.Value) Then
                    MsgBox myCtl.Name & " is not a number!"
                    myCtl.SetFocus
                    GoTo fin
                End If
                strEnc = Empty
            End Select
            If !op = "Like" Then myValue = "*" & ctl.Value & "*" Else myValue = ctl.Value
            strWhere = strWhere & "(" & _
             "[" & !FieldName & "] " & _
             !op & " " & _
             strEnc & myValue & strEnc & ") AND "
        End If
        .MoveNext
    Loop
    .Close
End With

If Not strWhere = Empty Then strWhere = Left$(strWhere, Len(strWhere) - 5)

' Process strWhere

Me.Filter = strWhere
Me.FilterOn = True

fin:

Set rs = Nothing: Set db = Nothing

--

For those of you thinking, "Didn't you say easier?" keep this in mind: the above snippet remains the same for a 1-field search form, or a 100-field search form, and no coding means never having to say, "Did I forget a pound sign?" Add a Form field to tblCondition and you can process all search forms in your database using a single function. It's not for everybody by any means, and certainly not for every situation, but I've found it useful and perhaps you will too.
0
 
LVL 41

Author Comment

by:shanesuebsahakarn
ID: 8056725
mcallarse, this particular post was not meant to be an exercise in constructing the best search form or technique. It's meant to be a simple tutorial for those unfamiliar with Access (hence the very basic instructions), and the demonstration of the use of different controls in the form.

I may be mistaken but you seem to be chasing my posts a lot. Perhaps this is just coincidence, and if that's the case, I apologise. But if I have done something to offend you and cause this situation, may we discuss it ?
0
 
LVL 9

Expert Comment

by:mcallarse
ID: 8056801
I think your post is great and am not trying to take anything away from it; I am merely trying to add-on with another dimension for more experienced users. As you know, there's no central place to do this, and subject matter was in line with the original post, so...

Re: the, "chasing my posts," bit, I was pointed to this specific Q by a totally unrelated user, but we _are_ on an, "Exchange," site, with the same questions available to both of us. We have experience in the same field, and are both active posters. It is possible that we will both comment on a Q without it being personal...right?
0
 
LVL 41

Author Comment

by:shanesuebsahakarn
ID: 8056888
That's fair enough, I thought I might have done something or posted something to offend you somewhere (which I am prone to doing), that's all.

Out of curiousity, where were you pointed to this post from ?
0
 
LVL 41

Author Comment

by:shanesuebsahakarn
ID: 8056936
'Sokay, I've just seen it :-)
0
 

Expert Comment

by:wonderfulcow
ID: 8057269
mcallarse was pointed to this specific question from my recent question post http://www.experts-exchange.com/Databases/MS_Access/Q_20535911.html
0
 
LVL 12

Expert Comment

by:nexusnation
ID: 8059285
>>which i am prone to doing

you can say that again. :-)
0
 
LVL 41

Author Comment

by:shanesuebsahakarn
ID: 8059529
<insult nexusnation>
0
 

Expert Comment

by:sihyer
ID: 8120539

silly question here buy bear with me, please.


Shanesuebsahakarn in your example, are you pulling the info directly from the table or are you going through a query first?  It's not working for me but i think i'm missing something very very basic and simple.

Thanks, allan
0
 

Expert Comment

by:sihyer
ID: 8120591

silly question here buy bear with me, please.


Shanesuebsahakarn in your example, are you pulling the info directly from the table or are you going through a query first?  It's not working for me but i think i'm missing something very very basic and simple.

Thanks, allan
0
 

Expert Comment

by:sihyer
ID: 8121067

silly question here buy bear with me, please.


Shanesuebsahakarn in your example, are you pulling the info directly from the table or are you going through a query first?  It's not working for me but i think i'm missing something very very basic and simple.

Thanks, allan
0
 
LVL 41

Author Comment

by:shanesuebsahakarn
ID: 8121784
Allan,

It should work with either a table or a query. When you say it isn't working, can you describe where you are displaying the results ?
0
 

Expert Comment

by:sihyer
ID: 8122458
Fixed it.  What was happening was that there were no results coming up in the subform and when using the separate docmd.openform to bring up the separate form, i couldn't get that to come up in datasheet view.  I had the child/master fields linked which was not allowing the filter to go through.

Here is my debug immediate info from a few searches:

[Model]="CH803"
[Model]="CR125R3"
[Model]="CR250R"
[Model]="CRF150F3"
[Model]="VTX1800S2"
[ModelName]="Rancher"
[Model]="TRX350FM3" AND [ModelName]="Rancher"
[Model]="CBR11XX"
[ModelName]="1500 Valkyrie"
[Model]="CBR900RR2" AND [ModelName]="954 RR"
[ModelName]="Rancher"

What i was wanting to know is how does the following statement remove the last appended 'and' from the string and would it be possible to append to the beginning the 'AND' on each subsequent statement instead of putting it after the first and each thereafter parameter then having to remove the last one at the end?

If strFilter <> "" Then strFilter = Left$(strFilter, Len(strFilter) - 5)

I left this statement out at first and that's how i got hung up on it.


This is the current code.  I had to strip down the original code for my purposes but i'll keep adding the search parameters as i go.


Private Sub FindUnit_Click()

Dim strFilter As String
Dim varItem As Variant

strFilter = ""

If Not IsNull(Me!cbofindmodel) Then strFilter = strFilter & "[Model]=" & Chr(34) & Me!cbofindmodel & Chr(34) & " AND "
If Not IsNull(Me!cbomodelname) Then strFilter = strFilter & "[ModelName]=" & Chr(34) & Me!cbomodelname & Chr(34) & " AND "

If strFilter <> "" Then strFilter = Left$(strFilter, Len(strFilter) - 5)


Debug.Print strFilter

If strFilter = "" Then
  Me!Subfrm.Form.FilterOn = False
Else
  Me!Subfrm.Form.Filter = strFilter
  Me!Subfrm.Form.FilterOn = True
End If

End Sub


ps~ sorry about the repeats above; i was refreshing waiting for responses and not realizing that it was reposting form data..
sorry, is that removeable?
allan
0
 
LVL 12

Expert Comment

by:nexusnation
ID: 8122887
disregard the double posts, allan. it happens to all of us.

oh, and this:
If strFilter <> "" Then strFilter = Left$(strFilter, Len(strFilter) - 5)
takes off " AND "
which is really only needed if you have a loop, because instead, you can have this:
If Not IsNull(Me!cbofindmodel) Then strFilter = strFilter & "[Model]=" & Chr(34) & Me!cbofindmodel & Chr(34) & " AND "
If Not IsNull(Me!cbomodelname) Then strFilter = strFilter & "[ModelName]=" & Chr(34) & Me!cbomodelname & Chr(34)

nex

p.s. <insults shane>
0
 
LVL 12

Expert Comment

by:nexusnation
ID: 8122902
i am sorry, i just realized that you don't want to take off the " AND ". leave the trim. the reason is because of the isnotnull's.

<insults shane again because he will laugh at me for not catching this mistake>


nex
0
 
LVL 41

Author Comment

by:shanesuebsahakarn
ID: 8123077
<laugh nex> Ha ha :-D

Allan, you can indeed put the AND at the start - you can then change the Left$ statement to:
strFilter = Mid$(strFilter,6) (to remove the first AND statement).
0
 

Expert Comment

by:sihyer
ID: 8123884
Thanks Shane and Andrew both.  Rest well tonite knowing that you've saved thousands of hairs from being ripped viciously from my scalp in a fit of rage.

Oh, and i'm sure you haven't seen the last of me by any stretch of the imagination.
Allan
0
 

Expert Comment

by:accesshelp11
ID: 8597640
HI Shanesuebsahakarn,

I really liked this How TO document...It's actually something related to what that i was doing in access.  I used you code to work around some of things that i was trying to do, but i got an error at some of your code.  

If Not IsNull(Me!txtFindAddress) Then strFilter = strFilter & "[Address] Like " Chr(34) & "*" & Me!txtFindAddress & "*" & Chr(34) & " AND " 

there probably should be a & after like", so i put that in, but when i run the program i'm getting an error - it's giving me a pop-up asking me to enter a parameter value - address from...could you please take a look at this and help me out. It's the same code that you wrote but with the &.  I left out a few lines and left the ones in where i was doing a search.  The first Combo4 like works well, it's just the second line that doesn't.

Private Sub Command6_Click()
Dim strFilter As String
Dim varItem As Variant

strFilter = ""

If Not IsNull(Me!Combo4) Then strFilter = strFilter & "[igcontrolnum]=" & Chr(34) & Me!Combo4 & Chr(34) & " AND "
If Not IsNull(Me!textreceivedfrom) Then strFilter = strFilter & "[Address from] Like" & Chr(34) & " * " & Me!textreceivedfrom & " * " & Chr(34) & " And """

If strFilter <> "" Then strFilter = Left$(strFilter, Len(strFilter) - 5)
DoCmd.OpenForm "IMCL5", , , strFilter

End Sub


Thanks,
Sam
0
 
LVL 41

Author Comment

by:shanesuebsahakarn
ID: 8598105
Hi Sam,

There is indeed a missing & in the code, but I can't edit the text.

For your second line, does your IMCL5 form definitely contain a field called "Address from" ? Also, you have " And """ at the end of the statement, it should just be " And "
0
 

Expert Comment

by:accesshelp11
ID: 8598416
yeah I understand that you can't edit the text. np!!!  

i changed the text box and called it source1 and i do have a field called source in IMCL5.  I don't get that pop us message anymore, but it opens my form but i get a blank form, i don't get any quried results...any clue why.  This is driving me crazy, i would appreciate your help...here is the code.Oh i changed the quotes at the end, i must have missed that...

Private Sub Command13_Click()

Dim strFilter As String
Dim varItem As Variant

strFilter = ""

If Not IsNull(Me!Combo4) Then strFilter = strFilter & "[igcontrolnum]=" & Chr(34) & Me!Combo4 & Chr(34) & " AND "
If Not IsNull(Me!source1) Then strFilter = strFilter & "[source] Like" & Chr(34) & " * " & Me!source1 & " * " & Chr(34) & " And "

If strFilter <> "" Then strFilter = Left$(strFilter, Len(strFilter) - 5)
DoCmd.OpenForm "IMCL5", , , strFilter
   
End Sub

thanks shane...
0
 
LVL 41

Author Comment

by:shanesuebsahakarn
ID: 8598455
The problem might be the * characters. You've got " * " in your query. Say you type in the word "East" into the text box. Because there are 2 spaces in that, the code will only match fields where the text contains "  East  " - ie 2 spaces, East, 2 Spaces. Try changing " * " to just "*" (no spaces). Does that resolve it ?
0
 

Expert Comment

by:accesshelp11
ID: 8598519
Thank you, that was the problem!!! I really do appreciate you help!!!

Thanks again!!! phew I'm glad that's resolved!!!
0
 
LVL 41

Author Comment

by:shanesuebsahakarn
ID: 8598542
NP, glad someone found this article to be of use!
0
 
LVL 1

Expert Comment

by:wolfsburged
ID: 8797475
Alright I've take the code and modified it to suit my needs, but when I execute it, my resulting form comes up blank... I changed the VB code on the last line to
DoCmd.OpenReport "Global Operations", , , strFilter

Which printed out a report with the correct search results, but when it's set to
DoCmd.OpenForm "Results", , , strFilter

Then I get my results form with all the fields blank... The fields are all named according to what they were called in the VB file and the code is obviously drawing the right data... How do I get the data into those fields?

0
 

Expert Comment

by:Alvarado2004
ID: 12377079
I must say that this was a very very useful tutorial!
The only problem I encounter is that if a non existent value is typed in a field then the form is shown with an added new record. Is there a way to correct this?
0
 
LVL 1

Expert Comment

by:jdhackett
ID: 13697959
The initial example works very well for me. However I also need to allow selection using a date field. I don't get any error messages, but I get the wrong results: all records with non balnk in the date field are returned, irrespective of the date I enter. The date is entered on the form in dd/mm/yyyy format. I have tried various approaches including enclosing the date in pound (#) marks and I have also attemped to reformat the date to US mm/dd/yyyy format, but I cannot get it to work.
Could somebody post some working code for me PLEASE.....

The system short date format is dd/mm/yyyy
0
 
LVL 41

Author Comment

by:shanesuebsahakarn
ID: 13698031
Please post a new question that refers back to this one.
0
 
LVL 1

Expert Comment

by:jdhackett
ID: 13698688
Done. Sorry, I thought it would make sense for other readers to keep the thread together. Never sure about the etiquette on boards like this. You go one place and get slammed for starting new threads. Next place, you get slammed for not starting new threads. Can't win.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question