Unanswered: Combo box updates but source table populates duplicate data with Unique set on column
Forms involved in Problem
Main Form - [Prospecting]
Field in Question - SubDivision = Combobox with RowSource from Table [SubDivisions]
On [Prospecting] Form [NewSubDivision] is Opened with OpenForm button wizard on [Prospecting]
NewSubdivision created from Table [SubDivisions]
Table SubDivisions has columns - SubDivision-Short Text - unique; Township - Short Text; 55+ - Yes/No
I am updating a combo (Subdivision) on main form [Prospecting] using VBA to requery the combo (Subdivision) by adding code to the close button on form NewSubDivision after adding new info to it. NewSubDivision is based on a table [SubDivisions].
The problem I am having is that the combo updates fine with the VBA but the table the form is based on also updates with the new data even if the new data happens to be duplicated. The column Subdivision is set to Unique. If I open the form (Subdivision) without the main form open I get the expeted error that the table could not be updated because it would create duplicate data.... then it follows with an error that the main form [Prospecting] could not be found...which is also expected as I was testing the form (Subdivision) to make sure no duplicates were being permitted.
Is there a way to check if data already exists and prevent the requery of the combo-box. I thought that access would stop the table being updated as normal as I intentionally added duplicate data..The Form (NewSubDivision) is being opened by a OpenForm button on the main form.
The VBA I am using to requery the combo-box on the mainform - currently in the OnMouseDown event on the close button on form (NewSubDivision) is as follows -
I solved the problem by moving the requery event from OnMouseDown to the SaveRecord button OnMouseDown event which prevents duplicate data being entered into the source table or of course the combo on the main form.
The reason I used the OnMousedown event rather than OnClick event is because the OnClick event contains embedded macro from the SaveRecord button wizard and I am not 100% confident with changing Macros in Access 2013 yet.
Last edited by DDEWILDE; 05-26-15 at 14:42.
Reason: Clarify reasoning