Update record

  • Thread starter Thread starter Marge
  • Start date Start date
M

Marge

Hello,

Essentially I have a form that has textfields to add
records to a table that it's associated with - lets say
there is 10 fields. However, 5 of the fields appear
identically in another table, so id like it to replace
the other table with the new fields. For example:

Other table:

Fname Lname Age

But the main form shows

old Fname new Fname old Lname new Lname Old Age new Age

the old fields are populated automatically from the other
table, and when you can fill up the new information out
and add a record to the table that has those 6 records,
but id like it to also check the 'old Fname', 'old Lname'
etc from the 'other table' to change it to the new fields

I hope that makes sense.
Thanks

Marge
 
Before doing this I think you should check the design of your database. You
are not meant to store the same information more than once in a relational
database, so the fact that you need to change identical fields in two tables
means that you have a design problem. It would be much better to see if you
can come up with a better design rather than trying to find a work around
for a bad design - which is essentially what your question is asking for.
 
I'm still not convinced you need to store the information twice.

The second table sounds like it's there to record changes that have been
made to the first - an audit trail or something similar? Are you wanting to
add a new record to the second table when something changes in the first, or
do you want to change an existing record in the second table? If you want to
add a record, then I understand what you're doing, but if you want to change
a record (so the two tables always have the same number of records - which
is what it sounded like from your question) then I still think that's the
wrong thing to be doing.

Anyway, assuming that you want to add data to the second table after the
first has been updated, then what you need to do is run an append query in
the BeforeUpdate event of the form. The following code assumes that your
main table is called tblMain and the second table (the one with the extra
fields for the old values) is called tblArchive. I have also assumed that
there is an ID field called ID which is the primary key in tblMain and a
foreign key in tblArchive (if you don't have this then I don't see how it
can work). The tables have fields for FirstName and LastName (+ OldFirstName
and OldLastName in tblArchive) and these are bound to controls on the form
called txtFirstName and txtLastName

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strNewFirstName As String
Dim strNewLastName As String
Dim strSQL As String

strNewFirstName = Me.txtFirstName
strNewLastName = Me.txtLastName

strSQL = "INSERT INTO tblArchive ( ID, FirstName, LastName, OldFirstName,
OldLastName ) "
strSQL = strSQL & "SELECT ID, '" & strNewFirstName & "' AS NewFirstName, '"
& strNewLastName & "' AS NewLastName , FirstName, LastName "
strSQL = strSQL & "FROM tblMain "
strSQL = strSQL & "WHERE ID=" & Me.ID & ";"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

End Sub

Hope this helps

Andrew
 
Hi,

I almost got it working, but I already see a problem.
After the form is closed the changes are made, how can i
get it so that there are 2 buttons "Submit and Close",
and "Close" <-- without saving changes?
Thanks

Marge
 
Hi,

I almost got it working, but I already see a problem.
After the form is closed the changes are made, how can i
get it so that there are 2 buttons "Submit and Close",
and "Close" <-- without saving changes?
Thanks

Marge
 
OK this is what Ive got , and its not working great...

Dim strPrimaryKey As String
Dim strProjectNo As String
Dim strCompany As String
Dim strProjectTitle As String
Dim strsite As String
Dim strCDNo As String
Dim strDateArchived As String
Dim struser As String
Dim strSQL As String

strPrimaryKey = Me.[Primary Key]
strProjectNo = Me.[Project No]
strCompany = Me.[Company]
strProjectTitle = Me.[Project Title]
strsite = Me.Site
strCDNo = Me.[CD No]
struser = fOSUserName
strDateArchived = Me.[Date Archived]


strSQL = "INSERT INTO CAD_Edits (ID, ProjectNo, Company,
ProjectTitle, Site, CDNo, DateArchived, oldProjectNo,
oldCompany, oldProjectTitle, oldSite, oldCDNo,
oldDateArchived ) "
strSQL = strSQL & "SELECT ID, '" & strPrimaryKey & "' AS
newPrimaryKey, '" & strProjectNo & "' AS newProjectNo, '"
& strCompany & "' AS newCompany, '" & strProjectTitle
& "' AS newProjectTitle, '" & strsite & "' AS newSITE, '"
& strCDNo & "' AS newCDNo, '" & strDateArchived & "' AS
newDateArchived, [Primary Key], [Project No], Company,
[Project Title], [Site], [CD No], [Date Archived] "
strSQL = strSQL & "FROM CAD_ArchivedProjects "
strSQL = strSQL & "WHERE ID=" & Me.[Primary Key] & ";"

MsgBox strSQL

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True


--------

Error saying that the amount of entries does not match...
something like that, see any errors?

Thanks
Marge
 
Marge,

You will either have to use an unbound form, or will have to find a way of
stopping the record from being saved except when your "Submit and Close"
button is clicked. I think the unbound form would probably be the best way
to go, but it will require a fair amount of VBA code.

You'll need to delete the ControlSource property of the form and its
controls, and also turn off navigation buttons. You can add a combo box that
lists your records and this can be used for navigation. If you've already
got a bound form that almost works, then I'd make a backup of the original
form before you do this!

Since the form is unbound your close button will only need to close the form
(DoCmd.Close), and it won't matter if the user closes the form in any other
way, so you can keep the "X" etc.

In the form open event you will need to open a recordset object that is
based on your first table. You can then add some code to the after update
event of the new combo box to find the selected record in the recordset, and
then populate the form controls with the appropriate fields from the
selected record.

Your submit and close button will need to run the append query - assuming
this is what you wanted - that I already described. In addition it will need
code to update the first table. You can use the edit and update methods of
your recordset object to do this.

I think it would be too much for me to try and write all the code for you -
hopefully this has given you enough to work with.

Good luck.

Andrew
 
Back
Top