Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
Set rstOut = CurrentDb.OpenRecordset("tmpLastDate", dbOpenDynaset)
rst.MoveFirst
So what part of the above code do I modify?
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
Set rstOut = CurrentDb.OpenRecordset("tmpLastDate", dbOpenDynaset)
rst.MoveFirst
So what part of the above code do I modify?
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).
Change it to "rst!EventDateAndTime"
Great! Thanks for forcing me to work through it. Error is gone! now time to play with it in the big DB!
Good luck!
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()"
Did you copy over the module to the main db?
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
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.
Do I need to create the table "tmpLastDate" with blank rows first?
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.
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
Go to Tool>References and see if any say MISSING. Also try "rst As DAO.Recordset"
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"
Not run, type.