ADO and comboboxes

[Volrath1979]

I am working on a Visual Basic program using adodc to write and read from an Access database.
I have the following code:

Private Sub Form_Load()

With Adodc1
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
App.Path & "\DataBase.mdb;Persist Security Info=False"

.RecordSource = "select FName from Table1 order by Fname"
End With

End Sub

I want to fill the list of a combobox with the names that the query returns. Please help!
Thanks in advance!

[Hack]

Welcome to the forums.

It has been years beyond count since I've used bound controls, so this is something of a shot in the dark, but combo boxes have a datasource property and I think you need to bind that to your data control that does the query.

[Volrath1979]

Nice to meet you.
Yes, I know that there is a datasource property but I don't know the way to bind it...

[FunkyDexter]

You actually want rowsource rather than datasource. Since you've got a reference to ADODC1 in your code I assume you've put a ADO Data Controller on your form and called it ADODC1. Select your datacombo and click on it's row source property, which should turn into a dropdown. Drop it down and you should see that your ADO data control is in the list, select it and you're done.

You'll probably have to call ADODC1.refresh to get the data control to pick up the data though.

BTW the datasource property is basically the same except it filters by the value in the bindid column and is used for showing information from child tables etc.

[Hack]

You actually want rowsource rather than datasource. Since you've got a reference to ADODC1 in your code I assume you've put a ADO Data Controller on your form and called it ADODC1. Select your datacombo and click on it's row source property, which should turn into a dropdown. Drop it down and you should see that your ADO data control is in the list, select it and you're done.

You'll probably have to call ADODC1.refresh to get the data control to pick up the data though.

BTW the datasource property is basically the same except it filters by the value in the bindid column and is used for showing information from child tables etc.

I don't think he is using a datacombo, but rather a regular, standard VB combo. Will this still work with that control?

[Volrath1979]

Yes I use the standard VB combo and it doesn't work...
I think that the solution is through code but I don't know the code I have to write...
Maybe using a loopin additem way? Really I don't know...

[Hack]

Now writing code to do this is something I know how to do.

Dim ADOCn As ADODB.Connection
Dim ConnString As String
Dim adoRS As ADODB.Recordset
Dim sSQL As String
 
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
App.Path & "\DataBase.mdb;Persist Security Info=False"
 
Set ADOCn = New ADODB.Connection
ADOCn.ConnectionString = ConnString
ADOCn.Open ConnString
Set adoRS = New ADODB.Recordset
sSQL = "select FName from Table1 order by Fname"
adoRS.Open sSQL, ADOCn
Do Until adoRS.EOF
    Combo1.AddItem adoRS.Fields.Item("fname").Value
    adoRS.MoveNext
Loop
adoRS.Close
ADOCn.Close
Set ADOCn = Nothing
Set adoRS = Nothing

[FunkyDexter]

I don't think he is using a datacombo, but rather a regular, standard VB combo. Will this still work with that control?

Ooops, my mistake . No I don't think it'll work with a standard combo. I think you can bind a standard combo but I've always used code instead - much more control.

[Volrath1979]

Well done! It worked! Thanks a lot guys!

[Hack]

Ooops, my mistake . No I don't think it'll work with a standard combo. I think you can bind a standard combo but I've always used code instead - much more control.

No need to be embarrassed Funky. I don't use bound controls so I actually didn't know the answer to that and was curious as it could be asked by someone else at some point.

Thanks for the info.

Well done! It worked! Thanks a lot guys!

Good deal!

[lesthan60]

Now writing code to do this is something I know how to do.

Dim ADOCn As ADODB.Connection
Dim ConnString As String
Dim adoRS As ADODB.Recordset
Dim sSQL As String
 
ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
App.Path & "\DataBase.mdb;Persist Security Info=False"
 
Set ADOCn = New ADODB.Connection
ADOCn.ConnectionString = ConnString
ADOCn.Open ConnString
Set adoRS = New ADODB.Recordset
sSQL = "select FName from Table1 order by Fname"
adoRS.Open sSQL, ADOCn
Do Until adoRS.EOF
    Combo1.AddItem adoRS.Fields.Item("fname").Value
    adoRS.MoveNext
Loop
adoRS.Close
ADOCn.Close
Set ADOCn = Nothing
Set adoRS = Nothing

Will similar code work with excel?

[Hack]

Will similar code work with excel?

How do you mean?

What do you have and what do you need done with it?

[lesthan60]

The form I will be building will need to read and write data to an excel file.

[lesthan60]

The form I will be building will need to read and write data to an excel file.

So, will a similar code work with excel?

[Hack]

You are not connecting to a database, but a Excel spreadsheet. The approach is completely different.

The first thing you would need to do is set a reference to the Microsoft Excel Object Library, and open your sheet up. Have you ever done that before?

[lesthan60]

No, I've not.

[Hack]

Ok. From the VB IDE, click on Project/References

Locate the Microsoft Excel Object Library (x.x) (where x.x is the version you have)

Click on the Checkbox, then click OK.

Now that you have a reference to Excel, you can open your worksheet.

Private Sub cmdOpenWorkSheet_Click()
Dim xls As Excel.Application
         Dim xlBook As Excel.Workbook        
          Set xls = New Excel.Application
         Set xlBook = xls.Workbooks.Open(App.Path & "\yourworksheetname.xls")
End Sub

[lesthan60]

Thanks Hack, OK - I've located and clicked reference to Excel, I've created a form and I've pasted your code to that form and I've created a Excel file and added that name to your code.
When I click text1, readwritetest.xls then open readwritetest.xls (read only) cell A1 has not changed. After exiting VB readwritetest.xls remains as read only.

visual basic code:-------------------------------------------------------------------------------

Private Sub cmdOpenWorkSheet_Click()
Dim xls As Excel.Application
Dim xlBook As Excel.Workbook
Set xls = New Excel.Application
Set xlBook = xls.Workbooks.Open(App.Path & "\readwritetest.xls")
xlBook.Sheets("sheets").Cells(1, 1) = Me.Text1
End Sub

Private Sub Text1_Click()
Call cmdOpenWorkSheet_Click
End Sub

[Hack]

Cool.

If you have questions on the reading and writing part, I would suggest you create a new thread in either Office Development or ClassicVB in which you could post your specific questions about your Excel Code.

This is, after all, Volrath1979's thread and dealth with ADO and Combo boxes. We are going off in a whole new direction here, and I think you need to start your own thread with your own specific questions.

[lesthan60]

Yes, I'll start a new thread in Office Development. Thanks.