doncee wrote:
This is the set up:
[Text6] is a date field on a form. Its control source is
the field "date_entered" in"Table1". If there is a date showing
in the date_entered field (Text6) & the status in Combo box2
shows "deleted" then the after update event should delete the
date in Text6 & return the field "date_entered to a null value
when the form is closed. Even though the debug window shows
rst!date entered as null when you step through this event the
table still has the original value when you view it after the
form is closed.
Private Sub Form_AfterUpdate()
If Me.Combo2.Column(0) <> "deleted" Then
Me.Text6.Enabled = True
Me.Text6 = ""
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("table1", dbOpenTable)
rst.Edit
IsNull (rst!date_entered)
rst.Update
rst.close
Set db = Nothing
End If
End Sub
What am I missing???
I'm sure its something simple cause what am trying to do
is pretty basic but absolutely refuses to work in this form
Thanks for any guidance you can give me
dc
Since you asked what was wrong with you code, here goes. There are a few
things about this code that seem to be doing things the hard way. Lets
look at some of your lines of code:
If Me.Combo2.Column(0) <> "deleted" Then
- This does the opposite of what you state you want to do. The code in
the If Block will run if the value of Combo2 is NOT equal to deleted.
Also, there is no test for the value of date_entered.
Me.Text6.Enabled = True
- This is not necessary since the Enabled Property is only for the User
interface, the control value can be changed regardless via VBA.
Me.Text6 = ""
- If Text6 is bound to Table1.date_entered and date_entered is a
Date/Time field then you cannot set it to an empty string. You can set
it to Null as in:
Me.Text6 = Null
This makes the rest of the code superfluous, but lets look at it anyway:
Set rst = db.OpenRecordset("table1", dbOpenTable)
rst.Edit
- You do not move to the current record before you do the edit, so it
will always be the first record of the table that is edited, not the
current record (unless the current record is the first record)
IsNull (rst!date_entered)
- This line does NOT change the value of rst!date_entered, instead it
should be:
rst!date_entered = Null
Since you are changing the value of a field in the current record, you
should run this in the BeforeUpdate event so the record doesn't have to
be saved twice. Also, you should never modify the value of a field via
the recordset that is also being changed via the control, this can cause
locking issues.
End result is that the code should be:
Private Sub Form_BeforeUpdate()
If Me.Combo2.Column(0) = "deleted" AND Len(Me.Text6 &"")>0 Then
Me.Text6 = Null
End If
End Sub
I hope that helps.
--
Bri