Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2015

    Unanswered: Pass Query Results to Form Based on Table


    New here, and somewhat new to databases, but here's the problem.

    It has to do with trucking routes. I have multiple drivers going to multiple locations and making pickups.

    A specific driver has a set route each weekday (so, for example, Bob has a Monday route, a Tuesday route, etc and Jill has her own Mon-Fri routes). On each route are various stops - different stores belonging to different organizations.

    I want to be able to pull all the information together so that I can (for example), ask how many total pickups were done for a given organization (looking at all stores on all routes over a given timeframe). To do this, I've got a "pickups" table that includes driver name, store information, date, weight of pickup, type of pickup, etc.

    To make life easier for the user, I'd like to open a form that displays the stops of a given route and lets them enter pickup information (weights and types).

    So far, I have a form where the user can enter two values - Driver Name (from a lookup) and date, and then press a button, which starts a query. The query then grabs the route for that driver and whatever day of the week the date falls on.

    So far, so good.

    Next, I want to have the query results - showing all the stops on the route - open in a form so that I can enter other information (Item Delivered, weight) and save that in a table of all transport information. All this so that the user doesn't have to enter the route stops.

    This last part I can't figure out.

    I think I might be able to do it by having a recordset for the form and the query, and pass info from the query recordset to the form recordset. Am I on the right track? Is there a better way to do this?

    Thanks - hope this is clearer than the mess in my head!

  2. #2
    Join Date
    Feb 2004
    New Zealand
    Provided Answers: 5
    make your head more mess up

    You could later Use Google Maps to display it

    My under standing on what you are saying it

    a drive select his/her route which them show in a subform all pickup then you could get the user to double click on the stop which would show a popup asking the (Item Delivered, weight) them when they close it I hide that stop as its been done.

    post some screen shots and table layout then we could Point you down the right track
    hope this help

    See clear as mud

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008

  3. #3
    Join Date
    Nov 2015

    some clarification (?)

    I can't post many details, since the work place is strict about privacy issues. But here's a scaled down version of the problem:

    I have stores A through H, and three drivers, Bob, Tom, and Jill. Bob’s route is stores A,B,C,D. Tom’s route is stores A,C,G,H, and Jill’s route is stores D,E,F,G.

    Behind these are “Driver” Table, with driver name, ID, and truck info; and “Store” Table, with store name (A-H), address, phone number, and contact person.

    We’re collecting all of the information about items picked up at each store into a “Pickup” table, with Fields: Date, Driver, Store, WeightOfFurniture, WeightOfBooks, WeightOfClothes

    The user starts with the driver’s name and date, clicks a button, and this opens a form with the following fields:
    Driver, Date, Store, WeightOfFurniture, WeightOfBooks, WeightOfClothes
    with driver and date filled in based on the initial entry, and “Store” having all listings for a given driver’s route.

    So I select Bob and a date (11-12) and get a form with:
    Name Date Store WtFurn WtBooks WtClothes
    Bob 11-12 A
    Bob 11-12 B
    Bob 11-12 C
    Bob 11-12 D

    and all the great data gets saved in the "Pickup" table so I can find out how much of a given item was picked up at a given store in a certain date range (or whatever . . .)


Tags for this Thread

Posting Permissions

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