Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Calgary, Alberta, Canada
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form linked to opening of report

    I have created a form in an Access 97 database which is acting as the initial interface to a report. From a switchboard, the user clicks on a command button which takes them to form, which is based on a query. The form has a bunch of committee names which can be chosen one at a time, or multiple options, by simply clicking a "button" beside the name that they desire. This button's control source is a yes/no field in the table/query called "selected" which simply gets set to a "yes" if the button is selected for a corresponding committe name. When the committee(s) have been selected, the user clicks on an "OK" button on the form, which launches the report. Upon exiting the report, an update query runs to "de-select" the buttons which had been "selected" in the form. This way the next time it is used, it is clean and ready to go.

    The problem I'm running into is that the report doesn't seem to display the data the first time around if only one "committee name" is selected from the form. If multiple names are selected the report displays info, however it's unpredictable what it shows. Sometimes it shows data matching both names which were selected, but more often than that, it just displays the data which matches the first name which was selected. Beyond that, if when the report is closed, and the response to the update query request message is "no" (not to run the update query), and the name is left selected, the next time the form is opened, assuming the choice you want is the one that was left selected from the previous time, the report displays exactly the information which it should.

    I apologize for the complexity of this description, but if anyone was able to follow, I'd appreciate any advice...I'm at a loss to knowing how to fix this.

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

    Re: Form linked to opening of report

    It sounds like a problem with whatever recordsource or filter you're using with the report. Is the report based on a query created using the selected items in the table? If so, post the SQL for that query and someone may spot a problem. If not, is the report based on a fixed recordsource and filtered based on the form? In that case explain how you're setting the report filter and what filter string you're passing.

    As for cleaning up your table, the easiest way is to run the update query when you open the form. That way, you're always starting with a clean slate. Use the Execute method to update the table, since that will eliminate the need to setwarnings on and off and won't bloat the database the way DoCmd.OpenQuery will.
    Charlotte

  3. #3
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Change the focus before clicking

    I think what is happening is the value of the control is not set until the focus leaves the control. I haven't worked out how to accept the value any other way so I always have to get around this by moving the focus a couple of times before running with data off the form eg.
    DoCmd.GoToControl "Ctrl1"
    DoCmd.GoToControl "Ctrl2"
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. #4
    Lounger
    Join Date
    Jan 2001
    Location
    Calgary, Alberta, Canada
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form linked to opening of report

    The report is based on a query created with the selected items from the table. Here follows the SQL of the query:
    SELECT MailingList.[Mailing ID], InjuryTopicsLookup.Selected, InjuryTopics.InjuryTopic, [prefix] & " " & [firstname] & " " & [middleinitial] & " " & [lastname] & " " & [suffix] AS [Full Name], MailingList.Prefix, MailingList.FirstName, MailingList.MiddleInitial, MailingList.LastName, MailingList.Suffix, MailingList.Nickname, MailingList.JobTitle, MailingList.OrganizationName, MailingList.Address, [city] & ", " & [province] & " " & [postalcode] AS [City Address line], MailingList.City, MailingList.Province, MailingList.PostalCode, MailingList.HomePhone, MailingList.WorkPhone, MailingList.WorkExtension, MailingList.CellPhone, MailingList.FaxPhone, MailingList.EmailAddress, MailingList.SpecialRequirement, MailingList.Ward, MailingList.Riding, MailingList.Constituency, MailingList.Birthdate, MailingList.DateJoined, MailingList.DateUpdated, MailingList.Language, MailingList.Gender, MailingList.Notes
    FROM (MailingList INNER JOIN InjuryTopics ON MailingList.[Mailing ID] = InjuryTopics.[Mailing ID]) INNER JOIN InjuryTopicsLookup ON InjuryTopics.InjuryTopic = InjuryTopicsLookup.InjuryTopic
    WHERE (((InjuryTopicsLookup.Selected)=Yes));

    Thanks for any help that anyone can be in identifying a problem.

  5. #5
    Lounger
    Join Date
    Jan 2001
    Location
    Calgary, Alberta, Canada
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change the focus before clicking

    Thanks for your suggestion. I follow what you're thinking, however had trouble implementing it with my limited knowledge of VBA. I attempted to simply add a line in the code for the "OK" button which is clicked on in order to launch the report. Below is the module: (my added line having it go to the "injury ok button"). Perhaps I implemented your suggestion incorrectly and/or failed to understand, as it didn't seem to have any affect on the outcome.

    Thanks for any more thoughts.

    Private Sub Injury_OK_button_Click()
    On Error GoTo Err_Injury_OK_button_Click

    DoCmd.GoToControl "injury OK button"

    Dim stDocName As String

    stDocName = "Injury Topics Member List"
    DoCmd.OpenReport stDocName, acPreview

    Exit_Injury_OK_button_Click:
    Exit Sub

    Err_Injury_OK_button_Click:
    MsgBox Err.Description
    Resume Exit_Injury_OK_button_Click

    End Sub

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

    Re: Form linked to opening of report

    Well, the first suspect when not all records turn up in a query is inner joins, and having 3 tables involved makes it just that much more suspect. If the topic is selected but you don't have a match in one of the other two tables, you won't get that combined record.
    Charlotte

Posting Permissions

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