Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Not Picking Right Employee from Combo List (2000)

    Hi,
    I have a table that lists employees. I have a form where I have a combo box that gets its data from the employee table. In the emp table I have 2 employees with the same last name. ex. Smith Bob, Smith John. When I select Smith, John, it always give me Bob. I'm assuming because he's first on the list and the combo box doesn't pick the right ID. I don't know how to fix this. In the emp table, I have an ID field that's an autonumber. My combo box code in the row source is: SELECT [tblEmployeeInformation].[lngEmpID#], [tblEmployeeInformation].[strLastName], [tblEmployeeInformation].[strFirstName] FROM tblEmployeeInformation ORDER BY [tblEmployeeInformation].[strLastName], [tblEmployeeInformation].[strFirstName];

    Thanks for any help.
    Deb Buck

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not Picking Right Employee from Combo List (2000)

    Is the combo box bound to a field in the record source of the form?

    If so, what is the Bound Column property of the combo box and to which field has it been bound?

    Otherwise, where and how do you use the result of the combo box?

  3. #3
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not Picking Right Employee from Combo List (2000)

    Hans,
    Yes, Bound Column, says 1 and it's bound to the lngEmpID# in the table. I used the combo wizard to create this box. This database basically has 3 tables. Emp, Certification, and a Junction table that has the primary key from the other 2 tables. I created a form based on the junction table and made the EmpID# and Cert# a combo box to make it easier for data entry. The combo boxes are so I can show the Emp Names and Certification instead of the autonumbers.
    Thanks for your input and help.
    Deb

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not Picking Right Employee from Combo List (2000)

    That looks allright, I don't see why the wrong EmpID would be selected. Could you post a stripped-down version of the database?
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>Attach the zip file to a reply.[/list]

  5. #5
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not Picking Right Employee from Combo List (2000)

    Ok Hans,
    I have been doing some more testing after reading your posts. Because I actually have another database this was happening so when it happened agained I figured I was doing something wrong. Ok, it is storing the right information, but when I run a particular report is when it will list wrong. Look at both reports and you'll see what's happening. Thanks for your help.
    Deb
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not Picking Right Employee from Combo List (2000)

    The cause of your problem is that your report rptListofFOEbyEmployee is grouped by strLastName only. In the group header, the data from the first detail record are available, so the first name of the first detail record for the specific last name is displayed.

    Open the report in design view, and activate the Sorting and Grouping window. Although you can only select field names from the dropdown list, you can enter expressions too in the Field/Expression column. Replace the first entry strLastName by the expression

    =[strLastName] & "," & [strFirstName]

    Alternatively, set the report to group on strFirstName too, and display the first name in the strFirstName header, not in the strLastName header.

  7. #7
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not Picking Right Employee from Combo List (2000)

    Hans,
    That worked and thanks again for teaching something new in Access.

    Deb

  8. #8
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not Picking Right Employee from Combo List (2000)

    Hans,
    I have another question. I had mentioned before that this was happening in another database. So I figured I'd be able to use the same steps below, but it's not working in this other database and I've concluded it's because I'm using an unbound form, with an unbound combo box. I'm using the form in place of a parament query so the user can select items from a list instead of having to type them in. So it says it's Bound Column is 1, but I don't have anything in the control source because it's an unbound form. Same thing, I'm picking an employee from an employee table.

    Any thoughts?
    Deb

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not Picking Right Employee from Combo List (2000)

    See <post#= 277639>post 277639</post#> above...

  10. #10
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not Picking Right Employee from Combo List (2000)

    Here you go. And thanks again.
    Deb
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not Picking Right Employee from Combo List (2000)

    You should use the employee ID, not the last name to select the records to display. The last name is ambiguous. You will have to make several changes:
    1. <LI>Open qryHoursByEmpOrJONAllDates in design view.
      <UL><LI>Add lngEmpID from tblEmployeeInformation to the query grid.
      <LI>Move the condition from strLastName to lngEmpID (cut and paste).
      <LI>Save and close the query.
    <LI>Open frmHoursByEmpOrJONAllDatesDialog in design view.
    <UL><LI>Select cboEmpName.
    <LI>Change the Row Source to tblEmployeeInformation.
    <LI>Change the Column Count from 2 to 3.
    <LI>Change the Column Widths to 0";1";1".
    <LI>Select cboJONList.
    <LI>Set the Limit to List property to Yes.
    <LI>Close and save the form.[/list]<LI>Open rptHoursByEmpOrJONAllDates in design view.
    <UL><LI>Select the imaginatively named text box Text21.
    <LI>Change the Control Source property to
    ="Total Hours for " & [strLastName] & ", " & [strFirstName] & " and " & [strMachineJON] & " = " & Som([SumOfintHoursWorked])
    <LI>Save and close the report.[/list][/list]Phew!

  12. #12
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not Picking Right Employee from Combo List (2000)

    Thanks Hans,
    That worked but my combo box isn't alphabatized. Is there a way to do that. I went into the table and created a custom sort, but that didn't help. Once again thanks!
    Deb

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not Picking Right Employee from Combo List (2000)

    In your sample database, everyone had last name SNYDER, so I didn't notice that the names were not ordered alphabetically <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    It doesn't help to set a sort order on the table, you must set a sort order on the row source of the combo box. Either set it to an SQL string again, this time including the ID:

    SELECT lngEmpID, strLastName, strFirstName FROM tblEmployeeInformation ORDER BY strLastName, strFirstName;

    or create a query with that SQL and use the query as row source.

  14. #14
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not Picking Right Employee from Combo List (2000)

    Thanks Hans, that worked. I thought I tried that, but must not have had it exactly as you. Thanks a ton. <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

    Deb
    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

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