I am having a small problem locating any information on this little problem on the net so I am coming here for help AGAIN.
I have a VB6 project that has a combobox. This is needing to display data from my SQL db. I have it displaying the data fine. But if I have a value already set in the db for this record i want it to display that item.
Unfortunately I am having issues with this. Here is the code I have so far.

Set rsRPh = New ADODB.Recordset
            rsRPh.Open "Select RPhID, firstname + ' ' + MI + '. ' + lastname as salerep from rph where IsSales = 1", adoConn
                Do While Not rsRPh.EOF
                    cboSalesRep.AddItem rsRPh!salerep
                    cboSalesRep.ItemData(cboSalesRep.NewIndex) = rsRPh!RPhID
                    rsRPh.MoveNext
                Loop
                rsRPh.Close
                Set rsRPh = Nothing
            If .SalesRep > 0 Then
                cboSalesRep.ListIndex = .SalesRep
            End If

Any suggestions are greatly appreciated.

thanks

Where is .SalesRep declared and where is its parents with statement?

Good Luck

the with statement contains about 150 lines of code so i didnt post all of it. the .salesrep is the id that is set in the table to associate the salesperson to the product. The .salesrep is pulling the correct information. The SalesRep is the same as the RPhID.
After looking at that code im not sure exactly what i was thinking. What i need it to do is display the sales rep that is set for this sale. but if the user needs to change this salesrep they can just click on the cbo.
Hope im not confusing anybody on this.

Has this been solved? Or is there still a question?

the .salesrep is the id that is set in the table

Is this Access? Because that is the only place where you would refer to a table in VB.
I am not a fan of Access...

this is connected to sql 2000 db. it is not solved as of yet.

Okay, several options here that I can think of but without further code I'm shooting in the dark but here is what I can guess from what I have read so far...

You have a table with sales reps names that has a unique ID.
The combo is populated with the sales reps names as pulled from database by some order but probably on the unique ID.

Problem at this point. While the unique ID is more than likely 1 based the combo box index is 0 based and if you could guarantee that reps would always never quit, retire, or die then all you would need is unique ID - 1 to get the sales rep name to display and inversly Index + 1 to set the sales rep. However, I doubt that you could gaurantee such a thing.

So now you want to display in the combo box the name of the rep that will be credited for the sale and you have the reps unique ID in the .SalesRep variable.

So, with this information you can...

Query the database for the reps name, then loop through the combo box looking for a match and once the match is found then use the index to display the name.

OR

You could create a type something like...

Private Type Rep
  RepName As String
  RepIndex As Integer
  RepUnique As Long
End Type
Dim Reps() as Rep

and when you populate the combo box with the rep names you also populate the Reps() array of Rep and use this for a lookup.

Now, I know there are other ways in which to solve this but with what info I have, the two above are the simplest.

Good Luck

I know nothing of sql so bear with me, and this code is vb.net not vb6, by the way.

Cbo.add "salesrep name or id"
cbo.listindex=0 nothing selected

cboSalesRep.ItemData(cboSalesRep.NewIndex) = rsRPh

combobox.itemData Property
data = combobox.itemData(item);
combobox.itemData(item) = data;

I assume that rsRPh changes to a new number on each add item?

"have a value already set in the db for this record i want it to display that item."

I've just swapped the references so the id is displayed and not the Sales rep name.
cboSalesRep.AddItem rsRPh!RPhID
cboSalesRep.ItemData(cboSalesRep.NewIndex) = rsRPh!Salerep

itemdata is only an identifier. Perhaps you should have multi column combobox so the first column shows "ID" the next item column shows "Sales Rep Name" and perhap the next column shows "Sales Area Number or Location"?

If anything I say makes no sense or is totally irrelevant to the Question, let me know and I'll stop being a pest, thankyou.
Is this what you want? If not, then be more specific.

after looking at my posts i see how it is confusing.
what i need is to set the text of the cbo to the salerep name (works already). I also want to set the rphid to the text. so when the user selects this sales rep and saves i can just use the id that is associated with the name listed when i save. saving only the id and not the name.

Then you should be able to use the click event of the combo box to query for the ID be it against the database, the text of the combo box (if you saved the ID with the text) or against its index (if you have some sort of way of doing that.

Good Luck

combobox.itemData Property
data = combobox.itemData(item);
combobox.itemData(item) = data;
This is not the exact proper coding but you see what I mean (my Excel is on a different computer. This computer is my internet only unit. It's an invention of mine. All my other computers can see it but "it" cannot see outside the box. This means if I get invaded by hackers or viruses they are stuck inside the box. ) -

for i=0 to cboBox.listCount-1
If cboBox.list(i).value ="Ted Bunson"
then strID =cboBox.itemData(i)
msgbox
strID
exit for
Next
end if

I'm just doing something else on my Excel computer so when I'm done with that I can write the proper code. Hopefully you can do it yourself (hehehe).

sorry for the delay in getting back. had other projects that had to be complete.
thanks for all of your help.
not sure why i had the problem in the first place because that code is working now. very odd i think.
i did remove the last if statement.
thanks for all of your input it is greatly appreciated.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.