Solved

VBA - AfterUpdate

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

733 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