CODEWITHSUNDEEP
ms-accessvbaruntime
Rob
Rob

Reputation: 3

Run-time 3464 in Access VBA. Trying to open a recordset with SQL select statement

I'm having trouble trying to get this code to work:

Public Sub GetDepot()
    Dim fs As Date
    Dim ss As Long

    fs = CurrentDb.OpenRecordset("SELECT Max(fldMovementDate) FROM [tblMovements] WHERE [fldMovementTypeIdfk] =1")(0)

    ss = CurrentDb.OpenRecordset("SELECT(fldMovementLocationIdfk) FROM [tblMovements]  WHERE [fldMovementDate]= '" & fs & "'")(0)

    MsgBox (ss)
End Sub

Ultimately I am trying to populate a textbox on a form with from its last depot location. The debug picks out where I am trying to set the ss variable. There is something going wrong with my SQL statement and have tried various things to correct it but to no avail. I have also tried storing the variable as different data types thinking that this might be where I've gone wrong but again this approach as yet hasn't worked to fix the problem.

The MsgBox at the end was just there to help try and debug but the code isn't getting that far. I understand that I will need to add more to this Sub to get it to complete what I need but cant get it any further.

I've also tried setting a MsgBox up to show what the fs variable is storing and this seems to be showing a correct date i.e. 18/02/16.

If any one could help it would be massively appreciated!

Upvotes: 0

Views: 105

Answers (1)

Gustav
Gustav

Reputation: 55806

Use DMax and DLookup for this:

fs = DMax("fldMovementDate", "[tblMovements]", "[fldMovementTypeIdfk] = 1")

ss = DLookup("fldMovementLocationIdfk", "[tblMovements]", "[fldMovementDate]= #" & Format(fs, "yyyy\/mm\/dd") & "#")

Or do like this:

Public Sub GetDepot()

    Dim rs As DAO.Recordset
    Dim ss As Long

    Set rs = CurrentDb.OpenRecordset("SELECT TOP 1 fldMovementLocationIdfk FROM [tblMovements] WHERE [fldMovementTypeIdfk] = 1 ORDER BY fldMovementDate DESC")

    ss = rs(0).Value
    rs.Close

    Set rs = Nothing

    MsgBox (ss)

End Sub`

Upvotes: 2

Related Questions