Link Excel form to Access query result or any other ADO

[Guest]

Is it possible to have the rowsource of a list box in a form be the result of
an access query.
Same question regarding a TextBox.

Many thanks,
Dan

 

[Guest]

this will work for combobox
Private Sub UserForm_Initialize()

On Error GoTo UserForm_Initialize_Err
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Path\to mydatabase\mydatabase.mdb"

rst.Open "SELECT DISTINCT table.field" _
& "FROM table;", _
cnn, adOpenStatic
rst.MoveFirst

With Me.ComboBox1
.Clear
Do
.AddItem rst!Field
rst.MoveNext
Loop Until rst.EOF
End With

UserForm_Initialize_Exit:
On Error Resume Next
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub

UserForm_Initialize_Err:
MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
Resume UserForm_Initialize_Exit
End Sub

 

[kathepoo]

I was trying to create a macro that went to several workbooks then to
a specific worksheet did an autofilter on a specific field the copied
and pasted those into a new work book. The paste of the selection
will be dynamic. I can give a specific cell destination for the paste
in a new work book. Do you have a macro that does something similar
in which I could tweak the code??

If you do I will be up a little while.