Subform Calculation

[MattNMNB]

I need help with making a calculation on a subform.

I currently have a database setup which tracks the attachment height of equipment
attached to utility poles. There are two tables included in the database. One called Pole_Data which contains information about each pole and another called Epuip_Data which contains info about attached equipment including its attachemt height (field name "Height&quot.

There is a one to many relationship between the pole data table and the equipment data table linked through a field named Pole_ID.

I have created a main form linked to the Pole_Data table and placed a subform on it linked to the Equip_Data table. As I navigate the records on the main form, the subform shows the corresponding equipment on each pole.

My problem is that I need to calculate the distance between each piece of equipment on the pole. In order to do this, I first need to sort the heights by decending order, which I have done. Next It seems like I need to be able to reference each record on the subform by its "row" so that I can subtract the height value in one record from
the height value in the adjacent record and return the results to a control on the subform. I can not figure out how to do this. Any ideas would be appreciated.

 

[Sameal]

Whoooo... thats quite a calculation. I'm not going to tell you exactly how to do it but I can give you some pointers of how to start. I gather from your account that you are a Technical User rather then a programmer. Here is the thing...

Your going to have to use a library of tools called the "Data Access Objects 3.6 or 3.5" (whatever). You do this by going to the Tools menu of the VBA editor that pops up behind Access to allow you to add code to your buttons, controls, forms, etc. In the Tools -> Refrences menu you will add a check next to the "Microsoft DAO 3.6 Object Library".

Next your going to have to add some code. What you explained sounds like your probably going to want to place this code in the On_Current event of your main form so that every time you change to a different pole it will run the code and calculate the distances or whatever.

Inside this event you need to first create a string variable that holds the SQL command line to gather the data neccesary to do the calculations. You can find help on SQL statements in the Access Help menus and also can play with it in the Query design.

You will then to open a recordset containing the data that your SQL statement gathered. Then you will have to cycle through that recordset, gathering the required data and adding it to your running result variable.

Here is a very basic version of what I'm talking about. Remember this is NOT the exact code you will use in any way.

Private Sub frmMain_OnCurrent()

Dim strSQL as String
Dim objRS as Recordset

strSQL = "SELECT Pole_ID, Height FROM tblPoles
WHERE Pole_ID = " & Me.txtPoleID

Set objRS = CurrentDb.OpenRecordset(strSQL)

Do While Not objRS.EOF
Debug.Print objRS.Fields(0), objRS.Fields(1)
objRS.MoveNext
Loop

Set objRS = Nothing

End Sub

That code basically will open a recordset with the [Pole_ID], and [Height] fields of the [tblPoles] table as it's columns. When the recordset is opened the current place of the cursor is sitting at the first record or EOF if the recordset is empty. This code will basically start at the beginning and will display the contents of the 0th (or First column) field and the 1st (or Second column) field for each record. When the recordset comes to the EOF (End Of File) the loop will end and the code will run to the 'End Sub' command.

You will have to play with this and get used to it before you will be able to apply your particular algorithm to it. But you use these same tools to get the data and work with it. Say you wanted to total up the sum of all the [Height] fields together you could do it this way.

Private Sub frmMain_OnCurrent()

Dim strSQL as String
Dim objRS as Recordset
Dim lTotal as Long

strSQL = "SELECT Pole_ID, Height FROM tblPoles
WHERE Pole_ID = " & Me.txtPoleID

Set objRS = CurrentDb.OpenRecordset(strSQL)

lTotal = 0

Do While Not objRS.EOF
lTotal = lTotal + objRS.Fields(1)
objRS.MoveNext
Loop

Set objRS = Nothing

MsgBox "The Height Total Is: " & lTotal, vbInformation,
"Results"

End Sub

Hope this helps you. I will continue to watch this thread so post here again if you don't understand something I said.