Run Update query immediately after record is created from form

[Solola]

I have a data-entry form (frmTDMEntry) into which the user enters about 15 fields of data into the table t_tdm. When the user tabs to the next blank, new record, I want Access to run an update query on the t_tdm.

Based on the fields that were entered in record #1, I have an update query (q_SIP_fields_update) that will update 11 additional fields for that record on t_tdm. I want this query to trigger automatically when a new record is created, so that the first record is updated. (I need this to happen, b/c my table has validation that requires that one of these 11 fields be unique. If it's not updated before a new record is created, the new record errors out - b/c both record #1 and record #2 have blanks in that field, and they're not unique).

I tried this code on the form's oncurrent method, but I get an run-time 3078 error when I first open my form - "Microsoft cannot find the input table or query." Also, I'm not sure how to prevent this query from running when I FIRST open the form for entering the first new record - I want it to update the first record only if I'm trying to then enter a second new record:

Private Sub Form_Current()
If Me.NewRecord Then
DoCmd.SetWarnings False
CurrentDb.Execute q_SIP_fields_update
DoCmd.SetWarnings True
End If
End Sub

I guess really, what I'd like it to do is trigger this update query immediately after the first record is saved to the table, regardless of whether or not I also enter a 2nd new record. But how do I do that? The data-entry person may be entering only 1 record, or 10 records at a time and will want to tab from one record to the next new one...

Please help?

 

[HiTechCoach]

You might be able to use enough duct tape and glue to make it happen.

IMHO, you have a bigger issue in that you need to do this at all. Sounds like a design issue. that should be addresses not patch with a lot of VBA code which will make the system very prone to "breaking".

 

[Solola]

IMHO, you have a bigger issue in that you need to do this at all. Sounds like a design issue. that should be addresses not patch with a lot of VBA code which will make the system very prone to "breaking".

I guess I'm not sure what you're asking - if you want to know why I'm doing capturing some data and udpating the record with other data, it's b/c I have to record point-in-time detail of dynamic data living in other tables. Also, I have to record new data that is not captured anywhere in any other table.

If your suggestion that I fix my design issue still stands after my explanation above, what do you suggest? I've been struggling with this, so I'm open to suggestions! Thanks!

 

[xenou]

I only read your first explanation quickly, but it seems you either want the triggered updates to occur before a new record is entered, or after a new record is entered.

You could use the FORM Before Update Event or FORM After Update Event in either case.

I suppose one concern would be if you run the before update event code and then the update fails - it seems to me the after update event is better for that reason. Not sure what you mean by "when a new record is created." That sounds to me like "just after a new record is created". But if the new record requires this information when it is created, then it might mean "just before a new record is created".