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!
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
''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