Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2007
    Posts
    45

    Unanswered: Combo Box problems in Access 2003

    I have a form that has 2 combo boxes that I need help with - one combo box is populated with employee names that are assigned to a workorder, and the other is populated with employees who are requesting the work be done - the form is based on the work order ID number which is an autonumber field.
    These bound combo boxes are pulling from queries based on two different tables - tblAssignedTo, tblRequestor. Here is the issue:
    1. Like most companies, from time to time, an employee leaves.
    2. We want the employee to continue to show up on those records for which they either were assigned to or requested a work order, however, we don't want them to continue to show up in the combo box as an option from which to choose when generating a new work order.
    3. So, I added a yes/no field called "inactive" to the tblAssignedTo and tblRequestor. If the employee quits, this box is checked.
    4. Then I changed the query for each combo box, added the Inactive field to the query, and put "0" as the criteria.
    5. This works as far as the inactive employees no longer show up in the drop down list, however, if you bring up one of the work order tickets in the form that these combo boxes are on, and the employee either was assigned to or requested work be done on this ticket, their name no longer shows up in that field and it looks like the field is blank. To find out who the requestor or assignee is on the work order, you have to look in the tables or print up the workorder report.

    Is there a way to limit what shows up in a drop down box when you are generating a new work ticket, but if you are going back into that record to update it or look at its history, that you can tell who was assigned or requested the ticket if they are inactive.

    Thank you for your help.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The best way would be to model it like this:

    People. Employees. Leavers.

    One table for all people and then 2 queries; one for employees and one for leavers (using your bit field).

    Your combos can therefore be populated using the respective queries and the reports etc can be based on the people table directly.

    Hope that makes sense
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Jill, I'm not sure how George's method would work, because it sounds like you want the form to show ex-employees, but not allow ex-employees to be selected. That means you need those ex-employees available in the combo box. As I saw your question, you were not asking about reports.

    I have about 3 ideas of ways to work with this situation.
    1. If the form is open to a new record, then exclude the ex-employees from the combo box. Generally you can test the field that is used as the key for a record to see if it is Null. If it is Null, then you are in a new record. This would be done in the OnCurrent event.
    2. Append, in the RowSource query, an "-EX" to the end of ex-employees names. Then you would have a policy for the users to not select these names with the "-EX" on the end of their names. You could follow that up with a BeforeUpdate event to check for the "-EX" on the end of the employee name. There should not be an ex-employee being selected from this list. They should only be being shown in the combo box. BUT, to allow for corrections, you could give the user the ability to select this ex-employee anyway.
    3. Using a UNION query, sort all the current employees to the front of the list (give them a sortorder field with an "A" in it). And, put all the ex-employees to the end of the list (with a sortorder of "B"). In the UNION query, sort by sortorder, then name. You could also put the "-EX" on the end of the ex-employee names here too.

    I do like George's idea of having only one table, but two queries for the requestor list and the assigned to list. I would put 2 new fields in the employee table for "Can be a requestor" and "Can be assigned". Then you can base your requestor and assigned queries on the employee table, and use these two fields to generate the correct list.
    Last edited by GolferGuy; 10-21-07 at 12:42.

  4. #4
    Join Date
    Apr 2007
    Posts
    45

    Thank you for suggestions

    Thank you George and Vic for your time. I have some comments and then some more questions on one of Vics suggestions:

    1. Creating one table to hold both requestor and assigned would not work - we have over 1500 employees in our company that can be a requestor, but only a handful that can be assigned to the work ticket - which is why I created the two seperate tables to begin with. Also, this system has been in use for over 8 months, and to reprogram the tables at this time (and all the reports and forms that go with it), would just be too time consuming.

    2. Because the goal is to limit the number of entries that show up in the drop down list (my boss HATES lengthy lists), I don't think Vic's suggestion #2 or #3 would accomplish my goal.

    However, Vic, I really liked your suggestion #1. But, I am a VBA idiot, and not sure how to implement your suggestion in the OnCurrent field. Can you expound on this suggestion?

  5. #5
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    1. If the form is open to a new record, then exclude the ex-employees from the combo box. Generally you can test the field that is used as the key for a record to see if it is Null. If it is Null, then you are in a new record. This would be done in the OnCurrent event.
    You will need two queries for each of the two tables, one with the ex-employees and one with the ex-employees omitted from the returned records. Let's call these two queries qryAssignedToWithEx and qryAssignedToWithOUTEx (qryRequestorWithEx/qryRequestorWithOUTEx). Now within the OnCurrent event, you can add this code.
    Code:
      If IsNull(Me!WorkOrderID) Then
        Me!cboAssignedTo.RowSource = "qryAssignedToWithOUTEx"
        Me!cboRequestor.RowSource = "qryRequestorWithOUTEx"
      Else
        Me!cboAssignedTo.RowSource = "qryAssignedToWithEx"
        Me!cboRequestor.RowSource = "qryRequestorWithEx"
      End If
    You will need to make sure that all the names in this code are changed to match the names of fields and queries that you are using.
    Please, let us know how this works.

  6. #6
    Join Date
    Apr 2007
    Posts
    45
    Vic:

    Thanks again for your suggestion. Althought I wasn't able to implement it, it got me on the right track to figure out what I needed to do. I wasn't able to implement it on the On Current Property because as soon as one field is filled out, then the WorkOrderID is populated. Therefore, it would only work for one of the combo boxes and that is only if someone entered all the information by strictly using the tab controls.

    So. this is what I did, I have tested it and it is working.

    In the On Got Focus property of the combo box, I did the following:

    If Me.ReqCombo.Value <> "" Then
    Me.ReqCombo.RowSource = "qryRequestor"
    Else
    Me.ReqCombo.RowSource = "qryRequestorInActive"
    End If
    Me.Refresh

    Thanks again for getting me on the right track!

  7. #7
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Hi Jill,

    Glad you got it to work. Being the simpleton that I am, I have done in the past what you are asking about and did it with no coding at all. I have a table with all the Job Names and one column with a checkbox for No Show. Then, in the query for the ComboBox that holds all the Job Names I have it set to show all the names where the CheckBox is set to "0". That's all I did. Now, the JobName is still in the database, doesn't show in the ComboBox and Does still show in the Forms, Tables where the JobName was initially used. That's about it. Seems you were right at that point in the beginning.

    have a nice one,
    BUD

  8. #8
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Jill,
    The OnCurrent event ONLY happens before anything happens as far as the user is concerned. So, when you think this would only work for one of the combo boxes, is not how the events in Access work. The OnCurrent will take care of both combo boxes at the point that a NEW (blank) record is presented to the user to fill in. OnCurrent does not get executed again until another record is presented to the form, and that could be either a new (blank) record, or one of the existing records in the table.

    I don't know why the OnCurrent did not work for you, but I do know that OnCurrent is what should have worked.

    Glad you did get what you wanted to work to work though.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •