Results 1 to 2 of 2
  1. #1
    Join Date
    May 2008
    Posts
    9

    Unanswered: Invalid use of Me. Keyword

    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!

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •