Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Oct 2005
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    open report in succession (Access 2000)

    In order to check for figures i want to make a function that opens and closes a report in a succcession depending on the option values chosen.Instead of clicking on the option group called Houses, i use the value of the field :
    Dim stDocName As String
    stDocName = "RptHouseReport"

    Me!Houses.Value = 1
    DoCmd.OpenReport stDocName, acPreview
    DoCmd.Close acReport, "RptHouseReport"


    Me!Houses.Value = 1
    DoCmd.OpenReport stDocName, acPreview
    DoCmd.Close acReport, "RptHouseReport"

    Me!Houses.Value = 3
    DoCmd.OpenReport stDocName, acPreview
    DoCmd.Close acReport, "RptHouseReport"


    etc... etc....

    However my function stops at the first report,, it opens the report for the first option and then stops, it stays wih the report opened.What is the reason for my wrong function ?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: open report in succession (Access 2000)

    Preview is an interactive state, something like a dialog. The user has to close it. It would be pointless to preview three reports in succession closing them in code because they wouldn't stay up long enough for anyone to see.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Oct 2005
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: open report in succession (Access 2000)

    Thank you for the arguments,it i clear to me now that i have to open a query instead .

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

    Re: open report in succession (Access 2000)

    Do you mean an action query, such as an update, append or delete query? If you open an action query in code, it performs its action, there is no need to close it.
    If you mean a select query, the argument mentioned by Charlotte applies: if you open and close a select query in code, the user won't have time to see it.

  5. #5
    New Lounger
    Join Date
    Oct 2005
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: open report in succession (Access 2000)

    Thank you very much for your reply.After your two answers it is perfectly clear to me that the idea with the reports was stupid. I mean now a select query but i do not know how to proceed.Actually the query consists of many rows.In the query there are the fields Students and Banks.My target is to have an alarm if the condition students = banks is not met. In some of these rows the condition Students = banks may not be met. What i want is that if in one or more rows, Students <> Banks to have the message
    MsgBox "Something is wrong with the balance of the class.", vbOKOnly, "Warning"
    Since the form is not based on the query, i want to use the DLookup function :
    DLookUp("Students", "Query1" )
    For opening the query i intend to use the OpenQuery command.
    What do you think? Am i obliged to open the query for that purpose or i could do it directly in some other way? Is it OK with my DLookup function?

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

    Re: open report in succession (Access 2000)

    You do not need to open a query, and it is probably better to use DCount than to use DLookup. Try this:

    Private Sub Command0_Click()
    Dim lngCount As Long
    lngCount = DCount("*", "TableBatches", "Students <> Banks")
    If lngCount = 0 Then
    MsgBox "Everything is OK.", vbInformation
    Else
    MsgBox "There are " & lngCount & " problem records.", vbExclamation
    End If
    End Sub

    This is the On Click event procedure for the command button on the form in the database you attached in an earlier thread.

  7. #7
    New Lounger
    Join Date
    Oct 2005
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: open report in succession (Access 2000)

    Thank you, Hans ! I tested your suggestion with a great success.What strikes me is the insight you have into the problems. I am pleasantly surprised becasue i was prepared for a time consuming work.I did not expect such a simple solution to such a complicated problem.
    Just 2 very smal questions. How can i improve the line :
    MsgBox "There are " & lngCount & " problem records.", vbExclamation
    In case there is only 1 problem record.In that case one should read " there is" instead of " there are "
    And lalso , how could i specify that the code t should apply only in case Students and Banks are not Null?

    Shokarov

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: open report in succession (Access 2000)

    The answer to your first question is:
    <pre>Private Sub Command0_Click()
    Dim lngCount As Long
    lngCount = DCount("*", "TableBatches", "Students <> Banks")
    If lngCount = 0 Then
    MsgBox "Everything is OK.", vbInformation
    ElseIf lngCount = 1 Then
    MsgBox "There is one problem record.", vbExclamation
    Else
    MsgBox "There are " & lngCount & " problem records.", vbExclamation
    End If
    End Sub
    </pre>


    With respect to the second question, do you wish to check if the Students are not equal to Banks AND that both these fieds should be Not Null?
    If so, try:
    lngCount = DCount("*", "TableBatches", "Students is Not Null And Banks is Not Null And Students <> Banks")

  9. #9
    New Lounger
    Join Date
    Oct 2005
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: open report in succession (Access 2000)

    Thank you Patt ! Now i have all perfect and i have no more questions since you solved all of them.A wonderful site

Posting Permissions

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