Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2005
    Posts
    4

    Adding Controls to a Form

    I have two tables: SalesDrivers and SalesDriversData.

    The sales drivers table just has two fields. DriverNumber and DriverName. The data table has an ID field (Primary Key), Driver Number, PalletsIn, PalletsOut, CratesIn, CratesOut, etc....

    I know, traditionally, the way to update this data table with a form is one record at a time. But the client has requested to have one form with a row of controls for each driver. I have the form set up and it works fine. It has a submit button that executes a SQL statement for each driver (or row of controls).

    The problem is adding/removing drivers. Right now it would take forever to add a row of controls and name each control, then add the code to execute the SQL statement.



    I want to be able to cycle through the drivers in the SalesDrivers table and add each row dynamically.

    Suggestions?

  2. #2
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275

    Re: Adding Controls to a Form

    Quote Originally Posted by msmayhew
    I have two tables: SalesDrivers and SalesDriversData.

    The sales drivers table just has two fields. DriverNumber and DriverName. The data table has an ID field (Primary Key), Driver Number, PalletsIn, PalletsOut, CratesIn, CratesOut, etc....

    I know, traditionally, the way to update this data table with a form is one record at a time. But the client has requested to have one form with a row of controls for each driver. I have the form set up and it works fine. It has a submit button that executes a SQL statement for each driver (or row of controls).

    The problem is adding/removing drivers. Right now it would take forever to add a row of controls and name each control, then add the code to execute the SQL statement.

    I want to be able to cycle through the drivers in the SalesDrivers table and add each row dynamically.

    Suggestions?
    What is the relationship between these two tables? Typically where there is a foreign key it is one-to-many, but from the way you described the form, it sounds one-to-one.

    If the relationship is indeed one-to-one, then I would question the need for two tables at all, and I would suggest that you set up your form to have just a single set of controls (one for each field) and set the form to be in continuous mode (and thus your client can see all the rows at once).

    If the relationship is one-to-many, I am having a hard time understanding how your form supports that...

    Or am I just missing something :roll:

  3. #3
    Join Date
    Dec 2005
    Posts
    4

    Detailed Explanation:

    Detailed Explanation:

    The company that the Access DB is for has around 12 Sales Drivers. Every day each driver leaves the company to make deliveries. This Access DB is designed to help the company keep up with how many pallets and crates the driver goes out and comes in with.

    The way the table is updated is by a form. The form has several rows of controls. Each row is for a Sales Driver. An Example of a typical row would be a checkbox to indicate if you wanted to add data for that driver. Next, a label with the drivers name, next a textbox for how many "Renegade pallets" the driver went out with, next another textbox for how many "40x40 pallets" the driver went out with. There are several more textboxes for entering how mnay pallets/crates the driver went out and came in with.

    At the bottom of the form is a "Submit" button. When the button is pressed, all the data from the first row is collected to form a SQL statement and then the SQL Statement is executed updating the data table. Then the same thing happens for the second row, then the third and so-on.

    As you have probably noticed by now this isn't a traditional access form. Its not directly tied to a table. I DID NOT want to set it up like this but this is how the client wanted it.

    Now my problem is if the client hires a new driver, not only do I have to add the driver to the 'tblSalesDrivers' table but I have to add a row of controls to the form. Then I have to add the code to execute when the "Submit" button is pressed.

    I would like the form to be able to cycle through the 'tblSalesDrivers' table and create a row of controls for each driver. I've done similiar things with controls in VB6 with control arrays but I can't figure it out in VBA.

    I hope this helps and isn't to lengthy.

  4. #4
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    I see now what you're doing.

    Unfortunately, the approach you outline is ultimately unsustainable. You might--might--get it to work after a lot of pain. But what happens if your client is really successful and expands to having 25 drivers? 50 drivers? You get the idea.

    In your shoes, I would propose the following.

    Table: tblDrivers
    DriverID (PK)
    DriverLName
    DriverFName
    <other fields as needed>

    Table: tblPalletTypes
    PalletTypeID (PK)
    PalletTypeName
    PalletTypeDescr

    Table: tblDriverPallets
    DriverID (PK)
    PalletTypeID (PK)
    LeftNumber
    ReturnedNumber

    I would then have a main form bound to tblDrivers, set up in continuous view. It would have a subform bound to tblDriverPallets, with frmDrivers joined to frmFriverPallets on DriverID. I would set up the subform as either continuous view or datasheet view.

    You might want some code to automatically add entries on tblDriverPallets every time you add a PalletType.

    This would enable your forms to dynamically adjust to both the number of drivers *and* the number of pallet types. Your client would have to get used to a different look and feel, but the benefits far outweigh that cost.

    Good luck with your project!

  5. #5
    Join Date
    Dec 2005
    Posts
    4

    Thanks!

    I've been trying to work with continuous forms but haven't really found a solution. (I am a VB Programmer that is very new to Access). You're solution sounds like it should work.

    Thanks again!

  6. #6
    Join Date
    Dec 2005
    Posts
    4
    Maybe I'm missing something but I just tried to set up the form as you said but it said a form can't be continuous if it has a subform on it.

    Also, I can't find where I would set the sub form to continuous/datasheet/etc.

  7. #7
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    You're most welcome. I probably oversimplified the problem to an extent, because you probably want to be able to track the daily results. That's easily fixed, though, by adding a date field to tblDriverPallets.

    Good luck with this. I know how hard it can be to persuade a client who really thinks s/he knows what s/he wants but that that thing is really not in their interests. (Although, I will always prefer that challenge to the challenge of the client who does not know what s/he wants 8) .)

  8. #8
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    Quote Originally Posted by msmayhew
    Maybe I'm missing something but I just tried to set up the form as you said but it said a form can't be continuous if it has a subform on it.
    Alas, it appears I gave you bad advice. I just pulled out my Access Developer's Handbook, and indeed Access cannot display a subform if the main form is in continuous view.

    Quote Originally Posted by msmayhew
    Also, I can't find where I would set the sub form to continuous/datasheet/etc.
    In the form designer, in the properties window, it's in the Form properties, Format tab. The property there is Default View; choices are Single, COntinuous, DataSheet, PivotTable and PivotChart.

    I would still recommend my basic approach, with the revision that the frmDrivers form be set up in Single view. You would only be able to see one driver at a time that way, but it would be a trivial matter to set up a report that shows what all the drivers are doing.

    Sorry for the wrong turn

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

Similar Threads

  1. Adding 2 fields together
    By Craig Spencer in forum Access
    Replies: 2
    Last Post: 04-08-2011, 02:10 PM
  2. Adding a single record
    By kfoyil in forum Forms
    Replies: 2
    Last Post: 11-22-2006, 09:12 PM
  3. Referring to fields in tab controls
    By AndrewAfresh in forum Forms
    Replies: 1
    Last Post: 06-03-2006, 05:10 PM
  4. Adding a Form to a web page
    By JohnnyO in forum Forms
    Replies: 3
    Last Post: 02-24-2006, 12:29 PM
  5. Adding Running Balance from Form to Reports
    By KIDGEO3 in forum Reports
    Replies: 1
    Last Post: 01-18-2006, 08:52 AM

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