How to update date field when other fields are changed?

[ittechguy]

Hopefully this is a simple problem.



I'm developing a form which allows me to change Equipment information. Its still rough but currently looks like this:



The form is bound to a query. The user would select something they want to change and then clicks "save". The "Physical Equipment Information" text boxes are locked and not update able, everything else can be changed.

I can figure out most of the VBA code behind this form, except for the 3 date fields. I've never worked w/ dates before, and so I find it confusing and also researching this hasn't helped much yet.

I want the "Last Updated" text box to update to today's date if there has been a change made to the Physical Location combo boxes or to the Logical Information boxes.

I want the "Installed" text box to update to today's date if Equipment Status combo box's value was changed from "In Storage" (EquipmentStatusPK 2) to "Operational" (EquipmentStatusPK 1) and/or if there has been a change to the "Change Physical Location" combo boxes. For an example, if the only thing that changes is the physical location then Equipment Status would remain "Operational" (not changed), and the installed text box would contain today's date and the uninstalled text box would also contain today's date, Also, the last updated text box would contain today's date because a change had been made.

If Equipment Status is "In Storage" then "Change Physical Location" combo boxes must be empty and Uninstalled text box must contain today's date (the date that change was made and user hit "Save").

If at all possible, I'm trying to keep most of my code in the Click event of cmdSave. I figure I'm going to have to use code to let VBA know if a change had been made, and then update the date text boxes accordingly. Perhaps this would be in the BeforeUpdate event of thee combo or text box.


What would you guy's recommend I do to set this form up how it needs to be?

[pbaldy]

Presuming the form is bound, you can compare the OldValue property of a control to the Value property to see its values before/after updating. This can be done until the data is saved.

[ittechguy]

I've been trying to use .OldValue, but I keep getting a "Object Doesn't Support This Property Or Method" error.

Here is my code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
         Dim ctl As Control
         Dim myForm As Form
         Set myForm = Me.Form
       
         For Each ctl In myForm
             If ctl.Value <> ctl.OldValue Then
                 Me.txtDateUpdated = Date
             End If
         Next
End Sub

I've tried just using one combo box at a time, and also using the method above. Same results.

[pbaldy]

You either have to use the Tag property or check the control type. Many don't have those properties, like labels, lines, etc.

[ittechguy]

Thank you! I tagged each control I wanted it to look for changes, and now it works.

For Each ctl In myForm              
     If InStr(1, ctl.Tag, "*") <> 0 Then
            If ctl <> ctl.OldValue Then
                     Me.txtDateUpdated = Date
                     MsgBox "Date Updated" 'For troubleshooting purposes, remove when form is finished. 
            End If
     End If
Next

One other question. How would I handle the EquipmentStatus combo box? I need it to update the date installed if it had been changed from 2 to 1 (In storage to Operational). Or delete DateInstalled and update DateUninstalled if changed from 1 to 2. I could use a If statement that goes something like "If EquipmentStausFK.Value = 1 then DateInstalled = Date." But that wouldn't check to see if a change has been made to EquipmentStatus, it would update the date regardless, and thats not what I want.

[ittechguy]

Here is what I have right now:

Private Sub Form_BeforeUpdate(Cancel As Integer)
         Dim ctl As Control
         Dim myForm As Form
         Set myForm = Me.Form
       
         For Each ctl In myForm
              If InStr(1, ctl.Tag, "*") <> 0 Then
                  If ctl.Value <> ctl.OldValue Then
                     Me.txtDateUpdated = Date
                     MsgBox "Date Changed"
                  End If
              End If
              If InStr(1, ctl.Tag, "&") <> 0 Then
                  If ctl.Value = 1 <> ctl.OldValue Then
                  '   Me.txtDateUpdated = Date
                     MsgBox "Installed Date Updated"
                     End If
                  If ctl.Value = 2 <> ctl.OldValue Then
                  MsgBox "Uninstalled Date Updated"
                  End If
              End If
         Next

I'm aware ctl.Value = 1 <> ctl.OldValue is not right. As it is, its giving me both msgBoxes no matter what change has been made, so it seems to be ignoring .value =1 or 2. I just don't know how to fix it.

[pbaldy]

Well, to test for it being changed from one thing to another, along the lines of:

If ctl.OldValue = 1 And ctl.Value = 2 Then

[ittechguy]

After a lot of trial and error, I've got it working using a boolean, as below:

Private Sub Form_BeforeUpdate(Cancel As Integer)
         Dim ctl As Control
         Dim myForm As Form
         Set myForm = Me.Form
         Dim inStorage As Boolean
         inStorage = False
         inStorage = inStorage Or Me.cboEquipmentStatus.Value = 2
         inStorage = inStorage Or Me.cboEquipmentStatus.Value = 3
         For Each ctl In myForm
              If InStr(1, ctl.Tag, "*") <> 0 Then
                  If ctl.Value <> ctl.OldValue Then
                     Me.txtDateUpdated = Date
                  End If
              End If
              If InStr(1, ctl.Tag, "&") <> 0 Then
                  If inStorage And ctl.Value <> ctl.OldValue Then
                  
                      If MsgBox("WARNING!! Changing Equipment status to In Storage will remove all Logial Equipment Information as well as the Room its assigned to." _
                      & " Are you sure you want to proceed?", vbYesNo) = vbYes Then
                          DoCmd.RunCommand acCmdSaveRecord
                          Call toStorage
                          Me.txtDateUninstalled = Date
                          MsgBox "Uninstalled Date Updated"
                      Else
                          Me.Undo
                      End If
                  ElseIf Not inStorage And ctl.Value <> ctl.OldValue Then
                      Me.txtDateInstalled = Date
                      MsgBox "installed date updated"
                  End If
            
              End If
         Next
End Sub
Private Sub toStorage()
                          Me.cboBuildingName = ""
                          Me.cboRoomName = ""
                          Me.cboCabinetName = ""
                          Me.txtEquipmentName = ""
                          Me.txtEquipmentIP = ""
                          Me.cboEquipmentNetworkType = ""
                          Me.cboSoftwareVersion = ""
End Sub

Hopefully you can understand my code. Right now, I'm trying to empty all the cbo and txt boxes in Private Sub toStorage. It was telling me I need to save the record first. So I added "DoCmd.RunCommand acCmdSaveRecord" However, that doesn't work either. it gives me this error:



...What can I do about that? Should I be using OnDirty instead of Before Update?

[ittechguy]

I'm attatching a copy of the database below. If anyone would take the time to take a look, I really appreciate it.

To reproduce the error, go to frmPopupChangeEquipment, change "Equipment Status" from "Operational" to "In Storage." Because I have not yet setup the save button, you'll have to hit F5. At which point the msgBox pops up, hit "Yes" and then you'll see the error saying that the record must be saved first.

Database FAKE DATA.zip


EDIT: After thinking about this for a while, I realize I've been going about this the wrong way. Rather than clearing the cbo and txt boxes, I should be updating the tables themselves. I'm looking into how thats done. Trying to use DAO recordsets.

[ittechguy]

Made a lot of progress, but its still not working. Now when I hit "save" it tells me that there's a write conflict and it asks if I want to save record or copy to clipboard.

Private Sub cboBuildingName_AfterUpdate()        Me.cboRoomName.Requery
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
     Dim ctl As Control
         Dim myForm As Form
         Set myForm = Me.Form
         Dim inStorage As Boolean
         inStorage = False
         inStorage = inStorage Or Me.cboEquipmentStatus.Value = 2
         inStorage = inStorage Or Me.cboEquipmentStatus.Value = 3
         For Each ctl In myForm
              If InStr(1, ctl.Tag, "*") <> 0 Then
                  If ctl.Value <> ctl.OldValue Then
                     Me.txtDateUpdated = Date
                  End If
              End If
              If InStr(1, ctl.Tag, "&") <> 0 Then
                  If inStorage And ctl.Value <> ctl.OldValue Then
                  
                      If MsgBox("WARNING!! Changing Equipment status to In Storage will remove all Logial Equipment Information as well as the Room its assigned to." _
                      & " Are you sure you want to proceed?", vbYesNo) = vbYes Then
                          
                          Me.txtDateUninstalled = Date
                          MsgBox "Uninstalled Date Updated"
                          Call toStorage
                      Else
                          Me.Undo
                      End If
                  ElseIf Not inStorage And ctl.Value <> ctl.OldValue Then
                      Me.txtDateInstalled = Date
                      MsgBox "installed date updated"
                  End If
            
              End If
         Next
End Sub


Private Sub toStorage()
     Set db = CurrentDb
     Set rs = db.OpenRecordset("SELECT * FROM tblEquipment WHERE EquipmentPK = " & txtEquipmentPK)
     With rs
 .Edit
   .Fields("CabinetFK") = Null
   .Fields("EquipmentName") = Null
   .Fields("EquipmentIP") = Null
   .Fields("EquipmentNetworkTypeFK") = Null
   .Fields("SoftwareVersionFK") = Null
 .Update
End With
End Sub




Private Sub cboRoomName_AfterUpdate()
        Me.cboCabinetName.Requery
End Sub
Private Sub cmdSave_Click()
    DoCmd.RunCommand acCmdSaveRecord
End Sub

EDIT: I believe the reason this is happening is because there are cbo and txt boxes on the form which are bound to fields in a query. So I am trying to update the table after making changes to box on the form, and then without saving the record, I am trying to also make changes to fields using a DAO recordset. I believe that is why I have a conflict.

I can't save the form in the BeforeUpdate event. So I'm not sure how to resolve this.

[ittechguy]

I figured it out. I needed to place the code in the Click event of my save button, not in the BeforeUpdate event of the Form. its working now.

[pbaldy]

Sorry, been grinding on a project. Glad you got it sorted out.