In this case, the TABLE UPDATED is named TICKSHEET, and the Visual Basic command to perform this action is:
CurrentDb.Execute sSQLSQL (as
Expressed in Visual Basic) | |
| Insert New Record, Set Fields Case Number and Admin Reason Equal to Corresponding Fields on Form | "INSERT INTO tbl_ticksheet ( case_number, admin_reason ) SELECT tbl_keycode_log.autoid, " & stQuote & "Keycode" & stQuote & "AS Expr1 FROM tbl_keycode_log WHERE autoid= " & Me.autoid |
| After Inserting New Record (Above), Update Case Number Field Equal to Corresponding Field on Form | "UPDATE tbl_ticksheet SET tbl_ticksheet.source = " & stQuote & Me.ticksheet_source_cbox & stQuote & "WHERE case_number = " & Me.autoid |
| After Inseritng New Record (1st Line), Update linked_to_keycode_log_table equal to True | "UPDATE tbl_ticksheet SET tbl_ticksheet.linked_to_keycode_log_table = True WHERE case_number = " & Me.autoid |
| Insert New Record into tbl_deleted_records_log, insert autoid from current form into the equivalent autoid field in the table. Insert the value "Quotes" into the field named table_name | "INSERT INTO tbl_deleted_records_log (autoid,table_name) SELECT tbl_quotes.autoid, " & stQuote & "Quotes" & stQuote & "AS Expr1 from tbl_quotes WHERE autoid = " & Me.autoid |
| Insert the currently logged in user into the "modified_by" field of the table tbl_deleted_records_log | "UPDATE tbl_deleted_records_log SET tbl_deleted_records_log.modified_by = " & stQuote & CurrentUser() & stQuote & " WHERE autoid = " & Me.autoid & "AND table_name = " & stQuote & "Quotes" & stQuote |
tblMain had a field "assigned_rep" which contained a representative's NAME. I created a 2nd table tblRepresentatives which was a list of these representatives with an autoid field. I created a new field in tblMain named "rep_id" which I wanted to "fill in" with the proper autoid assigned to the name present in the "assigned_rep" field in the same table. (The tblRepresentatives field with the name was also called "assigned_rep")
In other words, "match up" the autoid value with the name, in essence replacing the rep's name in this table with the autoid value from the rep table.
The following syntax did this:
UPDATE tblMain AS m
INNER JOIN tblRepresentatives AS r
ON m.assigned_rep = r.assigned_rep
SET m.rep_id = r.autoid
Also see: DLookup, great for plucking value from another table