If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Issue with Edit Form

Hi,

I have a main form with a button which, when clicked, opens up a form with a combo box to choose (or enter in) an product number. On this second form with the combo box, I have another button, which when pressed finds the record related to that ID number and opens up a third form which is the edit form.

My issue is that every time I select the first product number from the drop down combo box and click the "Find" button, it brings up two records of this. Somehow, it is changing one of the other product numbers to this product number (the one listed first on the list) but keeping all of the other fields the same (which is how I can tell that it's getting changed... the information for the two product numbers which are the same is completely different). Does anyone know why this is occurring or how I can prevent this from happening?

It's bound to the field in the table that I want to have it filter by...

So in the combo box it gives me the list of all the product numbers, so I can choose one and then click the "Find" button which brings up the edit form. I think it needs to be bound in order to find an existing product number...

I made the product number a primary field so that no duplicates can be made which fixes my initial problem of that duplicate number being made.
However, now when I try to close the form in which you choose the product number and click "Find" to apply the filter, I get the following error:

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

Followed by:
"You can't save this record at this time. Microsoft Access may have encountered an error while trying to save a record. If you close this object now, the data changes you made will be lost. Do you want to close the database object anyways?"

I'm assuming this is happening because it is once again trying to change that first value to a duplicate of a value that already exists in the table. But I don't understand why this is happening. Is there a way to stop this from happening?

no: don't bind the control to an underlying column if you want to use the list/combobox as a means of searching. if you are suign a bound control then changing the value of that control will change the value of column in the underlying table

what you should be doing is placing some code behind the combo to apply the filter

If the combo is bound, changing its value will also change the contents of the field to which it is bound into the table, the same way that when the contents of a textbox is changed, the form becomes "Dirty" and the modification is saved to the table when the current record of the form is saved.

Do you have any examples of what type of code needs to be placed to apply the filter?
I have no VB experience, which is why I've been trying to use all of the Access tools to accomplish everything I need...

but
1) change the combo to be an unbound control
2) click the combo, right click, select build event, select code
in the combo relevant event (can't tell you becuase its a while since Ive used VBA fdor this sort of thing)
place some that is similar to

you may also need another button to turn the filter off. in that buttons on click event
me.filteron = false

note that
text / string literals should be enclosed by ' eg "acolumn = 'mytext'"
date / time values should be expressed as US or ISO date format with the #
eg:-
"mydatecolumn = #12/02/2011#"
OR
"mydatecolumn = #2011/12/02#"