I have a Combo Box Situation

[chrisguk]

I have created a service based DB with two tables as a test.


Table structure is as follows:


tblSIMStock:
pkStockID - int
StockDetail
DataNumber
fkStockLocationID


tblStockLocation:
pkStockLocationID - int
LocationDetail


There is a relation setup between fkStockLocationID and pkStockLocationID


This is also added to the dataset diagram.


I drag the tablle SIMStock to the form and then change the field fkStockLocation ID to a combo box. I ensure it is setup as a bound item with tblStockLocationBindingsource.


This is where the problem starts. I debug and then add a new record, I select an item from the combo box and click save. Then add another record and so on. My combo box values do not change according to the values I previously selected. So for example, I have 3 values:


Home
Away
Repair


I select create 3 records with each of them populated. But when I re-navigate through the records they all default to home again.


Any ideas guys?

 

[jmcilhinney]

You need to bind your ComboBox twice: once to the parent table and once to the child table. That's how values get transferred from the parent to the child.

First, you need to display the parent table data in the drop-down list. To do that, select the parent BindingSource as the DataSource and the LocationDetail column as the DisplayMember.

Next, you need to expose the ID of the selected record. To do that, select the pkStockLocationID column as the ValueMember. This says that, when the user makes a selection, the value from the pkStockLocationID column will be returned by the SelectedValue column and, when the SelectedValue is set, the record with that value in the pkStockLocationID column will be selected.

Finally, you use that last fact to bind the child data. You open the (DataBindings) property and select the SelectedValue. You then select the child BindingSource as the data source and the fkStockLocationID column as the property to bind to. Now, the SelectedValue of the control provides the bridge between the PK of the parent table and the FK of the child.

 

[chrisguk]

John, thank you for your reply but as I said before you have to explain things to me like I have never used a computer before: I have annotated your quote below:

You need to bind your ComboBox twice: once to the parent table and once to the child table. That's how values get transferred from the parent to the child.

First, you need to display the parent table data in the drop-down list (WHAT DROP DOWN LIST). To do that, select the parent BindingSource as the DataSource and the LocationDetail column as the DisplayMember (IN WHAT AREA THE PROPERTIES WINDOW OR THE POP OUT WINDOW OF THE DROP DOWN BOX).

Next, you need to expose the ID of the selected record. To do that, select the pkStockLocationID column as the ValueMember (WHERE??). This says that, when the user makes a selection, the value from the pkStockLocationID column will be returned by the SelectedValue column and, when the SelectedValue is set, the record with that value in the pkStockLocationID column will be selected.

Finally, you use that last fact to bind the child data. You open the (DataBindings) property and select the SelectedValue (WHERE??). You then select the child BindingSource as the data source and the fkStockLocationID column as the property to bind to. Now, the SelectedValue of the control provides the bridge between the PK of the parent table and the FK of the child.

 

[chrisguk]

You replied to a post before regarding this and gave a different example.How would I implement this and will it do what I need to, When I say implement where do I put my items in the quotes etc?

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

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

 

[jmcilhinney]

That's not a different example. It's exactly the same thing. If you have a look at what I said earlier, you need to bind the parent by setting the DataSource, DisplayMember and ValueMember. That's exactly what I've done in that code you got elsewhere. It can be done in code or in the designer but they are the exact same properties.

Likewise, you can bind to the child data in either the designer or in code. I said earlier that you expand the (DataBindings) property, select the SelectedValue and then specify the data source and the source column. If you look at the last line of that code you will see that all those elements are there. If you follow the instructions I provided in the designer the the IDE will generate that code for you.

It's up to you to decide whether you want to do your binding in the designer or in code. If you want to do it in the designer then you need access to your data source in the designer, which usually means a typed DataSet. If you have a typed DataSet and you have added an instance to a form then it doesn't make sense not to do the binding in the designer.

 

[chrisguk]

This is what I done, its completely wrong I know... but im pulling my hair out:

        FkStockLocationComboBox.DisplayMember = StockLocationDetailColumn        FkStockLocationComboBox.ValueMember = fkStockLocationID
        FkStockLocationComboBox.DataSource = Me.SimStockTableAdapter


        FkStockLocationComboBox.DataBindings.Add("fkStockLocationID", StockLocationTableAdapter, "PKStockLocationID")

 

[jmcilhinney]

You obviously have a typed DataSet. Have you added an instance to the form in the designer?

 

[chrisguk]

Hi John,

First of all I want to thank you for your patience with me. Im working my way to be an Excel MVP and I know how frustrating it can be when you are trying to explain things to people that have no idea.

VB is absolutely brand new to me and I only installed the program on my PC 4 weeks ago, I have watched many tutorials on You tube but I can find nothing that relates to the question I have asked you.

I didnt know what a typed dataset was but I have a little idea now after reading. Can you please do me a favour just this once to ppoint me in the right direction. I am not a massive fan of point and click applications and that was one of the reasons I wanted to get away from access. I prefer the code method and wish to go down that road.

Would you be kiind enough to paste your code above and fill in the gaps with an explanation why the items are in the gaps. Maybe then I will get it. By the way I have no idea if I have an instance there or not.

 

[cjard]

Mmm.. I dont know if the datarelation is confusing things but you do NOT need a datarelation between the lookup table and the data table in order to have the combo decode a value e.g. "3" into a display e.g. "Mrs"

Lets assume you have a combo called x, a PERSON table having a TITLE column containing 1,2,3 and a TITLELOOKUP table containing 1=Mr, 2=Miss, 3=Mrs etc

x.DataSource = MyDataSet.TITLELOOKUP
x.DisplayMember = "TITLETEXT"
x.ValueMember = "TITLEID"
x.DropDownStyle= ComboBox.DropDownList 'make it not editable as that really screws things up
x.DataBindings.Add("SelectedValue", MyDataSet.PERSON, "TITLEID")


Forget the relation.. delete it, it's not necessary
When the combo is called upon to bind to a value 3 as its VALUE, it will, but it will DISPLAY "Mrs". If you change it to "Mr" it will poke a values of "1" into the back end table

After you drop combo on the form, the .Text property is bound to VALUE (in the (Bindings) part of the properties grid.. Delete that binding


I actually do all the above binding in the properties grid rather than in code, and i recommend you do too.. Fewer chances of error (i.e. the DataBindings.Add line you wrote was completely broken.. the visual designer wpould never have made that mistake, so use the visual designer wherever possible.. it really helps make things logical and easy to see how they work).
After dropping your combo, click it and expand everything in the properties grid in the viusual designer.. Set the datasource first, then set disp and val members, move the selectedvalue binding from Text to Value
(cut paste)

 

[cjard]

I am not a massive fan of point and click applications and that was one of the reasons I wanted to get away from access. I prefer the code method and wish to go down that road.

Get over this aversion, it is totally inconsistent and hypocritical to maintain such a premise, while using the Forms designer to design your form - all it does is write good, working code in the background.. Thousands of lines of it. To Say "i want to do it all in code so i understand what's happening" means you should sit down with notepad, and write every line, position every text box, set every font, nudge every pixel - you dont do that. The visual designers are not for dummies, nor do they generate bad, slow code.. They actually do an amazing job of writing better code in seconds than a human could in hours and let you get on with writing the logic of your program, rather than messing continually with presentational aspects. Further, while you use the visual designer to lay out your form and it puts all its code in InitComponent, you shouldnt then put further layout/control/presentational chunks of code in your own constructors where possible to avoid.. Set your data bindings in the viusual designer and they will also go in InitComponent(), where they should be

 

[chrisguk]

Hi,

Many thanks for your two replies, I am quite sure they will help a lot. I am kind of old school when we didnt have application designers etc and when they were first launched the rest of the community still wanted to hard code. Im talking UNIX based etc etc.

Anyway it is obvious that Microsoft have put a lot of work into building successful "Rapid Deployment Applications" as demonstrated in there expo on Windows 8. They even told the 3rd party vendor developers to use the designer interface and don't go messing with the code as the MS solution works perfect. They were in the most part referring to the Metro apps but I guess it counts for all apps.

Anyway, I am going to put your suggestion to work and will come back to you if I have an major problems.

Many thanks again for the informative post

 

[chrisguk]

Just one question how do I display the items in the "" (Quotes)

What worries me is that Combo boxes are used day in day out throughout millions of VB applications and I am wondering why I am finding this so difficult. Its not like I have not programmed in other languages. I will explain (Using the designer) what I think I understand so far and let me know if im going wrong anywhere.

Form: SIMStock

SIM Stock field items have been dragged on to the form in details view. One of those fields is fkStockLocationID.

I delete the default textbox and place a combo box on. Then I click on the little icon at the top left of the combo and select > Use data bound items > Then under "Data Binding Mode" I select the following:

DataSource = StockLocationBindingSource
DataMember = StockLocationDetail
ValueMember = pkStockLocationID
Selected Value = None

I debug my form then add a record. Click save after selected a value member from the combo box then proceed to add another record and click save.

What happens next: The values I selected in the Como box default to the last selected value in my current record . Why is this?

 

[cjard]

The Text property should not be bound and the SelectedValue should be bound - look in the (Bindings) sub-tree of the properties grid for the combo

You can upload your just DB here (empty of data if you wish) and I'll make a small demo project out of it an upload it to demonstrate

 

[chrisguk]

Hi there,

I have attached my project for you to see. Its not rocket science just a couple of basic forms.

 

[cjard]

will reply monday when i've access to a laptop with vs..