Hi all,

This is a continuation of my previous thread...

I have a working macro that runs a query on an access table and pulls the data into an excel spreadsheet.

I would like to take this a step further and create some sort of user form in Excel to allow users pick certain criteria from each field. For example choices for field- "Name" will include "John", "Josh". etc. Essentially have a user create a query of an Access 97 table and run from within Excel.
I am not sure of it I am going about it the right way. In my code, I use the Me.cboName.value and the error is Invalid use of Me keyword.

Here's my Code- Any suggestions will be appreciated!
Sub Selectfromtable2()

Dim dbs As Database
Dim rs As Recordset
Dim Ws As Worksheet
Dim Path As String
'Dim i As Object
Dim strSQL As String
Dim strStatus As String

ThisWorkbook.Activate
''Open the database
Path = "C:\Documents and Settings\ofomaiu\Desktop\Rtest.mdb"
Set dbs = OpenDatabase(Path)

strStatus = Me.cboStatus.Value

strSQL = "SELECT * FROM [Pretrial]" & _
" WHERE [Pretrial].[Name] = '" & strStatus & "'" & _
" AND [Pretrial].[Age]= 35" & _
" ORDER BY [Pretrial].[Score];"

Set rs = dbs.OpenRecordset(strSQL)
Set Ws = ActiveSheet

For i = 0 To rs.Fields.Count - 1
Ws.Cells(1, i + 1) = rs.Fields(i).Name
Ws.Range("A2").CopyFromRecordset rs
Next
Sheets("Output").Select
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
Range("A1").Select
End Sub

Thanks!

Hi

You have not said where this code is written/running.

If it is in a code Module (not a Form's Module) then you need to explicitly refer to the form object (which is loaded of cause), ie

strStatus = UserForm1.cboStatus.Value

HTH


MTB