How can I force a table update?

[Knowlton]

I am trying to use SQL to insert records into a details table where all but one field will contain the same data.
I have this working but my problem is I can only get the SQL to run in the AfterUpdate event of the form. This is a main form/subform setup and there has to be a record created it the main form's table before there can be a record added to the subform's table. I would like to be able to display a message that the records have been created. I have tried

DoCmd.Save acForm, "frmNewOrders"
        'process multiple loads
        Call PickupNumbers
        'send focus to close button
        Me.cmdCloseOrdersForm.SetFocus

before the focus goes to the close button but that hasn't worked. How do I get the table updated before the form is closing or moving to a new record?
Thanks!

[NeoPa]

It's very complicated to try to understand what you're talking about. Statements like "my problem is I can only get the SQL to run in the AfterUpdate event of the form." open a whole world of questions on its own.

If I were to guess, I would say you are going about something in a way completely different from what Access was designed to do (This is not at all uncommon). I'm open to hearing another attempt to explain what this is all about, but please bear in mind you need to explain more clearly than heretofore.

[Knowlton]

This is a form for entering orders for loads. The main form holds the data for the order: customer, date, PO Number, product, number of loads. The subform holds the data for the individual loads. All of this works fine. In the cases where there are multiple loads which all pickup and deliver to the same locations but each has its own pickup number, I have a sub routine (PickupNumbers) that loops through and creates these records without having to manually do so. It's my understanding that the form's data is only updated to the table when leaving the record, as in closing the form or moving to another record. I would like to be able to able to run this routine while still in the current record and at the end display a message that X number of load records have been created. In order to do this the main form's data needs to be saved to the table first. My question is how to force the save before the routine tries to run.
I hope this clears up what I'm trying to accomplish.
Thanks for your help!

[NeoPa]

Knowlton:
In order to do this the main form's data needs to be saved to the table first.

I wouldn't necessarily agree with that. It's true that it probably makes more sense to create these records only after the data is safely saved away, but your routine could work from the values in the form's controls if programmed to do so.

Forget that for now - it's important to understand but won't necessarily change your approach. You mention the Form_AfterUpdat e() event procedure, which is indeed the place to do what you describe, but you seem to be unhappy with this approach for some reason. That bit is still unclear. Your post certainly goes some way to making your situation clearer, but I still don't know what I can do to help at this point.

[Knowlton]

When this routine would be called all the main form's data would be entered. My objective is to be able to display a message that these records have been created in order that the person entering the information can know that everything is complete before navigating away from this order.
You mentioned "but your routine could work from the values in the form's controls if programmed to do so."
Can you give me some insight as how this might be accomplished?
Here is a portion of my code if it will help:

If Not IsNull(Forms!frmPickupNumbers!txtCommon) _
            And Not IsNull(Forms!frmPickupNumbers!txtVariable) _
            And Forms!frmPickupNumbers!txtRandom = "False" Then
        'there are common and variable parts to the pickup numbers and they are sequential
        'loop until load count is complete
        Do Until lngCounter = lngLoads
            'increment load count
            lngCounter = lngCounter + 1
            'concatonate the common and variable parts of the pickup number
            strSQL = _
                "INSERT INTO tblOrderDetails (OrderID, LoadNumber, PickupNumber, [RateType],[Rate],[FuelSurcharge], " & _
                    "[F/SRate],[Origin],[Destination],[Shipper],[Receiver]) " & _
                "Values (" & lngOrder & ", " & lngCounter & ", '" & strCommon & lngPkup & "', '" & strType & "', " & _
                    "" & lngRate & ", '" & strFsChg & "', " & lngFSRt & ", " & lngOrigin & ", " & lngDestination & ", " & _
                    "" & lngShipper & ", " & lngReceiver & ");"
            'Debug.Print strSQL
            CurrentDB.Execute strSQL, dbFailOnError
            'increment pickup number
            lngPkup = lngPkup + 1
        Loop

I appreciate your help!

[NeoPa]

Knowlton:
When this routine would be called all the main form's data would be entered. My objective is to be able to display a message that these records have been created in order that the person entering the information can know that everything is complete before navigating away from this order.

This doesn't appear to answer my question, and certainly gives me no further insight into what your problem is. However, it does mention wanting to send an appropriate message. With no understanding of any problem you have sending that message it's hard for me to help other than to say if you want to send a message at that point - do so. It should be as straightforward as that.

Knowlton:
You mentioned "but your routine could work from the values in the form's controls if programmed to do so."
Can you give me some insight as how this might be accomplished?

In your code you build up a SQL string using values from what appear to be local variables, but what you post doesn't really make that clear. I would guess the data somehow comes from a table somewhere. To get the process to run based off the data before it is saved you simply need to build the string with data from the controls on your form instead.

If I wasn't clear enough earlier, I think doing so is unwise unless you can be absolutely sure that the data is reliable. Until it is saved away, there are generally opportunities for the data to change. If this were to happen after you'd built records based on it, that would be bad news I'm sure you'll appreciate. Nevertheless, that's the way to do it if you're sure it's what you want to do.