Add record in subform

[mattc66 via AccessMonster.com]

I have a button that adds data from a subform to another form.

If there is already data in the subform I want the new data to be added to
the record. Below is what I had tried and it's not working. Can anyone help
me with the syntax.


DoCmd.GoToRecord , Forms![frmMain]![sfrmSubform], acNewRec
Forms![frmMain]![sfrmSubform].Form![FldName] = me.txtData1
Forms![frmMain]![sfrmSubform].Form![FldName2] = Me.txtData2

Matt

 

[Allen Browne]

Add the data to the RecordsetClone of the other subform:

Dim rs As DAO.Recordset
Set rs = Forms![frmMain]![sfrmSubform].Form.RecordsetClone

rs.AddNew
rs!FldName = Me.txtData1
rs!FldName2 = Me.txtData2
rs.Update

Forms![frmMain]![sfrmSubform].Form.Bookmark = rs.LastModified
Set rs = Nothing

 

[mattc66 via AccessMonster.com]

The below is coming back as not a valid bookmark.

Any ideas?

Forms![frmMain]![sfrmSubform].Form.Bookmark = rs.LastModified

Add the data to the RecordsetClone of the other subform:

Dim rs As DAO.Recordset
Set rs = Forms![frmMain]![sfrmSubform].Form.RecordsetClone

rs.AddNew
rs!FldName = Me.txtData1
rs!FldName2 = Me.txtData2
rs.Update

Forms![frmMain]![sfrmSubform].Form.Bookmark = rs.LastModified
Set rs = Nothing

I have a button that adds data from a subform to another form.

[quoted text clipped - 6 lines]

Forms![frmMain]![sfrmSubform].Form![FldName] = me.txtData1
Forms![frmMain]![sfrmSubform].Form![FldName2] = Me.txtData2

 

[mattc66 via AccessMonster.com]

Its failing on the rs.AddNew saying there is no related record in the main
table. Any ideas?

Add the data to the RecordsetClone of the other subform:

Dim rs As DAO.Recordset
Set rs = Forms![frmMain]![sfrmSubform].Form.RecordsetClone

rs.AddNew
rs!FldName = Me.txtData1
rs!FldName2 = Me.txtData2
rs.Update

Forms![frmMain]![sfrmSubform].Form.Bookmark = rs.LastModified
Set rs = Nothing

I have a button that adds data from a subform to another form.

[quoted text clipped - 6 lines]

Forms![frmMain]![sfrmSubform].Form![FldName] = me.txtData1
Forms![frmMain]![sfrmSubform].Form![FldName2] = Me.txtData2

 

[Allen Browne]

Matt, I made an assumption that sfrmSubform is bound to a table (or to an
updatable query.)

Is it bound?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Its failing on the rs.AddNew saying there is no related record in the main
table. Any ideas?

Add the data to the RecordsetClone of the other subform:

Dim rs As DAO.Recordset
Set rs = Forms![frmMain]![sfrmSubform].Form.RecordsetClone

rs.AddNew
rs!FldName = Me.txtData1
rs!FldName2 = Me.txtData2
rs.Update

Forms![frmMain]![sfrmSubform].Form.Bookmark = rs.LastModified
Set rs = Nothing

I have a button that adds data from a subform to another form.

[quoted text clipped - 6 lines]

Forms![frmMain]![sfrmSubform].Form![FldName] = me.txtData1
Forms![frmMain]![sfrmSubform].Form![FldName2] = Me.txtData2

 

[mattc66 via AccessMonster.com]

Yes it is bound to the main form.

Matt, I made an assumption that sfrmSubform is bound to a table (or to an
updatable query.)

Is it bound?

Its failing on the rs.AddNew saying there is no related record in the main
table. Any ideas?

[quoted text clipped - 17 lines]

Forms![frmMain]![sfrmSubform].Form![FldName] = me.txtData1
Forms![frmMain]![sfrmSubform].Form![FldName2] = Me.txtData2

 

[mattc66 via AccessMonster.com]

My command button is in a form that is not bound to the main form or subform.

Matt, I made an assumption that sfrmSubform is bound to a table (or to an
updatable query.)

Is it bound?

Its failing on the rs.AddNew saying there is no related record in the main
table. Any ideas?

[quoted text clipped - 17 lines]

Forms![frmMain]![sfrmSubform].Form![FldName] = me.txtData1
Forms![frmMain]![sfrmSubform].Form![FldName2] = Me.txtData2

 

[Allen Browne]

Open the subform in design view.
In the Properties box, look at the Record Source property.
(First property on the Data tab, looking at the form's properties.)

If the propert is blank, the form is unbound, and Access has no idea where
to save the record to. If it has a valid entry (table name, query name, or
SQL statement), it should work.

The location of the button doesn't matter.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yes it is bound to the main form.

Matt, I made an assumption that sfrmSubform is bound to a table (or to an
updatable query.)

Is it bound?

Its failing on the rs.AddNew saying there is no related record in the
main
table. Any ideas?

[quoted text clipped - 17 lines]

Forms![frmMain]![sfrmSubform].Form![FldName] = me.txtData1
Forms![frmMain]![sfrmSubform].Form![FldName2] = Me.txtData2

 

[mattc66 via AccessMonster.com]

The subform has a query in the record source.

Open the subform in design view.
In the Properties box, look at the Record Source property.
(First property on the Data tab, looking at the form's properties.)

If the propert is blank, the form is unbound, and Access has no idea where
to save the record to. If it has a valid entry (table name, query name, or
SQL statement), it should work.

The location of the button doesn't matter.

Yes it is bound to the main form.

[quoted text clipped - 9 lines]

Forms![frmMain]![sfrmSubform].Form![FldName] = me.txtData1
Forms![frmMain]![sfrmSubform].Form![FldName2] = Me.txtData2

 

[Allen Browne]

Good. Now, is the query updatable?
If you open the query itself are you able to add a new record?

If you can't you won't be able to add a new record to the form based on that
query.

If you can, you will be able to if the form's properties are set correctly.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The subform has a query in the record source.

Open the subform in design view.
In the Properties box, look at the Record Source property.
(First property on the Data tab, looking at the form's properties.)

If the propert is blank, the form is unbound, and Access has no idea where
to save the record to. If it has a valid entry (table name, query name, or
SQL statement), it should work.

The location of the button doesn't matter.

Yes it is bound to the main form.

[quoted text clipped - 9 lines]

Forms![frmMain]![sfrmSubform].Form![FldName] = me.txtData1
Forms![frmMain]![sfrmSubform].Form![FldName2] = Me.txtData2

 

[mattc66 via AccessMonster.com]

Yes its updatable and I can manually add this. It must be my syntax some
where in the code.

Good. Now, is the query updatable?
If you open the query itself are you able to add a new record?

If you can't you won't be able to add a new record to the form based on that
query.

If you can, you will be able to if the form's properties are set correctly.

The subform has a query in the record source.

[quoted text clipped - 13 lines]

Forms![frmMain]![sfrmSubform].Form![FldName] = me.txtData1
Forms![frmMain]![sfrmSubform].Form![FldName2] = Me.txtData2

 

[mattc66 via AccessMonster.com]

Would it mater if there is already some data in the subform we are adding to?

Good. Now, is the query updatable?
If you open the query itself are you able to add a new record?

If you can't you won't be able to add a new record to the form based on that
query.

If you can, you will be able to if the form's properties are set correctly.

The subform has a query in the record source.

[quoted text clipped - 13 lines]

Forms![frmMain]![sfrmSubform].Form![FldName] = me.txtData1
Forms![frmMain]![sfrmSubform].Form![FldName2] = Me.txtData2

 

[Allen Browne]

Not at all.

Matt, in the VBA window, place the cursor in AddNew, and press F1 to see how
it works

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Would it mater if there is already some data in the subform we are adding
to?

Good. Now, is the query updatable?
If you open the query itself are you able to add a new record?

If you can't you won't be able to add a new record to the form based on
that
query.

If you can, you will be able to if the form's properties are set
correctly.

The subform has a query in the record source.

[quoted text clipped - 13 lines]

Forms![frmMain]![sfrmSubform].Form![FldName] = me.txtData1
Forms![frmMain]![sfrmSubform].Form![FldName2] = Me.txtData2

 

[mattc66 via AccessMonster.com]

Hi Allen,
Thanks for all your help. Everything appears to be correct. I am at a loss..
See below Code, do you see anything that may be wrong.

The message I get is: RUN TIME ERROR 3201
You Cannot add or Change a record because a related record is required in
table. "tblOrder"

The tblOrder is the source for the frmOrder. I can add to the subform
sfrmOrderDetails just fine manually without a problem.

Private Sub cmdPostPrice_Click()

Dim rs As DAO.Recordset

Set rs = Forms![frmOrder]![sfrmOrderDetails].Form.RecordsetClone
rs.AddNew
rs![Item] = Me.Item
rs![Description] = Me.Description
rs.Update
Forms![frmOrder]![sfrmOrderDetails].Form.Bookmark = rs.LastModified
Set rs = Nothing

End Sub

Not at all.

Matt, in the VBA window, place the cursor in AddNew, and press F1 to see how
it works

Would it mater if there is already some data in the subform we are adding
to?

[quoted text clipped - 14 lines]

Forms![frmMain]![sfrmSubform].Form![FldName] = me.txtData1
Forms![frmMain]![sfrmSubform].Form![FldName2] = Me.txtData2

 

[Josh]

Sounds like you don't have a related record in tblOrder. If
referential integrity is enforced, then you can't add a "many" record
if there is not a "one" record already.

HTH

Hi Allen,
Thanks for all your help. Everything appears to be correct. I am at a loss..
See below Code, do you see anything that may be wrong.

The message I get is: RUN TIME ERROR 3201
You Cannot add or Change a record because a related record is required in
table. "tblOrder"

The tblOrder is the source for the frmOrder. I can add to the subform
sfrmOrderDetails just fine manually without a problem.

Private Sub cmdPostPrice_Click()

Dim rs As DAO.Recordset

Set rs = Forms![frmOrder]![sfrmOrderDetails].Form.RecordsetClone
rs.AddNew
rs![Item] = Me.Item
rs![Description] = Me.Description
rs.Update
Forms![frmOrder]![sfrmOrderDetails].Form.Bookmark = rs.LastModified
Set rs = Nothing

End Sub

Not at all.

Matt, in the VBA window, place the cursor in AddNew, and press F1 to see how
it works

Would it mater if there is already some data in the subform we are adding
to?

[quoted text clipped - 14 lines]

Forms![frmMain]![sfrmSubform].Form![FldName] = me.txtData1
Forms![frmMain]![sfrmSubform].Form![FldName2] = Me.txtData2

 

[mattc66 via AccessMonster.com]

Problem solved by Allen Browne.

I needed to add:

rs!OrderID = Me.QuoteID
I had forgotten to assign the foreign key value (i.e. the OrderID.)

Access assigns this for you when you type a new record into the form, but
you must assign it yourself when adding a record to the RecordsetClone.

Thanks
Matt

Sounds like you don't have a related record in tblOrder. If
referential integrity is enforced, then you can't add a "many" record
if there is not a "one" record already.

HTH

Hi Allen,
Thanks for all your help. Everything appears to be correct. I am at a loss..

[quoted text clipped - 33 lines]

Forms![frmMain]![sfrmSubform].Form![FldName] = me.txtData1
Forms![frmMain]![sfrmSubform].Form![FldName2] = Me.txtData2