Solved

VBA - AfterUpdate

Posted on 2006-11-10
3
1,330 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

747 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

16 Experts available now in Live!

Get 1:1 Help Now