Field default value from query in MS Access

Question

I have a field on a table in MS Access, tblMyTable.SomeID, and I want to set the default value as a user preference in tblUserPref.DefaultSomeID. It doesn't appear that I can set the default value to use a query in the table definition of tblMyTable. I have a form where records are entered into tblMyTable. I've tried to set the default value of the field on the form, but doesn't seem to accept a query either. So, as a last resort, I'm trying to do it with VBA. I can query the value that I want in VBA, but I can't figure out which event to attach the code to.

I want to run the code whenever a new blank record is opened in the form, before the user starts to type into it. I do not want to run the code when an existing record is opened or edited. However, if the code runs for both new blank records and for existing records, I can probably code around that. So far, all of the events I have tried on the field and on the form itself have not run when I wanted them to. Can anyone suggest which event I should use, and on which object?

Answer 1

You probably want to put that code in the "Before Insert" event for the Form it self (none of the objects on the form).

Correction: That won't actually trigger until your user starts entering data - so you just need to make sure that the fields you want to have defaults for come after the first data entry field.

You could also check for a new record in the "On Current" event.

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

The disadvantage with this is the new record is created/marked dirty immediately when you enter it. So, if you thoughtlessly step through the records, you can end up running of the end and creating several extra records with just default data in them - so you will have to do something to trap that sort of condition (e.g., a required field, etc.)

Answer 2

You're right. You can't set the control's default value property to a value that isn't known at compile time. That value will be determined at run time. So the solution is to set the control's value property, not the defaultvalue property, during the form's current event. Note, getUserID() is a public function used to determine who the user is.

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

Answer 3

I don't know how you're determining who the current user is, but I will assume it's something you can call programmatically. In the interest of simplicity, I am just going to use Access' built-in "CurrentUser" method for this example. (User-level security required, otherwise it defaults to "Admin".)

Create a public function in a VBA module to return the current user's default value:

Public Function InsertDefaultSomeID() As String

InsertDefaultSomeID = DLookup("DefaultSomeID", "tblUserPref", _
                              "UserID='" & CurrentUser & "'")

End Function

In tblUserPref, you need a [UserID] field and a [DefaultSomeID] field. Define a default for your current user.

Then, on your form bound to tblMyTable, open the Properties for the [SomeID] field and set the Default Value property to:

=InsertDefaultSomeID()

Save your form, log on as a user with a known default, and try inserting a new record. Your default value should be automatically populated.

Answer 4

He're a suggested alternative approach. Rather than explicitly INSERTing the default when the user has not specified an explicit value, instead leave that value as missing (I'd probably model this in a dedicated table and model the missing value by, well, not INSERTing a row, but I know many people aren't averse to having many nullable columns in their tables). Then you can replace the missing value in a query. This may or may not be valid in your application, as I say just another approach to handling missing data :)

Answer 5

I'm not certain I've understood the problem, but I think you're asking to insert a value in the field that is drawn from a different table, based on some runtime information (such as the user name). In that case, you could use the domain lookup function, DLookup(), and you'd pass it the name of the field you want returned, the name of the table or query you're looking it up from, and the criteria for limiting the result to one row (which would, I assume, depend on values you can gather at runtime). That DLookup() formula could then be set permanently as the default value on the form control, and would not cause the form to be dirtied before you've created a real record.

Of course, I may have completely misinterpreted what you're trying to do, so this may not work, but you seemed to want to look something up in a recordset and use the result as your value for new records, and DLookup() would allow you to do that without any coding at all (as well as having the benefit of not dirtying the record prematurely).