Event for combo box and make changes visible to graph

[Lanox]

Hello,
I am just a self-teached beginning access user, so every usefull comment is welcome.

I have a form that retrieves some parameters X1, ..., Xm (X all together) and a multiplier Y from a database. Then it computes at several points Z1, ..., Zn function values f(X,Y,Zi) and stores them in a table. From this table a XY-graph is constructed and displayed in the form.

In a combo box the user can override the multiplier Y by entering a new multiplier Y. Via vba code, the table with the function values is emptied and filled with the new function values (and points Z offcourse).

There are two problems:
1. When the vba code that recomputes the table is triggered by the "On Change" event of the combo box, the vba code is called by every keystroke. Eg if the new multiplier gets the value 1.2345, than the code is called by "1", ".", "2", "3", "4" and "5". When the code is triggered by "On enter", than the code is only executed when a new value is entered, the user leaves the combo box and clicks again on the combo box. Only at this last click the code is executed.
How can I make sure that the user enters a new value and the vba code is only executed once when the user pushes "enter"?

2. The table that is recomputed is shown on the form and is displayed correctly when the code has finished. The place where the XY-graph is located on the form becomes blank after executing the code. The form has several pages, and when I leave the page with XY-graph and then return to it again, the XY-graph is displayed correctly again. Why is this happening and how can I make sure that the XY-graph is displayed correctly when the vba code is finished?

For the record, the last line of the vba code is "Me.Refresh".

Thank you,
Lanox

 

[NickHa]

How can I make sure that the user enters a new value and the vba code is only executed once when the user pushes "enter"?

Use the control's AfterUpdate event instead of its Change event handler.

Why is this happening and how can I make sure that the XY-graph is displayed correctly when the vba code is finished?

Try requerying the graph's data source in the form.

Me.GraphControl.Requery

 

[Lanox]

Hi Nick,

thanks for your reply. The suggestion regarding AfterUpdate works like a charm. But regarding the second problem, my vba doesn't recognise "GraphControl" in

Me.GraphControl.Requery

I have tried

Me.MyGraph.Requery

where "MyGraph" is the name of the graph. VBA compiles and runs it, but it doesn't fix my problem.

 

[NickHa]

I used the name Graphcontrol as a dummy - so changing it to your own control's name is correct.

Has the data source of your graph been updated by the time you execute the requery? It sounds like you need to update the table(s) to which the graph control is linked. Check the linked fields in particular, to make sure relevant updates have been made.

If you give more details about the data source(s) of your graph and how they relate to your form data source(s), I may be able to give you something more specific. Also, please post the full code which you use prior to the Requery.

 

[Lanox]

I think the updating of the table is not really the problem. The area where the graph should be is completely blank. There is no graph at all, not even one with empty data.

The Row Source looks is a query and looks like this:

SELECT Time, Calibration,Empirical,Override FROM [Primary Revision];

Primary Revision is the table that is emptied in the vba code and then refilled by newly calculated data.
When the line

Me.Refresh

is called, the table Primary Revision is correctly displayed on the from, but the graph area becomes blank.

Full VBA code:

Private Sub Override_Multiplier_AfterUpdate()
Call UpdateMyForm(False)
End Sub
 
Private Sub UpdateMyForm(NewMultiplier As Boolean)
Dim SQLstr As String, Underlying As String
Dim DB As DAO.Database
Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset, rs3 As DAO.Recordset
Dim Kappa1 As Double, Kappa2 As Double, Sigma1 As Double, Sigma2 As Double, Rho As Double, Multiplier As Double, NewSigma1 As Double, NewSigma2 As Double
Dim Time() As Double
Dim Power As Boolean, MyTime As Double
Dim MyTenorNr As Integer
Dim Vol1 As Double, Vol2 As Double, Vol As Double, NewVol1 As Double, NewVol2 As Double, NewVol As Double
ReDim Time(1 To 9)
Time(1) = 1 / 260
Time(2) = 1 / 12
Time(3) = 1 / 4
Time(4) = 1 / 2
Time(5) = 1
Time(6) = 2
Time(7) = 3
Time(8) = 5
Time(9) = 10
'Delete tabel content
SQLstr = "DELETE * FROM [Primary Revision];"
DoCmd.SetWarnings False
DoCmd.RunSQL (SQLstr)
DoCmd.SetWarnings True
Me.Refresh
 
Set DB = CurrentDb()
 
'Select Calibration Parameters
SQLstr = "SELECT Factor, Value, TimeStamp "
SQLstr = SQLstr & "FROM [HJM Params] "
SQLstr = SQLstr & "WHERE [HJM Params].SetNr=GetFromSet(""SetNr"") And [HJM Params].VRName = """ & Me.Revise_Primary & """;"
Set rs1 = DB.OpenRecordset(SQLstr)
rs1.MoveFirst
Do While Not rs1.EOF
If rs1.Fields("Factor") = "kappa1" Then
Kappa1 = rs1.Fields("Value").Value
ElseIf rs1.Fields("Factor") = "kappa2" Then
Kappa2 = rs1.Fields("Value").Value
ElseIf rs1.Fields("Factor") = "sigma1" Then
Sigma1 = rs1.Fields("Value").Value
ElseIf rs1.Fields("Factor") = "sigma2" Then
Sigma2 = rs1.Fields("Value").Value
ElseIf rs1.Fields("Factor") = "rho" Then
Rho = rs1.Fields("Value").Value
ElseIf rs1.Fields("Factor") = "stress multiplier" Then
Multiplier = rs1.Fields("Value").Value
End If
rs1.MoveNext
Loop
 
 
If NewMultiplier Then
Me.Override_Multiplier = Multiplier
End If
 
NewSigma1 = Sigma1 / Multiplier * Me.Override_Multiplier
NewSigma2 = Sigma2 / Multiplier * Me.Override_Multiplier
 
'Power Underlying?
SQLstr = "SELECT Category FROM Underlyings WHERE VRName = """ & Me.Revise_Primary & """;"
Set rs1 = DB.OpenRecordset(SQLstr)
rs1.MoveFirst
If rs1.Fields("Category") = "Power" Then
Power = True
Else
Power = False
End If
 
'Empirical volatilities
SQLstr = "SELECT Tenor1, Empirical FROM Variances WHERE VRName = """ & Me.Revise_Primary & """;"
Set rs1 = DB.OpenRecordset(SQLstr)
rs1.MoveFirst
Set rs2 = DB.OpenRecordset("Primary Revision")
Do While Not rs1.EOF
MyTime = Time(rs1.Fields("Tenor1"))
If Power Then
MyTenorNr = rs1.Fields("Tenor1")
Else
MyTenorNr = rs1.Fields("Tenor1") - 1
End If
 
Vol1 = Sigma1 / Kappa1 * (1 - Exp(-MyTime * Kappa1))
Vol2 = Sigma2 / Kappa2 * (1 - Exp(-MyTime * Kappa2))
Vol = 1 / MyTime * Sqr(Vol1 * Vol1 + Vol2 * Vol2 + 2 * Rho * Vol1 * Vol2)
NewVol1 = NewSigma1 / Kappa1 * (1 - Exp(-MyTime * Kappa1))
NewVol2 = NewSigma2 / Kappa2 * (1 - Exp(-MyTime * Kappa2))
NewVol = 1 / MyTime * Sqr(NewVol1 * NewVol1 + NewVol2 * NewVol2 + 2 * Rho * NewVol1 * NewVol2)
 
With rs2
.AddNew
.Fields("Time") = MyTime
.Fields("Calibration") = Vol
.Fields("Empirical") = Sqr(rs1.Fields("Empirical"))
.Fields("Override") = NewVol
.Update
End With
 
rs1.MoveNext
Loop
' Refresh form
Me.Refresh
Me.Primary_Revision_Graph.Requery
 
 
 
End Sub

 

[NickHa]

It seems to me that your code deletes all records in [Primary Revision], leaving the table empty by the time you hit Me.Refresh at the end of UpdateMyForm. I just tried a graph with and without data and its behaviour reflects your symptoms (i.e. the graph is blank when the table is empty).

If the table appears correctly when you switch to another form and back, it suggests that something is updating the table during that switch (maybe in the forms' Current event?).

I suggest a better way to delete the table contents would be

'Delete tabel content
CurrentDb.Execute "DELETE * FROM [Primary Revision];"

which can replace

'Delete tabel content
SQLstr = "DELETE * FROM [Primary Revision];"
DoCmd.SetWarnings False
DoCmd.RunSQL (SQLstr)
DoCmd.SetWarnings True

I noticed that your loop after the comment 'Select Calibration Parameters reflects only the last record in the recordset - is that your intention?

'Select Calibration Parameters
SQLstr = "SELECT Factor, Value, TimeStamp "
SQLstr = SQLstr & "FROM [HJM Params] "
SQLstr = SQLstr & "WHERE [HJM Params].SetNr=GetFromSet(""SetNr"") And [HJM Params].VRName = """ & Me.Revise_Primary & """;"
Set rs1 = DB.OpenRecordset(SQLstr)
rs1.MoveFirst
Do While Not rs1.EOF
If rs1.Fields("Factor") = "kappa1" Then
Kappa1 = rs1.Fields("Value").Value
ElseIf rs1.Fields("Factor") = "kappa2" Then
Kappa2 = rs1.Fields("Value").Value
ElseIf rs1.Fields("Factor") = "sigma1" Then
Sigma1 = rs1.Fields("Value").Value
ElseIf rs1.Fields("Factor") = "sigma2" Then
Sigma2 = rs1.Fields("Value").Value
ElseIf rs1.Fields("Factor") = "rho" Then
Rho = rs1.Fields("Value").Value
ElseIf rs1.Fields("Factor") = "stress multiplier" Then
Multiplier = rs1.Fields("Value").Value
End If
rs1.MoveNext
Loop

A tidier solution would be

'Select Calibration Parameters
SQLstr = "SELECT Factor, Value, TimeStamp "
SQLstr = SQLstr & "FROM [HJM Params] "
SQLstr = SQLstr & "WHERE [HJM Params].SetNr=GetFromSet(""SetNr"") And [HJM Params].VRName = """ & Me.Revise_Primary & """;"
Set rs1 = CurrentDb.OpenRecordset(SQLstr)
If Not rs1.EOF Then
  rs1.MoveLast
  Select Case rs1!Factor
  Case "kappa1": Kappa1 = rs1!Value
  Case "kappa2": Kappa2 = rs1!Value
  Case "sigma1": Sigma1 = rs1!Value
  Case "sigma2": Sigma2 = rs1!Value
  Case "rho": Rho = rs1!Value
  Case "stress multiplier": Multiplier = rs1!Value
  Case Else
  End Select
End If
rs1.Close

The question is, what do you do if a) no records in the recordset or b) none of the selected values is present?

This last part has nothing to do with your problem - it's just something I noticed as I was trying to understand your code.

BTW I would avoid using field names which are also VBA words (such as "Value")

rs1.Fields("Value").Value

is what drew my attention to this section of code, because it stands out as an anomaly. It clearly works, but just not good practice.