subform not loading fast enough

MArgente · June 28th, 2007, 08:24 AM

I was given the task of reworking a vba form that was given to me. The original programmer used a combo box and after the user picked an item, a subform was loaded and made visible. The subform was loaded nearly instantly and if the user change the selection in the combo box, the form update almost instantly.

When I reworked the form, for some reason, the form has a delay when loading and updating. I now pull information from the subform right after it is loaded to make some calculations but since the form isn't always completely loaded or updated, i get the error: You entered an expression that has no value.

Any suggestions?

mmcdonal · June 28th, 2007, 09:06 AM

Without the code, I am not sure why the subform loads slowly. Subforms are always problematic beyond merely displaying and updating data.

I would suggest that rather than taking your calculated values from the subform, you take them directly from the results the subform will be displaying, and bypass the subform.

Did that help?

mmcdonal

MArgente · June 28th, 2007, 09:34 AM

Yea, I understand where you're coming from. You're saying just pull the data from the tables the subform pulls it from. Hence, getting rid of the middle man. The way I rearranged the form requires more steps (which is probably why it is taking it long to load) If I really have to, I will just store values locally and use them.

Here's the before and after code:
before

Code:

Private Sub CUSIP_Number_AfterUpdate()
On Error GoTo Err_CUSIP_Number_AfterUpdate

Me![Broker ID].Visible = True
Me![Broker Short Name1_Label].Visible = True
Me![cusip death holder].Visible = True '<== SUBFORM
Me.Cusip_death_holder.Requery

If Me.[cusip death holder]![Include Interest Flag] = True Then
    Me.Number_Day.Enabled = True       '<== SUBFORM ACCESS
    Me.Accrued_Interest.Enabled = True
Else
   Me.Number_Day.Enabled = False
   Me.Accrued_Interest.Enabled = False
End If

If Me.Cusip_death_holder![Interest Rate] = 0 Then
   Me.Interest = ""
Else
   Me.Interest = Me.Cusip_death_holder![Interest Rate]
End If
 Exit Sub


Err_CUSIP_Number_AfterUpdate:

    MsgBox Error$
    Exit Sub
End Sub

after

Code:

Private Sub CUSIP_Number_AfterUpdate()
On Error GoTo Err_CUSIP_Number_AfterUpdate

If Me![CUSIP Number] <> ".new" Then          'if deceasedHolder has some CUSIPS already
    Me![cusip death holder].Visible = True  '<==== THIS IS THE SUBFORM TO BE LOADED
    Me![lblCompany].Visible = True
    Me![Company ID].Visible = True
    Me![Company ID].Enabled = False
    Me![CompanyIDDisplay].Visible = True

    Me![Broker Short Name1_Label].Visible = True
    Me![Broker ID].Visible = True
    Me![Broker ID].Enabled = False
    Me![BrokerIDDisplay].Visible = True

    Me![PickCusipLabel].Visible = True
    Me![PickCusip].Visible = True
    Me![PickCusip].Enabled = False

    '<About 30 controls visibility set to true>

    Dim db As Database
    Dim rs As Recordset
    Dim rsInterest As Recordset '<= this is current workaround

    Dim sqlString As String
    Dim sqlInterest As String   '<= *

    sqlString = "SELECT deceasedHolder.[Company ID], Company.[Company Short Name], " & _
        " deceasedHolder.[Broker ID], Broker.[Broker Short Name], deceasedHolder.[Date Received], " & _
        " deceasedHolder.[Quantity], deceasedHolder.[Interest], deceasedHolder.[Number Day], deceasedHolder.[Date Entered], " & _
        " deceasedHolder.[Accrued Interest], deceasedHolder.[Next Put Date], deceasedHolder.[Last Payable Date], " & _
        " deceasedHolder.[Met Holding Period], deceasedHOlder.[All Required Paperwork], deceasedHolder.[Original Death Certificate], " & _
        " deceasedHolder.[Misc Comments], deceasedHolder.[Date Entered], deceasedHolder.[Last Update Date], deceasedHolder.[User Id of Last Update] " & _
        " FROM (deceasedHolder INNER JOIN Company ON deceasedHolder.[Company ID]= Company.[Company ID]) " & _
        " INNER JOIN Broker ON deceasedHolder.[Broker ID] = Broker.[Broker ID] " & _
        " WHERE (((deceasedHolder.[Name]) = '" & Me![Name] & "')" & _
        " AND ((deceasedHolder.[Cusip Number]) = '" & Me![CUSIP Number] & "'));"


    Set db = CurrentDb()
    Set rs = db.OpenRecordset(sqlString)

        If Not rs.EOF Then
            '<Populated form with data from rs (not related to subform)
            '<Set some visibilities to true>
        End If
    rs.Close
    Set rs = Nothing

    sqlInterest = "Select * FROM Cusip WHERE Cusip.[Cusip Number] = '" & Me![PickCusip] & "';"
    Set rsInterest = db.OpenRecordset(sqlInterest) '<Workaround for slow subform

        If Not rsInterest.EOF Then
            Me![Interest] = rsInterest![Interest Rate]
            If rsInterest![Include Interest Flag] = True Then
                Me![Number Day].Enabled = True
                Me![Accrued Interest].Enabled = True
                calculate_acrued_interest
            Else
                Me![Number Day].Enabled = False
                Me![Accrued Interest].Enabled = False
                Me![Number Day] = ""
                Me![Accrued Interest] = ""
            End If
        End If
    rsInterest.Close
    Set rsInterest = Nothing
    db.Close

    Me![PickCusip].RowSource = " SELECT Cusip.[Cusip Number] " & _
        "FROM Cusip " & _
        "WHERE Cusip.[Company ID] = '" & Me![CompanyIDDisplay] & "';"


Else '<Irrelevant for the current problem>
End If
Exit Sub
Err_CUSIP_Number_AfterUpdate:

    MsgBox Err.Description
    Exit Sub
End Sub

mmcdonal · June 28th, 2007, 10:30 AM

I would load the data here, and then do the controls on the Subform's On Current event.

Then take this data as you are getting it in your code, and set controls on the main form as well. Or, add addtional code to the AfterUpdate event to set values on those controls.

Also, you may want to consider basing the subform on a local table, and then build your data into that table. Then the code looks something like:

Take Parameters
SetWarnings False
Delete all data in table
Append new data using parameters to local table
SetWarnings True

COuld be a matter of running a few queries.

mmcdonal

MArgente · June 28th, 2007, 10:42 AM

I ended up doing a quickfix. just retrieving the info from the subform's table (but still displaying the subform). Realistically, I'd like to do it another way, but being on a deadline, the runtime is negligible in this case.

Thanks

SerranoG · June 29th, 2007, 08:39 AM

Side note: You're setting the Me.Interest = "" at one point. If Interest holds an interest rate, you cannot assign a string value to something that is supposed to hold a number. You're better off setting Me.Interest = Null.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

MArgente · July 2nd, 2007, 08:47 AM

Thank you Greg. I didn't even think of that. However, Me.Interest is a text box on the form so when I tested it, nothing happened, but it is good practice anyway to do things like that.

SerranoG · July 5th, 2007, 07:17 AM

Yeah, the name "textbox" is misleading IMHO. It should be called something like a data box.

In the case of a bound form, the textbox could hold data of type text, integer, long, single, double, boolean, date, etc. It's based on the table field type. For an unbound form, I assume the textbox merely holds data of type variant until you do something with it and pass it along to a declared variable.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division