Using Combo Box to Query

[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

 

[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

 

[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?

 

[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.

 

[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?

 

[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

 

[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