Problem with .AbolutePosition

[dealwi8me]

Hello this is my code,

Me.Label323.Caption = ""

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT * FROM VALUEAPP_CAT")

If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst
    Do Until rs.EOF = True
    
With rs
  If .AbsolutePosition = .RecordCount - 1 Then
     Me.Label323.Caption = Me.Label323.Caption & " " & rs!VAL & " " & rs!SText & " = "
  Else
     Me.Label323.Caption = Me.Label323.Caption & " " & rs!VAL & " " & rs!SText & " + "
  End If
End With
        rs.MoveNext
    Loop
    
End If

rs.Close
Set rs = Nothing

Me.Label323.Caption = Me.Label323.Caption & " " & Forms!frmApplication!Amount

Let's say that VALUEAPP_CAT has the following records.
(2, "x 100"),(3,"x200"),(4,"x1")

The output should be 2 x100 + 3 x200 + 4 x1 = 804, instead I get 2 x100 = 3 x200 = 4 x1 = 804

Any ideas what's wrong with my code?

Thank you in advance.

 

[LordLoki]

I would first check if RecordCount and AbsolutePosition are giving you a proper Value

Put sth like
Debug.print(.AbsolutePosition & "/" & .RecordCount) after the With RS and post the result.

 

[dealwi8me]

The result is:
0/1
1/2
2/3

 

[LordLoki]

I am not an expert in access but i think you need to do it in this order
1. Do movelast
2. Do recordcount and save it to a var
3. Do movefirst
4. Compare absoluteposition to your var.

I will post the code when im back from lunch if needed

Edit:

Private Sub Command2_Click()
Me.Label323.Caption = ""

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT * FROM VALUEAPP_CAT")

If Not (rs.EOF And rs.BOF) Then
    rs.MoveLast
    test = rs.RecordCount
    rs.MoveFirst
    Do Until rs.EOF = True
    
With rs
  If .AbsolutePosition = test - 1 Then
     Me.Label323.Caption = Me.Label323.Caption & " " & rs!Val & " " & rs!SText & " = "
  Else
     Me.Label323.Caption = Me.Label323.Caption & " " & rs!Val & " " & rs!SText & " + "
  End If
End With
        rs.MoveNext
    Loop
    
End If

rs.Close
Set rs = Nothing

Me.Label323.Caption = Me.Label323.Caption & " 800"

End Sub

 

[dealwi8me]

Thank you that worked.

Here's the code I added

....
If Not (rs.EOF And rs.BOF) Then
   rs.MoveLast
   reCount = rs.RecordCount
   rs.MoveFirst

Do Until rs.EOF = True
With rs
 If .AbsolutePosition = reCount - 1 Then
....

 

[vbaInet]

Let's clean up your code a bit:

    Dim rs As DAO.Recordset
    Dim strCaption As String
    
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM VALUEAPP_CAT", dbOpenSnapshot)
    
    With rs
        If Not (.EOF And .BOF) Then
            Do Until .EOF = True
                strCaption = strCaption & !Val & " " & !SText & " + "
                .MoveNext
            Loop
            
            Me.Label323.Caption = Left(strCaption, Len(strCaption) - 3) & " = " & Forms!frmApplication!Amount
        Else
            Me.Label323.Caption = "No records."
        End If
    End With
    
    rs.Close
    Set rs = Nothing

Also you shouldn't use Val as a field name. It's a reserved keyword and it could cause you problems. And finally, please adopt better naming conventions than "Label323". You can call it lblSomeName.