Page 1 of 4 123 ... LastLast
Results 1 to 15 of 49
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Keeping Selected Year (Access 2000)

    I'm setting up an opening screen where the user selects a year they want to open. But that year selection would be for doing invoices,
    payments and reports. I still want to be able to look at a students account and see if they owe from previous years, and make payments to
    previous years (I apply payments to each line of an invoice). My question is...how do I tell the invoice form and the payment form that I want
    to use the selected year, but when I want to see their whole account, I want to see all years?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Keeping Selected Year (Access 2000)

    You may well need two different forms to let you choice between a specific year and all years, but you may be able to set filters on subforms or forms depending on how you display data. It would involve writing VBA code to select either a year (or All) from a combo box or text box and then set the filter and turn it on. Of course two forms wouldn't require VBA code, so you may find that more attractive.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Keeping Selected Year (Access 2000)

    But if I have one form that has a combo box on it to select the year, how do I pass that selection to other forms?
    For example, I select the 2003/2004 year, then I open the student form and select a student, then click on the Invoices
    button and enter the lines of the invoice. These lines of the invoice will have year 2003/2004 attached to it. How do
    I do that? I would be doing the same thing for the Payments button/screen.

  4. #4
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Keeping Selected Year (Access 2000)

    Jennifer,

    Two methods come to mind for passing information from one form to another. Both require VBA code:

    Method 1 -
    In Form1, which has the selection combo box, copy the combo box value into a global variable. When the student form is opened, it picks up the value from the global variable.

    Method 2 -
    In Form 1, cause the student form to be loaded, but set "Visible=False". Then directly assign the combo box value into the invisible, but loaded, form in the appropriate text or combo box control.
    Then set Form 1 invisible and the student form visible. Have the student form Activate event coded to use the provided data.

    I'm sure other Loungers will have other ideas, but these two come to mind immediately.

    -- Jim

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Keeping Selected Year (Access 2000)

    Well, while I was waiting I tried making a query that lists the fees that have the year that I selected. When I run the query, it lists the fees that correspond to that year--so that works. But then I select the student, then click the Invoice button to get to the Invoice screen. Here, I want a drop-down box listing the year's (that I selected) fees only. I tried to do the drop-down combo box by calling the query that lists the fees from the year selected, but it didn't work. I just keep getting an error message that says "No value given for one or more required parameters". I don't know what this means. Any ideas?

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Keeping Selected Year (Access 2000)

    I wasn't able to get my database small enough to attach, so I'm emailing it to you. I hope that's okay.

    When you open up the database, open the "frmSelect School Year Main" form first and select a school year. Then when you open up the "qrySelected Year Fees" query, it will list the selected school year's fees. Then open the "frmInvoices by Student" form. If you get a message about an "Account Info Subform", ignore it. I haven't finished putting in some other queries. Just click on OK. You then select a student to invoice here on the Last Name drop-down. Then you click the Invoice Details button. Now I'm not sure why I'm getting a message saying "you cancelled the previous operation". Anyway, click OK. Now this is where the problem lies.

    I want to be able to have a drop-down combo box in the subform that lists the fee names that correspond to the year that I previously selected. I just can't seem to get it to work. I also want to be able to change the amount if I want to, but not affect the original amount. Some students come in part way through the year, so you wouldn't charge them the full amount. So you would put in a lesser amount. But the other students full amounts should not change.

    I hope I'm being clear enough. Let me know if I'm not. Thanks for any help.

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Keeping Selected Year (Access 2000)

    In order to get the database small enough, have you tried <!profile=HansV>HansV<!/profile>'s method:<UL><LI> Make a copy of your database and work with that.
    <LI> Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI> In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI> Remove or modify data of a confidential nature.
    <LI> Do a compact and repair (Tools/Database Utilities).
    <LI> Make a zip file containing the database; it should be below 100kB.
    <LI> If you have difficulties getting the zip file below 100 KB, save the database in access 97 format and then zip it.
    <LI> Attach the zip file to a reply.[/list]
    Gre

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Keeping Selected Year (Access 2000)

    Thanks so much! It worked great! I already tried everthing except the Compact Database utility. That did it.
    Attached Files Attached Files

  9. #9
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Keeping Selected Year (Access 2000)

    Jennifer,

    Sorry -- I've been away from the Lounge thread until the last 20 minutes. I have downloaded your example and will examine it more closely over the weekend.

    -- Jim

  10. #10
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Keeping Selected Year (Access 2000)

    Jennifer,

    I've attached a slightly modified version of the MDB file you sent earlier. The "... cancelled the previous operation" message is now gone. It was related to the way you defined the filter string. You'll see my revised VBA statement.
    I also made several of the form event entries "Private" (which is the normal default for such entries) where, somehow, the "Private" qualifier had gotten deleted. Also added some function return value type-casting where needed.
    Lastly, I placed a new combo box on the Invoices form that is populated automatically via the predefined query you already had created. While I could not get the combo box wizard to let me set it up, I was able to manually fill-in the necessary fields in the control's property box. It seems to work as you outlined your requirements.

    Hope the above is useful to you.

    -- Jim
    Attached Files Attached Files

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Keeping Selected Year (Access 2000)

    I still couldn't get the school year part to work in your example. But that's okay because I think the way I was doing it was a bit complicated, so I changed the way I wanted to select the year. It's in my <post#=357627>post 357627</post#> . I don't know if you can help me with this one or not.

  12. #12
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Keeping Selected Year (Access 2000)

    Jennifer,

    I agree that moving the year selection combo box into the Invoice form is more "user friendly". However, why not place the Fee Schedule combo box (from my earlier example) immediately below the year selection box? Then, just change the reference in the query to point to the relevant year selection box. There have been several threads posted relating to cascading combo boxes. It seems this is a reasonably straightforward example of that logic.

    -- Jim

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Keeping Selected Year (Access 2000)

    Well, I want to be able to adjust the price of each fee if I need to. But I don't want the amount I change it to to effect the amount that is in the fee table because the most students will have the full amount. Only students leaving throughout the year will have different amounts. I hope you can understand this. I just can't seem to get the invoice to accept the lines on my invoice. Do you have any idea what I'm doing wrong?

  14. #14
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Keeping Selected Year (Access 2000)

    You are saying that, in effect, you want to be able vary the fees in any case - even though that only seldom applies. Wouldn't it be simpler to have a "Adjustment" Value - which would be the reduction in the Fee when someone left part the way through the year? That would make most Invoices generate automatically.

    If you really didn't want the possibility of an Adjustment normally mentioned on your reports, you should be able to set them with a blank row that only gets filled in when Adjustment actually has a Value.

    HTH
    Gre

  15. #15
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Keeping Selected Year (Access 2000)

    An adjustment field sounds like a good idea.

    But I still don't know how to be able to go into Invoice Details from the student's screen, select the year, then have the lines show up that have been entered for that year (if there's any), and allow additional lines on the invoice. When you have selected the year and you enter a line on an invoice, I only want the list of fees for that particular year to show up. I hope you know what I mean. I just don't know how to say to filter the Fees combo box in the Invoices subform when I select the year in the Invoices form. Is this possible?

Page 1 of 4 123 ... 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
  •