Don't know how to save related ONE field to the MANY record?

[epb]

I have a form for CustomerOrders that uses a query that pulls field data from CustOrders MANY table and the related EngData1 ONE table. There are a few related fields in the ONE table that I need to save in the CustOrders MANY record that show the state of the related fields at the time of the order input.

My Simple question...How do I save some related table fields from the ONE table to the MANY record? What is the proper syntax?



Please keep in mind, I don't want to hear about 99.99% of the time you should never save ONE table data to the MANY record. In this case it needs to be done.

This seems straightforward but I am not getting the related field data to save. Here is example code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
'Provide the user with the option to save/undo
'changes made to the record in the form

    If MsgBox("Changes have been made to this record." _
        & vbCrLf & vbCrLf & "Do you want to save these changes?" _
        , vbYesNo, "Changes Made...") = vbYes Then
        'Save some info and then "DoCmd.Save"
             Me.Text265 = Me!ID
             Me.EngDataID = Me.ID
             Me.PENETRANT = Me![PENETRANT INSP]
             Me.[HEAT TREAT] = Me![ENGDATA1_HEAT TREAT]
             Me.[MAG PARTICLE] = Me![MAG PARTICLE INSPECT]
             Me.[OTHER] = Me!ENGDATA1_OTHER
             If (Me!cboPrintNo.Column(4) = "") Then
                Me.CLASS = "N/A"
             Else
                Me.CLASS = Me!cboPrintNo.Column(4)
             End If
             Me.[CLASS 2] = Me!cboPrintNo.Column(5)
             Me.BLASTING = Me!ENGDATA1_BLASTING
             Me.[MATERIAL] = Me!ENGDATA1_MATERIAL

            'Save the record
             DoCmd.Save
     
        Else
            DoCmd.RunCommand acCmdUndo
    End If
End Sub

The only field data that appears to be saving is the cboPrintNo.Column(x) from a comboBox on the form. All other field data comes up as null when I know it is not null.

Thanks...

[pbaldy]

Check the column count property of the combo.

[epb]

Hi, thanks...The column count is working all other references are not.

Examples of what is working:
Me.CLASS = Me!cboPrintNo.Column(4)


Examples of what is not working and is not saving:

Me.Text265 = Me!ID
Me.EngDataID = Me.ID
Me.PENETRANT = Me![PENETRANT INSP]
Me.[HEAT TREAT] = Me![ENGDATA1_HEAT TREAT]
Me.[MAG PARTICLE] = Me![MAG PARTICLE INSPECT]
Me.[OTHER] = Me!ENGDATA1_OTHER

[pbaldy]

Sorry, I misread what wasn't working. Have you made sure the referenced controls have a control source of a field in the table? Have you set a breakpoint and examined what is coming from the sources? Can you post the db here for testing?

[Missinglinq]

Assigning the Value of an Unbound Combobox to a Textbox is a pretty standard procedure, but assigning the Values of what appear to be half a dozen Textboxes, to other Textboxes on the same Form, is not standard! Are all of these Controls actually on a Single Form, as opposed to being on a Form and its Subform?

Assigning the Value of one Control (Me.ID) on a Form to two Controls on the same Form, is also odd!

Me.Text265 = Me!ID
Me.EngDataID = Me.ID

In addition, there's a problem with your line

If (Me!cboPrintNo.Column(4) = "") Then

If the Field that is displayed in Column(4) has simply been left empty, it will not be equal to a Zero-Length String (""), which is what your code is checking for, it will be Null! To check for ZLSs and Nulls, you'd need something like

If Nz(Me!cboPrintNo.Column(4), "") = "" Then

instead.

And lastly, the DoCmd.Save command does not Save a Record! It saves Design Changes that have been made to an Object, in this case, a Form! To Save a Record you'd use either

DoCmd.RunCommand acCmdSaveRecord

or

If Me.Dirty Then Me.Dirty = False

But both of those are unneeded in the Form_BeforeUpdate event (saving the Record is what is happening, when you get to the Form_BeforeUpdate event, and will, in fact, in this event, pop an error!

Linq ;0)>

[epb]

Thank you pbaldy and Linq...

Lets start out with this:

Assigning the Value of one Control (Me.ID) on a Form to two Controls on the same Form, is also odd!

Me.Text265 = Me!ID
Me.EngDataID = Me.ID

Yes, that is odd. I was just testing if the text name of the field on the form would be set to Me!ID in the same way as the Data Control Source name.

Assigning the Value of an Unbound Combobox to a Textbox is a pretty standard procedure, but assigning the Values of what appear to be half a dozen Textboxes, to other Textboxes on the same Form, is not standard! Are all of these Controls actually on a Single Form, as opposed to being on a Form and its Subform?

In this case, there is no subform needed. There are a number of fields from two tables on the form. This is an Order entry form. Part table fields need to be seen on the Order entry form for reference and then some need to be saved as time sensitive info that may change over time. Like the cost of a process may increase or decrease at a later time. I agree that saving values from a textbox to another textbox is probably wrong. I just don't know how to do what I need to do and this was setup by the database creator.

Here is what I am trying to do...I need to display some related Part fields as a reference on the Order form. There needs to be a review of this info. I also need to save some fields from the PART table into the ORDERS table because they are time sensitive. We need to know what the status was at time of ORDER entry. How do I save related table fields from the PART record into the ORDERS record?

So what event do I put this code into if asking to save a record and allowing an undo if false? Are there any good examples of this?

There is a query setup for the ORDER FORM with a relation from the PART table to the ORDERS table. All fields on the form all pulled by this query. Don't know if that is the correct way to display and save the related info or not.

[Missinglinq]

You're describing a One (Order) to Many (Part) relationship, here, and your statement "In this case, there is no subform needed" is simply incorrect, as you've told by a number of experts on another forum. I don't believe you'll ever solve this, satisfactorily, until you accept this fact!

As for the changing cost of parts, over time, that will have to be taken into account, but you could do that either with a Date Field and a Parts/Cost Table or a Cost Field, saved in the Table the Subform is based on.

Linq ;0)>

[epb]

Hello Linq...

I understand what you are saying but I do not think you are understanding what is being done. I don't need a subform for the order entry. You could say there are MANY order lines and ONE Part but during the order line entry it is one order line being entered and only one part being looked at. ONE part and ONE order line.

At the time of this one order line being entered what was the revision level of a particular Part process and what was the cost, etc.

The database creator used DLOOKUP to pull this information and put it into a textbox control. Many of these controls were invisible and used later to save the information into the ORDERS form. I have used a combobox with multiple columns to pull the related part information to make it visible. I am just wanting to know if there is another way to pull the table information from the Part record and save it to the Orders record? How would you do this if you had no control on the form? I ask becasue I want to know how it is done?


This is really what I am trying to do:

...a Cost Field, saved in the Table the Subform is based on.

How would you save the CostField in the table of the subform? I think this would be the answer. The ORDERS table would be the subform in the PARTS From.

Also, I was wondering if you could answer the question I asked about the right Event to ask about saving the record:

So what event do I put this code into if asking to save a record and allowing an undo if false? Are there any good examples of this?

The above saving of field data is now working. I just would ask that you would explain what event the code to save should go into.

Please keep in mind that I am taking something that someone else created that has many problems and I am trying to properly fix things. I have to do this a step at a time.

[epb]

And lastly, the DoCmd.Save command does not Save a Record! It saves Design Changes that have been made to an Object, in this case, a Form! To Save a Record you'd use either

DoCmd.RunCommand acCmdSaveRecord

or

If Me.Dirty Then Me.Dirty = False

But both of those are unneeded in the Form_BeforeUpdate event (saving the Record is what is happening, when you get to the Form_BeforeUpdate event, and will, in fact, in this event, pop an error!

So what you are saying is...I need to leave out the DoCmd.RunCommand acCmdSaveRecord under the IF THEN and Keep the DoCmd.RunCommand acCmdUndo under the ELSE Statement? Because the record is being saved at the time.