Do not want my form to update my table 1

[ACSeval]

Hi All. I have a form with a combo box used to select the criteria for a report. The drop down is linked to a table with all of the possible choices to be used for criteria.

However, when the user selects from the combo box, the program tries to add an entry to my table (from which the choice was made), essentialy creating a duplicate entry in the table.

Example: Let's say the table has a list of colors: green, red, blue, white. When the user selects the color from the drop down (combo box form to be used as query criteria), let's say they chose green, the query would run and show me all matches for "green," but then when the form closes, my table would show: green, green, red, blue, white. The choice selected becomes duplicate.

This happens every time the form is run, and duplicates become triplicates and so forth.

Any ideas as to how to prevent this from happening? I don't know VBA or SQL (yet).

Thanks.

NOTE: I tried setting my "color" variable as a Primary Key in the table, and this keeps the table from being updated, however, it gives an error every time the form is run, and that's no good either...
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship..." Thing is, I never meant to request any changes.

 

[Mike555]

Recreate the combo boxes using the Combo Box wizard. In about the 4th or 5th step, simply specify that the value be remembered for later use instead of being stored in a field.

--
Mike

In defeat: unbeatable. In victory: unbearable. -- Winston Churchill

 

[RickSpr]

Equivalently, simply erase the Control Source properties of the combo box controls. For a report criteria form, you could (and should) probably also erase the form's Record Source property.

The Control Source property ties a control to a field in the form's recordset. When Control Source is set, moving among records causes the control to display the stored field value for each record, and updating the control causes the record field to be updated.

The Record Source property ties the form to a table, so that the form displays the records from the table, and updating the form updates the current row in the table.

Generally, for a criteria form used with a report, you don't need the form itself to be tied to a table at all, since you aren't displaying records and don't want to update the table.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[ACSeval]

Thanks. I think I've got it now. Deleting the Control Source and Record Source properties didn't do it, as the drop down either had no information to view (to set the criteria) or, the information wouldn't populate to my query/report. Also, for whatever reason, my Access 97 doesn't appear to have a Control Box Wizard.

While I was playing with the ideas you gave me, I realized that the update/error message occured when I tried to close out of my form box with the "X" at the top right corner. What I did was to disable that, and instead added a "Cancel" button that was designed to close the box. This seems to keep it from trying to update my source table.

I don't know why it works, but it does.