Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Join Date
    Nov 2011
    Posts
    103

    Unanswered: VBA help needed for Reports

    Ok, so I'm trying my best not to come to this forum every time I have an issue but I can't seem to figure out any of the big issues. I swear I do research for like a day or two before starting a thread.

    So here's my next issue. I have a report that needs to look into a report and grab the corresponding data. It's connected to my previous thread titled "Needing Complicated VBA to Sum." I need the fields Amt1-7 to be filled with data from reports CalendarYrAmt, fields YearXAmt (X representing the number of years) that has the same year has fields Year1-7 in the report titled Lease_Schedule with only one exception, Amt7 field will be the sum of rest of the years to come after Year6.

    I know I'm probably not explaining this very well so I attached a same database sample that I did with the other thread with sample report built into it. Hopefully it helps.

    I've also tried to write my own code using some of the help that I received by the most generous user on the thread that's been solving all my issues but with my limited ability and understanding of the code I didn't have much luck. I have no idea how to use Dim or control.

    Any help would be appreciated.

    Thanks.
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I had a look at the sample database you posted, however there are some things I don't understand.

    In the "Form_Open"procedure of the report "Lease_Schedule", we have:
    Code:
                If [Reports]![Lease_Schedule]!["Year" & i].Value = Me.Controls("Year" & i).Value Then
                   [Reports]![Lease_Schedule]!["Year" & i & "Amt"].Value = Me.Controls("Amt" & i).Value
                End If
    However, this:
    Code:
    [Reports]![Lease_Schedule]!["Year" & i].Value
    can be simplified in:
    Code:
    Me.Controls("Year" & i).Value
    so you actually wrote:
    Code:
    If Me.Controls("Year" & i).Value = Me.Controls("Year" & i).Value Then
        Me.Controls("Year" &  i & "Amt").Value = Me.Controls("Amt" & i).Value
    End If
    Which I don't understand the meaning of, nor your actual intention for writing it. Can you please explain?

    Moreover, the report is not bound, it does not fetch its data from any table or query. Is this normal? (it's hard to tell as I don't understand your goal).

    Finally, the way you compute the values for "Year2", "Year3", etc. is not canonical and could cause problems. For adding one year to a date (say, for [Year2]), you should use in the ControlSource property of the control:
    Code:
    = DateAdd( "y"; 1; [Year1])
    Have a nice day!

  3. #3
    Join Date
    Nov 2011
    Posts
    103
    Well, crap. Sorry. Maybe I shouldn't even have put that code in there.

    What I was trying to tell it to do was for the report to look into all Year1 through Year40 in the forms CalendarYrAmt and if the Years are equal to the any of the years in CalendarYrAmt form fields Year1 through Year40 then in the Amt1 through Amt6 fields in the report plug in the corresponding data of Year1Amt through Year40Amt that matches... Does that makes sense??? I'm really trying my best.

    I thought that Me.Control only refers to the currently opened form not the entire database. I think I meant to put [Reports]![CalendarYrAmt]!["Year" & i].

    Once that's done I need the Amt7 field under header "Thereafter" to sum the remaining amounts of YearXAmt fields after the last year of Year6 field.

    I know that the reports are unbound but it was the only way I that I could think of to set this up. As of right now I get the reports and append it to the table CalendarYrAmount. The form CalendarYrAmt is the only form that I have that's unbound.

    Also I copied your code to revaluate "Year2, Year3 etc" and I keep getting Invalid Syntax error box.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by tobabygu View Post
    I thought that Me.Control only refers to the currently opened form not the entire database. I think I meant to put [Reports]![CalendarYrAmt]!["Year" & i].
    "Me" is a reference to the current object (Form, Report, Class) from where the code is run. Since the code is in the class module of the report named "Lease_Schedule", "Me" designates this report in that context.

    For the rest, I'm stil trying to figure out what precisely you try to achieve (I'm a little slow sometimes). What I don't understand is why you need a reference to the form "CalendarYrAmt". In other words, why you can't pick up the values from the table "CalendarYrAmount".
    Have a nice day!

  5. #5
    Join Date
    Nov 2011
    Posts
    103
    No it can. I'm just trying to pull from the Forms only because I knew that's where the data was originated from. If it's easier to pull from the table that would be fine.

  6. #6
    Join Date
    Nov 2011
    Posts
    103
    So how do I specify the code to look into certain field in a certain table without using field list pane but by searching through all the available fields for a match?

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    consider using a domain function such as dlookup
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Nov 2011
    Posts
    103
    But how do I tell VBA to look at all the fields and find a match? It's not just looking at a single field but 40 fields to find 1 matching field.

    I also need the sum of the fields that are not included in the matched fields for the future years.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    anywhere you see columns (or tables) called xxxx1, xxxx2... xxxxn its nearly always a result of bad design
    I'd suggest you redesign your tables by normalising the design
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Nov 2011
    Posts
    103
    I'm trying to say that I don't know the expression that I need to use under dlookup.

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I spent some time re-reading the messages in your former thread. However, try as I might, I'm not able to figure out what values you want the report to display. How is it possible to establish a relationship among the text boxes "Amt1", "Amt2", etc. in the report "Lease_Schedule" and some columns of the table "CalendarYrAmount", or even text boxes in the form "CalendarYrAmount"? What's the logic behind, what are the rules?

    In any case, I sincerely believe that you should seriously consider reorganizing you database and try to normalize it. As it is, the same kind of problem will come again every time you'll want to modify something into it.
    Have a nice day!

  12. #12
    Join Date
    Nov 2011
    Posts
    103
    I don't think I quite understand what your question is. I was attempting to write a similar code to my previous thread (in which you flawlessly solved my issue) where if the year on the Year1 field matches any 1 of the 40 YearX fields that the Amt1 text field would be filled in with corresponding 1 of the 40 YearXAmt fields. I guess you're saying that it may not be capable of doing that because the report isn't connected to any table or forms. Am I understanding this right?

    If that is the issue how do I rearrange my database to that it would be more "normalized" and the relationship are available between my reports, forms, and tables (if this become a question for a completely new thread please let me know)?

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by tobabygu View Post
    I guess you're saying that it may not be capable of doing that because the report isn't connected to any table or forms. Am I understanding this right?
    This is one of the reasons indeed. Another one is that you cannot manipulate the controls of a report as easily as you can in a form, once this report is open. Many properties that can be dynamically changed by code in an open form are "locked" ("read-only", if you prefer) once the rendering process of a report is complete.
    Have a nice day!

  14. #14
    Join Date
    Nov 2011
    Posts
    103
    So does that mean once Amt1, Amt2, etc fields are filled in with data that it can not be changed? If that's the case, what if I create a query? Is there a way to have a query look into all 40 fields to find 1 matching case?

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can assign a value to a control in a report or form via code providing the control isn't bound to a data source (ie a column from a table / query
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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