Access Programming - Forms

Calculating Totals on Subform

When calculating sum on a subform don’t use calculated field in subform as it can be invalid if there is no data. Often find that it updated after you need it.

Instead use the Dsum function, triggered when you exit the subform:

Public InSubForm As Boolean

Private Sub JobItem_Enter()

    InSubForm = True

End Sub

Private Sub JobItem_Exit(Cancel As Integer)
    
    InSubForm = False
    Call JobTotal

End Sub

Public Sub JobTotal()

    Me.TotalAmount = DSum("Amount", "JobItem", "JobID = " & Me.JobID)
  
End Sub

Updating Mainform from Subform

If a form on the main form needs to be updated when changes are made on the subform, use the subform Exit event:


Private Sub JobItem_Exit(Cancel As Integer)
    
    Call JobTotal

End Sub

Public Sub JobTotal()

    Me.TotalAmount = DSum("Amount", "JobItem", "JobID = " & Me.JobID)
  
End Sub

Refreshing Data Updated on Other Forms

If a combobox on a form has new entry added whilst the form is open, the simplest way cater for this, is in the forms Activate event to requiery the combobox. (The activate will always be triggered if the user navigates away to another form and then returns.)

Hide Datasheet Column

Me!TaskSubform.Form!Noted.ColumnHidden = True

More on datasheet resizing and repositioning columns

Comboxes Delete with Single Key

If a combobox has a selected item, and you later want to clear it. You would either have to select the entire text and press backspace, or delete character by character. By intercepting the KeyDown event on the dropdown to check for backspace or del keys you make this much easier.

Private Sub sectorId_KeyDown(KeyCode As Integer, Shift As Integer)

    If KeyCode = 8 Or KeyCode = 46 Then ' Backspace or Del
        KeyCode = 27 ' Escape
        Me.sectorId = Null
        Call FindJobs ' Requery Subform      
    End If    
End Sub

Filter Subform by Search Text and Combo; Filter as User Types

When filtering a subform based on a search criteria the user enters, in order to detect the last key you have to use the .Text property. This is only valid if the control has the focus, so the code in the function CurrentValue will then return the .Value property.

' C O N T R O L S

Private Sub Client_Change()
    Call FindJobs()
End Sub

Private Sub sectorId_AfterUpdate()
    Call FindJobs
End Sub

Private Sub sectorId_KeyDown(KeyCode As Integer, Shift As Integer)
    ... ' See pervious section

' F U N C T I O N S

Private Sub FindJobs()

    Dim w As String, a as String, clientFind as String
  
    clientFind = CurrentValue("Client") 

    If Not IsNull(Me.sectorId) Then
        w = w & " AND sectorId = " & Me.sectorId
    End If

    If Nz(clientFind, "") <> "" Then
        w = w & a & client.clientId IN (SELECT client.clientId FROM client WHERE clientName LIKE '" & clientFind & "*')"
        a = " AND " 
    End If

    Me.JobListSub.Form.RecordSource = "SELECT job.*, client.clientName FROM job LEFT JOIN client ON job.clientId=client.clientId" & iif(w <> "", " WHERE "& 
End Sub

Private Function CurrentValue(fieldName As String) As String

     CurrentValue = Nz(Me(fieldName).Value, "")     
     On Error Resume Next     
     CurrentValue = Nz(Me(fieldName).Text, "") 

End Function

Call Subform Procedure/Function

Forms!Timesheet!ExpenseSubform.Form.FilterChange ' Call statement does not work

Delete Record in Datasheet/Continuous Form

The following method works on a continuous form, but not on a datasheet:

Private Sub HourID_DblClick(Cancel As Integer) ' Field on form    

    DoCmd.RunCommand acCmdDeleteRecord
    Me.Requery

End Sub

The follwing works on a datasheet:

    Private Sub HourID_DblClick(Cancel As Integer) ' Column field on datasheet    
 
    RunSQL ("DELETE FROM _Hour WHERE HourID = " & Me.HourID)
    Me.Requery

End Sub

Saving Current Record : Docmd.RunCommand or Me.Dirty = False

Access applies the RunCommand acCmdSaveRecord to whichever form happens to have focus. That may not be the form that is running the code.

Me.Dirty is safer, because you are specifying which form record to save.

Save Subform Before Updating Records via Query

An error can occur when there are unsaved records (particularly a new record) in a subform and some control event, on the main form, triggers a bulk update of all records displayed in the subform. Unless subform is saved first, running an update query will give a record updated by other user error.

Private Sub Hourlyrate_LostFocus() ' Field on Main form

      If Me.InvoiceDetailSub.Form.Dirty Then
          Me.InvoiceDetailSub.Form.Dirty = False
      End If

      sql = "UPDATE InvoiceItems SET Net = " & Nz(Me.Hourlyrate, 0) & " * (Nz(Hours, 0) + (Nz(Mins, 0) / 60)) WHERE InvoiceID = " & Me.InvoiceID 
      CurrentProject.Connection.Execute (sql)

      Call SumInvoice ' Calculate totals

      Me.InvoiceDetailSub.Requery

  End Sub

Bespoke Software Development - Reliability, Expertise, Commitment

Access Programming - Forms

Calculating Totals on Subform

Updating Mainform from Subform

Refreshing Data Updated on Other Forms

Hide Datasheet Column

Comboxes Delete with Single Key

Filter Subform by Search Text and Combo; Filter as User Types

Call Subform Procedure/Function

Delete Record in Datasheet/Continuous Form

Saving Current Record : Docmd.RunCommand or Me.Dirty = False

Save Subform Before Updating Records via Query

Resources

Access Database
Solutions

Bespoke Software Development - Reliability, Expertise, Commitment

Access Programming - Forms

Calculating Totals on Subform

Updating Mainform from Subform

Refreshing Data Updated on Other Forms

Hide Datasheet Column

Comboxes Delete with Single Key

Filter Subform by Search Text and Combo; Filter as User Types

Call Subform Procedure/Function

Delete Record in Datasheet/Continuous Form

Saving Current Record : Docmd.RunCommand or Me.Dirty = False

Save Subform Before Updating Records via Query

Resources

Access Database Solutions

Access Database
Solutions