First Effort Using VBA Code on ComboBox

  • Thread starter Thread starter ShadesOfGrey
  • Start date Start date
S

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
 
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
Click to expand...

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
 
All,
Click to expand...
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.
Click to expand...
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.
Click to expand...
FYI, it gives no messages even with SetWarnings on...first thing I
checked.  :)
Click to expand...
Stan
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Statuscmb_AfterUpdate()
Click to expand...
   DoCmd.SetWarnings False
Click to expand...
   DoCmd.RunSQL "UPDATE MasterData SET MasterData.StatChgDate =
Date() WHERE MasterData.IDNum = [forms]![DataEntry]![IDNum"
Click to expand...
   DoCmd.SetWarnings True
Click to expand...
End Sub
Click to expand...

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 -
Click to expand...

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