Registers

[Rolando São Marcos]

I have the following table:

PartnerType (RefIDNPartner LONG, RefIDType LONG, BeginDate DATETIME, EndDate
DATETIME, PRIMARY KEY (RefIDNPartner, RefIDType),
FOREIGN KEY (RefIDNPartner) REFERENCES Partner(IDNPartner),
FOREIGN KEY (RefIDType) REFERENCES Type(IDType));

When i introduce a new register, i want the EndDate field of register1 has
the same date that the BeginDate filed of register2 that i am introducing.

I wope i make myself clear.

Thanks.

 

[Douglas J Steele]

I'm assuming that this is your repost, based on the advice I gave you when
you asked this question yesterday, even though you didn't add any details at
all.

I'm further assuming that, since you trimmed the SQL Server groups from this
post, that you are using Access, not SQL Server.

The only way to do something like this in Access is through VBA code in the
form that's doing the updates. There is no way to do this if you're updating
the table directly: you must be updating the table through a form.

In your form's BeforeUpdate event, put code that updates the previous
record. I'm assuming that you want to change the EndDate for whatever row in
the table has the same RefIDNPartner value as the row you're about to
insert, and a Null EndDate. Assuming you've got the RefIDNPartner stored in
a variable lngRefIDNPartner, and the BeginDate you're about to use in
variable dtmBeginDate, your SQL would look something like:

strSQL = "UPDATE PartnerType " & _
"SET EndDate = " & Format(dtmBeginDate, "\#mm\/dd\/yyyy\# ") & _
"WHERE RefIDNPartner = " & lngRefIDNPartner & " " & _
"AND EndDate IS NULL"