Look up record based on one criteria, populate fields, increment & re-populate

[Russell. via AccessMonster.com]

Folks,

Going around in circles, maybe trying to do this the wrong way?

I have an MS Access 2007 table which has tenants names in it. If there are
two tenants in one property they have the same contract number.

I am trying to create a macro that looks up a contract number and then
populates two text boxes with First Name, Last Name of Tenant 1 and then goes
to the next record and populates two text boxes with First Name, Last Name of
Tenant 2.

I don't seem to be able to look up the record, and I don't seem to be able to
go to the next record! Here is my Macro code:

GotoControl
[Contract]

FindRecord
"[Contract Number]" **This is a text box where I put in
the contract number**
Whole Field
All
No
All
Yes
Yes

SetValue
[Forms]![Macro Test Form]![FirstName1]
[First Name]

SetValue
[Forms]![Macro Test Form]![LastName1]
[Last Name]

NextRecord

SetValue
[Forms]![Macro Test Form]![FirstName2]
[First Name]

SetValue
[Forms]![Macro Test Form]![LastName2]
[Last Name]

I'd certainly appreciate any assistance anyone can offer me as to where I am
going wrong!

Thanks in advance!
Russell.

 

[KARL DEWEY]

This uses two queries. First query named Russel.

QRY Russel ---
SELECT Q.ContractNumber, Q.FirstName, Q.LastName, (SELECT COUNT(*) FROM
YourTableName Q1
WHERE Q1.[ContractNumber] = Q.[ContractNumber]
AND Q1.[FirstName] &Q1.[LastName] < Q.[FirstName] & Q.[LastName])+1
AS Rank
FROM YourTableName AS Q
ORDER BY Q.ContractNumber, Q.FirstName, Q.LastName;

SELECT YourTableName.ContractNumber,
First(IIf([Rank]=1,[YourTableName].[FirstName])) AS [1st Tenent First Name],
First(IIf([Rank]=1,[YourTableName].[LastName])) AS [1st Tenent Last Name],
Last(IIf([Rank]=2,[YourTableName_1].[FirstName])) AS [2nd Tenent First Name],
Last(IIf([Rank]=2,[YourTableName_1].[LastName])) AS [2nd Tenent Last Name]
FROM (Russel LEFT JOIN YourTableName ON (Russel.ContractNumber =
YourTableName.ContractNumber) AND (Russel.LastName = YourTableName.LastName)
AND (Russel.FirstName = YourTableName.FirstName)) LEFT JOIN YourTableName AS
YourTableName_1 ON (Russel.ContractNumber = YourTableName_1.ContractNumber)
AND (Russel.LastName = YourTableName_1.LastName) AND (Russel.FirstName =
YourTableName_1.FirstName)
GROUP BY YourTableName.ContractNumber
HAVING (((YourTableName.ContractNumber) Like "*"));

 

[Russell. via AccessMonster.com]

Karl,

Thank you - very generous of you to spend so much time on this.

I am going to work through this and try to make it work.

Thank you.
Russell.

This uses two queries. First query named Russel.

QRY Russel ---
SELECT Q.ContractNumber, Q.FirstName, Q.LastName, (SELECT COUNT(*) FROM
YourTableName Q1
WHERE Q1.[ContractNumber] = Q.[ContractNumber]
AND Q1.[FirstName] &Q1.[LastName] < Q.[FirstName] & Q.[LastName])+1
AS Rank
FROM YourTableName AS Q
ORDER BY Q.ContractNumber, Q.FirstName, Q.LastName;

SELECT YourTableName.ContractNumber,
First(IIf([Rank]=1,[YourTableName].[FirstName])) AS [1st Tenent First Name],
First(IIf([Rank]=1,[YourTableName].[LastName])) AS [1st Tenent Last Name],
Last(IIf([Rank]=2,[YourTableName_1].[FirstName])) AS [2nd Tenent First Name],
Last(IIf([Rank]=2,[YourTableName_1].[LastName])) AS [2nd Tenent Last Name]
FROM (Russel LEFT JOIN YourTableName ON (Russel.ContractNumber =
YourTableName.ContractNumber) AND (Russel.LastName = YourTableName.LastName)
AND (Russel.FirstName = YourTableName.FirstName)) LEFT JOIN YourTableName AS
YourTableName_1 ON (Russel.ContractNumber = YourTableName_1.ContractNumber)
AND (Russel.LastName = YourTableName_1.LastName) AND (Russel.FirstName =
YourTableName_1.FirstName)
GROUP BY YourTableName.ContractNumber
HAVING (((YourTableName.ContractNumber) Like "*"));

Folks,

[quoted text clipped - 47 lines]

Thanks in advance!
Russell.