Results 1 to 11 of 11
  1. #1
    Join Date
    May 2010
    Posts
    23

    Unanswered: Multi Default Sub Form

    I’m using a template from HiTechCoach’s website—OrderEntry Template.

    When you open the Add An Order and Details form how would you have the subform already populated by products. Rather than choosing the products one at a time.

    For my own needs it would make sense to have four or five items from the product table as default purchases.

    What avenue should I take to accomplish this:
    A multi-select sub-form?

    VBA coding?

    I’ve included a link to the template for those who aren’t familiar:


    Downloads-HiTech Coaching for Access Databases and more ...

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I haven't looked at the template, but I have what might be a similar situation. I have a subform for driver activity, where they can have activity for one or more of our available rates. What I do is run an append query when they load the form, so all available rates are listed. Users can just tab down the quantity field adding a quantity where appropriate. When they exit the form, I delete any records with a zero quantity.
    Paul

  3. #3
    Join Date
    May 2010
    Posts
    23
    PBaldy.

    There might be similarities.

    I'm hoping, however, that rather than selecting records one at a time on the subform, the subform would open with the selections already defaulted.

    Then, perhaps, a check box on each record would allow the user to deselect certain records if they don't apply.
    Last edited by rlb2252; 05-28-10 at 17:15.

  4. #4
    Join Date
    May 2010
    Posts
    601
    I have used the same method that Paul has describe and it works well.

    To expand on Paul's method:
    I used the form's On Current event. If it was a new record (Me.NewRecord = True) then I would ask the used if they would like to load all the default products. If yes, then I would run an append query to add all the products. You could add all the items with a quantity of 1 or zero, it is your choice. You could even ask the user.

    The way you would de-select is not to use a check box, but as Paul suggested, simply set the quantity to zero.

    I have also used method where the a pop up form was opened listing all the products. The user could enter the quantity by the desired items. When the pop-up form closed, it would append all the items with a quantity <> 0 to the order.

    I have even used "kits" when when you pick one item code and it automatically add (append query) all the product in the kit as line items.
    Last edited by HiTechCoach; 05-28-10 at 17:12.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I think you misunderstood me. In my application, the subform would have records for each rate (product). It is then very simple for the user to tab down the records, adding quantities as applicable. Sounds like Boyd has done something similar. Post back if you still don't understand, and I'll post a picture or sample.
    Paul

  6. #6
    Join Date
    May 2010
    Posts
    23
    PBaldy:

    I think I get it--although a sample would be great as well.

    If I'm understanding this, you have an append query that opens with your subform. On the subform are listed your specific services/items.

    Finally, by not selecting an item, that item is eliminated on close.

  7. #7
    Join Date
    May 2010
    Posts
    23
    Quote Originally Posted by HiTechCoach View Post
    I have used the same method that Paul has describe and it works well.

    To expand on Paul's method:
    I used the form's On Current event. If it was a new record (Me.NewRecord = True) then I would ask the used if they would like to load all the default products. If yes, then I would run an append query to add all the products. You could add all the items with a quantity of 1 or zero, it is your choice. You could even ask the user.

    The way you would de-select is not to use a check box, but as Paul suggested, simply set the quantity to zero.

    I have also used method where the a pop up form was opened listing all the products. The user could enter the quantity by the desired items. When the pop-up form closed, it would append all the items with a quantity <> 0 to the order.

    I have even used "kits" when when you pick one item code and it automatically add (append query) all the product in the kit as line items.
    HiTech:

    You've given me three different methods of accomplishing my goal.

    Method 3, using "kits" sounds most interesting--although I've never heard the term before. Explain please.

    My take is that a kit may be a grouping of certain values/records which can be triggered by, say, a boolean switch on a form.

    Meanwhile, I'm going to attempt the "append" method. The only downside to Append might be which records to append in response to what criteria.

    In other words I'd need this append query designed with an "If" argument based on a "switch" on the main form. Which may in fact bring me back to this "kits" idea.

  8. #8
    Join Date
    May 2010
    Posts
    23

    One other thing....

    My append attempt met with some problems.

    What I did was tried to append the Sub-Forms underlying table--the Order Details Table--but what I really need to do is append the Sub-Form itself that's showing on the screen.

    Is there something I'm missing?

    Any suggestions.

  9. #9
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by rlb2252 View Post
    My append attempt met with some problems.

    What I did was tried to append the Sub-Forms underlying table--the Order Details Table--but what I really need to do is append the Sub-Form itself that's showing on the screen.

    Is there something I'm missing?

    Any suggestions.
    It would help to see what you did.

    Can you post the VBA code that you are using to run the append?
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    As Boyd said it would help to see it. However, I think you did the right thing. You add the data to the detail table that the subform is based on, then either the master/child links or query you based the subform on would make it display only the applicable records.
    Paul

  11. #11
    Join Date
    May 2010
    Posts
    23
    Ok. I used a simple append query as a test. And yes it will append the Orders Detail table that the Sub Form is based on.

    Two things though: One, how should I trigger the append query: A control button on the form or sub form? Some kind of event in the property sheet?

    (Right now I just created an append query that I have to run manually. With zero criteria. So it really just appends the entire Product Table to the Order Details Table. I've written no code. So I doubt you really need to see anything.)

    2nd, I really wanted to see the appended records in the sub form window as a group--without selecting them one by one.

    What am I missing?
    Last edited by rlb2252; 05-28-10 at 23:04.

Posting Permissions

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