Run-Time error '424'

[waxdart23]

I am using the following to pass two items of data from a form to a Module -

Call UpdatePerformanceAdd(StewID.Value, StartDate.Value)

The module is as follows -

Public Sub UpdatePerformanceAdd(lStew_Num As Long, lStartDate As Date)
    Dim Dbs As Database: Set Dbs = CurrentDb
    Dim rstPerf As Recordset
    Dim rstFuture As Recordset
    Dim strSQL As String
    strSQL = "SELECT Performances.ID, Performances.Perf_Date, Performances.Duration FROM Performances "
    strSQL = strSQL & "WHERE (((Performances.Perf_Date)>=" & lStartDate & "));"
    Set rstPerf = Dbs.OpenRecordset(strSQL)
    Set rstFuture = Dbs.OpenRecordset("StewardAvailability", dbOpenDynaset)
    With rstFuture
        rstPerf.MoveLast: rstPerf.MoveFirst
        While Not rstPerf.EOF
            'add new record to availability table
            .AddNew
            !Stewards_ID = lStew_Num     'copy performance id #
            !Perf_ID = rstPerf!ID       'copy staff ID number
            !Availability = False       'set availability to false
            !Memo = " "                  'Leave memo field blank
            !Duty_length = rstPerf!Duration 'copy duty duration
            .Update                     'update recordset
            rstPerf.MoveNext           'move to next contracted employee
        Wend
    End With
    rstFuture.Close
    rstPerf.Close
    strSQL = ""
    Set Dbs = Nothing
End Sub

When I run this I get the following error "Run-Time error '424'; Object Required". The 'HeadID.Value' is a value from the database and this works fine. However, the StartDate.Value (which seems to be causing the error) is a text box in which the user enters a date to be used as part of the query in the module.

Anyone any ideas how to fix this?

 

[Mile-O]

Try making this change:

"WHERE (((Performances.Perf_Date)>=#" & lStartDate & "#));"

 

[Mile-O]

And possibly:

Call UpdatePerformanceAdd(StewID.Value, DateValue(StartDate))

 

[waxdart23]

Thanks, using DateValue(DateStart) stopped the errors.
However this date value is meant to act as a user entered date filter so that only records with a date >= this value is copied into the joining table.

When I run this it adds ALL records regardless of the date. So this filter isn't working.

Any ideas where I am going wrong?

 

[Mile-O]

The only other thing I can think of is (I'm sure, but not 100% sure) that fields with non alphanumeric characters in their names must be surrounded by square brackets.

Public Sub UpdatePerformanceAdd(lStew_Num As Long, lStartDate As Date)
    Dim Dbs As Database: Set Dbs = CurrentDb
    Dim rstPerf As Recordset
    Dim rstFuture As Recordset
    Dim strSQL As String
    strSQL = "SELECT Performances.ID, Performances.[Perf_Date], Performances.Duration FROM Performances "
    strSQL = strSQL & "WHERE (((Performances.[Perf_Date])>=#" & lStartDate & "#));"
    Set rstPerf = Dbs.OpenRecordset(strSQL)
    Set rstFuture = Dbs.OpenRecordset("StewardAvailability", dbOpenDynaset)
    With rstFuture
        rstPerf.MoveLast: rstPerf.MoveFirst
        While Not rstPerf.EOF
            'add new record to availability table
            .AddNew
            ![Stewards_ID] = lStew_Num     'copy performance id #
            ![Perf_ID] = rstPerf!ID       'copy staff ID number
            !Availability = False       'set availability to false
            !Memo = " "                  'Leave memo field blank
            ![Duty_length] = rstPerf!Duration 'copy duty duration
            .Update                     'update recordset
            rstPerf.MoveNext           'move to next contracted employee
        Wend
    End With
    rstFuture.Close
    rstPerf.Close
    strSQL = ""
    Set Dbs = Nothing
End Sub

 

[Rich]

You'll almost certainly have to use the US date format to return the correct records, try
Performances.[Perf_Date])>=#" &Format( lStartDate,"mm dd yyyy") & "#));"

 

[Mile-O]

I always forget about the date formatting issue because I ensure that every date I use is in the dd-mmm-yyyy format.

 

[Rich]

That's when you get problems, if your referencing/returning date in strings you have to convert the date field to the US format, which is of course mm dd yyyy

 

[waxdart23]

I tried this and it is still ignoring the date filter.

I tried running it as below and obviously got an error as the WHERE statement is incomplete.

    strSQL = "SELECT Performances.ID, Performances.Perf_Date, Performances.Duration FROM Performances "
    strSQL = strSQL & "WHERE (((Performances.Perf_Date)>=));"

I don't understand why it is able to run with the filter in but ignore it completely, but it will give error messages if nothing is there.

What seemed sooo easy when I started has turned into a disaster.

All past and future suggestions appreciated as ever.