Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subform Behavior (A2k)

    I am attaching a copy of my database. You'll see that I have one main form (frmParticipants) and three subforms (sbfStudentClasses, sbfGuests, & sbfBilling). What I'm having difficulty with is totaling the price for the guests on the sbfGuests and "plugging" this info into the sbfBilling. I've been away on vacation for almost 2 weeks and almost had this worked out before I left, but am still having brain fade from Lake Tahoe!

    Please help!

    Thanks so much-

  2. #2
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform Behavior (A2k)

    Forgot attachment!
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform Behavior (A2k)

    Let's try again
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform Behavior (A2k)

    <P ID="edit" class=small>(Edited by HansV on 14-Aug-03 00:14. Replaced attachment - posted wrong version originally)</P>I have attached a modified database. I used DSum to calculate the sum of guest prices on sbfBilling. This won't update itself, so I created a command button that updates the value.

    I don't understand why you can select a student type and price in each individual record of sbfBilling. As you will see, this leads to nonsense results as you move from record to record in sbfBilling. You may want to reconsider this aspect of your design.
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform Behavior (A2k)

    Thanks Hans!

    >>I don't understand why you can select a student type and price in each individual record of sbfBilling. As you will see, this leads to nonsense results as you move from record to record in sbfBilling. You may want to reconsider this aspect of your design<<

    Well, should I change that to be like it is on sbfGuests? I just want the sbfBilling to pull all the info for the charges for student type and all guests associated with that student. Can you suggest a better way?

  6. #6
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform Behavior (A2k)

    Okay, here's what I'm thinking I should do. On frmParticipants add the cboStudentType and the txtStudentPrice. Remove those same controls from sbfBilling, and then duplicate the info (modified for StudentType and StudentPrice) that you used for GuestType and GuestPrice. Does that sound right?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform Behavior (A2k)

    Yes, that's what I would do.

  8. #8
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform Behavior (A2k)

    Thanks, that's what I've been working on all day. The cboStudentType is behaving, but the txtStudentPrice is not. I have checked the code in the AfterUpdate event of cboStudentType and it's correct. After comparing everything, the only thing I can find is that curProductPrice is not listed in the IntelliSense window, only curStudentPrice. I have used the same row source that was used on sbfBilling (tblPricing). Here's the code:

    Private Sub cboStudentType_AfterUpdate()
    If IsNull(cboStudentType) Then
    Me.curStudentPrice = Null
    Else
    Me.curStudentPrice = Me.curProductPrice
    End If
    End Sub

    Any idea why this is happening?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform Behavior (A2k)

    You will need to change the record source of the Participants form to include curProductPrice. See attached modified database.
    Attached Files Attached Files

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Subform Behavior (A2k)

    Hans: I've been looking over your sample DB because I'm working on a somewhat similar project. In particular, I'm studying the Participants form and its subforms, and I'm puzzled by the behavior of the sbfStudentClasses. When first opened, the subform displays three classes for Dr. Fonseca, and there's a blank row that appears ready for the entry of a fourth class. Yet, when I click in that blank row--or when I click on the "Add New Record" button--that fourth row disappears and the Add New Record button turns gray. What's going on here?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform Behavior (A2k)

    Hi Lucas,

    Gabi wanted to restrict the number of classes to a maximum of three per student. The On Current event of sbfStudentClasses sets the AllowAdditions property to True if the number of records is less than 3, and to False otherwise:

    Me.AllowAdditions = (Me.Recordset.RecordCount < 3)

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Subform Behavior (A2k)

    Ah-ha! Thanks!

  13. #13
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform Behavior (A2k)

    Hi Lucas,

    This database has been a 3 month work in progress and when I started I knew only what I'd learned in Access classes, but had no practical experience. It has evolved to what it is due to Hans' tireless help and advice. Now, it's almost complete and I just have to add the bells and whistles. I'd be interested in seeing your database as well, for design ideas. How much Access experience do you have??

  14. #14
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subform Behavior (A2k)

    Hans, thank you for your help on the record source. When do I need to click the update button on sbfBilling?

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform Behavior (A2k)

    If you update the student price on the main form, or the guest price on sbfGuests, the text boxes on sbfBilling won't be updated automatically, so you need to press update then. When you open the form, and when you move to another student, the numbers should be OK, it's only when a price is added or modified that sbfBilling needs to be updated.

Page 1 of 2 12 LastLast

Posting Permissions

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