Variable Record Set

[SeanP]

I am trying to create a userform which has a series of ComboBoxes which would
be populated from an excel sheet using DAO.

I am using http://word.mvps.org/FAQs/InterDev/FillListBoxFromXLDAO.htm as a
reference on how to do this.

I want each ComboBox to be dependent upon each other. So really my question
is when retreiving a record set using

Set rs = db.OpenRecordset("SELECT * FROM `my_record_set`")

can `my_record_set` be a variable? I tried declaring a varible as a string
using

Dim myvariable As String

Set rs = db.OpenRecordset("SELECT * FROM myvariable")

but that does not seem to work, anyone know how I can do this?

 

[Anand.V.V.N]

Hi Sean,

Set rs = db.OpenRecordset("SELECT * FROM myvariable")
but that does not seem to work, anyone know how I can do this?

try this instead and ssee if it works

Set rs=db.OpenRecordset("Select * FROM " & myvariable)

This should work I guess

Anand

 

[SeanP]

I couldn't get that to work. I am setting declaring the variable as follows:

Dim myvariable As String
myvariable = "my_string_here"

' Open the database
Set db = OpenDatabase(".\test1.xls", False, False, "Excel 8.0")

' Retrieve the recordset
Set rs = db.OpenRecordset("Select * FROM & " myvariable)

Is there something else wrong?

 

[David Sisson]

Set rs = db.OpenRecordset("Select * FROM & " myvariable)

Move the ampersand outside of the quote.

Set rs = db.OpenRecordset("Select * FROM " & myvariable)

 

[SeanP]

Well that worked but now I found what I am trying to do does not seem to work
I thought I could populate a Combobox with a record set and then populate a
second combobox based on my first selection like so:

Private Sub ComboBox1_Change()

myvariable = ComboBox1.Text
'the text in combobox1 is the same name as the referenced Named Range on
the excel sheet, but is seems you can't set a string variable to ComboBox text

Set rs = db.OpenRecordset("Select * FROM " & myvariable)

With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With

ComboBox2.ColumnCount = rs.Fields.Count

ComboBox2.Column = rs.GetRows(NoOfRecords)

'cleanup code omitted

End Sub

Any thoughts on a better way to do this?

 

[David Sisson]

Step through the Sub and make sure ComboBox1.Text actually has a value.
If it does, can you hardcode the name for the DB and make it work?
Does ComboBox1.Text have any extra spaces? If so, use
TRIM(ComboBox1.Text)
Make sure the named range actually exists in Excel.

It appears you are using the correct code based on the article, but
I've not that familier with DAO.

Anyone else?

 

[Anand.V.V.N]

Hi Sean,

With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With

You can try this one, it might help

With rs

..Movefirst
..Movelast
..Movefirst
end with

NoOfRecords = .RecordCount

The above code gives me success all the time.

What's this code for? I just want to know.

ComboBox2.ColumnCount = rs.Fields.Count

ComboBox2.Column = rs.GetRows(NoOfRecords)

Anand