Using Combo Box to Query (1 Viewer)

[kannon]

Hello. I am new to Microsoft Access 2007 - learning by trial and error. I do not yet know how to do programming and was hoping to do the following without programming. Would appreciate any help/insight.

Essentially I am trying to make a form that has three different combo boxes. These are key fields from a table I have of all company employees. The key fields I want to query on are Division, Position, and Grade. Division has a choice of 5, Position a choice of 3 entries, and Grade a list of 4 choices.

I got this to work in Excel using the Auto Filter but now am trying to figure how to do this in Access. I would like the user to pick any of these three combo boxes to use as sort criteria.

For instance, the total table list is 100 employees. When you select Org A from the Org Combo Box, then you have only 50 employees that meet the criteria. If you select Engineer for Position you get 20 of the 50 and so for. Or you could start with Position and have 75 listings for Engineer that comes from multiple Divisions.

Hope this makes sense. I have just been unable to get this to work. I have a form made from the Table with all of the key fields (name, address, ..), the combo boxes for Division, Position, and Grade but can't figure how to link the combo box selections to a query and then update the form.

I know I sound like a beginner - I am!! Any help would be appreciated. I told my boss I would have a simple form to show him on Monday. I may have leaped before I looked.

Thanks!!

Kannon

 

[Uncle Gizmo]

Have a look at this download and Video explanation HERE:

 

[JIMR]

Uncle Gizmo, I am trying to do the same thing. I have tried to use the link but our IT department blocked this as Social Networking. Is there another reference somewhere for this approach to filtering a forms data?

Thanks

 

[Uncle Gizmo]

Demo *.mdb file available HERE:

I have reproduced some slides taken from a PowerPoint presentation. This first one is a picture of the working form. Most people get to the stage of adding three combo boxes or more, the combo boxes control the selection appearing in the subform, however they have a problem when a combo boxes left empty, instead of the expected result of returning all available options for that combo box, none are returned.

You may notice in the Picture below that the combo boxes display the word: <ALL> ... This is added by using a union query, union queries are not available from the MS Access query designer grid, you have to write them manually.
.

.
..
The Picture below is of the same form in design view, the left hand combo box property sheet has been opened showing the combo box row source displayed in the zoom window. Notice it is basically a select query with the addition of the word "distinctrow" and the final part which begins union select.
.

.
..
Below is the combo box row source (incorrectly marked as recordsource in the pictures below)
.

.
..
In this picture below you can see the parts which have been added to a basic select query, "distinct row" just make sure the query only returns one item, where the table may have several items that are the same name. And below, the "union query" which adds a Star "*" to the left hand column of the query results and the text"<ALL>" to the right hand column of the query results. However the combo box only displays "<ALL>" because although the first column is present in the combo box it is hidden by the combo box settings.
.

.
..
The picture below just shows a basic select statement, it's not used in the example it's here to show you what the union query is based on.
.

.
..
The picture below shows the query designer grid for the query "qryMup" there's only enough room to show the criteria for the first to columns, but the third column would be very similar to the first two if it was displayed. You may notice that this criteria contains a function.
.

.
..
Below is the full criteria of the first combo box, notice the function "fCboSearch"
.

.
..
Below is the full SQL of the query "qryMup"
.

.
..
The picture below is of the function "fCboSearch" basically what happens is the combo box result is passed in as the variable "vCboSearch" and processed. This function is designed to detect if the combo box is empty, in which my case it may contain a Null value or it may contain what is termed a zero length string "". If it contains either of those then they are replaced with the "*" which instructs the query to return all the results for that combobox election.
.

 

[JIMR]

This looks to be exactly what I am looking for. I will work to get this setup

Thanks, Uncle Gizmo!

 

[JIMR]

Uncle Gizmo, sorry to be a pest here but I am unable to get to the video instructions either because of the IT restrictions. is it possible to make this video available as a file on this thread?

 

[Uncle Gizmo]

>>>is it possible to make this video available as a file on this thread?<<<

Unfortunately this forum does not support embedded video, however this is a link to where the video his hosted.

http://www.viddler.com/explore/TonyHine/videos/108/fullscreen

 

[JIMR]

Thanks for posting the hosted site. It too is blocked so I will have to get this from my home PC.

 

[JIMR]

Uncle Gizmo,

I am having difficulty preparing a good select distinctrow/union statment for the form that I would like to filter using a series of combo boxes. This is my latest Sql statement (not a code guy) which is generating an error messege that states the number of columns do not match.
SELECT DISTINCTROW tbl_WorkOrders.WorkOrderID,tbl_WorkOrders.WODescription, tbl_WorkOrders.Requester, tbl_WorkOrders.Date/TimeCreated, tbl_WorkOrders.Date/TimeClosed, tbl_WorkOrders.comments, tbl_Assests.AssetID, tbl_Assests.Location, tbl_Assests.SubLocation1, tbl_Assests.SubLocation2, tbl_Assests.SubLocation3, tbl_WorkOrderStatus.WorkOrderStatusID, tbl_WorkOrderStatus.WOStatus, tbl_WorkOrderType.WorkOrderTypeID, tbl_WorkOrderType.WOType, tbl_ProblemCodes.ProblemCodeID, tbl_ProblemCodes.ProblemCode, tbl_FailureCodes.FailureCodeID, tbl_FailureCodes.FailureCode, tbl_ActionCodes.ActionCodeID, tbl_ActionCodes.ActionCode, tbl_Technicians.TechnicianID, tbl_Technicians.Technician FROM tbl_WorkOrders UNION SELECT "*","<ALL>" From tbl_WorkOrders

I am using a query to create the form based off of the Work Order Table.

tbl_WorkOrders
WorkOrderID (PK)
AssetID (FK)
WorkOrderDescription
WOStatus (FK)
WOType (FK)
Requester
ProblemCode (FK)
FailureCode (FK)
ActionCode (FK)
Technician (FK)
Date/TimeCreated
Date/TimeClosed
Comments

Not sure how to write the SQL Statement, do you have any suggestions?

 

[JIMR]

With the exception of the AssetID all other FK's may not accually be FK's. I placed them in the WorkOrders table and set the to lookup to their primary table via a combo box. I am mentioning this because I am not sure if it changes the way the Select statement should be written.

 

[Uncle Gizmo]

The combo boxes which specify the data to be queried should only return one or two columns. The union statement part of the query should match this, if there's Two columns it should fill both columbs. If there are more columns it should return a value for each column.

 

[JIMR]

If I am understanding you correctly it appears to me that I can not filter my form using the combo box method?

I would likely use up to 6 Different combo boxes to filter 6 fields but populate all of the remaining fields.

I only have 1 combo box that I am working with, is that part of the problem?

 

[Uncle Gizmo]

Download the example in this thread:

http://www.access-programmers.co.uk/forums/showpost.php?p=834832&postcount=4

 

[JIMR]

I have been able to get most of the combo boxes to work but on a form out side of the tabbed form that I need them to work in. I am positive that its due to the criteria that I am placing in the qry for the combo box. I am not sure how to get to the page index of the tabctl0. it is page index 4.
this is the last criteria that I tried to use:
Like fCboSearch([Forms]![frmWorkOrders]![Forms]![tabctl0]![Forms]![page4]![Forms]![frmtestcbosearch]![Combo5])

can you point me in the right direction on this one?

thanks

 

[Uncle Gizmo]

>>>I am not sure how to get to the page index of the tabctl0. it is page index 4<<<

I can only assume from this comment that you are assuming that the path to the control is affected by th Tab that it happens to be on. This isn't the case, it doesn't matter what Tab your control is on, you access it in exactly the same way as you would if it was on the form. However if the control is within a subform window, then there is a difference in the path.

 

[JIMR]

Your are right I am attempting to get this to work on a tabbed form and I did not explain that well enough. This is a form build using a query then I inserted the form into the tab. So since I am very green in access I am assuming this is a subform and I need to properly write the path and unfortunetally I do not know what that is. Can you assist?

 

[quinita]

Helo Uncle Gizmo


I used the Multiple combo boxes example to Query. That is working fine. The problem is that I also want to be able to add new records in the same form. When I had new records the table updates but the query doesn’t. So I can’t see the new records in my subform. How can I fix this problem?

Thanks
Qunita