Accessing a table on a Subform

[NW27]

Hi,
I'm just starting out as a VB & Access programmer. I have plenty of programming experience just not in these languages so to speak.

I have a form with a "Record Source" of "Trade".
On this form there is also a Subform with a "Record Source" called "Exit"
It is a one to many relationship. One Trade to many Exits.

In Form View, the form shows the selected Trade row and the filtered exits ie where Exit.Trade_ID = Trade.Trade_ID.

I have a button on the Trade form and I need some code that will perform some calculations and update both the Trade table and Exit table.

As an example in pseudo code of the Trade table row update -

 Trade.Profit = 0;
 While Not Exit.EOF AND Exit.Trade_ID = Trade.Trade_ID
 Trade.Profit = Trade.Profit + Exit.Price-Trade.Entry

Given that the form/s already have the tables open, do I need to create new variables and connections to the database or can I reference these already?

Thanks,
Neil.
I have bought some books on these but!!! Is it my age or the book
It's just not clicking.

 

[apr pillai]

For the current Trade Record on the Main Form have several records on the Sub-Form (one-to-many relationship). I assume that the Exit.Price value is the summary of values of Exist Records on the sub-form. The Sub-Form may be in Datasheet View, right? As you can see, we have to assume several aspects of your issue. Based on whatever info you have provided and based on other assumptions the following solution is suggested:

• Create a Textbox on the Footer Section of the Sub-Form.
• Write the expression: =sum([Price]) in the Control Sour of the Textbox.
• Change the Name Property Value of the textbox to ExitPrice. This will automatically calculate the total of all Exit records related to the Trade.TradeID on the main form. Since, the Sub-Form is in datasheet view this will not be visible on the form. You can bring this value in a Textbox on the main form.
• Create a Textbox on the Main Form (Trade Form).
• Write the Expression: =SubformName.Form![ExitPrice] in the Control Source property. Replace the SubformName with the correct name of the Sub-Form.
• Change the Name Property value of this textbox to TotalPrice.
• On the button click event procedure you do the calculation and update action with the sample code below:

Private Sub cmdBtn_Click()
Dim rst As dao.Recordset, m_TotalPrice as Double
Me.Refresh
m_TotalPrice = Me![TotalPrice]

Me!Profit = Me!Profit + (m_TotalPrice-Me!Entry)

'Update the Sub-Form Records

Set rst = SubFormName.Form.RecordsetClone
rst.MoveFirst
Do While Not rst.EOF
    rst.Edit
    rst![SummaryValue] = m_TotalPrice 'replace [SummaryValue] with correct name
    rst.Update
    SubFormName.Form.Bookmark = rst.Bookmark

rst.MoveNext
Loop
rst.Close
End Sub

Make changes to the control names or code wherever necessary. The above code updates individual records on the Sub-Form related to the Trade.Trade_ID on the Main-Form, besides changing the Profit value on the main-form.

 

[NW27]

Hi,
Thanks for responding.
Trade is a Table on the main form and has Entry and Profit columns.
Exit is a subform table with Price being a column within the table.
There may be 1..3 rows in the exit table that match the corresponding trade table.
I need to loop through each one separately and do calculations on each one separately.
So there is 1 Entry price and multiple (say 3) exit prices. So this gives 3 separate profits.
Not a summary on the subform, I will calculate a summary on the main form later.

So on the main form, I need to loop through each of the rows on the subform and manually do the calculation.
Instead of using the data that is already displayed on the main form and subform, I could use a query where
Select Price from tbExit where tbexit.Trade_ID = Trade.Trade_ID

Neil.

 

[NW27]

Thanks apr pillai,
I'm finally understanding
This is how it looks and works
But! Am I using the correct syntax ???
I'm still not sure on where to use . and ! and Me's and [] but... it does work

Dim rst As dao.Recordset, Sample As Double
Me.Refresh
Me!Trade_Profit_Gross = 0
Set rst = [Exit_Subform].Form.RecordsetClone
rst.MoveFirst
Do While Not rst.EOF
    rst.Edit
    
    If Me!Direction = 1 Then
        Me!Trade_Profit_Gross = Me!Trade_Profit_Gross + (rst![Exit_Price] - Me!Entry_Price) 'Long Trade
    ElseIf Me!Direction = -1 Then
        Me!Trade_Profit_Gross = Me!Trade_Profit_Gross + (Me!Entry_Price - rst![Exit_Price]) 'Short Trade
    Else
        'Warning
        MsgBox "No Trade Direction"
    End If
    
    rst.Update
    [Exit_Subform].Form.Bookmark = rst.Bookmark
rst.MoveNext
Loop
rst.Close
End Sub

Neil.

 

[apr pillai]

Glad that I could help.

The dot (.) separater is used for referencing object properties or methods.

rst.MoveNext is calling the MoveNext method to move the record pointer to the next record.

rst.Fields(0).Name returns the name of the first field of the recordset.

When more than one action is required it can be programmed in the following manner too:

With rst
.MoveNext
Amt = ![Unit Price]

' ! symbol asks VBA to return value contained in the [Unit Price] control. This way we don't have to repeat rst with each statement like:
rst.MoveNext
rst![Unit Price]
'[] Control Names must be enclosed within [] when there are spaces in them.

End with

Amt = .[Unit Price].Value ' is the correct usage when . is used instead of ! you are referencing the Value property

Amt = .[Unit Price] also works because VBA assumes .Value is the default property you are refering to.

Me is an Alias name for the current form. The usage can be in any of the following Syntax:

• Forms!MainForm
• Forms("MainForm")
• Form_MainForm
• Me

Referencing a control, say [Unit Price] on a Sub-Form from MainForm:

• =Forms![MainForm]![Detail Form].Form![Unit Price]
• =Me![Detail Form].Form![Unit Price]
• =[Detail Form].Form![Unit Price]

Refer this Article for example of updating Sub-Form records from Main Form with VBA: Updating Sub-Form Recordset from Main Form