Query Results Saving To Table

[kdenney]

I have a DB in Access that I am pulling search criteria from a table using a query and then displaying those results into a tabular form. However, when I close out of the forms/queries etc. the data is being saved to the table. My query is a "select" query not append so I am not sure why this would happen. It will even add a record if no records exist for that recordset. Any help would be greatly appreciated.

 

[xenou]

Find the query that feeds the data and make sure under its properties that it is set to a "snapshot" type of query rather than a "dynaset" type of query. It's rather awkward at first finding this, but if you right click in the design grid in the empty space you can get to query properties.

Yes, SQL Select does not (by definition) alter data. However, it is a feature of Access that queries can be bound to data sources in an interactive manner.

 

[kdenney]

It is still doing it. I not sure how to attach the file but if you could explain it I will send it to you. What this is for is a church that needs to keep track of their new attendee's to see how their retension rate is going as well as keep track of phone numbers etc. Here is the file.

Basically when you open the file you can add or search. Add works fine. Then click search. Once you hit search it brings up a form that allows you to enter criteria to run the query by. Then it has a macro that runs the query and displays the results in a form. (Query By Form method) Once you close out of the query etc. it then saves that information to the table which is not what I want it to do. (It doesn't ask either). Thank you once again for your help.

 

[xenou]

In the form properties set it disallow additions, deletions, or changes. Probably something like this (notice that we make the form a snapshot type of recordset too which is probably overkill because that should also make the form "read only"). By setting the additions property to no you should also not get any "new record" options in the form. If you set Allow Edits to Yes then users can edit (make changes to data) but not add new records.

It's actually somewhat unclear how a user can search for a record that then adds it to the database. One would think if the record doesn't exist it would not be found, hence there would be nothing to save. But overall, it sounds like you need to set up the form so that it doesn't allow additions.

<img alt="form properties" src="http://northernocean.net/etc/mrexcel/20110417_formproperties.png" />

 

[kdenney]

By doing this you cannot add the criteria in the fields to run the query. It isn't the form that results from running the query that changes the information I don't think. I think it is the form that you add the criteria to that runs the query and displays the results form that actually adds to the table. If I set all these properties like you have I then cannot enter the criteria needed to run the query. How do I attach the file to have you take a look at it on here?

 

[xenou]

I think it is the form that you add the criteria to that runs the query and displays the results form that actually adds to the table.

How does this form work?

 

[kdenney]

How does this form work?

How would you accomplish the task then? What would cause the form to append the table like that?

 

[Norie]

Something must be 'saving the data' to the table - it's not default behaviour.

What code is behind this form, if any?

 

[kdenney]

Something must be 'saving the data' to the table - it's not default behaviour.

What code is behind this form, if any?

Well I would post the database file but I am not sure how. If you could explain it I could post the db file

 

[xenou]

It's not possible to upload a db file in this forum. As far as how I would do this ... I would prefer to use Access' inbuilt filtering and Find tools to locate records. This is usually as simple as right clicking in a field, using the Record menu, or hitting Control + F