# Solved: Access 2007 Query By Form



## hollinshead (Aug 5, 2009)

Hi there, i am relatively new to access and so a little naive with it all. I have created a database that purely stores details of what is in a candidates Cv. These details are things such as gender, nationality, and then others such as qualifications, spoken languages etc. Some of those are multi value combo boxes where the details have been entered in, there not looked up or anything.
Now the situation is that i am purely wanting to use the database to search the database, and want to do this using a form whioch contains a range of text boxes and combo boxes so that whenever i select the relevant data, it runs the query and produces the details that match that. Obviously if one of the combo boxes is left blank then i want it to disregard this field criteria. I have been told that the only way i would be able to do this is through VB code, which to be honest im not the best at. Ive tried small just to get it working first of all but im having issues. my code is dsplayed below:

Private Sub cmdSearchCriteria_Click()
Dim strWhere As String
strWhere = vbNullString

'Search for Gender
If Not IsNull(gendersearch) Then
If strWhere = vbNullString Then
strWhere = "(tblCandidateDetails.Gender] = " & gendersearch & ")"
Else
strWhere = strWhere & " And ([tblCandidateDetails.Gender] = " & gendersearch & ")"
End If
End If

If strWhere = vbNullString Then
DoCmd.OpenTable "tblCandidateDetails"

Else
'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

If you could just use a normal query and link the combo boxes to the query,(which i can do) but then place the action that 'if the box is null then disregard that criteria' then this would be a lot easier. Has anyone got any ideas.

Thank you very much in advance, this issue really has been bugging me and is vital that I find a solution. i hope to hear from you soon

Kind regards

Luke


----------



## PRNMATT (Aug 11, 2009)

I have implemented what I feel you are describing, where I have provided my client with the following Form presentation:
1) Provide at the top of the Form a ComboBox for each Field
filter selection. Where the Combobox lists the various choices
for each Field. Note: If a Field is not used, the Default entry is "*"
So for ten Fields, there would be ten ComboBoxes.
2) Our objective in utilizing the contents of the above ten Comboboxes
to to build an SQL Where clause. You can perform this by utilizing a
custom Modual or Subroutine. Its best to pass the where SQL string in
the argument of the Function or Subrouting.
3) When you call the Report you want, simply pass this Where sql string in
the vb Report DoCmd calling statement as shown below:

DoCmd.OpenReport strReportName, acViewPreview, , strSQLFilter

Where strSQLFilter is provided by the Function or Subroutine outlined
in (2) above.

I hope this helps!
prnmatt


----------



## OBP (Mar 8, 2005)

The method that you are using is Ok and works well, what problem are you having with it?
Too VBA Intensive?
You can create a Query as you say, if you use the 
Like "*" forms![formname]![fieldname] & "*"
In the criteria row it will ignore blank Search fields, but it will "Pattern Match" your data
So if you enter "Test" in the Search Field it will 
"My Test" and "Test data" ot "Testament".
i.e anything with test in it


----------



## hollinshead (Aug 5, 2009)

Hi
thank you for your support with this matter, Like you suggested if it is easier to do this all in a query instead of using VB code behind the scenes then that would be fantastic.

One of the combo boxes i have got is Army Corp/Service storing row source that i have already entered into the field in the table for which army area they worked in whether it be infantry etc. This combo box is set to allow multiple values to be selected, so i think this is where the problem will be. Can i still use the query method to get the result needed, and if so what do you suggest that i enter into the criteria field. As well as that i need to make sure that if ntohing is selected then this field isnt taken into account in to query. Currently i have just what is linking that combo box to the query;

[Forms]![frmCandidateSearchCriteria]![ArmyCorpService]

Thanks again for your time and efforts, any guidance will be greatly appreciated. This problems killing me.

Luke


----------



## OBP (Mar 8, 2005)

Luke the Multiple Selection is going to be a Problem in a Query, the only way that I can think of to overcome it is to use some simple VBA to put the Multiple Selections in to separate fields to use with the Query.
Can your Database be converted to Access 2003 format?
Or at least the Tables and the Search Form?
You should be able to open a new Access 2003 format database and then Import the Tables and the Search Form.
If you then remove any Personally Identifying data from the tables like Names, address, emails, SSN numbers etc you can post a zipped copy on here for us to "play with" to find the best method.
Your Form Filter in the First Post looks OK, why don't you want to continue with that?


----------



## hollinshead (Aug 5, 2009)

hi there 
thankyou for your reply, for some reason when i apply that code, nothing happens and it doesnt do any action. i think it might be because im applyign the filter but im not stating for it to open a report or something like that. Its probably some simple do cmd that ive left out. I just wanted it to display in a table in normal view. i think that maybe going back to using 2003 may be a little hard for me to do. Do you think that using the first bit of code i was using would cause any issues with selecting multi items in the combo box? Thanks again

Luke


----------



## hollinshead (Aug 5, 2009)

Another things that i have noticed is that the data in the combo boxes are linked to the table, i am only saying that "if the data that is displayed in that combo box is the same in the table, then......" That may be the reason why. What do you think?


----------



## OBP (Mar 8, 2005)

From your previous Post it appears that your problem with the VBA Filter is the fact that you haven't got the Table's fields on your Form to be filtered.
See the attached database for an example.


----------



## hollinshead (Aug 5, 2009)

Thankyou very much for your efforts, that database was extremely helpful and definitely exlplained a lot. Ive noticede that you have the search fields, and the results for that criteria on one form. Would i be able to put these on separarte forms, as there are a large amount of criteria fields. This would avoid my form being considerable large. Thanks again for your time and efforts. much appreciated.

Luke


----------



## OBP (Mar 8, 2005)

Luke, if you want to use a different form for output the VBA gets more complicated for setting the Filter.
You may be better off using a Select query or even a VBA created SQL Query.
Why not try it with 2 forms and I will see if we can modify the VBA code to suit.
I will need to know the name of the Form that will display the data.


----------



## hollinshead (Aug 5, 2009)

Hi there the name of the form is frmCriteriaSearchResults. This form is a continuous form linked to the tblCandidateDetails. Currently This table obviously shows all the data as isnt filtered. 

The form i would like to have the search criteria on is called frmCandidateSearch Criteria. This has around 21 fields from the table ranging from combo boxes, text fields and also check boxes. One example is the combo box cboProfessionalExp which is a multi select box that displays the candidates commercial experience.

Thanks again


----------



## OBP (Mar 8, 2005)

Do you want to be able to search for any or all of these at the same time?


----------



## OBP (Mar 8, 2005)

With the requirement for using a Multiselect list I think you will better off using a VBA SQL generated query, otherwise I don't think the Query Criteria can pick up the multi-selections, unless we use the multiple field method I mentioned in post #5.
Any chance that you can post or email me a zipped Access 2000 format version of your database?
You won't need to use the 2000 version, it is only for me to check which method is likely to work best.


----------



## hollinshead (Aug 5, 2009)

In answer to the previous message, if you can i would luike to be able to search for all or some. The combo boxes or text boxes that are left blank, then ignore. 

Here is the database, however i was not bale to convert the it to a 2000 database as it was saying that some of the featrues requires 2007. I have sent it anyway to see if it still helps. I will try and get it converted


----------



## hollinshead (Aug 5, 2009)

Here is an updated one which may make it easier. If yo are able to open it that is it will show all the fields and the data that needs to be sorted. Thanks again i definitely owe you one.


----------



## OBP (Mar 8, 2005)

Sorry I can't open Access 2007 databases at all.
What you can do is Create a new Access 2000 database Import your Tables in to it and then create a blank search form.
Re-open the original database in another instance of Access, copy all the Search fields and paste them on the new form.

However if you can manage VBA you could have a look at the attachment which has the VBA for Creating a new Query each time for your search.
This line needs to be removed the first time you run a search as there is no query to delete (or create a query called "NewQueryDef")

.QueryDefs.Delete "NewQueryDef"


----------



## hollinshead (Aug 5, 2009)

Hi there i have been able to get the database formatted in 2000 format, so you can have a look if you have time. Thank you so much for your efforts on this.


----------



## OBP (Mar 8, 2005)

Ok I have had a quick look at the Table and I think you should really have Sub tables for those Items that have more than one piece of data in them. For one thing it makes changing the data easier and also Searching the data as well.
The down side is that you will also need subforms for that data.
The only way that you can extract the Search matches at the moment is by using the "Like" function.
You also have a few problems with items already selected for the Combos that are not in your list.
It is not a good idea to use "Value lists", it is much better to have dedicated tables for listing those items.


----------



## OBP (Mar 8, 2005)

I have been looking at your Search Form, in the Multi select lists would you want to search for Records that Only Include all items selected?
Or records that Include Any one or more of the items selected?


----------



## hollinshead (Aug 5, 2009)

yes any of the records, so for example if the user only selects Human resources on the search form, then it searches for candidates that have that profession as well as any other professions they have, but if they haven't got that profession then they are not in the results.


----------



## OBP (Mar 8, 2005)

But what if they select 2 professions?


----------



## hollinshead (Aug 5, 2009)

then the candidates must have at least them two professions. That's what i would like to happen anyway. Is this going to be a problem do ya think? Anyway thanks for your support so far.


----------



## OBP (Mar 8, 2005)

Ok, I am keeping this simple, which method do you want to try first, the Query (might be difficult), or the QueryDef new version using VBA like that which I posted in the Word doc.?


----------



## hollinshead (Aug 5, 2009)

well the easiest one really, as long as it comes with the same results. Will the QueryDef method cause any restrictions or will it be exactly the same. I;m not that sure how to really implement this option, but if you think its best then ill follow your guidance.


----------



## OBP (Mar 8, 2005)

The query def uses the same kind of VBA that you used on the Filter, but it creates an SQL query that replaces the one that supplies your search results form with it's data each time you run a search.
The ordinary select query with Criteria from the Form requires a new Field on the Form for each of your Multi select List boxes as the Query Criteria can't work with the list box directly.
You do however have a problem with your data as some of the data in the table is not on your List's list.
i.e. Spoken Language = "Arabic;English" is not on the list.
I have created an unbound Text Box on the Search Form which will allow you to select English and then Arabic and place the ";" between them.
That works Ok, but you will need to be careful that the data in the Table is controlled which is why I suggested earlier on in this Thread that you use Tables for all of those Selections.
I have attached a copy showing the Multi select working for the Spoken Langauge, it places the selected langauges in Text69 and uses that in the query that supplies the data to your search results form, try Arabic and English.
We can attempt to expand on this for all of the fields if you like or try the QueryDef version. It may be that with the query def version you will have to Compact & Repair the database more often to prevent it getting larger as replacing the query each time but I haven't tested it.

Did you look at the VBA Code I posted on the QueryDef version?


----------



## OBP (Mar 8, 2005)

This version has the QueryDef added using the Like * for comparison and as you can see finds 3 Records that include Arabic and English in the Spoken langauge.
The original query has now been overwritten. So to directly compare them in the same database you need a separate query and Search results Form.


----------



## OBP (Mar 8, 2005)

I have tested it and it does add a few bytes to the database each time that you run a Search.


----------



## OBP (Mar 8, 2005)

With this version I have got everything working down as far as Experience using the QueryDef method.
However the search for more than one item in then list is still a bit problematic.
A Candidate that has 
Administration;HSE;IT;Proj Management
as their experince would not be found if you look for Administration and Proj Management due to the data in between the 2 items.
It may be better to search for them Individually, which can be done in the VBA if required.


----------



## hollinshead (Aug 5, 2009)

What i have decieded to do is to completely change the way in which the database works. As you advised that value lists creates problems, i have decieded to use lookup tables for all of the fields where vlaue lists were previously used. Do you think this will make the search a lot easier. I have attatched the ammended database if you have time. See what ya think. Thanks anyway OBP, im extremely grateful for your help.


----------



## OBP (Mar 8, 2005)

I notice that you haven't yet set up any Relationships between your tables. Also your new Tables should have Autonumber "ID" fields, they should not all be called ID as that becomes very confusing for Access, it is better to name them meaningfully like ProfEXpID for the Professional Experience table.
Breaking out these tables does not necessarily make the searching easier but it does make maintaining the Combo box lists much easier. So you should now develop Forms for Updating each of those tables.


----------



## hollinshead (Aug 5, 2009)

Thanks for your support, i will get on that straight away


----------



## hollinshead (Aug 5, 2009)

Hi OBP, As you advised earlier that what was essentially for my database to work is to normalise the tables and create relationships between them, which i have done. Now that i have adopted this approach is it possible to still have the same layout as previous with the multi select list boxes on the search form and also the same method to input a new candidate into the DB, as like i said im not sure whether you can have the same method as before using multi select list boxes. Any Ideas? I have attatched the ammended database if that makes it any easier. Thanks for your patience and support on this.


----------



## OBP (Mar 8, 2005)

That must be the longest Zip file name I have seen on here .
I will take a look.


----------



## OBP (Mar 8, 2005)

Can you format it in Access 2003 please?


----------



## hollinshead (Aug 5, 2009)

yes i must admit it is a long one. Sorry i forgot about the format ill get it changed. Ill change the name as well. Its just ive been using copies to keep the original backups.

Heres the ammended 2003 format anyway. Thanks again


----------



## OBP (Mar 8, 2005)

Looking at your current design with a separate "Sub table" for all of the various options I don't think that your old design of data input form or Search form is going to work.
I didn't realise that you were going to use Many to Many linked tables for that data as that changes the way that the data can be entered as well as the way it is searched.
So where do you want to start?


----------



## hollinshead (Aug 5, 2009)

I know my situation is a little complex, and i wasnt sure what the best way to go about it. So let me get my ehad around this, we cant use unnormalised tables as the search for more than one option in a combo box wouldnt be picked up. Using the normalised tables we are restricted as we cannot display the data say for example a candidates spoken languages or qualifications in one list or combo box. Apart from using sub forms for all these fields to display and inputting data, is there another way?

I was thinking of how we can go about trying to sort this out. I know this probably wont work, but ill run it by you anyway. If we set up a query by form and link the list boxes to the criteria of the query in the related places, could we then use

Docmd.Open Form, whereCondition:= 'The query'

However the problem i have come across with this is that if they are left blank, then it will search in the query for records that are blank in that field.


----------



## OBP (Mar 8, 2005)

Luke, I had the search working quite well on your original version in the last database that I posted.
But my concern was that you should have tables for the "Selectable" combo items.
Now that you have added Many to many tables the only way to handle Input of the Multiple Items is using small Subforms. I have created one for the Professional Experience as an example if you want to see it.
So do you want to continue with the current version or would you like to go back to your original version, but import all of the Sub tables for the selectable items?
I would not worry about the Search right now so much as the Input of the data.
There is one other way that you could have multiple selections and that is to have them already in the Sub table, for instance for languages you could have
English
English & French
English & French & Arabic
French
Arabic
Arabic & French
etc.


----------



## hollinshead (Aug 5, 2009)

Okay ill concentrate on the method if input at this point. With what option to use, the last option mentioned maybe be okay for the languages and things, but some of the origninal fields in the list boxes had options for 20 different things such as qualifications, so it would be a big job for that one. The original option, even though this was the layout i needed, isnt going to work with the list boxes, unless we use the "Like' cause, which wouldnt really work if some of the data was quite a like but wasnt one of the options selected. The option that i can then see working is the sub form option, which may not look at good, but if it works then I can worry about the rest later.
If you could send me that example you created that would really help. Just to make sure that were on the same lines. Anyway thanks again OBP,


----------



## OBP (Mar 8, 2005)

Here is the version with the Subform (tblCandidatesProfessionalExp Query) added, note that I have added the CandidatesID to the frmAllCandidatesDetails form where I have added and tested it to provide a Link for the subform.
It currently has 2 entries for the first record and you use the Vertical Scrollbar to view or add the other records.


----------



## hollinshead (Aug 5, 2009)

Thanks very much for your time and efforts on this. I have managed to get to where i need. Your support, patience and efforts on this have been tremendous and i am truely greatful for your time. Thanks again, couldnt of done it without you.
regards
Hollinshead


----------

