Solved

VBA - AfterUpdate

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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 …

635 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