Cyber-Drugs
asked on
VBA - AfterUpdate
Hi guys,
I have a Form (frmMainMenu)
Which has a listbox (lstVehicles)
and a SubForm (frmVehicle)
Inside the subform, I have a number of textboxes linked to the Table tblVehicle, but I want to do this...
When the value inside the field "Driver" changes, I want to Insert the new Driver's name into tblDriverHistory, as well as update the current driver name inside tblVehicle.
What's wrong with the code I have so far guys?
Private Sub Driver_AfterUpdate()
Dim rs As DAO.Recordset
Dim sDriverName As String
' Get most recent Driver`s name
Set rs = CurrentDb.OpenRecordset("S ELECT TOP 1 DriverName FROM tblDriverHistory WHERE VehicleUID=" & Me.Parent.lstVehicles.Valu e & " AND DriverName='" & Me.Value & "' ORDER BY UID DESC")
Do Until rs.EOF = False
sDriverName = rs!DriverName
rs.MoveNext
Loop
' Check Driver Name is not null
If sDriverName <> "" Then
' Check Driver Name does not match New Driver Name
If sDriverName <> Me.Value Then
' Insert new Driver into History Table
CurrentDb.Execute "INSERT INTO tblDriverHistory (VehicleUID, DriverName, StartDate) VALUES ('" & Me.Parent.lstVehicles.Valu e & "', '" & sDriverName & "', '" & Date & "')"
' Update current Driver in Vehicle Table
CurrentDb.Execute "UPDATE tblVehicle SET Driver='" & sDriverName & "' WHERE UID='" & Me.Parent.lstVehicles.Valu e & "'"
End If
End If
End Sub
Cheers!
I have a Form (frmMainMenu)
Which has a listbox (lstVehicles)
and a SubForm (frmVehicle)
Inside the subform, I have a number of textboxes linked to the Table tblVehicle, but I want to do this...
When the value inside the field "Driver" changes, I want to Insert the new Driver's name into tblDriverHistory, as well as update the current driver name inside tblVehicle.
What's wrong with the code I have so far guys?
Private Sub Driver_AfterUpdate()
Dim rs As DAO.Recordset
Dim sDriverName As String
' Get most recent Driver`s name
Set rs = CurrentDb.OpenRecordset("S
Do Until rs.EOF = False
sDriverName = rs!DriverName
rs.MoveNext
Loop
' Check Driver Name is not null
If sDriverName <> "" Then
' Check Driver Name does not match New Driver Name
If sDriverName <> Me.Value Then
' Insert new Driver into History Table
CurrentDb.Execute "INSERT INTO tblDriverHistory (VehicleUID, DriverName, StartDate) VALUES ('" & Me.Parent.lstVehicles.Valu
' Update current Driver in Vehicle Table
CurrentDb.Execute "UPDATE tblVehicle SET Driver='" & sDriverName & "' WHERE UID='" & Me.Parent.lstVehicles.Valu
End If
End If
End Sub
Cheers!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cheers Steve,
Makes a bit more sense now after looking at how your code is layed out. :)
Makes a bit more sense now after looking at how your code is layed out. :)
automagically because your cont (txstDriver)
should be
automagically because your control (txtDriver)