MS Access: Setting the default field value using a query

At present, the field has a default value of 100 and an input mask of 00000000;; ” “. However, despite setting the text align to left, the default value is not placed in the leftmost position of the field. To fix this issue, adjust the input mask to “100”#####;;” ” and set the default value in the table properties.


Question:

In MS Access, I have a field named tblMyTable.SomeID, and I intend to establish the default value based on tblUserPref.DefaultSomeID user preference. Unfortunately, it’s not possible to use a query in the
table definition
of tblMyTable to set the default value. I have attempted to set the default value of the field on the form, but it doesn’t accept a query either. Therefore, I am attempting to use VBA as a last resort. Although I can query the value I need in VBA, I am uncertain which event to connect the code to.

My goal is to execute the code when a new
blank record
is initiated in the form, precisely before the user inputs any data. I aim to avoid executing the code when an existing record is opened or modified. However, if the code runs for both new empty records and
existing records
, I can work on the code to accommodate such instances. Unfortunately, I have tested several events on both the field and the form, but none of them executed at the desired time. Can anyone suggest the appropriate event that I should use and the object on which to apply it?


Solution 1:

It’s unclear if I fully comprehend the issue, but it seems like you’re requesting to input a value in a field that originates from a distinct table, using dynamic data (like the user’s name). If that’s the case, you may use the DLookup() function to perform a domain lookup, and specify the name of the field you wish to retrieve, the name of the table or query you’re searching, and the criteria to limit the outcome to one row (which probably depends on the runtime values you’ve collected). The DLookup() formula could then be permanently designated as the default value on the form control, without affecting the form’s state until an actual record has been established.

It’s possible that my understanding of your intentions is incorrect, but if you intend to search for a value in a
recordset and use the result
and use it as the value for new records, DLookup() might be a suitable option that requires no coding and avoids prematurely altering the record.


Solution 2:


The method of determining the
current user
is unclear to me. However, I will assume it can be called programmatically. To keep things simple, I will be using Access’ “CurrentUser” method for this example. Please note that this method requires user-level security, otherwise it will default to “Admin”.

Develop a VBA module’s public function that can provide the default value of the current user.

Public Function InsertDefaultSomeID() As String
InsertDefaultSomeID = DLookup("DefaultSomeID", "tblUserPref", _
                              "UserID='" & CurrentUser & "'")
End Function

Create a [UserID] field and a [DefaultSomeID] field in tblUserPref. Then, specify a default value for the currently logged-in user.

Access the Properties of the [SomeID] field on your form linked to tblMyTable and modify
set the default value property
to:

=InsertDefaultSomeID()

Once you have saved your form, you can log in as a user who has a known default setting. Then, attempt to add a new record and observe as the default value is automatically populated.


Solution 3:


Consider placing the code within the “Before Insert” event of the form itself rather than any of its objects.

To clarify, the trigger will only activate once the user begins inputting information. Therefore, it is necessary to arrange the fields in a way that ensures the desired default values apply to the appropriate fields following the initial data entry field.

In the event of “On Current”, you have the option to verify if there is a fresh record.

Private Sub Form_Current()
    If Me.NewRecord Then
        Me.f2 = "humbug"
    End If
End Sub

The downside of this approach is that the moment you input a new record, it is instantly marked as dirty or created. As a result, if you carelessly navigate through the records, you may accidentally create multiple new records with default data. To prevent this from happening, you will need to implement measures such as requiring mandatory fields to be filled in.


Solution 4:

It’s accurate that you cannot assign an unknown value to the control’s default value property at
compile time
. The value would be determined during runtime. Therefore, to resolve this issue, you need to set the control’s value property instead of the defaultvalue property while the form’s current event is executing. It’s worth noting that getUserID() is a public function that identifies the user.

Private Sub Form_Current()
    On Error GoTo Proc_Err
    Dim rs As DAO.Recordset
    Dim fOpenedRS As Boolean
    If Me.NewRecord = True Then
        Set rs = CurrentDb.OpenRecordset("SELECT DefaultSomeID " _
        & "FROM tblUserPref WHERE UserID = " & getUserID())
        fOpenedRS = True
        rs.MoveFirst
        Me!txtPref.Value = rs!DefaultSomeID
    End If
Proc_Exit:
    If fOpenedRS = True Then
        rs.Close
    End If
    Set rs = Nothing
    Exit Sub
Proc_Err:
    MsgBox Err.Number & vbCrLf & Err.Description
    Err.Clear
    Resume Proc_Exit
End Sub

Frequently Asked Questions