Macro to update another table from a form

[Rodw]

Hi all,

I am sure this is a easy question for most of you, but I am struggling to find the right way of going about this.



I have a table which is linked to a form. When a user updates a combox I would like to update this change in another table.
In my main table linked to my form i would like to copy the following fields;
Products.ID;Products.Name and update this to a table called Detail.ID;Detail.Name;Detail.Date (this is today's date).

I am not sure how to write the sql or macro to 'afterupdate' function.

If this is to vague, please let me know.

Thanks
Rod

[ItsMe]

It is best to just place the ID or Primary Key value in another table's Foreign Key field. Including both the ID and Name fields from the same record is duplicating data and is not a Normalized data structure. Also, avoid using words that are reserved by Access in names of objects and fields. Date is a reserved word.

Your combo has an After Update event you can place VBA code in. You could use DAO to update a record in another table or SQL or a combination of both. Here is an untested example of DAO adding a record to a table using data from the form's current record. Perhaps you would want to search for a record in the other table and Update a matched record based on the combo selection. I am not real clear on what you are trying to do.

Dim rs as DAO.Recordset
set rs = Currentdb.Openrecordset("Detail", dbopendynaset)

If rs.eof = true then
msgbox "There was a problem. No records were found. Now Exiting."
set rs = nothing
exit sub
end if

rs.addnew
rs![ID] = Me.ID.value
rs![Date] = Date()
rs.Update

rs.close
set rs = nothing

[ranman256]

The query would be called like: quUpdProdFromForm
(update or append)

UPDATE Detail Set [date]= " & Date() & " [Name]='" & forms!frmMain!txtName & " where [Products.ID] = " & forms!frmMain!cboBox


The form button ''afterupdate'' action would have the macro name you made...(i.e) mUpdProducts

The macro would have

docmd.openQuery
quUpdProdFromForm

[Rodw]

Thank you both for your reply. I have tried the DOA method and i get the error "msgbox "There was a problem. No records were found. Now Exiting."". I apologies if my original question was not clear enough.
I'll try again.

On a form I have an ID which is a primary key plus many other fields from a table called products. I would like to populate another table with this ID if a certain record is added or amended, plus add the date of the change.

Therefore Products.Camp in the form can be added or updated.

When this is added or updated then this needs to recorded in a table called Movement. Therefore add the products.ID and Products.camp to Movement.ID and Movement.camp with a time stamp of today in a field called movement.changed.

As I have fields in the form after this field I would like this to run in the background via the macro.

Thank you once again for the assistance.

Rod

It is best to just place the ID or Primary Key value in another table's Foreign Key field. Including both the ID and Name fields from the same record is duplicating data and is not a Normalized data structure. Also, avoid using words that are reserved by Access in names of objects and fields. Date is a reserved word.

Your combo has an After Update event you can place VBA code in. You could use DAO to update a record in another table or SQL or a combination of both. Here is an untested example of DAO adding a record to a table using data from the form's current record. Perhaps you would want to search for a record in the other table and Update a matched record based on the combo selection. I am not real clear on what you are trying to do.

Dim rs as DAO.Recordset
set rs = Currentdb.Openrecordset("Detail", dbopendynaset)

If rs.eof = true then
msgbox "There was a problem. No records were found. Now Exiting."
set rs = nothing
exit sub
end if

rs.addnew
rs![ID] = Me.ID.value
rs![Date] = Date()
rs.Update

rs.close
set rs = nothing

[ItsMe]

Can you post the code you used?

The message you got was an error trap. The VBA you wrote checked for existing records in the table and did not find any.

This validation is not necessary when appending records but is good to have when updating records.

[Rodw]

Here is the code I used.

Option Compare Database


Private Sub Camp_AfterUpdate()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Movement", dbOpenDynaset)


If rs.BOF = True Then
MsgBox "There was a problem. No records were found. Now Exiting."
Set rs = Nothing
Exit Sub
End If


rs.AddNew
rs![ID] = Me.ID.Value
rs![Date] = Date
rs.Update


rs.Close
Set rs = Nothing
End Sub

Thanks
Rod

[ItsMe]

The following will create a new record in the table named "Movement". It will populate the field named ID with the form's current ID field value and the field named Date with the current date. Date() is the current date and Now() is the current date and time.

 Dim rs As DAO.Recordset
 Set rs = CurrentDb.OpenRecordset("Movement", dbOpenDynaset)

rs.AddNew
 rs![ID] = Me.ID.Value
 rs![Date] = Date
 rs.Update

rs.Close
 Set rs = Nothing
 End Sub

I removed the error trapping because you do not need it for an append process. I placed it in there because I thought you might want to change things around and UPDATE some records that are relative to the value of your combo.

Let us know how it goes or if we are not understanding what you are trying to accomplish.

[Rodw]

Itsme - that works a dream and thank you very much for all of your time and input.
Regards
Rod