You're probably gonna tell me I need DAO.Recordset

[Stanski21]

Hi all,
There is a very long story behind this but I'll just post my current problem for now...
Essentially, I'm trying to create an active employee list from a central 'employees table' table in order to run a variety of queries from it which will be date sensitive
I'm kinda new to the idea of running SQL within VBA so I'm sure that's the problem.
Anyway, here's where I get stuck with a 3075 error (full code posted... the SQL is in the last Sub):

Option Compare Database

Private Sub btnDateSet_Click()

datefrom = Form_frm_DatePicker.ctrDateFrom
dateto = Form_frm_DatePicker.ctrDateTo

ErrHandler:
    Select Case Err.Number
    Case Is = 13
        Beep
        MsgBox "Invalid date(s), please try again", vbCritical, "Uh-Oh!"
        Response = acDataErrContinue
        Exit Sub
    End Select

ActiveEmployees
End Sub

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    If DataErr = 2279 Then
        Beep
        MsgBox "That's not a real date...", vbCritical, "Uh-Oh!"
        Response = acDataErrContinue
        Exit Sub
    End If
End Sub


Private Sub ActiveEmployees()
On Error GoTo ErrorHandler
    
    Dim strSQL As String
    Dim strTable As String
    strTable = "tblActiveEmp"
    'Delete the table if it exists
    DoCmd.DeleteObject acTable, strTable
    strSQL = "SELECT * INTO tblActiveEmp FROM tbl_EmployeeDetail WHERE (((tbl_EmployeeDetail.StartDate)>=[Forms]![frm_DatePicker]![ctrDateFrom]') AND (('tbl_EmployeeDetail.[InWork?])=True));"
    CurrentDb.Execute strSQL
Exit Sub

ErrorHandler:
    Debug.Print Err.Number
    If Err.Number = 7874 Then
    Resume Next
End If

End Sub

 

[sneuberg]

I took out the parenthesis and single quotes as they were just confusing and delimited and concatenate in the form reference. Maybe this will work.

strSQL = "SELECT * INTO tblActiveEmp FROM tbl_EmployeeDetail WHERE tbl_EmployeeDetail.StartDate >= #" & [Forms]![frm_DatePicker]![ctrDateFrom] & "# AND tbl_EmployeeDetail.[InWork?]=True;"

If your dates are not in the american format (mm/dd/yyyy) you will have to fix that too. Let us know it that's the case.

 

[sneuberg]

Also suggest putting dbFailOnError in the CurrentDb.Execute strSQL statement like

CurrentDb.Execute strSQL, dbFailOnError

so that it won't fail invisibly for key violations, etc

 

[Stanski21]

Thanks for getting back to me so quickly!

I've tried your amendment to the SQL. I'm not getting the error anymore (WIN!) but I'm still not getting the records I'm after i.e. Date set on the form is 1st June, but it's pulling records from before that.

If your dates are not in the american format (mm/dd/yyyy) you will have to fix that too. Let us know it that's the case.

I wonder if this is the problem... I hadn't considered that. My settings are for UK formatted dates (dd/mm/yyy) and all entries are in that format so would that cause a problem still? When I was running the same SQL string from within a query, it worked fine.... am I coming off like a total noob?

 

[sneuberg]

To understand why this is a problem I suggest reading about this in this site which has the following function you should put in a module

Function SQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

Then you can use it in the SQL statement like:

strSQL = "SELECT * INTO tblActiveEmp FROM tbl_EmployeeDetail WHERE tbl_EmployeeDetail.StartDate >= #" & SQLDate([Forms]![frm_DatePicker]![ctrDateFrom]) & "# AND tbl_EmployeeDetail.[InWork?]=True;"

 

[MarkK]

You shouldn't need to create a table as part of a routine data management strategy. Just write a query that returns the active employees.

 

[gemma-the-husky]

this seems to me to be a simple? matter of determining active employees.

active employees might be employees who have not left or died. It might also be employees who are not absent, or not employed on other projects. (ie not available)

in all these cases, you need a query(s) to select the matching employees.

you shouldn't need to iterate the employees, one at a time, to evaluate the above - which is why you use recordset processing.

 

[Stanski21]

Thanks all for your replies.

Sneuberg, I have read the article you have suggested and it all makes sense now. It's all knowledge that goes into the Bank. Thank you!
The updated SQL string you posted however brings me back to the 3075 error.

In saying that, having some time over the weekend to think, and it seems that writing a query or using recordset is indeed the way to go.

I was trying to avoid a query for this section as I then split employees performance markers from 6 other tables and use SQL to rank certain ones, bringing them back together in a final query on which to base a report.

On successfully writing this before, I came across the 'Cannot Open Anymore Databases' error when I split the database which I put down to one of two causes:
I had exceeded more than 16 joins after the split (it was working before I split it), or the fact that there were way too many recordsets being accessed.
The one piece of advice that is echoed through all forums, tutorials etc was that I need to close the recordset after I open it.

Anyway... That's what started me down this path. Problem is, I couldn't get the recordset process working. I'll keep trying because this is undoubtedly a skill I need if I'm going to continue using Access.

However any pointers towards other tutorials/videos etc would be very much appreciated.
I read through Allen Browne's pages on DAO.Recordset but I'm not any further forward.

 

[gemma-the-husky]

generally you would need a query anyway!

If you need to do something, that just can't be done from a simple select query, or a simple update or append query, then you may need to do it with recordset processing. Alternatively you may be able to create a temporary table with a make table query. Consider what you are trying to do in terms of the data.

if after all this, you still need a recordset then this. you will STILL need a query (or table) of some sort to feed the process.

Then the code you need is effectively

open a recordset based on your query
if there are any records then
loop start    
     process the record
     read next record
loop until end of file

this is generally slower than doing the same thing with a bulk query - although sometimes it's the only way. Let's say you want to write a csv file in a particular format. docmd.transfertext doesn't do it

instead, you can do it with a recordset, reading and writing each line separately.

 

[sneuberg]

Sneuberg, I have read the article you have suggested and it all makes sense now. It's all knowledge that goes into the Bank. Thank you!
The updated SQL string you posted however brings me back to the 3075 error.

If you are still pursuing this please add

Debug.Print  strSQL

just before

CurrentDb.Execute strSQL

Run the code and post what you get in the Immediate Window. Also post your code as you currently have it.

 

[Stanski21]

Thanks both.
I've just come back from a long and much needed holiday in the sun.
Your help has both been invaluable and appreciated. From reading through, I feel I've been doing my usual trick of over-complicating things. I've managed to fix the problem I was having by using the same simple queries I had before, just in a different order which has cut down on the joins.
However I've now come across another potential use for recordsets... I'll have a play with the above and see what I come up with.... the main reason I love this job; press a button and see what happens!