Insert default value into ms access table from code

Question

I want to insert OfficerID of the officer currently logged in as a default value into the table T_FileCases.

Already in the login form I have a field that holds officer's name (UserName) when they are logged in successfully but when this happens I also want to automatically insert their OfficerID into the table T_FileCases automatically, instead of using a combo box for selecting officers name when creating a new case.

Me.Hold_User_ID = Nz(DLookup("OfficerID", "Table_Officers", "Names='" & Me.UserName & "' and NRC='" & Me.PWD & "'"), -1)
    ' check to see if we have a good login
    If Me.Hold_User_ID = -1 Then
        MsgBox "Sorry boss, you typed invalid username or password.", vbExclamation
        Exit Sub
    Else
        ' load the users access level into the holder form field
        Me.hold_level = DLookup("Access_Level", "Table_Officers", "Names='" & Me.UserName & "' and NRC='" & Me.PWD & "'")

    End If

Matt Weller · Accepted Answer · 2014-01-12 09:43:20Z

1

On the Before Insert event of the data entry form you could create an event procedure to check for the UserID and populate the field with the appropriate value.

This would require that the UserID is able to be retrieved at this point, either by keeping the login form hidden in the background or by assigning the value from the login form to a Global variable at the point of validation.

answered Jan 12, 2014 at 9:43

Matt Weller

2,7742 gold badges23 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1868306 Over a year ago

Is it like this: Private Sub Form_BeforeInsert(Cancel As Integer) If Me.txtCaseID.Value >= 1 Then INSERT INTO T_Cases (OfficerID) values Forms.F_FileCases!txtCaseID; End If End Sub

Collectives™ on Stack Overflow

Insert default value into ms access table from code

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related