Form linked to opening of report

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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”

Reply | Quote

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

Reply | Quote