Setting records in subform by pressing button in masterform

[haukalid]

Hi,

I have an orderDB base on the NorthwindDB. In the orders form I would like
to have a button witch creates a new post in the main form, and also set some
of the records in the subform (orderdetails --> itemID, quantity, price etc).

In thw Onclick-event in the new-post-button I've tried to use :
With Me.orderdetails_subform.Form.RecordsetClone
.AddNew
!itemID = "39999"
!quantity = "1"
!price = "185"
End With

But I can't get it work.

I'm a newbee in vb-coding...

 

[Marshall Barton]

I have an orderDB base on the NorthwindDB. In the orders form I would like
to have a button witch creates a new post in the main form, and also set some
of the records in the subform (orderdetails --> itemID, quantity, price etc).

In thw Onclick-event in the new-post-button I've tried to use :
With Me.orderdetails_subform.Form.RecordsetClone
.AddNew
!itemID = "39999"
!quantity = "1"
!price = "185"
End With

You need to use:
.Update
to save the data.

 

[Dale Fye]

I had no idea you could add records to a recordsetclone. When you do so, do
they show up in the original recordset?

Dale

 

[haukalid]

No, the records doesn't show up in the orginal recordset. I get an
errormessage saying that there are related records in the order table that
are needed.

In my subform, witch are viewed in datasheet-format, I have not included
controls for the orderID and the orderdetailsID from the orderdetailstable. I
think I'll have to set theese values in the code before adding new records.


--
---------------------------------
RM Haukalid, Norway
---------------------------------


Dale Fye skrev:

I had no idea you could add records to a recordsetclone. When you do so, do
they show up in the original recordset?

Dale

 

[Marshall Barton]

I had no idea you could add records to a recordsetclone. When you do so, do
they show up in the original recordset?

Yes, as long as the new record has no vaildation or
relational integrity violations,

I've used this kind of thing lots of times for a form button
to duplicate the current record.

 

[Marshall Barton]

No, the records doesn't show up in the orginal recordset. I get an
errormessage saying that there are related records in the order table that
are needed.

In my subform, witch are viewed in datasheet-format, I have not included
controls for the orderID and the orderdetailsID from the orderdetailstable. I
think I'll have to set theese values in the code before adding new records.

Well, if the record can not be saved because of a validation
or relational integrity violation, it can not possible show
up. Your analysis is mostly correct, but it is not
absolutely necessary to have a text box on the subform
that's bound to the foreign key field. However, you must
set the foreign key field in the new record to the primary
key field in the main form's record. You could add it this
way:
!OrderID = Parent.OrderID

If the order detail table's primary key (OrderDetailsID) is
an autonumber, then you can not set it's value in the
recordset. Otherwise, yes, you do need to set it.

Note that it is essential that the subform's record source
includes both fields.

 

[krissco]

No, the records doesn't show up in the orginal recordset. I get an
errormessage saying that there are related records in the order table that
are needed.

In my subform, witch are viewed in datasheet-format, I have not included
controls for the orderID and the orderdetailsID from the orderdetailstable. I
think I'll have to set theese values in the code before adding new records.

It may be necessary to explicitly refresh to the master table prior to
inserting the record in your subform (in the event of a foreign key
violation).

Try:

me.refresh
With Me.orderdetails_subform.Form . . .
.. . .
end with

-Kris

 

[haukalid]

I had to add
!orderID = Me.orderID

Thank you all for your help!