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

    Unanswered: OpenRecordset(Name)

    Hello,

    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 & "='@yahoo.com';"
    qryEBS = "SELECT * FROM EBS WHERE " & strFieldfilter & "='@yahoo.com';"



    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]

    myRecordset.MoveNext
    Loop

    '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
    'objItem.Save
    objItem.Display
    'objItem.Send



    Set myRecordset = Nothing

    AttachmentExit:
    Set objItem = Nothing
    Set objOutlook = Nothing
    Exit Sub

    AttachmentErr:
    MsgBox Err.Description, vbExclamation

    End Sub

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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
    Posts
    2

    OpenRecordset(Name)

    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
    Posts
    5,442
    Provided Answers: 14
    Not sure where the problem is but this was tested and works:
    Code:
    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
  •