To write data into those fields into tblDevices are several possibilities. Today I'd write a couple of events for transactions form. About decade ago I used more difficult approach:
I have an UDF (a useful one for any approach you may use)
Code:
Option Compare Database
Option Explicit
- - - - - - - -- - - - - - - - - - -
Public Function ValidValue(parTable As String, IdField As String, IdCond As String, IdIsString As Boolean, RetField As String, DateField As String, parDate As Date)
Dim dbs As Database
Dim rs As Recordset
Dim varQstr As String
' The function returns the value of RetField from table parTable
' where IdField equals IdCond and DateField is nearest past or equal to parDate.
' The parameter IdIsString must be True, when IdField has Text format
On Error GoTo Err_ValidValue
Set dbs = CurrentDb
varQstr = "SELECT [" & RetField & "] FROM " & parTable & _
" WHERE [" & DateField & "] <= " & Format(parDate, "\#mm\/dd\/yyyy\#") & _
" And [" & IdField & "] = " & IIf(IdIsString, "'" & IdCond & "'", IdCond) & _
" ORDER BY [" & DateField & "] DESC"
Set rs = dbs.OpenRecordset(varQstr)
rs.AbsolutePosition = 0
ValidValue = rs.Fields(0).Value
Err_ValidValue:
rs.Close
dbs.Close
Set rs = Nothing
Set dbs = Nothing
End Function
The function returns latest value from table with entry date before or equal of date determined with parameter.
In devices form, I have a couple of invisible unbound text boxes with formulas as control sources (UserLevel 99 is for archived devices)
Code:
txtCurrUserID=ValidValue("tblTransactions";"DeviceID";[txtDeviceID];True;"UserID";"TransactDate";Date())
txtCurrUL=IIf(Nz([txtCurrUserID];"")="";99;DLookUp("UserLevel";"tblUsers";"UserID='" & [txtCurrUserID] & "'"))
Then BeforeUpdate event of devices form controls, when to write calculated data into tblDevices (In this app, normally all controls in devices form and transactions subform are disabled, except a combo for device selecting, and a combo where user can open the device for editing, or to save the record and disable all controls and subform. So even when user only edits/adds a transaction, the entry in tblDevices is always updated)
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.txtCurrUserID.Requery
...
If Not IsNull(Me.txtDeviceID) Then
If Me.CurrUserID <> Me.txtCurrUserID Then
Me.CurrUserID = Me.txtCurrUserID
End If
If Me.CurrUL <> Me.txtCurrUL Then
Me.CurrUL = Me.txtCurrUL
End If
End If
End Sub
For additional security, devices form Current event also checks for same
Code:
Private Sub Form_Current()
...
Me.txtCurrUserID.Requery
...
If Not IsNull(Me.txtDeviceID) Then
Me.CurrUserID = Me.txtCurrUserID
Me.CurrUL = Me.txtCurrUL
...
End If
End Sub