How do you select a prior record based on certain selection criter

[gg]

I have 3 records with the following fields: ContractNoand AgreementType.
The first record is
contractNo=1,AgreementType=OrigAgreemt1,LastUpdate=9/1/05 10am
The 2nd record is contractNo=1, AgreementType=Amendmt1,LastUpdate=11/2/05
10am.
The 3rd record is contractNo=1, AgreementType=Amendmt2, LastUpdate=11/2/05
2:00pm.

I have a form from which a user will search and select a specific
contract_no and agreement type and its related fields. In this example, on
the contract status field, the user will update it from "Pending" to "Active"
for record #3. However, the previous record #2 needs to have the status
changed from "Active" to "Closed."
I'm trying to systematically update record#2 (previous record) to "C"
(closed status) once a new record (for the same contract#) is generated. I
can use the LastUpdate field to determine what was the most current record
for that contract#.

If I am on record#3 on the form, how do I select contract#2 and change the
status w/o bringing record #2 up?
Thank you!

 

[Marshall Barton]

I have 3 records with the following fields: ContractNoand AgreementType.
The first record is
contractNo=1,AgreementType=OrigAgreemt1,LastUpdate=9/1/05 10am
The 2nd record is contractNo=1, AgreementType=Amendmt1,LastUpdate=11/2/05
10am.
The 3rd record is contractNo=1, AgreementType=Amendmt2, LastUpdate=11/2/05
2:00pm.

I have a form from which a user will search and select a specific
contract_no and agreement type and its related fields. In this example, on
the contract status field, the user will update it from "Pending" to "Active"
for record #3. However, the previous record #2 needs to have the status
changed from "Active" to "Closed."
I'm trying to systematically update record#2 (previous record) to "C"
(closed status) once a new record (for the same contract#) is generated. I
can use the LastUpdate field to determine what was the most current record
for that contract#.

If I am on record#3 on the form, how do I select contract#2 and change the
status w/o bringing record #2 up?

Use the form's RecordsetClone.

I'm not at all sure what else you have going on in the form,
such as its sorting. If the records are sorted by the last
Update field and if that field has unique values, you might
be able to locate the "previous" record with:

With Me.RecordsetClone
.BookMark = Me.Bookmark
.MovePrevious
If Not .BOF Then
!Status = "C"
Emd With

 

[gg]

Use the form's RecordsetClone.

I'm not at all sure what else you have going on in the form,
such as its sorting. If the records are sorted by the last
Update field and if that field has unique values, you might
be able to locate the "previous" record with:

With Me.RecordsetClone
.BookMark = Me.Bookmark
.MovePrevious
If Not .BOF Then
!Contract_Status = "C"
End With

Hi Marshall,

I tried using the code above, however, for the selected contract_no
(i.e.contract_no=2 and doc_type=OA), contract_status still does not update to
"S". The record that I am at is the current record. Do I need to limit the
previous record to only those contract_no=2?

Thank you!

 

[Marshall Barton]

I tried using the code above, however, for the selected contract_no
(i.e.contract_no=2 and doc_type=OA), contract_status still does not update to
"S". The record that I am at is the current record. Do I need to limit the
previous record to only those contract_no=2?

Like I said, I don't know what else your form is doing. If
your definition of "previous" is for a single contract
number, then it seems logical to either limit the form to
display only records for the specified contract (as I
thought before), or to search for the previous record with
the same contract number as the current record.

It sounds like you want to display all the contracts and
just search for the appropriate(?) "previous" record, which
you have not explained.

Unfortunately, you have either changed terminology or
introduced additional fields into the problem. So I don't
know the details of what you have or how to do what you
want.

Is doc_type different from AgreementType?

Is "OC" the same as "Amendmt2"??

Before, you said you want to set the Status = C (for
Closed), now you want it to be S???

I now think(?) you will might need to use the FindPrevious
method inatead of the MovePrevious method:

With Me.RecordsetClone
.BookMark = Me.Bookmark
.FindPrevious "contract_no = " & Me.contract_no _
& " And doc_type=OA"
If Not .NoMatch Then
!Contract_Status = "C"
End If
End With

You will have to analyze that and modify it to exactly meet
your specifications. And don't forget that the records must
be sorted ascending by whatever determines "previous" (the
update field???).

 

[gg]

Like I said, I don't know what else your form is doing. If
your definition of "previous" is for a single contract
number, then it seems logical to either limit the form to
display only records for the specified contract (as I
thought before), or to search for the previous record with
the same contract number as the current record.

It sounds like you want to display all the contracts and
just search for the appropriate(?) "previous" record, which
you have not explained.

Unfortunately, you have either changed terminology or
introduced additional fields into the problem. So I don't
know the details of what you have or how to do what you
want.

Is doc_type different from AgreementType?

Is "OC" the same as "Amendmt2"??

Before, you said you want to set the Status = C (for
Closed), now you want it to be S???

I now think(?) you will might need to use the FindPrevious
method inatead of the MovePrevious method:

With Me.RecordsetClone
.BookMark = Me.Bookmark
.FindPrevious "contract_no = " & Me.contract_no _
& " And doc_type=OA"
If Not .NoMatch Then
!Contract_Status = "C"
End If
End With

You will have to analyze that and modify it to exactly meet
your specifications. And don't forget that the records must
be sorted ascending by whatever determines "previous" (the
update field???).

Thank you, Marshall! I had to tweak it a little but got it working.
Appreciate all of your help over the past months.