First Effort Using VBA Code on ComboBox

[ShadesOfGrey]

All,

I inherited a database that used an update query called from a macro
to change the date stored in a table. That is, whenever the Status
was changed, it would put the date it was changed into a date field.
I decided that the macro/query route was unwieldy and the macro was
causing some other problems, so I replaced those with the below event
procedure whenever I update the contents of the combo box.

Although this compiles and runs without error, it doesn't put a date
into MasterData.StatChgDate. In fact, it doesn't do anything at all,
as far as I can tell. I've never done this before so I'm not sure if
this is a syntax problem or a basic misunderstanding of how to use VBA
to run an Update query. Any advice would be most appreciated.

FYI, it gives no messages even with SetWarnings on...first thing I
checked.

Stan
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Statuscmb_AfterUpdate()

DoCmd.SetWarnings False

DoCmd.RunSQL "UPDATE MasterData SET MasterData.StatChgDate =
Date() WHERE MasterData.IDNum = [forms]![DataEntry]![IDNum"

DoCmd.SetWarnings True

End Sub

 

[John W. Vinson]

All,

I inherited a database that used an update query called from a macro
to change the date stored in a table. That is, whenever the Status
was changed, it would put the date it was changed into a date field.
I decided that the macro/query route was unwieldy and the macro was
causing some other problems, so I replaced those with the below event
procedure whenever I update the contents of the combo box.

Although this compiles and runs without error, it doesn't put a date
into MasterData.StatChgDate. In fact, it doesn't do anything at all,
as far as I can tell. I've never done this before so I'm not sure if
this is a syntax problem or a basic misunderstanding of how to use VBA
to run an Update query. Any advice would be most appreciated.

FYI, it gives no messages even with SetWarnings on...first thing I
checked.

Stan
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Statuscmb_AfterUpdate()

DoCmd.SetWarnings False

DoCmd.RunSQL "UPDATE MasterData SET MasterData.StatChgDate =
Date() WHERE MasterData.IDNum = [forms]![DataEntry]![IDNum"

DoCmd.SetWarnings True

End Sub

I'd guess that it's not updating the record because there is a conflict
between the Form editing the record and your code attempting to edit the same
record; and that the Setwarnings is concealing the error message that would
alert you to the problem.

Why not have a textbox named txtStatChgDate (which could be invisible) on the
form bound to StatChgDate? Your event code could be

Private Sub Statuscmb_AfterUpdate()
Me!txtStatChgDate = Date
End Sub

Be a lot simpler and would not suffer the conflict.

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[ShadesOfGrey]

All,

I inherited a database that used an update query called from a macro
to change the date stored in a table.  That is, whenever the Status
was changed, it would put the date it was changed into a date field.
I decided that the macro/query route was unwieldy and the macro was
causing some other problems, so I replaced those with the below event
procedure whenever I update the contents of the combo box.

Although this compiles and runs without error, it doesn't put a date
into MasterData.StatChgDate.  In fact, it doesn't do anything at all,
as far as I can tell.  I've never done this before so I'm not sure if
this is a syntax problem or a basic misunderstanding of how to use VBA
to run an Update query.  Any advice would be most appreciated.

FYI, it gives no messages even with SetWarnings on...first thing I
checked.  

Stan
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Statuscmb_AfterUpdate()

   DoCmd.SetWarnings False

   DoCmd.RunSQL "UPDATE MasterData SET MasterData.StatChgDate =
Date() WHERE MasterData.IDNum = [forms]![DataEntry]![IDNum"

   DoCmd.SetWarnings True

End Sub

I'd guess that it's not updating the record because there is a conflict
between the Form editing the record and your code attempting to edit the same
record; and that the Setwarnings is concealing the error message that would
alert you to the problem.

Why not have a textbox named txtStatChgDate (which could be invisible) onthe
form bound to StatChgDate? Your event code could be

Private Sub Statuscmb_AfterUpdate()
Me!txtStatChgDate = Date
End Sub

Be a lot simpler and would not suffer the conflict.

--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -

Well dang. Talk about going around your hind end to get to your
elbow... Thanks!

 

[John W. Vinson]

Well dang. Talk about going around your hind end to get to your
elbow... Thanks!

<chuckle> Haven't heard that expression since I was a kid in Arkansas... but
exactly. Sometimes these dang computers funnel you into doing things the hard
way!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com