Updating a table then deleting current record

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

Good afternoon all,
I have a dB that I am working on that a user can submit a request to add
something to a table that is used as a record source. Once the user hits
submit, it sends out an email to a group mail box to tell managers what has
been requested and also puts the information in a temp table (tbl_TEMP). The
manager can then go into the dB and access the manager area, in there, there
is a form which prompts them for the MainID. After the manager enters that
it runs a query and pulls that information from the temp table. From here
the manager needs to be able to accept the change or deny it. I am having
issues with the Accept feature. Since there is quite a few tables that it
could possibly need to get appended to I have gone with the following script
to do the append if there is information in the field of temp table.

Function Accept()
'Servicing Standards
If "tbl_TEMP.Servcngstndrds" <> "" Then
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tbl_ServStand ( ServStand ) SELECT tbl_TEMP.
Servcngstndrds FROM tbl_TEMP"
End If
End Function

(I have this for every field of the temp table that goes to all different
tables)

This is making it want to append ALL the information from the temp tables
field and not just from the current record that was pulled up which is what I
need. I need as well, that after the information from the current record is
appended it needs to get deleted out of the temp table.

My current way of doing it is with a macro that turns SetWarning off, runs
the code, then runs a delete query (which it never seems to get to), then
turns back on the warnings.

Thanks in advance
Bill
 
Hi Bill,
instead of
If "tbl_TEMP.Servcngstndrds" <> "" Then
you have to write something like this:

If len(dfirst("Servcngstndrds","tbl_TEMP") & "")>0 Then
 
Thanks a lot Alex...
I feel I am getting much closer but I am not quite there yet. When I run it
now it still appends all the temp information from the table and not just the
current record.. After it appends it deletes everything out of the table not
just the current record. How do I go about telling it to just affect the
current record. Thanks.

Bill
 
Hi Bill,
in this case you have to filter temp table by current record, for example:

DoCmd.RunSQL "INSERT INTO tbl_ServStand ( ServStand ) SELECT
tbl_TEMP.Servcngstndrds FROM tbl_TEMP Where tbl_TEMP.<SomeID>=" &
me.<SomeID>

where <SomeID> - is a unique field, which identifies current record
 
Back
Top