Neew help with a query in a database using DAO 3.6

[Neutrall]

Hi ,

I have the following code : to test the entry in a database :

Sub test()
Dim x As Integer
    x = Add_NewBatch_DB(125, "12/05/2011", "20/06/2015", "20/06/2015")
End Sub


Private Function Add_NewBatch_DB(CptMach As Long, TmpSupp As Date, TmpNP As Date, CurrDte As Date) As Integer
'Function will return the ID number of the newly inserted batch

    Dim MainData As Database        'Database Connection
    Dim Batch As Recordset          'Table connection
    Dim SQL As String

    'Open Database and open the recordset
    Set MainData = OpenDatabase(ActiveWorkbook.Path & DB_FILE_PATH_NAME)
    Set Batch = MainData.OpenRecordset("tbl_result_batch")
    
    Batch.AddNew
    Batch!CompteurMachine = CptMach
    Batch!TempSupplementaire = TmpSupp
    Batch!TempNonProductif = TmpNP
    Batch!CurrDate = CDate(CurrDte)
    Batch.Update
   
    SQL = "SELECT ID FROM tbl_result_batch WHERE CurrDate=" & CDate(CurrDte) & " AND CompteurMachine =" & CptMach
    Set Batch = MainData.OpenRecordset(SQL, dbOpenDynaset)
    
    MsgBox Batch!ID
    
    'Close connection
    Batch.Close
    MainData.Close
    Set Batch = Nothing
    Set MainData = Nothing
End Function

My goal here if to to able to retreive the ID Field of the last inserted element with my function "Add_NewBatch_DB"

But when I enter " WHERE CurrDate=" & CDate(CurrDte) & " " in my SQL command, I get no result. If I add the " ' " before and after the date, I get a invalid argument response.

How Can I :

1- Get the value of the ID field of the last inserted element (Auto increment field)
2- Use Date in my SQL request?

Thank you!