Could someone please explain how to use OpenRecodset? I consider myself to be pretty good with Access, but I just can't get this. I am making a form that calculates the average time. I tested it out with a table called "Average Time" and used this for OpenRecordset:
Set RS = DB.OpenRecordset("Average Time")
When I tried doing the same thing in the production database, the records where in a query. No problem I thought. I replaced "Average Time" with the name of the query. Now I get "Run-time error '3061'". With I little research I found out it is due to the criteria in the query referencing my form fields. I did some more research to find out how to do it with criteria, and it doesn't make any sense to me.
With OpenRecordset you open a kind of table, and not a query. If you want to open a query, write the SQL-string instead of the tablename (db.openrecordset("Select * from bla",dbopendynaset) ) or use the querydef-object of the DB.
Thanks for the help. It seems to be working better now. One thing is causing problems with the SQL though. I need to use just the time portion of a date/time stamp. So my code looks like this:
strSQL = "SELECT [Employee - Actual Daily Arrival Time].Name, Format([First Badge Access],'Medium Time') AS [Arrival Time] FROM [Employee - Actual Daily Arrival Time] WHERE Name = '" & cmbName.value &"' And [First Badge Access] Between '# & txtStartDate.value & #' And '# & txtEndDate.value & #';"
The Format([First Badge Access],'Medium Time') is causing problems. If I just use [First Badge Access] it works, but the output is incorrect because it includes the date with the time. If I make a standard query with the output of that string, it works. So what am I doing wrong?