Binding Foreign Key to Combobox

[daniness]

Hello All,

Can anyone please advise me on this:

I have 2 tables:

Locations
location_refnbr (pk)
location_name
office_refnbr (fk)


Offices
office_refnbr (pk)
office_name

On my first form, I have a combobox that is bound to the Locations table and displays all the different locations. Once a location is selected, a 2nd form is loaded which has textboxes which are also bound to the Locations table and are populated with the corresponding data.

Here's my issue: on the 2nd form, I have combobox that is bound to the Offices table. Currently, when this form loads all the available offices, displaying with the first office in the list. I need it to populate with the correct office based on the selection made on the combobox on the first form. I guess what I'm asking is, how does one bind a combobox to a foreign key b/c it is the foreign key, office_refnbr, from the Locations table that needs to display in the Office combobox.

I've tried a variety of bindings, including:

'Me.cboOffices.DataBindings.Add("text", frmEditDel.LocationsBindingSource, "office_refnbr")

'Me.cboOffices.DataBindings.Add("text", Me.OfficesBindingSource, "office_name")

'Me.cboOffices.DataBindings.Add("text", frmEditDel.LocationsBindingSource, "office_name")

'Me.cboOffices.DataBindings.Add("SelectedValue", Dos_DataSet.FKOfficesLocationsBindingsource, "office_refnbr")

'Me.cboOffices.DataBindings.Add("Selected Value", frmEditDel.LocationsBindingSource, "office_refnbr")

'Me.cboOffices.DataBindings.Add("Selected Value", Dos_trackDataSet.locations, "office_refnbr")

'Me.cboOffices.DataBindings.Add("ValueMember", Dos_trackDataSet.locations, "office_refnbr")

None of these are working and I've been ripping my hair out because of it. Could someone please advise?

 

[jmcilhinney]

You need to bind the ComboBox twice: once to the parent table and once to the child table, e.g.

myComboBox.DisplayMember = "ParentName"
myComboBox.ValueMember = "ParentID"
myComboBox.DataSource = parentTable

myComboBox.DataBindings.Add("SelectedValue", childTable, "ParentID")

When a record is selected in the child table that sets the SelectedValue of the ComboBox, which selects an item in the parent table and displays the corresponding parent name. When the users selects a parent name in the ComboBox that sets the SelectedValue property to the correspond parent ID, which updates the foreign key of the current child record.

 

[daniness]

Thanks, jm.

I tried what you suggested, but keep receiving an argument exception..."This causes two bindings in the collection to bind to the same property. Paramenter name: binding". I checked the code, but don't see any other lines that are binding anything for SelectedValue. Any idea?

 

[daniness]

You need to bind the ComboBox twice: once to the parent table and once to the child table, e.g.

myComboBox.DisplayMember = "ParentName"
myComboBox.ValueMember = "ParentID"
myComboBox.DataSource = parentTable

myComboBox.DataBindings.Add("SelectedValue", childTable, "ParentID")

When a record is selected in the child table that sets the SelectedValue of the ComboBox, which selects an item in the parent table and displays the corresponding parent name. When the users selects a parent name in the ComboBox that sets the SelectedValue property to the correspond parent ID, which updates the foreign key of the current child record.

JM, regarding what you're saying here, I'd like to clarify one thing: on the first form, even though the combobox is linked to the child table, this is what determines what office is populated upon the 2nd form's load event, depending on which location is selected on the first form. The first form's combobox is linked to the table which has the foreign key of office_refnbr. So based on this, I'd like to get the 2nd form's combobox, which is linked to the parent table of Offices, containing the primary key of office_refnbr, to populate with the corresponding office based on the selection made in the Locations combobox. Kinda backwards, I know. Any advice and pointers are appreciated always!