How to calculate fields in 2 tables in VBA

[dluhut]

Hello,

I'm a new access beginner, especially in VBA.

I have 2 tables - Customer and Carrier.

Each table has 3 headers (fields). Address, Latitude and Longitude.

I would like to know, how I take the value of the field (i.e Latitude) in each table for my calculation?



In Excel, I can specify the column (i.e Range("B" & x) - where 'x' is the row number)...but how do I do it in Access.

Dim db As DAO.Database
Dim rsCustomer As DAO.Recordset
Dim rsCarrier As DAO.Recordset

Set db = CurrentDb
Set rsCustomer = db.OpenRecordset("Customer")
Set rsCarrier = db.OpenRecordset("Carrier")

 
Do While Not rsCustomer.EOF
    
    custLat = rsCustomer.Fields("Latitude").Value
    custLon = rsCustomer.Fields("Longitude").Value
    custLatTimesPiOver180 = Customer Latitude of the row * piOver180
    TempClosestDistance = 99000000

    Do While Not rsCarrier.EOF
    
        'More codes here to calculate 
        
        rsCarrier.MoveNext
    
    Loop

    rsCustomer.MoveNext

Loop


rsCustomer.Close
rsCarrier.Close
db.Close

Set rsCustomer = Nothing
Set rsCarrier = Nothing
Set db = Nothing

[pbaldy]

Well, you're already getting a value from the recordset, so do you mean for a specific record? It would help to know what you're trying to accomplish, but if you want a particular record, instead of opening the recordset on the whole table (typically a bad idea anyway), open it for the desired record:


Set rsCustomer = db.OpenRecordset("SELECT * FROM Customer WHERE Address = " & Me.Address)

which would get an address from the form. That presumes a numeric field; if text:

Set rsCustomer = db.OpenRecordset("SELECT * FROM Customer WHERE Address = '" & Me.Address & "'")

[ssanfu]

I, too am unsure what you are trying to accomplish. But I played with your code and got this far:

'For each customer, this will calculate "custLatTimesPiOver180", then
' for each record in the table "Carrier", do more calcs.

Public Sub Calc_It()
   ' 3.141592653589793238462643383279502884197169399375105
   Const pi = 3.14159265358979 ' to 20 places

   Dim db As DAO.Database
   Dim rsCustomer As DAO.Recordset
   Dim rsCarrier As DAO.Recordset
   Dim piOver180 As Double

   Set db = CurrentDb
   Set rsCustomer = db.OpenRecordset("Customer")
   Set rsCarrier = db.OpenRecordset("Carrier")

   piOver180 = pi / 180

   Do While Not rsCustomer.EOF

      custLat = rsCustomer.Fields("Latitude")
      custLon = rsCustomer.Fields("Longitude")
      
      custLatTimesPiOver180 = custLat * piOver180
      
      MsgBox custLatTimesPiOver180
      
      TempClosestDistance = 99000000

      Do While Not rsCarrier.EOF
         'More codes here to calculate
         'do something
         rsCarrier.MoveNext
      Loop

      rsCustomer.MoveNext
   Loop

   rsCustomer.Close
   rsCarrier.Close

   'You didn't open it, so don't close it!!
   '   db.Close

   Set rsCustomer = Nothing
   Set rsCarrier = Nothing
   Set db = Nothing
End Sub