What's wrong with this code???

[doncee]

This is the set up:
[Text6] is a date field on a form. Its control source is
the field "date_enter ed" 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_enter ed 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_AfterUpdat e()

If Me.Combo2.Colum n(0) <> "deleted" Then
Me.Text6.Enable d = True
Me.Text6 = ""
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordse t("table1", dbOpenTable)
rst.Edit
IsNull (rst!date_enter ed)
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

[scott]

On Thu, 23 Mar 2006 16:51:59 -0600, doncee
<no************ *****@charter.n et> wrote:

This is the set up:
[Text6] is a date field on a form. Its control source is
the field "date_enter ed" 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_enter ed 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_AfterUpdat e()

If Me.Combo2.Colum n(0) <> "deleted" Then
Me.Text6.Enable d = True
Me.Text6 = ""
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordse t("table1", dbOpenTable)
rst.Edit
IsNull (rst!date_enter ed)
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

Should probably move this to the form's BeforerUpdate event.

I'm assuming you're trying to set the value of Text6 to NULL if the
combo is anything other than deleted? If so, try this:

If Me.Combo2.Colum n(0) <> "deleted" Then Me.Text6=Null

This assumes:
your combo is a single column combo
your form is bound to table1, or a query containing table1
Text6 is bound to date_entered

[Bri]

doncee wrote:

This is the set up:
[Text6] is a date field on a form. Its control source is
the field "date_enter ed" 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_enter ed 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_AfterUpdat e()

If Me.Combo2.Colum n(0) <> "deleted" Then
Me.Text6.Enable d = True
Me.Text6 = ""
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordse t("table1", dbOpenTable)
rst.Edit
IsNull (rst!date_enter ed)
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.Colum n(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.Enable d = 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_ent ered 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.OpenRecordse t("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_enter ed)
- This line does NOT change the value of rst!date_entere d, instead it
should be:
rst!date_entere d = 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_BeforeUpda te()
If Me.Combo2.Colum n(0) = "deleted" AND Len(Me.Text6 &"")>0 Then
Me.Text6 = Null
End If
End Sub

I hope that helps.

--
Bri

[Bob Quintal]

There is lots wrong with the code. See my comments inline.

doncee <no************ *****@charter.n et> wrote in
news:Xn******** *************** **@216.196.97.1 31:

This is the set up:
[Text6] is a date field on a form. Its control source
is
the field "date_enter ed" 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_enter ed 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_AfterUpdat e()

If Me.Combo2.Colum n(0) <> "deleted" Then
Me.Text6.Enable d = True
Me.Text6 = ""
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordse t("table1", dbOpenTable) ' you are on the first record of the table. yo need something to
' set the recordset to the appropriate record. rst.Edit
' Here you are testing whether your current record is null. You
'are not changing the value to null IsNull (rst!date_enter ed)
rst.Update
rst.close
Set db = Nothing
End If

End Sub
General comment, why go to all the trouble of opening a
recordset. All you need is to do is put
If Me.Combo2.Colum n(0) <> "deleted" Then
Me.text6 = null 'set the new value.
end if
in the form's BeforeUpdate event.
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

--
Bob Quintal

PA is y I've altered my email address.

[doncee]

Bob Quintal <rq******@sympa tico.ca> wrote in
news:Xn******** **************@ 207.35.177.135:

There is lots wrong with the code. See my comments inline.

doncee <no************ *****@charter.n et> wrote in
news:Xn******** *************** **@216.196.97.1 31:

This is the set up:
[Text6] is a date field on a form. Its control
source is
the field "date_enter ed" 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_enter ed 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_AfterUpdat e()

If Me.Combo2.Colum n(0) <> "deleted" Then
Me.Text6.Enable d = True
Me.Text6 = ""
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordse t("table1", dbOpenTable)

' you are on the first record of the table. yo need
something to ' set the recordset to the appropriate record.

rst.Edit

' Here you are testing whether your current record is null.
You 'are not changing the value to null

IsNull (rst!date_enter ed)
rst.Update
rst.close
Set db = Nothing
End If

End Sub

General comment, why go to all the trouble of opening a
recordset. All you need is to do is put
If Me.Combo2.Colum n(0) <> "deleted" Then
Me.text6 = null 'set the new value.
end if
in the form's BeforeUpdate event.

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

Thanks a lot. Works like a charm.
You sure simplified it for me.
dc

[doncee]

Bri <no*@here.com > wrote in
news:T0HUf.1736 34$sa3.121722@p d7tw1no:

doncee wrote:

This is the set up:
[Text6] is a date field on a form. Its control
source is
the field "date_enter ed" 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_enter ed 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_AfterUpdat e()

If Me.Combo2.Colum n(0) <> "deleted" Then
Me.Text6.Enable d = True
Me.Text6 = ""
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordse t("table1", dbOpenTable)
rst.Edit
IsNull (rst!date_enter ed)
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.Colum n(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.Enable d = 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_ent ered 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.OpenRecordse t("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_enter ed)
- This line does NOT change the value of rst!date_entere d,
instead it should be:
rst!date_entere d = 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_BeforeUpda te()
If Me.Combo2.Colum n(0) = "deleted" AND Len(Me.Text6
&"")>0 Then
Me.Text6 = Null
End If
End Sub

I hope that helps.

--
Bri

Your coding was what I needed.
Thanks for your help
dc

[doncee]

scott <scott@NoSpam_I nfotrakker.com> wrote in
news:gc******** *************** *********@4ax.c om:

On Thu, 23 Mar 2006 16:51:59 -0600, doncee
<no************ *****@charter.n et> wrote:

This is the set up:
[Text6] is a date field on a form. Its control
source is
the field "date_enter ed" 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_enter ed 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_AfterUpdat e()

If Me.Combo2.Colum n(0) <> "deleted" Then
Me.Text6.Enable d = True
Me.Text6 = ""
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordse t("table1", dbOpenTable)
rst.Edit
IsNull (rst!date_enter ed)
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

Should probably move this to the form's BeforerUpdate
event.

I'm assuming you're trying to set the value of Text6 to
NULL if the combo is anything other than deleted? If so,
try this:

If Me.Combo2.Colum n(0) <> "deleted" Then Me.Text6=Null

This assumes:
your combo is a single column combo
your form is bound to table1, or a query containing table1
Text6 is bound to date_entered

Thanks for your help. Just what I needed.
works great
dc