Find most recent measurement through code

[todmac]

Hello Everyone,


I have been trying to get a database together to track the most recent measurements of different items, in different positions on heavy equipment.
I tried to get a query that would do this but couldnt manage it.
I made an attempt and writing code that could find it once the user updates the unit number, but I cannot get it to work.

Again I am trying to find the most recent measurement (by date) for a specific unit number, item, and wheel position.
Any help would be greatly appreciated, I cant seem to get going on this project and need to wrap it up soon.

Private Sub UnitNumber_AfterUpdate()
Dim UnitNumber As Integer
Dim db As DAO.Database
Set db = CurrentDb
Dim StrSql As String
Dim LFTreadDepth As Integer
Dim Item As String
Dim WheelPosition As String
Item = "Brake Lining"
WheelPosition = "Right Front"
UnitNumber = Me.UnitNumber
StrSql = "select main.measurement where main.unitnumber = unitnumber and main.item = item and main.Wheel_position= WheelPosition"
CurrentProject.Connection.Execute StrSql
End Sub

[ssanfu]

Try this:

Private Sub UnitNumber_AfterUpdate()
   On Error GoTo Err_Handler

   Dim db As DAO.Database
   Dim r As DAO.Recordset
   Dim StrSql As String
   Dim Item As String
   Dim WheelPosition As String
   Dim UnitNumber As Integer
   Dim LFTreadDepth As Integer

   Set db = CurrentDb

   Item = "Brake Lining"
   WheelPosition = "Right Front"
   UnitNumber = Me.UnitNumber

   StrSql = "SELECT  main.measurement FROM Main "
   StrSql = StrSql & " WHERE main.unitnumber = " & UnitNumber & " AND main.item = '" & Item & "' AND main.Wheel_position = '" & WheelPosition & "'"
   StrSql = StrSql & " ORDER BY [YourDatefield] DESC "

   Set r = db.OpenRecordset(StrSql)

   If Not r.BOF And Not r.EOF Then

      'do something with the measurement field value

   Else
      MsgBox "No measurement found for Unit " & UnitNumber & ", " & Item & " and " & WheelPosition
   End If
   '   CurrentProject.Connection.Execute StrSql    '<< only used for action queries, not select queries

Exit_Here:
   'close recordset and clean up
   StrSql.Close
   Set StrSql = Nothing
   Set db = Nothing

Err_Handler:
   MsgBox Err.Description & "  " & Err.Number & " -   " & UnitNumber & ", " & Item & " and " & WheelPosition, vbExclamation, "Error"
   Resume Exit_Here

End Sub

Change [YourDatefield] to the name of your date field.

The text fields didn't have required delimiters in the SQL string.
You didn't say what you wanted to do with the value if found........

[todmac]

Thank you so much for your help!

I updates the code, but can't seem to get past this error

Syntax error (missing operator) in query expression ‘main.unitnumber = ‘9400’ AND main.item = ‘Brake Lining’ AND main.Wheel Position= ‘Right Front”.

Perhaps it's an issue with the quotes? I see the final one is a double quote and the rest are single?

[pbaldy]

What is your actual code, because Steve's wouldn't have resulted in that? Also, is this appropriate to what you want?

http://www.baldyweb.com/LastValue.htm

You'd have to adapt it since you would group by more fields.

[todmac]

What is your actual code, because Steve's wouldn't have resulted in that? Also, is this appropriate to what you want?

http://www.baldyweb.com/LastValue.htm

You'd have to adapt it since you would group by more fields.

Sorry no, that isnt nearly complex enough for the number of variables I have.

I copied the code exactly, so not sure.

[pbaldy]

Steve's code didn't put quotes around unitnumber, and did include the closing single quote, so I can't imagine that error came from his code. You can use this to see the completed SQL:

http://www.baldyweb.com/ImmediateWindow.htm

[June7]

The double quotes define literal string. Concatenate variables. Steve's code shows that.

Is unitnumber a text type field? If it is, also needs apostrophe delimiters.

Put [] around any names with spaces or special characters/punctuation (underscore is exception). Does Wheel_Position really have an underscore in it?