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]

Re: What's wrong with this code???

On Thu, 23 Mar 2006 16:51:59 -0600, doncee
<nodbcspam9814w anted@charter.n et> wrote:
[color=blue]
>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[/color]

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]

Re: What's wrong with this code???


doncee wrote:[color=blue]
> 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[/color]

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]

Re: What's wrong with this code???

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

doncee <nodbcspam9814w anted@charter.n et> wrote in
news:Xns978FAC0 99A8CEmedbcSWBE ll@216.196.97.1 31:
[color=blue]
> 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)[/color]
' you are on the first record of the table. yo need something to
' set the recordset to the appropriate record.[color=blue]
> rst.Edit[/color]

' Here you are testing whether your current record is null. You
'are not changing the value to null[color=blue]
> IsNull (rst!date_enter ed)
> rst.Update
> rst.close
> Set db = Nothing
> End If
>
> End Sub
>[/color]
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.
[color=blue]
> 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
>[/color]



--
Bob Quintal

PA is y I've altered my email address.

[doncee]

Re: What's wrong with this code???

Bob Quintal <rquintal@sympa tico.ca> wrote in
news:Xns978FC3F 55C801BQuintal@ 207.35.177.135:
[color=blue]
> There is lots wrong with the code. See my comments inline.
>
> doncee <nodbcspam9814w anted@charter.n et> wrote in
> news:Xns978FAC0 99A8CEmedbcSWBE ll@216.196.97.1 31:
>[color=green]
>> 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)[/color]
> ' you are on the first record of the table. yo need
> something to ' set the recordset to the appropriate record.[color=green]
>> rst.Edit[/color]
>
> ' Here you are testing whether your current record is null.
> You 'are not changing the value to null[color=green]
>> IsNull (rst!date_enter ed)
>> rst.Update
>> rst.close
>> Set db = Nothing
>> End If
>>
>> End Sub
>>[/color]
> 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.
>[color=green]
>> 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
>>[/color]
>
>
>[/color]

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

[doncee]

Re: What's wrong with this code???

Bri <not@here.com > wrote in
news:T0HUf.1736 34$sa3.121722@p d7tw1no:
[color=blue]
>
> doncee wrote:[color=green]
>> 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[/color]
>
> 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
>[/color]

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

[doncee]

Re: What's wrong with this code???

scott <scott@NoSpam_I nfotrakker.com> wrote in
news:gcd622dksq 3mk808f1t6namcf ta7dqja5b@4ax.c om:
[color=blue]
> On Thu, 23 Mar 2006 16:51:59 -0600, doncee
> <nodbcspam9814w anted@charter.n et> wrote:
>[color=green]
>>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[/color]
>
> 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
>[/color]

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