Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Mar 2012
    Posts
    58
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Access database sample for route deliveries

    I am trying to use Access 2010 to do the following:

    The company specializes in delivery of product to nursing homes. There are 12 delivery routes throughout the region. Each driver is responsible to reorder product. Product available can be 400 selections. The driver manually fills-in a paper sheet that has product code, product description, order number, and date. If I did this in Excel I would have 12 sheets designated each route listing all the product available. I cannot seem to get a handle on how I would do this in Access. Wondered if anyone had a sample database I could look at to get started. I am open to any suggestions at this point.

    J

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,536
    Thanks
    0
    Thanked 23 Times in 23 Posts
    I would do that with a form (delivery routes) and sub form (ReorderProducts).
    The tabales required would be:
    DeliveryRoutes
    ReorderProducts
    Product (this is used as a lookup table)

    If you require any more help just let us know.

  4. #3
    Star Lounger
    Join Date
    Mar 2012
    Posts
    58
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi

    I created the two tables and I have a combo box that is working but why can't I just type in the value. It only works if I choose from the combo box. I used an event procedure OnChange. me.txtDescription.value=me.cboItemID.column (1)



    J

  5. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,536
    Thanks
    0
    Thanked 23 Times in 23 Posts
    I mentioned to have 3 tables, not just 2.

    You should be able type in the product description in the combo box. There should be no need for the OnChange event for the combo box.
    The combo box should have a value of the primary key of the Products table, not the description. The bound column should the key to the products table. The number of columns should be set to 2, and the column widths should be 0;5 (eg) with the zero to say don't show the key but only the description.
    If you are having any more problems why not post a compacted zipped database with sensitive data changed.

  6. #5
    Star Lounger
    Join Date
    Mar 2012
    Posts
    58
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Combo Box

    Hi

    Attached is the test database. The combo box works when you choose from the list. The combo box does not work when you type in the Item ID. Appreciate the help.

    J
    Attached Files Attached Files

  7. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,536
    Thanks
    0
    Thanked 23 Times in 23 Posts
    Mine works, although i would not have set it up like this.

    The idea of a combo box with a lookup table behind it is to have 2 columns (like you have) only one of them being visible (the name or description) the other column becomes the bound column (like you have) but this remains hidden.

    I notice you have a column in the products table called Item Codes and the corresponding in the Reorders table is ItemID. It's a good idea to keep these names the same.
    Are your products known by this code or the description? I would tend to have a unique key to the products table (normally an AutoNumber is a good idea).

    Why have the description in the Reorders table as well?
    What i did was change the combo box to display the Description instead of the item code. In doing so you make the Item codes column a zero width (to hide it). I also made the query behind the combo box sort ascending on Description. I also deleted the description from the form as the combobox would now show this.
    Attached Files Attached Files

  8. #7
    Star Lounger
    Join Date
    Mar 2012
    Posts
    58
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi

    Thank you for the help but I need to be able to enter item codes which I know pretty much by heart. Can you switch it around?

    J

  9. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,536
    Thanks
    0
    Thanked 23 Times in 23 Posts
    It should work as you had it, it worked on mine with the Item codes being entered.

  10. #9
    Star Lounger
    Join Date
    Mar 2012
    Posts
    58
    Thanks
    11
    Thanked 0 Times in 0 Posts
    HI

    Thank you for the help. I do not use this forum very often so I am having trouble posting a new thread. I have another question. This involves a calculated field. If I have a field On Hand and a field Received and I build the following formula in a query: [On Hand]+[Received] is there a way for the formula to replace the number On Hand? To put in it in general terms A+B=C the results of C to replace A.

    J

  11. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,536
    Thanks
    0
    Thanked 23 Times in 23 Posts
    Where do you wish to do this, are both fields in the one table?
    I suggest you run an update query to change records in the table, but is that what you want to do?

  12. #11
    Star Lounger
    Join Date
    Mar 2012
    Posts
    58
    Thanks
    11
    Thanked 0 Times in 0 Posts
    HI

    Both fields are in the one table. Can I do this in a select query versus an update query?

    J

  13. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,536
    Thanks
    0
    Thanked 23 Times in 23 Posts
    Do you wish to permanently change the field [On Hand] in the table, if so use an update query. If you do this, the equation is no longer valid.

    Would you please explain exactly what you are try to do.

  14. #13
    Star Lounger
    Join Date
    Mar 2012
    Posts
    58
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Hi

    I did as you suggested with an update query and it worked great. Had to relearn how update queries work. Thank you again for the help.

    Jean

Posting Permissions

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