Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2003
    Posts
    67

    Unanswered: Combo Box Adds Selection to Table

    I have a combo box on a form that allows the user to select the employee's last name. The RowSource of the box is SELECT EmployeesQry.LastName FROM EmployeesQry;

    It's based on a query instead of the EmployeesTbl because that was the only way I could get the employees' names to show up instead of their ID number (Smith vs. 31).

    I realized after I'd populated the db with 80+ records that every time I made a selection in the Employees combo box, it added that record to the EmployeesTbl. The result is that I have, for example:

    ID....LastName
    31....Smith
    32....Smith
    33....Smith
    34....Smith
    17....Cook
    18....Cook
    19....Cook

    etc. When the drop-down box is opened, each name appears once for every record associate with that name.

    Apparently using the query was a bad idea. Anyone have a GOOD idea?

    Let me know if more info is needed. Thanks!

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Question Re: Combo Box Adds Selection to Table

    Originally posted by Jaycee
    I have a combo box on a form that allows the user to select the employee's last name. The RowSource of the box is SELECT EmployeesQry.LastName FROM EmployeesQry;

    It's based on a query instead of the EmployeesTbl because that was the only way I could get the employees' names to show up instead of their ID number (Smith vs. 31).

    I realized after I'd populated the db with 80+ records that every time I made a selection in the Employees combo box, it added that record to the EmployeesTbl. The result is that I have, for example:

    ID....LastName
    31....Smith
    32....Smith
    33....Smith
    34....Smith
    17....Cook
    18....Cook
    19....Cook

    etc. When the drop-down box is opened, each name appears once for every record associate with that name.

    Apparently using the query was a bad idea. Anyone have a GOOD idea?

    Let me know if more info is needed. Thanks!
    Did you intend for the combo box to be bound to the table or is it just for looking up something?

    Gregg

  3. #3
    Join Date
    Sep 2003
    Posts
    67

    Re: Combo Box Adds Selection to Table

    Originally posted by basicmek
    Did you intend for the combo box to be bound to the table or is it just for looking up something?

    Gregg
    It's just for looking up employees' names, then it's intended to store the selection in another table.

  4. #4
    Join Date
    Nov 2003
    Posts
    267
    With out see you DB

    It sound like you are doing one of two things. Either you are not adding it into the second table and are really adding it back into the orginal table. OR you have the Combo box directly bound to the to the query trough a recordset control which is automatically adding the duplicate record when you are select.

    If these thoughts don't help. Try posting a sample of your DB and we can take a closer look.
    S-

  5. #5
    Join Date
    Sep 2003
    Posts
    67
    Originally posted by sbaxter
    With out see you DB

    It sound like you are doing one of two things. Either you are not adding it into the second table and are really adding it back into the orginal table. OR you have the Combo box directly bound to the to the query trough a recordset control which is automatically adding the duplicate record when you are select.

    If these thoughts don't help. Try posting a sample of your DB and we can take a closer look.
    S-
    The zipped db (Access XP) is attached. Thanks much!
    Attached Files Attached Files

  6. #6
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by Jaycee
    The zipped db (Access XP) is attached. Thanks much!
    A question and hopefully an answer:

    Did you intend to bind the Employee field in the form to the LastName field of the Employees table (its doing this through the query).
    If you are just trying to store the Employee in the EventDetails table but display the last name as a selection, then bind the form field to the EmployeeID field of the query (ControlSource). In the RowSource for the Employee form field, delete the text that is there and click the elipse (...) beside it. In the query design grid pull in the Employees table and drag down the EmployeeID and LastName fields. Select sort ascending on the LastName field. Right click in the query window and select properties. In the Unique Values property, select yes. Close the properties page and close the query design grid. Select yes to keep the new RowSource settings.
    Change the ColumnCount setting to 2" and set the columnWidths to 0";1"

    I did this with your form and it seems to work as I imagine you intended.

    Gregg

  7. #7
    Join Date
    Sep 2003
    Posts
    67
    Originally posted by basicmek
    A question and hopefully an answer:

    Did you intend to bind the Employee field in the form to the LastName field of the Employees table (its doing this through the query).
    If you are just trying to store the Employee in the EventDetails table but display the last name as a selection, then bind the form field to the EmployeeID field of the query (ControlSource). In the RowSource for the Employee form field, delete the text that is there and click the elipse (...) beside it. In the query design grid pull in the Employees table and drag down the EmployeeID and LastName fields. Select sort ascending on the LastName field. Right click in the query window and select properties. In the Unique Values property, select yes. Close the properties page and close the query design grid. Select yes to keep the new RowSource settings.
    Change the ColumnCount setting to 2" and set the columnWidths to 0";1"

    I did this with your form and it seems to work as I imagine you intended.

    Gregg
    Gregg,

    Thanks! Instead of going about the usual procedure for creating a combo box, I had to monkey around with this box a good bit in order to get the Alpha Buttons macro to work properly AND to get the LastName stored as text instead of EmployeeID number. Did those work OK when you made your changes?

    I'll go ahead and follow your instructions (thanks for the detail!); in the meantime, if there's anything I need to know re the macro or text vs. ID issues, please let me know.

  8. #8
    Join Date
    Sep 2003
    Posts
    67
    Originally posted by Jaycee
    Gregg,

    Thanks! Instead of going about the usual procedure for creating a combo box, I had to monkey around with this box a good bit in order to get the Alpha Buttons macro to work properly AND to get the LastName stored as text instead of EmployeeID number. Did those work OK when you made your changes?

    I'll go ahead and follow your instructions (thanks for the detail!); in the meantime, if there's anything I need to know re the macro or text vs. ID issues, please let me know.
    Gregg,

    Well, I wasn't successful. I followed the instructions, but now it's adding the EmployeeID number to the EmployeesTbl where before it was adding the name (it also returns the ID number in the EventDetailsTbl). Also, the Alpha Buttons macro returns a message "There are no records for that letter," which I guess is because it's looking for a name in a list of numbers.

    Where'd I go wrong?

    Jaycee

  9. #9
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by Jaycee
    Gregg,

    Well, I wasn't successful. I followed the instructions, but now it's adding the EmployeeID number to the EmployeesTbl where before it was adding the name (it also returns the ID number in the EventDetailsTbl). Also, the Alpha Buttons macro returns a message "There are no records for that letter," which I guess is because it's looking for a name in a list of numbers.

    Where'd I go wrong?

    Jaycee
    Did you change the ControlSource in the combo boxes property to EmployeeID? In the query, that field was bound to the EventDetails table so it should have saved it there and nowhere else.

    When I ran your macro buttons, they all returned good info except for when your message box popped up.

    If you get rid of all the duplicates in the employee table, you will only have one of each in the list with the RowSource set that way. I noticed that you have an autonumber field set as the primary key. This is going to allow as many additions to the employee table of the same name as you have disk space for. I recommend setting the key to some other unique value that has to be manually set and not autonumber. You might even want a multipart key consisting of LName, FName and phone or something like that. Where I work each employee is assigned a unique employee number and that's what we use in our database.

    The RowSource for the combo box should have come up looking like something like this:

    SELECT DISTINCT EmployeesTbl.EmployeeID, EmployeesTbl.LastName FROM EmployeesTbl ORDER BY EmployeesTbl.LastName;

    Also, since the lastname field was the only one coming from another table, you shouldn't need to use a query. Just use the EventDetails table as the forms Record Source.

    Keep pluggin. It'll work out.

    Gregg

  10. #10
    Join Date
    Sep 2003
    Posts
    67
    Originally posted by basicmek
    Did you change the ControlSource in the combo boxes property to EmployeeID? In the query, that field was bound to the EventDetails table so it should have saved it there and nowhere else.

    When I ran your macro buttons, they all returned good info except for when your message box popped up.

    If you get rid of all the duplicates in the employee table, you will only have one of each in the list with the RowSource set that way. I noticed that you have an autonumber field set as the primary key. This is going to allow as many additions to the employee table of the same name as you have disk space for. I recommend setting the key to some other unique value that has to be manually set and not autonumber. You might even want a multipart key consisting of LName, FName and phone or something like that. Where I work each employee is assigned a unique employee number and that's what we use in our database.

    The RowSource for the combo box should have come up looking like something like this:

    SELECT DISTINCT EmployeesTbl.EmployeeID, EmployeesTbl.LastName FROM EmployeesTbl ORDER BY EmployeesTbl.LastName;

    Also, since the lastname field was the only one coming from another table, you shouldn't need to use a query. Just use the EventDetails table as the forms Record Source.

    Keep pluggin. It'll work out.

    Gregg
    I'm just about plugged out! I had started out with the EventDetailsTbl as the form's recordsource, but couldn't get the macro to work because LastName is not a field in that table -- only in the EmployeesTbl. Another dBforums expert suggested the query, and I thought it was working till I realized it was adding each new record to the EmployeesTbl.

    I agree it would be better to use the EventDetailsTbl as the record source, but again -- it doesn't have a LastName field, and the macro is looking for that. Do I need to add one there in addition to the one in the EmployeesTbl?

    The RowSource for the box reads exactly as you noted.

    When I change the Control Source to EmployeeID, the drop-down is a list of IDs instead of names. I got rid of all the duplicates in the EmployeesTbl, then added several new records. When I checked the table again, it was adding Employee ID numbers to the list where before it had been adding the names.

    BTW, number vs. text is an issue with other fields as well. I very much need to see the stored text in the tables and queries (and consequently in reports) instead of ID numbers since, by themselves, they're pretty meaningless.

    Does the EmployeesTbl need an ID number (auto or manual)? Would it simplify things to leave it out? Seems most of my problems relate to autonumbers.

    Also, how come the macro buttons work for you, but not for me? I get the message no matter which letter I select.

    Thanks for your patience and assistance!!!

Posting Permissions

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