Update a table based on current record

[Guest]

I have two tables one that stores information regaring food, portion, cal and
fat(called tblpantry). The other table stores Journaling of the
day(tbljournaling. I want to be able to select an item in the form that I
created for journaling and have that information then stored in the
journaling table. Is there a way to copy a record that is selected to a
different table? If so how?

 

[Pieter Wijnen]

Sub btnUpdJournal_click()
Dim Db AS DAO.Database
Dim RsAdd AS DAO.Recordset
Dim RsC AS DAO.Recordset
Dim Fld AS DAO.Field

Set Db = Access.CurrentDb
Set RsAdd = Db.OpenRecordset("JOURNAL",DAO.DbOpenDynaset, DAO.dbAppendOnly)
' Open an updatable/appendable Cursor against the Table
RsAdd.AddNew ' Add new entry
Set RsC = Me.RecordsetClone ' copy of form's data
RsC.BookMark = Me.Bookmark ' Move to the selected record

For Each Fld In RsC.Fields ' Loop through the Fields, Assume all fields in
"Parent" in "Child" Else use If's
RsAdd.Fields(Fld.Name) = Fld.Value
Next 'Fld

RsAdd.Update
RsAdd.Close : Set RsAdd=Nothing
Set RsC = Nothing
Set Db = Nothing ' Close all objects
End Sub

HTH

Pieter

 

[Pieter Wijnen]

Sub btnUpdJournal_click()
Dim Db AS DAO.Database
Dim RsAdd AS DAO.Recordset
Dim RsC AS DAO.Recordset
Dim Fld AS DAO.Field

Set Db = Access.CurrentDb
Set RsAdd = Db.OpenRecordset("JOURNAL",DAO.DbOpenDynaset, DAO.dbAppendOnly)
' Open an updatable/appendable Cursor against the Table
RsAdd.AddNew ' Add new entry
Set RsC = Me.RecordsetClone ' copy of form's data
RsC.BookMark = Me.Bookmark ' Move to the selected record

For Each Fld In RsC.Fields ' Loop through the Fields, Assume all fields in
"Parent" in "Child" Else use If's
RsAdd.Fields(Fld.Name) = Fld.Value
Next 'Fld

RsAdd.Update
RsAdd.Close : Set RsAdd=Nothing
Set RsC = Nothing
Set Db = Nothing ' Close all objects
End Sub

HTH

Pieter

 

[Guest]

So does the button for this need to be on the form for the journal table or
on the subform for the pantry table where the record is that will be copied
to the journal table???

Thanks

 

[Guest]

So does the button for this need to be on the form for the journal table or
on the subform for the pantry table where the record is that will be copied
to the journal table???

Thanks

 

[Guest]

Hi CAT -- Before you go further you should note it appears you may have a
design problem. A properly normalized database should not have the exact
information stored in two tables.

What is the relationship between tblpantry and tbljournal? What exactly are
you trying to accomplish?

 

[Guest]

Hi CAT -- Before you go further you should note it appears you may have a
design problem. A properly normalized database should not have the exact
information stored in two tables.

What is the relationship between tblpantry and tbljournal? What exactly are
you trying to accomplish?