Counting records of linked table over local server.

[mrmarc]

Here is what I have tried.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim x As String


Set db = OpenDatabase("S:\Traceability V2\Shore Fresh Logistics_be_be.accdb")

Set rs = db.OpenRecordset("tblPalletRecords", dbOpenTable)




For i = 0 To rs.RecordCount - 1
    
    
If IsNull(rs.Fields("PalletNumber")) = True Then
    
    GoTo NextOne1
    
   Else
    
    x = rs.Fields("PalletNumber")
       
            
    If x = Me.PalletNumber.Value Then
            
            Me.Status.Value = rs.Fields("Status")
    
          
    End If
        
            
End If
        
NextOne1:
        rs.MoveNext
        
    Next i


rs.Close
Set rs = Nothing
db.Close

I have 7 records in my linked table and it's only counting 4. Any ideas?

 

[mrmarc]

Thanks xenou!

What I ended up doing is creating a local query that retrieved all the fields from the linked table. I am now using it like this:

Set db = CurrentDb

Set rs = db.OpenRecordset("qryStatusFind")




If rs.EOF Then
      z = 0
   Else
      rs.MoveLast
      rs.MoveFirst
      z = rs.RecordCount
End If




For i = 0 To z - 1
   
If IsNull(rs.Fields("PalletNumber")) = True Then
    
    GoTo NextOne1
    
   Else
    
    x = rs.Fields("PalletNumber")
       
            
    If x = Me.PalletNumber.Value Then
            MsgBox "Success"
            Me.Status.Value = rs.Fields("Status")
    
          
    End If
        
            
End If
        
NextOne1:
        rs.MoveNext
        
    Next i


rs.Close
Set rs = Nothing
db.Close

Do you see a problem with doing it this way?

And Yes, sometimes there are multiple "PalletNumbers" that need the information changed.

Thanks in advance.

 

[xenou]

The only problem I see is that (i.e., let's say you have two pallet numbers in the query that are equal to Me.PalletNumber.value):

first the loop encounters the first match and changes the value of me.status (fine)
second the loop encounters the second match and changes the value of me.status (again)

Now the first change is lost. So the code as written is doing unnecessary work, isn't it?

 

[mrmarc]

Negative.

The first change is not lost. Both records are changed to the same status, which is what i want.

Thanks Xenou! Let me know if see anything else.

 

[xenou]

Okay. Still no need for a loop though. Just query for the records you want, not all records and then looping through all records.