Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBA - AfterUpdate

Posted on 2006-11-10
3
Medium Priority
?
1,391 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 39

Accepted Solution

by:
stevbe earned 2000 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

715 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