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?

 

[Joe4]

Where exactly in this code is any counting occurring?

Note that the code is checking the value of the "PalletNumber" field, and if it is null, it is skipping it.

 

[mrmarc]

Hi Joe,

It's counting when "For i = 0 to rs.RecordCount -1". Basically I am trying to count ALL records so that my loop knows how many to search.

And yes, there are no Null values in the PalletNumber field.

 

[Joe4]

When counting all of the records in a recordset, you need to move to the last record before getting your count.
See: https://msdn.microsoft.com/en-us/library/bb243791(v=office.12).aspx
Notice the ".MoveLast" command before getting the count.

 

[mrmarc]

Thanks Joe.
My records count is now working. BUT now im getting a "No Current Record" error.

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

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


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




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

Any Ideas?

 

[Joe4]

If you are going to loop through all your records after your move to the end (to get your count), you will need to move back to the beginning.
So before your "For ..." loop, you will need a ".MoveFirst" command.

If you do a Google search on "Access VBA loop through recordset", you can see different methods of looping through recordsets in Access VBA.

 

[mrmarc]

Joe. Thank you so much. Its working now. I cannot tell you how much I appreciate people like you who are will to help.

Here is my final code for anyone else experiencing the same problem:

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

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


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
            
            Me.Status.Value = rs.Fields("Status")
    
          
    End If
        
            
End If
        
NextOne1:
        rs.MoveNext
        
    Next i


rs.Close
Set rs = Nothing
db.Close

 

[Joe4]

Yeah, recordsets can be a little tricky to work with if you are not used to them.
Glad we got it working!

 

[mrmarc]

Joe,

After a few more entries, I'm having the same problem.

There are 13 records in the table but z is only equal to 10.

It seems like it's not recognizing the recent entries.

 

[xenou]

There is no need to count records. Just begin your loop:

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

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

If Not rs.EOF Then
	Do While Not rs.EOF
		
		If Not IsNull(rs.Fields("PalletNumber")) Then
		
			If rs.Fields("PalletNumber") = me.PalletNumber.Value Then
				Me.Status.Value = rs.Fields("Status")
			End If
			  
		End If
		
		rs.MoveNext
	
	Loop
End If
        

rs.Close
Set rs = Nothing
db.Close

YOu probably shouldn't even be looping. The effect of this loop is that if you find a "match" and then another one, the first get's wiped out so it's pointless. If there is only one "match" to find, query for it directly:

dim sql As String
sql = "SELECT Status FROM tblPalletRecords WHERE PalletNumber = " & Me.PalletNumber.Value
set rs = db.OpenRecordset(sql)
If Not rs.EOF Then
    Me.Status.Value = rs.Fields("Status")
End if
rs.Close
set rs = Nothing
db.Close