Results 1 to 15 of 15
  1. #1
    donnan33 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    52

    Combo Boxes on a From

    I have attached the part of my database I am having trouble with. I am trying to get the combo boxes to work as follows.



    when project number is clicked the client number is automatically put in client number field. and then the work order number that goes with project number picked is listed in work order number combo box.

    It works now from project# to workorder# but doesn't put in the client number?

    I have tried them separatley and they eash work. I am trying to do it all in one click. Maybe my code is wrong or i need more code?

    If anyone can help it would be greatly appreciated.

  2. #2
    dblife's Avatar
    dblife is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2011
    Location
    South Shields, UK
    Posts
    95
    can you save and upload in 2003 format?

  3. #3
    donnan33 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    52
    sure....Thanks again for any help you can give.

  4. #4
    dblife's Avatar
    dblife is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2011
    Location
    South Shields, UK
    Posts
    95
    it seems your tables are not structured correctly for what you want to do.
    please see the attached images.
    image 001 shows your current entity relationship [how your tables are linked relationally]
    image 002 shows how they *probably* should be linked - i say probably because i do not know your business rules.
    can i assume, one project only has one client but can be associated with multiple work orders?
    if so this is the table structure for you - image 002.
    i have not gone any further as i do not know what impact this will have on the rest of your db.
    let me know if there is anything else i can help with.

  5. #5
    donnan33 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    52
    Ok. I reset the relationship. I still keep getting errors I can't figure out.....attached is the db with the form with the errors. it still will not show the workorder associated with the project number.

    Each project number has only one client number but many work orders.

    It's really driving me crazy....If there is anything else you can maybe figure out it would be greatly appreciated.

    thanks

  6. #6
    donnan33 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    52

    combo box lookups

    I have a form with combo boxes.

    Project Number
    Client Number
    Work order Number

    for each project number there is only 1 client number but many work order numbers.

    I am trying to make it when user clicks on project number, the client number pops in and then it will only list the work orders that go with that project in the work order combo box. I have been trying different ways and I keep getting an error no matter how I set it up. I have attached a copy of just the part I am trying to get to work. if anyone could help it would be greatly appreciated.

  7. #7
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    From looking at your database, you do not need the joining table since you already relate the project-client to the workorderlist. Also, I noticed that you had lookups in your joining table. Although Access has this capability, it is generally not recommended. This site explains why.

    Also, it is recommended to not have spaces or special characters in your table or field names. I removed the hyphen you had in the tblProject-Client. The underscore is OK but not the hyphen.

    Typically when you are representing a one-to-many relationship, you use a main form based on the table that is on the one side of the relationship and a subform based on the table that makes up the many side of the relationship.

    In the attached modified database, I created a form frmProjectClient that illustrates this. I also added a combo box in the header for search purposes. Then you select a project from the combo box, the form migrates to that particular project record and shows the project info in the main form and the related work orders in the subform.

    BTW, do you have any other tables? It is best to get all of your tables and relationships set up before you do any forms.

  8. #8
    donnan33 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    52
    I have attache dwhat my full form will look like. I was just trying to have when clicked in the project number box, client number pops in (which I have setup now). but to also make it where only work orer number for that project is in list. I need all the data to be bound and entered. Each work order has multiple units to enter as well. I'm not sure if what you made is going to work for me.........

  9. #9
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Can only 1 person work on a work order?

  10. #10
    donnan33 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    52
    Yes. Only one user enters daily hours and what they worked on. they can work on multiple projects and/or work orders in the same day. each project number has multiple work orders under it. each work order has all the different units or areas of work.

    the project number, work order number and unit can change and have multiple ones.

    only thing to stay the same is employee, date and hours for that day.

    I am sorry if it is confusing. I have been trying every which way to get that one section to do what I explained in previous message. Thanks so much for your help.

  11. #11
    donnan33 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    52
    I am sorry, I misunderstood. Each employee can work on all projects and work orders listed. Each day each employee will need to enter their time and what they worked on.

  12. #12
    donnan33 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    52
    I have it working now in the frmworkordernumber when it is just that form by itself. when it is put into main form as a subform I keep getting one error. I cannot figure this out. I'm sure it is probably something really stupid or easy and I am just missing it. Can you please check? I attached my latest copy.

  13. #13
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Each employee can work on all projects and work orders listed
    Based on the above, your tables are not structured properly. As you currently have it you have the employeeID field in the work order table. With this setup, you can only have 1 person work on each work order. If many people can work on the same work order that describes a one-to-many relationship. Also, since a person can work on many work orders, you have another one-to-many relationship. When you have two one-to-many relationships between the same two entities (people and workorders in your case), you have a many-to-many relationship which is handled with a junction table.

    I also noticed that you have both the projectID and clientNumber in the work order table. This is unnecessary and violates good database practices. Since the project and client are associated with each other in the projectclient table, you only need to reference the projectID in the work order table.

    You do not need the tblWorkOrderList, it is redundant.

    Also, I think you need a table to hold the client info since I assume that a client can have many projects (one-to-many relationship).

    And I see that you are still using lookups at the table level. Please read my first post for why doing so is not a good idea. I can cause a multitude of problems.

    Also, I would not waste time doing forms until your table structure is set up properly. As the table structure changes you will have to recreate all the forms.

    Other problems I see:

    tblEmployeeName
    you repeat the employee name and number, you only need the empoyeeID. Also you have multiple times. That would be a one-to-many relationship, so the times should be in a separate but related table as records not fields.

    I see redundant info in the units & code list tables at well that will need to be cleaned up.

    You may want to take a look at this site. It explains the rules of normalization which are key to designing a good table structure. The site just provides an overview, so you will want to check out other sites to get a better understanding.

    In the attached database, I have taken your structure and modified it to what it should look like. I did not know what to do with the units/codes tables. Can a more than one unit/code apply to a work order?

    If you run a third shift then I would get rid of the tblEmployeeAttendance table and make a direct join between the employee table and the attendance detail table. You will have to modify the joining field and I would include the date and the time in the dteAttend field. This will make time calculations much easier (having the date & the time together in 1 field).

  14. #14
    donnan33 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    52
    thank you. I will look at the above. Each employee will be entering their own time and data daily. This database is for them to enter there daily work hours and units. Each project can have multiple work orders. each work order can have multiple units.

    And each employee can work on more than one project/workorder each day. They only will have one set of work hours for each day which means only one date. They then will pick the project, work order and then enter any/all the units they worked on under each work order.

    I need it where when they enter this on the form they only have to enter the least amount of data and it groups together in a way where they can enter it like above. I am pretty new to access. i thnak you very much for your help. I am going to look at the link and your attachement and go from there.

  15. #15
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    And each employee can work on more than one project/workorder each day. They only will have one set of work hours for each day which means only one date. They then will pick the project, work order and then enter any/all the units they worked on under each work order.
    Based on the above, the structure I proposed is incorrect.

    With respect to tblCodeList, will a VBuildCode only have 1 corresponding task code as you currently show or can there be more than 1.

    With respect to the units, can there be only 1 code associated with it as you currently indicated in tblUnits?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 06-04-2014, 10:45 AM
  2. using two combo boxes
    By mcguires99 in forum Forms
    Replies: 13
    Last Post: 11-14-2011, 01:19 PM
  3. Help with combo boxes?
    By 107295 in forum Access
    Replies: 5
    Last Post: 02-09-2011, 01:03 AM
  4. Combo Boxes
    By Duncan in forum Access
    Replies: 9
    Last Post: 10-26-2010, 08:45 PM
  5. combo boxes
    By thewabit in forum Forms
    Replies: 7
    Last Post: 01-01-2010, 08:51 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums