Record not saving properly

[ponneri]

Hi.

I have a bound form in Access 2007 that opens up another bound form during data entry, after filling in a few fields. This new form is not related or linked (but borrows one field's value from the main form - as i have set it's control source property) and has some more fields that are filled in.

And when the user clicks the "save" command button on form, all the fields on the second form are saved into it's underlying table except the field that got it's value from the first form. Why ?

The code in my "save" button is simple.

docmd.save
msgbox "Record saved successfully", vbokonly
docmd.gotorecord , , acnewrec

Can somebody correct me please ?

 

[pbaldy]

How does that control get it's value? What is the control source of it?

 

[ponneri]

Thank you Paul.

I've set the control's source as

= forms![po_form]!po_number (i.e the first form's field value)

And it's working fine, in the sense - it shows in the second form but does not save to it's underlying table.

 

[arnelgp]

it will not save because it is not bound. to make it bound, put the field from the table as recordsource. on the load event of your second form:

private sub form_load()
me.control = forms![po_form]!po_number
end sub

 

[pbaldy]

In order to save, the control source would have to be the name of the field you want the data to save to. All you have there is a reference to another form, which will simply show whatever is there. You can change the control source to the field name, and place the value there programmatically:

DoCmd.OpenForm "SecondFormName"
Forms!SecondFormName.po_number = Me.po_number

 

[pbaldy]

I'll get out of the way. Must be too slow, or not qualified or something.

 

[ponneri]

Thank you all very much for the inputs.

I did try one of the options earlier, as suggested.

private sub form_load()
me.ponum = forms![po_form]!po_number
end sub

For this, I get an error at runtime saying, "You cannot assign a value to this ....."

Anyway, I'll try them all again and see.

 

[arnelgp]

you cannot assign because the the control has a control source, which is an expression. use the table's field name.

 

[The_Doc_Man]

After re-reading this a couple of times, I think I know what is wrong.

Let's say that the main form's recordsource is QueryA which contains FieldAX, and the sub-form's recordsource is QueryB which contains FieldBX, where the value you wanted to have saved in QueryB is kept in FieldBX but derived from FieldAX.

I'm going to assume that you cannot or do not want to establish relational integrity between FieldAX and FieldBX, and that for some reason, neither is eligible as a key/index field. Therefore, you cannot link the main and sub based on those two fields.

The reason you cannot save the value from FieldAX into FieldBX is because the .ControlSource on the form with QueryB as a .Recordsource HAS to be FieldBX in order to save a value to that field. Not [QueryA]![FieldAX] or a Forms("Parent").Recordsource![FieldAX], but simply [FieldBX] - which implies the sub-form's .Recordsource.

So.... the real problem is establishing the value of the control holding FieldBX before you try to do a Save. I think that your BETTER result might occur if you did something on the Sub-form's "Enter" or "Current" event to COPY the field of interest from the parent form to the sub-form's relevant control and then do your save based on having that sub-form's control point to a field in the sub-form's "natural" .recordsource that contains the value.