Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 48
  1. #16
    matt704 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    49
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)


    Set rstOut = CurrentDb.OpenRecordset("tmpLastDate", dbOpenDynaset)
    rst.MoveFirst

    So what part of the above code do I modify?

  2. #17
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    None of that, you must change the name of the field where the error is happening. In the strSQL there is a list of field names and you must use the one pertaining to that. I'm trying to show you how to read the code.

    "Set rst=" is creating the list of records that will be used in the loop to process the data. The strSQL is just a query, you can copy and paste it into query design and see what the query looks like. The name of the date field in the query is the one where the error is happening. (I seem to have changed it everywhere else except this one line).

  3. #18
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Change it to "rst!EventDateAndTime"

  4. #19
    matt704 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    49
    Great! Thanks for forcing me to work through it. Error is gone! now time to play with it in the big DB!

  5. #20
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Good luck!

  6. #21
    matt704 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    49
    Got another hurdle to jump. when I run it in the large DB I get "Compile error: User-defined type not defined" AND this line is highlighted in the VBA code "Public Function GetLastDT()"

  7. #22
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Did you copy over the module to the main db?

  8. #23
    matt704 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    49
    Yes I did copy and paste the module and then tried to run via a Macro. I updated the table where the data was pulled from and some of the field names

  9. #24
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    That line is highlighted because it is telling you that there is a compile error inside that function. Go to VBA editor and compile it manually (Debug>Compile) and it will tell you what is going on. You may have Option Explicit in this database and not in the other.

  10. #25
    matt704 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    49
    Do I need to create the table "tmpLastDate" with blank rows first?

  11. #26
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Oh yes! You can just copy it over/import it. It doesn't have to be blank - look at the function, it deletes all the data before it runs.

  12. #27
    matt704 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    49
    No I'm getting the "Compile error: User-defined type not defined" and in the VBA code window "rst As Recordset" is highlighted in blue

  13. #28
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Go to Tool>References and see if any say MISSING. Also try "rst As DAO.Recordset"

  14. #29
    matt704 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    49
    None of the Available References that are checked say "MISSING" (i assume that would be in the location? I am not sure where to run the "rst As DAO.Recordset"

  15. #30
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Not run, type.

Page 2 of 4 FirstFirst 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 07-16-2016, 11:38 AM
  2. Replies: 6
    Last Post: 04-19-2016, 03:58 PM
  3. Replies: 7
    Last Post: 09-02-2014, 08:18 AM
  4. Subtracting a date/time field from a number to get date/time
    By Lifeseeker1019 in forum Programming
    Replies: 4
    Last Post: 03-28-2014, 12:59 PM
  5. Replies: 5
    Last Post: 03-19-2014, 02:31 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums