Why is selected item in combo box saved duplicate??

[xuanb]

Hi Experts,
I have a combo box based on a table/query row source.

Me.cboInstallation.RowSource = "SELECT installation FROM MyTable WHERE region = '" & Me.cboRegion & "';"
Me.cboInstallation = Me.cboInstallation.ItemData(0)

The Control Source for cboInstallation is a field called installation.

Now, when I select an item from cboInstallation, make some other changes on the form and close the form the selected installation item is saved as a duplicate record in the database. I understand that it's probably because the selected item appears in the text area of the combo AS WELL AS in the combo list itself...but how to avoid a duplicate record save??? PLEASE HELP ANYONE! Thanks!

 

[Trendsetter]

I am guessing that you have a primary key in my table and you are not bringing it into cboInstallation. When you modify the record it writes to the table but because there is no primary key to keep it unique it writes a new record which is a duplicate of installation but not of the primary key.
you need to bring the PK into the combo make that the bound column ans a column width of 0 so that only the second column shows in the combo dropdown.

 

[judgehopkins]

Get the installation information from a table with one column and one field and do not set a primary key.

Works for me....

Judge Hopkins

"...like hunting skunks underwater...."
John Steinbeck, The Grapes of Wrath

 

[xuanb]

Thanks Trendsetter and judghopkins.
This got me all thinking about my database structure and normalization rules. So I'm gonna improve the db and then will try to get the PK into the combo again.
Greetings

 

[judgehopkins]

Also, you need to check the relationships in your database. If, for example, you delete an entry in a table that is a primary key and is also linked to another table, you are asking for a real mess!

Judge Hopkins

"...like hunting skunks underwater...."
John Steinbeck, The Grapes of Wrath

 

[xuanb]

Ok, good thinking. I will set enforce data integrity rules and cascade the deletion.

BTW, I enjoyed reading Grapes of Wrath and ate biscuits with passion for 3 weeks after finishing the book...

 

[xuanb]

Trendsetter or judgdhopkins,
How do I reference the hidden PK in my 2 column combo?

"SELECT Facilities.InstallationID FROM Facilities " & _
" WHERE Facilities.InstallationID = " & Me.cboInstallation.Column(0)??? "

 

[xuanb]

Darn...I should add that my 2 column combo doesn't work the way you said I should bring the PK into the combo's hidden column

"SELECT InstallationID, InstallationName FROM Installations " & _
" WHERE RegionName = '" & Me.RegionName & "'"

Me.InstallationName.BoundColumn = 2
Me.InstallationName.ColumnCount = 2

What's going on?

 

[Trendsetter]

Your query has InstallationID as the first entry therefore in the combo this will be column(0) and first to show...In the combo property page set column count as 2, bound column as 1 and in column widths set 0;3.5cm - this will show only the installation name but will select the ID as that is the bound column.