Seeking Suggestions

[Ray Todd Jr]

I have a details form that contains the details of an individual with the
following fields:

firstname
lastname
Address1
Address2
City
State
Zip

I have code that when the Address1, Address2, City, State or Zip fields are
changed the original is copied to the Notes field to keep the original
address available for the user, should it be needed.

The code that I have works great, BUT what I am trying to prevent is when
the original address is either null or empty to not execute the 'archiving'
function.
The code as I currently have it:

Private Sub Form_Dirty(Cancel As Integer)

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim Old_Address1 As String
Dim Old_Address2 As String
Dim Old_City As String
Dim Old_State As String
Dim Old_Zip As String
Dim strNotes As String
Dim strSQL As String

Dim DefID As Long
Dim NotesID As Long

DefID = Me.DefendantID

If IsNull(Me.Address1) Or Me.Address1 = "" Then
Else
Old_Address1 = Me.Address1
End If

If IsNull(Me.Address2) Or Me.Address2 = "" Then
Else
Old_Address2 = Me.Address2
End If

If IsNull(Me.City) Or Me.City = "" Then
Else
Old_City = Me.City
End If

If IsNull(Me.State) Or Me.State = "" Then
Else
Old_State = Me.State
End If

If IsNull(Me.Zip) Or Me.Zip = "" Then
Else
Old_Zip = Me.Zip
End If



Set db = CurrentDb()
Set rs = db.OpenRecordset("taDEFENDANTSNOTES", dbOpenDynaset,
dbAppendOnly)

rs.AddNew
rs!DefNoteDate = Now()
rs!DefNoteTime = Now()
rs!DefendantID = DefID
NotesID = rs!DefNotesID
rs.Update
rs.Close

If IsNull(Me.Address2) Or Me.Address2 = "" Then

strNotes = "The previous address was:" & Chr$(13) & Chr$(10)
strNotes = strNotes + Chr$(13) & Chr$(10)
strNotes = strNotes + Old_Address1 & Chr$(13) & Chr$(10)
strNotes = strNotes + Old_City & ", " & Old_State & " " & Old_Zip

Else

strNotes = "The previous address was:" & Chr$(13) & Chr$(10)
strNotes = strNotes + Chr$(13) & Chr$(10)
strNotes = strNotes + Old_Address1 & Chr$(13) & Chr$(10)
strNotes = strNotes + Old_Address2 & Chr$(13) & Chr$(10)
strNotes = strNotes + Old_City & ", " & Old_State & " " & Old_Zip

End If

strSQL = "Update taDEFENDANTSNOTES set DefNote= """ & strNotes & """
where DefNotesID=" & NotesID

db.Execute strSQL, dbFailOnError

Set db = Nothing
Set rs = Nothing

End Sub


Can someone please give me suggestions on how to better improve this code to
achieve what I am attempting to do?

Thanks,

Ray.

 

[Dennis]

First off, you can use the "NZ" function to not have to test for IsNull in
addition to '=""'. The code would become:

If Nz(Me.MyVar) = "" then (whatever)

or

If Nz(Me.MyVar) <> "" then (whatever)

 

[Klatuu]

There are mutliple problems with your design.
The previous address info should actually be held in an archive table in
fields as they are in the current table. This would allow for multiple
address changes. As it is, only the most recent previous address is retained.

Also, this code:

rs!DefNoteDate = Now()
rs!DefNoteTime = Now()

is storing the same value in two fields. It is incorrect to store date and
time in differnt fields, but to store the same value in two fields in a
record is of no value whatsoever.

And, you are using the wrong event. The form is dirtied as soon as the
first character is entered in the first field. You code could go in either
the form's Before Update or After Update event. The problem with the Before
Update event is that if yout Update is canceled, you have stored incorrect
data. The problem with the After Update event is that once the form is
updated, the original values of the address fields are lost.

I would suggest you change your approach. Add the addtional table with a
foreign key field that would related it to the main record.
In the form Before update event, store the data to be saved in module level
variables. Update the address archive table with the record in the After
Update event of the form if any of the Address information was changed.

 

[JString]

I know what you want to do but I don't know exactly what the error is so it's
hard to come up with suggestions. At first glance though it looks like you
might want to try the following:

Instead of writing the procedure within an event procedure, declare it
elsewhere like like:

private sub yourprocedurename()
your code here
end sub

Then in the right event procedures:

Private Sub Form_Dirty(Cancel as Integer)
yourprocedurename
End Sub

That way you can reuse the sub where needed in the form where it is declared.

The BeforeUpdate event might be more appropriate than the Dirty event for
what you are trying to do since BeforeUpdate occurs after the user is
finished modifying the form's data.

You might also want to try using the OldValue property of a control.
Something like this might work in the BeforeUpdate event:

If field1.value <> field1.oldvalue And field1.oldvalue <> "" Then
notes.value = yourstringhere
End If

Hope that helps