Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Thanked 0 Times in 0 Posts

    Is control source a formula? (Access 2000)

    I have a form that is based on a query, which includes some formulas. How would I programmatically determine if the control source of a control is a formula in the query as opposed to a field in the query?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Is control source a formula? (Access 2000)

    The control source property is a string. If the first character of this string is "=", it is a formula.

    Dim ctl As Control
    Dim strControlSource As String
    Set ctl = Me!ControlName
    strControlSource = ctl.ControlSource
    If strControlSource = "" Then
    MsgBox "Unbound control"
    ElseIf Left(strControlSource, 1) = "=" Then
    MsgBox "Calculated control (formula)"
    MsgBox "Bound to field"
    End If

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Arlington, Virginia, USA
    Thanked 3 Times in 3 Posts

    Re: Is control source a formula? (Access 2000)

    If the control on form is a calculated control, HansV's reply applies. If the control is bound to calculated field in query, you can use DAO methods & properties to determine whether or not field is updatable (this assumes the query used for form's RecordSource is otherwise an updatable query). Specifically you can use the DAO Field object's DataUpdatable property to make this determination. Example:

    Public Function IsControlSourceCalculated(ByRef strFrm As String, ByRef strCtl As String) As Boolean
    On Error GoTo Err_Handler

    Dim db As DAO.Database
    Dim qry As DAO.QueryDef
    Dim strFld As String
    Dim strMsg As String

    ' Assumes: form is open; RecordSource is a saved query; and _
    ControlSource is field from RecordSource, not a calculated control:
    Set db = CurrentDb
    Set qry = db.QueryDefs(Forms(strFrm).RecordSource)
    strFld = Forms(strFrm).Controls(strCtl).ControlSource
    IsControlSourceCalculated = Not qry.Fields(strFld).DataUpdatable

    Set db = Nothing
    Set qry = Nothing
    Exit Function
    Select Case Err.Number
    Case 0
    Resume Next
    Case Else
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbExclamation, "ERROR MESSAGE"
    Resume Exit_Sub
    End Select

    End Function

    Example of calling function from open form:

    Private Sub CheckControlSource()

    MsgBox "Control: " & Me.ActiveControl.Name & vbCrLf & _
    "ControlSource: " & Me.ActiveControl.ControlSource & vbCrLf & _
    IsControlSourceCalculated(Me.Name, Me.ActiveControl.Name), _

    End Sub

    I tested this using form based on query with four fields, 2 bound to table fields, 2 using calculated expressions. Added textbox for each field, called CheckControlSource on each control's On Enter event. The resulting msgbox correctly identified whether or not the control/field was bound (updatable) or calculated (not updatable). You may be able to adapt this, but note comments in sample code (you can use HansV's code to determine if control is calculated or not before calling function to avoid error for calculated controls). (But if you designed the form, and the query, wouldn't you already know which fields are based on calculated expressions or not??) The only other way I can think of to do this is would be to parse the query SQL, but that would probably be more trouble than it's worth.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts