Link to home
Start Free TrialLog in
Avatar of shanesuebsahakarn
shanesuebsahakarnFlag for United Kingdom of Great Britain and Northern Ireland

asked on

HOWTO: Create a search form

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
Avatar of nexusnation
nexusnation
Flag of United States of America image

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...
Avatar of shanesuebsahakarn

ASKER

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 :)
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)
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&
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 https://www.experts-exchange.com/questions/20525288/Open-invitation-to-all-Access-Experts.html why I think this won't work. None the less I think samples shouldn't give syntax errors.

C U :-)

Nic;o)
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.
ASKER CERTIFIED SOLUTION
Avatar of Netminder
Netminder

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Argh, I think I edited it out when I changed the Chr(37) to Chr(34)! I'll get it fixed!
Avatar of sihyer
sihyer

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
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.
I got an error on this line

If Not IsNull(Me!txtFindAddress) Then strFilter = strFilter & "[Address] Like " Chr(34) & "*" & Me!txtFindAddress & "*" & Chr(34) & " AND "
oops, sorry, I found the error, there's the extra " in front of the Chr(34) - my bad.
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?
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?
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).
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.
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 ?
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?
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 ?
'Sokay, I've just seen it :-)
>>which i am prone to doing

you can say that again. :-)
<insult nexusnation>

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

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

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
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 ?
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
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>
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
<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).
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
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
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 "
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...
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 ?
Thank you, that was the problem!!! I really do appreciate you help!!!

Thanks again!!! phew I'm glad that's resolved!!!
NP, glad someone found this article to be of use!
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?

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?
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
Please post a new question that refers back to this one.
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.