locked
Error 3078 in Access 2013 RRS feed

  • Question

  • I am not technical at all and have no idea about Microsoft Access or code. But I am trying to come up with an Events Database for use in work. I am following the VBA Intermediate youtube tutorials to create a calendar using the Events database template. But I am having a problem with the code I have just built using an strSQL.

    No matter how i change the code a different error will appear - currently error 3078 appears as my strSQL is said to not be defined. I do not know how to define it or if the SQL I am using has errors.I have tried changing the openrecordset to opendynaset but it does not accept this. I have tried the findfirst option but that also does not work. I have renamed the strSQL as the query name that I am trying to open - still nothing. I have checked if the table name does exist in my immediate window to which it says that it does not - however my query is quite clearly in the database and there to be manipulated.

    As I said I am not technical so it is probably a very simple issue but I have no idea what it is. Below is the module I have used to create the latest PublicSub Load Array - the rest of the formula loads it is only this module i am having issues with:

    Option Compare Database
    Option Explicit

    Public Sub LoadArray()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String

    strSQL = "SELECT * FROM Events WHERE (((Events.[Use Again])>=(DateAdd('h',-24,Now())) Or (Events.[Use Again]) Is Null)) ORDER BY Events.[Use Again];"


    'Debug.Print strSQL



        
    Set db = CurrentDb
    Set rs = db.OpenRecordset("strSQL")

    With rs

        Debug.Print .RecordCount
        .Filter = "[Use Again]=03/06/2017"
        Set rsFiltered = .OpenRecordset
        Debug.Print rsFiltered.RecordCount
        rsFiltered.MoveLast
        
        
        .Close
    End With

    Set rs = Nothing
    Set db = Nothing



    End Sub

    Any help would be appreciated!


    • Edited by Admin2012 Tuesday, February 14, 2017 5:35 AM
    Tuesday, February 14, 2017 5:23 AM

Answers

  • The following line of code:

    Set rs = db.OpenRecordset("strSQL")

    should be changed to:

    Set rs = db.OpenRecordset(strSQL)

    The current code is treating strSQL as a literal rather than a variable.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Admin2012 Tuesday, February 14, 2017 10:07 PM
    Tuesday, February 14, 2017 1:28 PM

All replies

  • The following line of code:

    Set rs = db.OpenRecordset("strSQL")

    should be changed to:

    Set rs = db.OpenRecordset(strSQL)

    The current code is treating strSQL as a literal rather than a variable.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Admin2012 Tuesday, February 14, 2017 10:07 PM
    Tuesday, February 14, 2017 1:28 PM
  • As you self describe yourself as a code novice - I would suggest you purchase one of the Calendar add-ons to Access that one can find out in the market.

    Calendars are tricky.  I've even contributed to Microsoft suggestion area that they add a Calendar form as an embedded feature of the Access product but......

    Tuesday, February 14, 2017 2:36 PM
  • Thank you so much!
    Tuesday, February 14, 2017 10:19 PM