Access User Form unintentionally altering data table records

Question

I have been using MS Access to aid in generating pdf reports based on a table. I have created a form that contains a text box for entering a client's name (this value is in the main table) and a button that when clicked runs the code:

Private Sub cmdPrintRecord_Click()

    Dim strReportName As String
    Dim strCriteria As String

    strReportName = "Current SP Report"
    strCriteria = "[Owner]='" & Me![Owner] & "'"
    DoCmd.OpenReport strReportName, acViewPreview, , strCriteria

End Sub

The idea here is to generate an individual PDF report based on the clients name.

The above procedure has been able to do that successfully however, I have encountered that as I run it, the data in my table is affected, specifically the client name field.

For example: I'll run a report for client "Anthony" and it shows 10 products which is correct, but then if I go back and run that same report again it will show 11 products. It is as if the procedure here is altering the data table.

How can I troubleshoot this issue and or are there any alternatives recommended?

Thanks.

Attached is the MS link where I obtained the source code: https://support.microsoft.com/en-us/kb/209560

Answer 1

If the Control Source of a form control (textbox, combo box) is linked to a table then it will modify that table. In your case, you want to receive user input based on selections from a table and not to modify the table itself. You want to use the "Row Source" property to limit the selection to table items and clear the Control Source. This pulls potential options from the table without changing existing table entries.

Like this:

Notice that the source of options for the combobox is a query that defines what items should appear in the dropdown but no Control Source is specified.