By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,183 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Please help me!!!!!

P: 1
I've been struggling for a few weeks over creating a database. I'm trying to create a way to track work orders placed and completed and who we assigned to the jobs. My biggest issue I've run into is - I can assign anywhere from 1 to 30 people on a job. I need to be able to query by individual employees and also would like to print a report showing all employees on a particular work order. When I used a multi-select list to choose my employees from - it returned only the first employee picked from the list. I can't get all the other ones to show up. And would I be able to query by employee if it was the 3rd of 4th or 25th person I assigned to the job? Any help you can give me would be greatly appreciated. Newbie here!!! Thank you
Dec 20 '07 #1
Share this Question
Share on Google+
2 Replies


P: 68
I've been struggling for a few weeks over creating a database. I'm trying to create a way to track work orders placed and completed and who we assigned to the jobs. My biggest issue I've run into is - I can assign anywhere from 1 to 30 people on a job. I need to be able to query by individual employees and also would like to print a report showing all employees on a particular work order. When I used a multi-select list to choose my employees from - it returned only the first employee picked from the list. I can't get all the other ones to show up. And would I be able to query by employee if it was the 3rd of 4th or 25th person I assigned to the job? Any help you can give me would be greatly appreciated. Newbie here!!! Thank you
I think you have to use code to take advantage of a multi-select list, and that without this it'll only ever return the first value selected. At least, that how I use them. Typically I build a query string within the AfterUpdate event for the listbox, and then e.g. set the record source of a form or control using that string, or replace an existing query (i.e. the record source for a report) with this new definition using querydef. My AfterUpdate event would usually look something like this;

Expand|Select|Wrap|Line Numbers
  1. Dim ctl As Control
  2. Dim varItm As Variant
  3. Dim ArgCount As Long
  4. Dim MySQL As String
  5. Set ctl = Me.lstEmpID
  6. ArgCount = 0
  7.  
  8. MySQL = "Select * FROM tblEmployees WHERE"
  9.  
  10. For Each varItm In ctl.ItemsSelected
  11. If ArgCount = 0 Then
  12. MySQL = MySQL & " EmpID = & ctl.Column(0, varItm)"
  13. Else
  14. MySQL = MySQL & " OR EmpID = & ctl.Column(0, varItm)"
  15. End If
  16. ArgCount = ArgCount + 1
  17. Next varItm
  18. Ďand then use this final MySQL to create a new query or as a recordsource
HTH
Kevin
Dec 21 '07 #2

100+
P: 274
Kevin is rite; this is the only way you can achieve this task. I also had a very bad time doing same task last week. But I tried same thing and it worked.
Good luck
Dec 21 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.