updating record

[kingsgambit]

I have build a personal database, where the user can enter a persons details, and holidays taken.
The database is based on a Pay No that the user enters for each person. If they change that number all the holiday records will be lost. Is there a way to update the holidays records with the new pay no

 

[WayneRyan]

kingsgambit,

You can use the BeforeUpdate event like this:

' ************************************
Dim dbs As Database
Dim sql As String

Set dbs = CurrentDb
sql = "Update YourTable " & _
"Set PayNo = " & Me.PayNo & " " & _
"Where PayNo = " Me.PayNldValue & ";"
dbs.execute(sql)
' ************************************

I haven't tried it but it should work.

I called your field PayNo because I prefer not to
use embedded spaces or symbols in variables.

hth,
Wayne

 

[Pat Hartman]

Keys should not be changeable. If you want to be able to change PayNo, then you should NOT use it as your primary key especially since you have related tables. Use an autonumber primary key and use that in the relationships. You can define a unique index for PayNo so that it cannot be duplicated and continue to allow the user to use that field as the one to lookup records or print on various reports and forms.

If you must keep PayNo as the primary key (and I strongly recommend against it), you change your relationships to enforce referential integrity (which they should already bee set to) and then you will be allowed to check cascade update and cascade delete. With those properties checked, if you change the value of PayNo, Access will automatically propagate the change to any related tables and if you delete the primary PayNo record, Access will automatically delete any related records.