Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Location
    Little Rock, Arkansas, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo Box Confusion (Access97)

    I'm sorry this is a long story, but I don't know how to shorten it any more.

    I have a database that tracks production in our plant. Production is logged by shift by machine. We had a flat table with fields for date, shift, machine, item produced, units produced, and 15 fields for the units scrapped due to each of 15 different scrap classification codes.
    I thought it would be much simpler to break out the data into 3 tables:
    1. tblShifts: Date, Shift, and a primary key DSID
    2. tblProduction: DSID, units produced, machine, and item
    3. tblScrap: DSID, machine, item, units scrapped, and scrap code.

    This made my reports and queries much simpler. To prevent errors, I populated the first table with every possible shift for the entire decade (D shift works Thurs-Sun in our plant, but I had records showing production from D shift on Mondays and Tuesdays, I want to prevent these data entry errors).

    The problem is that I have a data entry form where they need to enter the production records (30+ per shift, 3 x a day, every day). They need to enter the DSID, which is a 5 or 6 digit auto number. I used a combo box with 3 columns (DSID, Date, Shift) with the Key hidden. When the box drops down, you can see date and shift, but if it is not dropped down, you don't know what shift it is. So I put in a second combo tied to the same control to display the shift. But the list is sorted ascending by date and shift and starts with 1/1/2000 every time. No one is going to scroll through 5000+ records to find the right one, so they just want to punch in 11-8-02 and hit tab, which results in a "not in list" error message. Then they want to go to the next combo and punch in the shift ("A") and hit tab, which takes the first "A" shift sorted ascending, which is 1/4/2002.

    I keep telling myself that this has to work, Access is designed to be a relational database, but I can't figure out why everything worked easier when it was a flat database.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Combo Box Confusion (Access97)

    First of all, using a combo box with 5000+ entries doesn't provide reasonable performance in most situations. You might want to limit the combo row source to the previous 60 days or something like that, and you might consider creating an expression as part of the row-source that concatenates the shift to the right of the date so it appears as a single field. That way you could immediately set the DSID as they type, and it would be visible after they left the combo. Otherwise, if you continue with the two combo approach, you need to change the row-source for the second combo on code once the first combo has been updated. There are numerous threads on how to do that in the recent past.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Box Confusion (Access97)

    Well I know you're right in way, flat file databases are much easier to understand, but you hit the nail on the head really when you said they made your report and queries much simpler, that's one advantage of changing your flat file database into a rational database. The other advantages are a saving of space and ease of maintenance when you change some data!

    Combo boxes are quite difficult to get your head around at first, but if you persevere you'll soon get the hang of them. What you need to do is create a text box next to the combo box with your date and shift in, lets call it cboShift, and the text box txtShift. In this text box txtShift put the following code where cboShift is the name of your combo box (if it's not already called cboShift).

    =[cboShift].[Column](2)

    Now if you change the number in brackets after

Posting Permissions

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