Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2013

    Unanswered: OpenRecordset(Name)


    I have been trying to open a recordset using SQL statement which is stored in a variable and the variable is provided by the user via InputBox. However, VBA is searching for a database object and returns an error. If I hardcode the argument as a string variable it seems to work fine, but the user should choose a recordset. here is the code:

    Private Sub Command17_Click()

    Dim objItem As Object
    Dim objOutlook As Object
    Dim blnNeedToQuit As Boolean

    Dim mailbody As String
    Dim strFieldfilter As String
    Dim qryEBS As String
    Dim qryINFONET As String
    Dim qryName As String

    strFieldfilter = InputBox("enter a field name to filter", "Field")
    strName = InputBox("enter the query name", "Query")

    qryINFONET = "SELECT * FROM INFONET WHERE " & strFieldfilter & "='';"
    qryEBS = "SELECT * FROM EBS WHERE " & strFieldfilter & "='';"

    Dim myDatabase As DAO.Database
    Set myDatabase = Application.CurrentDb

    Dim myRecordset As DAO.Recordset
    Set myRecordset = myDatabase.OpenRecordset(strName, dbOpenDynaset)

    'Save the records in a string variable
    Do Until myRecordset.EOF
    mailbody = mailbody & vbCr & myRecordset![ID] & " | " & myRecordset![Role] & " | " & myRecordset![UserName] & " | " & myRecordset![Email]


    'MsgBox mailbody

    ' Reference Outlook application object
    On Error Resume Next
    Set objOutlook = GetObject("Outlook.Application")

    ' If Outlook isn't running, then create an instance of it
    If Err Then
    Set objOutlook = CreateObject("Outlook.Application")
    blnNeedToQuit = True
    End If

    On Error GoTo AttachmentErr

    Set objItem = objOutlook.CreateItem(0)
    objItem.To = ""
    objItem.CC = ""
    objItem.Subject = "*TEST! PLEASE, IGNORE*"
    objItem.Body = "this message was sent as a test. " & vbCr & mailbody

    Set myRecordset = Nothing

    Set objItem = Nothing
    Set objOutlook = Nothing
    Exit Sub

    MsgBox Err.Description, vbExclamation

    End Sub

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    the the postioning of your apostrophe's/text delimters
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2013


    Thank you for the reply, but I'm not sure that I understand what you mean.
    I would like VBA to take the value of strName which for example will be the string stored in qryEBS. Instead VBA searches for an Access object called strName. I only use apostrophe to delimit the filters in the SQL statement. Can you give me an example of what you have in mind. Thanks.

  4. #4
    Join Date
    Mar 2009
    Provided Answers: 14
    Not sure where the problem is but this was tested and works:
    Dim strName As String
    Dim myDatabase As DAO.Database
    Dim myRecordset As DAO.Recordset
    strName = InputBox("enter the query name", "Query")
    Set myDatabase = Application.CurrentDb
    Set myRecordset = myDatabase.OpenRecordset(strName, dbOpenDynaset)
    Have a nice day!

Posting Permissions

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