Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Combo box on MS Access form

Posted on 2011-02-22
13
Medium Priority
?
446 Views
Last Modified: 2012-08-13
I have a form based on a query, it is a basic Support Call logging form. I have a combo box on the form and I can select Engineers names within that combo box and change the data shown on the form. I have done this with a criteria in the select query ([Forms]![Call Log Listing]![engineer]).
I would like to be able to enter "ALL" in the combo box so as to show all records on the form regardless of Engineer.
Any ideas gratefully rcvd
Iain
0
Comment
Question by:iainmacleod
  • 7
  • 6
13 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34951864
see this link for instruction
How to add '(all)' to a combo box or a list box control in an Access 2000 application

http://support.microsoft.com/?kbid=210290
0
 

Author Comment

by:iainmacleod
ID: 34952452
Thanks for that, however I am struggling with it. I am using Access 2010??
Does that make a difference?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34952633
No..

post the rowsource of your combo box..
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:iainmacleod
ID: 34952901
I must be missing something basic in this, my row source type is AddAllToList and my row source is a Table that contains a list of engineers names. I dont understand where to paste the AddAllToList function on the form....
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34952966
use a query as the rowsource of the combo box
something like this

select engineernames from tableName

what is the name of the table and field names that has the engineer names?
0
 

Author Comment

by:iainmacleod
ID: 34953174
Ok got that working: SELECT Username from ENGINEERS
0
 

Author Comment

by:iainmacleod
ID: 34953252
Still not sure about the rest of it though. Where do i put the code that allows "ALL"??
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34953288
to add <All> to the selection, you have to use a union query as described in the link posted above.

SELECT Username from ENGINEERS
Union
SELECT '<All>'  from ENGINEERS
0
 

Author Comment

by:iainmacleod
ID: 34953450
I am afraid I am still not getting it.
I now can select "ALL" in my combo box, but I do not get any records displayed, I change to an engineers name and I get his jobs.
Must be missing something or just being a little dim. Thanks for your help so far.
Iain
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34953501
post the SQL statement of the query where you are using the combo box as filter..
0
 

Author Comment

by:iainmacleod
ID: 34959569
Sorry, i am not getting this:
I have a select query that my form that contains the combo box is based on:

SELECT [Call Logging].*, [Call Logging].[Allocated Engineer]
FROM [Call Logging]
WHERE ((([Call Logging].[Allocated Engineer]) Like [Forms]![Call Log Listing]![engineer]));

My row source of the combo box is:

SELECT Username from ENGINEERS
UNION SELECT 'All'  from ENGINEERS;

When I select an Engineer in the combo box my form displays all of that engineers bookings fine, When I select ALL in my combo box I would like to see all engineers bookings.

Thanks for your patience
Iain
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 34960390
try this query, copy and paste


SELECT [Call Logging].*
FROM [Call Logging]
Where IIF([Forms]![Call Log Listing]![engineer]="All",[Allocated Engineer], [Call Logging].[Allocated Engineer] = [Forms]![Call Log Listing]![engineer])
0
 

Author Closing Comment

by:iainmacleod
ID: 34962169
Many thanks for all of your help... That worked a treat.
Best regards
Iain
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

886 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