You are using the AfterUpdate event of your combo to execute an Append query
statement to record its OldValue in another table.
If the combo initially had no value, the OldValue would be Null. You need to
specify the word "Null" in your append query statement to have the null
appended. Assuming the combo's bound column is numeric, use Nz() to assign
the word Null, as in the example below.
To get an error message if the query does not work correctly, use the
Execute method, with the dbFailOnError switch. You will end up with
something like this:
Private Sub Me.MyCombo_AfterUpdate()
Dim strSQL As String
strSQL = "INSERT INTO MyHistoryTable (SomeField) SELECT " &
Nz(Me.MyCombo, "Null") & " AS MyValue ...
dbEngine(0)(0).Execute strSQL, dbFailOnError
End Sub
Because you are using the combo's AfterUpdate event, the value gets written
even if the user undoes the record instead of saving it. If you wanted to
write the history only when the value really was changed, you would need to
save the OldValue of the combo into a variable in the BeforeUpdate event of
the *form*, and then in the form's AfterUpdate event, compare its value and
write the record if they are not the same.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Chris Thompson" <ch**************@ntlworld.com> wrote in message
news:35**************************@posting.google.c om...
I have an Access 97 database to record the current location and status
of files in our office.
Each file has a record one field of which is it's current location.
On the form for each record is a 'limit to list' combobox from which
the user can update the file's current location.
I would like to be able to record the history of where a file has
been, so to do this I set up a second table linked to the first one by
a one to many relationship. I then coded the afterupdate event of the
bound combobox to run an INSERT INTO action query when the combobox is
changed.
This works just fine apart from the very first time the location is
selected ie when the contents of the combobox go from NULL to a
selected value the afterupdate event (of the control) does not seem to
be triggered. Hence the first selected location is not recorded and
the file's location history is incomplete.
Can anybody suggest why the afterupdate event is not being triggered
or how to get around this?
Your help would be very much appreciated.
Chris Thompson.
