Can't .(dot) datasource property for a DBCombo Box in VB5 2

[GIDEONR]

I have been trying to drive a DBCombo box in code using a with statement and .Property but the .DataSource property doesn't appear in the list and when I type it in, the debug throuws up an error.

Can Anyone suggest why?

 

[swilliams]

Can you post the code you are trying to use??

In the absence of code to see how you are trying to do this, I would do it like this:

1. Put a combo box on your form.
2. In the Load event of the form, add code to:
(i) create a recordset
(ii) loop through the recordset, adding the items to the combo box.

Example:

Private Sub Form_Load()
Dim db As Database
Dim rs As Recordset
Dim sSql As String

Set db = workspaces(0).OpenDatabase("c:\aa\db1.mdb", False, False)
sSql = "SELECT DISTINCT Field1 FROM Table1 ORDER BY Field1"
Set rs = db.OpenRecordset(sSql, dbOpenSnapshot)
Do Until rs.EOF
Combo1.AddItem rs("Field1&quot
rs.MoveNext
Loop
End Sub

Simon

 

[GIDEONR]

SWilliams asked to see the code, so here it is, probably not very clever as I'm a newbie.

With dbcProductCode ' This is a DBCombo Box
.Rowsource = "datProductTable" ' Data Control Loaded from previous code
.ListField = "Product"
.DataSource = "datProduction" ' TYyped in but not recognised
.DataField = "CompanyProductID"
End With
The datProductTable loads a query into a 2nd data control to run the DBCombo box.

 

[swilliams]

Personally, I do not like using data control, although it is a great way to begin (and that is exactly how I began).

To explain how I would now do what you are trying to do, I would use the code below:

Private Sub Form_Load()
Dim db As Database
Dim rs As Recordset
Dim sSql As String

Set db = workspaces(0).OpenDatabase("c:\aa\db1.mdb", False, False)
sSql = "SELECT DISTINCT CompanyProductID, Product FROM Table1 ORDER BY Product" ' still need to change table name
Set rs = db.OpenRecordset(sSql, dbOpenSnapshot)
Do Until rs.EOF
Combo1.AddItem rs("Product&quot
Combo1.ItemData(Combo1.NewIndex) = rs("CompanyProductID&quot
rs.MoveNext
Loop
' forgot the following last time
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

To get the CompanyProductID when a product is selected, use:

Combo1.ItemData(Combo1.ListIndex)

BUT: be sure to check that Combo1.ListIndex <> -1 as the above line will give an error when this is the case (all it means is that nothing has been selected.

Simon

 

[GIDEONR]

Thanks to SWilliams for the comments. I am trying to replicate the suggested way of doing it am still struggling with the SQL syntax I think. I have run the query in VisData and it worked, any suggestions!!

Below is code I have been using if it helps

sSQL = &quot;SELECT ProductID, CompanyProductID+&quot;, &quot; +ProdName AS Products FROM tblProducts ORDER BY CompanyPoductID&quot;
Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
Do Until rs.EOF
cboProductSelect.AddItem rs(&quot;ProdName&quot
cboProductSelect.ItemData(cboProductSelect.NewIndex) = rs(&quot;CompanyProductID&quot
rs.MoveNext
Loop

 

[swilliams]

I don't undestand what you are trying to do with the +&quot;, &quot; + part?????

If you can answer these questions, then I will be able to give you the sql statement:

1. What is the table name you are selecting from??
looks like tblProducts

2. Which field is the ID field to the table??
looks like ProductID

3. Which field holds the product names??

Simon

 

[GIDEONR]

SWilliams answers to your questions: -
The &quot;, &quot; was put into the SQL to cancatenate the companyid with product name.

Ans 1 - Your right tblProducts

Ans 2 - The actual Primary key for the table is ProductID

Ans 3 - ProdName hold the name of the product

By the way I have now had a chance to try your code in the combo box and seem to be having trouble with the code line

cboProductSelect.ItemData(cboProductSelect.NewIndex) = rstProducts you suggested. Have I missed something. I have attached the code I have used to support what I have done

strProductSQL = &quot;SELECT ProductID, CompanyProductID+&quot;&quot;, &quot;&quot; + ProdName AS Products FROM tblProducts ORDER BY CompanyProductID ASC&quot;
Set rstProducts = db.OpenRecordset(strProductSQL, dbOpenSnapshot)
'
Do Until rstProducts.EOF
cboProductSelect.AddItem rstProducts(&quot;Products&quot
' cboProductSelect.ItemData(cboProductSelect.NewIndex) = rstProducts(&quot;CompanyProductID&quot
rstProducts.MoveNext
Loop
'
cboProductSelect.ItemData (cboProductSelect.ListIndex) This line is also causing debug errors!

Sorry to take up so much of your time but it is gratefully appreciated.

GideonR

 

[swilliams]

If you want to concatonate the two fields to show in the combo box, there are two ways to do it:

1. Amend the SQL statement to read:
sql = &quot;SELECT ProductID, CompanyProductID & &quot;&quot;, &quot;&quot; & ProdName AS Products FROM tblProducts ORDER BY CompanyProductID ASC&quot;
Then, when you use the AddItem method of the combo box, use the field ProdName in your recordset:
Combo1.AddItem rsProducts(&quot;ProdName&quot

2. Select the three distinct fields in the sql statement:
sql = &quot;SELECT ProductID, CompanyProductID, ProdName FROM tblProducts ORDER BY CompanyProductID ASC&quot;
Then when you use the AddItem method of the combo box, use the two fields you want to show:
Combo1.AddItem rsProducts(&quot;CompanyProductID&quot & &quot;, &quot; & rsProducts(&quot;ProdName&quot

You are probably getting an error on the ItemData method of the combo box, as you are trying to the set the value to a field that is NOT selected in the sql statement.

Although you are using this field to build another composite field, the field rs(&quot;CompanyProductID&quot does not exist.

To get around both of these errors, try using method #2 above.

You last problem is with cboProductSelect.ItemData(cboProductSelect.ListIndex).
This may be because ItemData(Index) only works when Index >= 0. However, when nothing is selected in the combo box, ListIndex will be -1, so cboProductSelect.ItemData(cboProductSelect.ListIndex) will equate to cboProductSelect.ItemData(-1) when nothing is selected, so giving an error.

Simon