Problem looking up values by selecting value in combobox

Hello all,

I'm developping a database application and i'm having problems selecting values.
I have two tables
There is a one to many relation between TBLCLIENTS.CLIENT_ID and TBLJOBS.CLIENT_ID
In my form i want a combobox selecting JOB-ID.
After the JOB_ID is selected i want my client fields updated depending on CLIENT_ID field.
Can anyone help me out here?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Can you not use an SQL query to achive this?

Update TBLCLIENTS SET Name = 'newname', Address='....   WHERE JOB-ID = '...

JackUziAuthor Commented:
There is only a relation on the CLIENT_ID field.
This is because there are more jobs for one client.
ah ok.

Update TBLClients,a TBLJOBS,b SET a.Name='...', a.Address='...   WHERE a.CLIENT_ID=b.CLIENT_ID AND JOB_ID = '...
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

JackUziAuthor Commented:
Do you also know how to put this in code in the combobox.selectedchanged event?
JackUziAuthor Commented:
I think i was a bit confusing by saying i wanted to update my client fields.
On my form there are also databound textboxes to display the client data.
So when I select a job-ID then i would show the corresponding client that is related to that job.
Try code like this to bind your combobox

   ComboBox.DataSource = TBLJOBS
   ComboBox.DisplayMember = "JOB_ID"
   ComboBox.ValueMember = "CLIENT_ID"
   ComboBox.DataBindings.Add("SelectedValue", TBLCLIENTS, "CLIENT_ID")

You shouldn't then need any other code in any of the combobox event subs.  As a new JOB_ID is selected in the conbobox, the relevant CLIENT_ID becomes the combobox's new SelectedValue, and the last line of the code will shift the pointer in the TBLCLIENTS table to the appropriate record for that.

JackUziAuthor Commented:
I'm getting an error on the line where i set the valuememeber to CLIENT_ID.
System.ArgumentException was unhandled
  Message="This causes two bindings in the collection to bind to the same property.
Parameter name: binding"
What other databinding code you do you have?

JackUziAuthor Commented:
This code is run in the onload event of the form.

        cboJOB_ID.DataSource = Me.DBDataSet.TBLJOBS
        cboJOB_ID.DisplayMember = "JOB_ID"
        cboJOB_ID.ValueMember = "CLIENTID"
        cboJOB_ID.DataBindings.Add("SelectedValue", Me.TBLCLIENTBindingSource, "CLIENTID")

Then on the form I have textboxes where the databinding.text property is tblclient.client_name, tblclient_street, ...
JackUziAuthor Commented:
OK, the problem was that i dragged the JOB_ID combobox from the datasources window on the form.
So all properties concearning databindings are allready filled in.

I removed them and runned the code like you sugested but nothing changes in the textboxes.
(Databindings).text property is set to tblclientbindingsource - clientName and so on.
Textboxes are dragged from the datasource window.
Is this correct?
My apologies.  First for the delay in coming back - but it is Sunday evening where I am ;-)  Second for not checking which version of VB.NET you were on before I commented.  And third for misleading you that the link could be automatic by adding

        cboJOB_ID.DataBindings.Add("SelectedValue", Me.TBLCLIENTBindingSource, "CLIENTID")

That only works the other way round on a Many To One Relationship.

You can scrap that line and, instead, in cboJOB_IDSelectedIndexChanged put

        TBLCLIENTBindingSource.Filter = "CLIENTID = " & cboJOB_ID.SelectedValue

I think the terminology in that is right - I've tried to take it from the object names your code uses - but you need to check it.  The basic idea is what you need.  That is, use the CLIENTID obtained from the jobs combobox to apply a filter to the information displayed about the client.

I hope that hasn't held you up too much.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JackUziAuthor Commented:
Thanks Roger
I was searching for this answer for a few days now and you provided me the answer.
Now I can finally get to the other stuff.

By the way, Don't you know some interesting web links about database programming, because I'm new to VB.NET development and I don't understand that databinding issue so good.

Thank you
Have a look at some of the hits on this search string

There's a sample database - Northwind, another one is Pubs (short for publishers, not licensed premises) - that MS provides for free and many articles/examples use that for illustration.  So mention of it in a search will usually throw up stuff you can actually try out to see what's happening.

If you want a more rounded approach/description then I find the section Headed "Displaying Data on Forms in Windows Applications" in the Help File a good starting point.

JackUziAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.