Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2010

    Unanswered: Create a Recordset From Two Tables

    Hello all,

    I am using VBA in Access to create a recordset with a passed parameter from an input box. The query is run on two tables. When I run the code I get an err message "No Current Record". It's passing the parameter so I don't understand what the problem is.

    The statement is:

    strOtg = InputBox("Outage")

    strSQL = "SELECT [tbl_Resource File].EmpID, [tbl_Resource File].Last_Name, " & _
    "[tbl_Resource File].First_Name, [tbl_fall 08].[Project Description], " & _
    "[tbl_fall 08].[Activity Desc#], [tbl_fall 08].[Travel In], [tbl_fall 08].[Travel Out], " & _
    "FROM [tbl_Resource File] RIGHT JOIN [tbl_fall 08] ON " & _
    "[tbl_Resource File].Open_Plan_Name = [tbl_fall 08].Resources_ " & _
    "WHERE ((([tbl_Resource File].EmpID) Is Not Null) and " & _
    "(([tbl_fall 08].[Project Description]) = '%" & strOtg & "%'))"

    Is the better question here whether or not I can even create a recordset like this?

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    Yes you can create a RecordSet that way:
    Dim strSQL as String
    Dim rst As DAO.Recordset
    strSQL = "SELECT ... FROM ... WHERE ... ORDER BY ..."
    Set rst = Currentdb.OpenRecordSet(strSQL, <options>)
    To identify the cause of an error in the SQL statement, copy the contents of strSQL from the immediate window (debug.print strSQL) and paste it in the Query Designer (set in SQL View). When you switch to Datasheet View it will point out where the problem is, if any: Perhaps there is no record matching the criteria.
    Have a nice day!

  3. #3
    Join Date
    Jan 2010
    Thank you Sinndho! Worked like a charm. I was missing the boat!

Posting Permissions

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