Solved

VBA - AfterUpdate

Posted on 2006-11-10
3
1,334 Views
Last Modified: 2008-02-26
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("SELECT TOP 1 DriverName FROM tblDriverHistory WHERE VehicleUID=" & Me.Parent.lstVehicles.Value & " 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.Value & "', '" & sDriverName & "', '" & Date & "')"
            ' Update current Driver in Vehicle Table
            CurrentDb.Execute "UPDATE tblVehicle SET Driver='" & sDriverName & "' WHERE UID='" & Me.Parent.lstVehicles.Value & "'"
        End If
    End If
End Sub


Cheers!
0
Comment
Question by:Cyber-Drugs
  • 2
3 Comments
 
LVL 39

Accepted Solution

by:
stevbe earned 500 total points
ID: 17913683
Me.Value -- no such animal ... you need to reference a field or a control.
You could use the control that is bound to the Driver field's .OldValue instead of looking it up all over again.
There also is no need to update the current driver as that will happen automagically because your cont (txstDriver) is bound to the Driver field. I also think you only need to add the Old driver, so if you need to see the entire driver history of a vehicle you can UNION the History and Current info.

Private Sub Driver_AfterUpdate()
    ' Check Driver Name is not null
    If Len(Me.txtDriver.Value & vbNullString) > 0 Then
        ' Check Driver Name does not match New Driver Name
        If Me.txtDriver.OldValue & vbNullString  <> Me.txtDriver.Value & vbNullString Then
            ' Insert old Driver into History Table
            CurrentDb.Execute "INSERT INTO tblDriverHistory (VehicleUID, DriverName, StartDate) " & _
                                        "VALUES ('" & Me.Parent.lstVehicles.Value & "', '" & Me.txtDriver.OldValue & "', '" & Date & "')"
        End If
    End If
End Sub
0
 
LVL 39

Expert Comment

by:stevbe
ID: 17913685
typo ...

automagically because your cont (txstDriver)

should be

automagically because your control (txtDriver)
0
 
LVL 4

Author Comment

by:Cyber-Drugs
ID: 17913705
Cheers Steve,

Makes a bit more sense now after looking at how your code is layed out. :)
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now