Find Matching Records In Different Table

[Guest]

I have two tables that are related CASE_NUM_YR & CASE_NUM fields (used as
primary key) Table A with two part primary key field (CASE_NUM_YR &
CASE_NUM) and Table B has three part primary Key field (CASE_NUM_YR &
CASE_NUM & SEQNO).

I need to use VB code to locate matching records in table B that match a
record currently in focus from table A. I would think that I could use a
recordset for table B, but at this point still unable to come up with
reasonable and working solution by myself. I need to read table A, strip
data out of A (names and address info of 3 people) building temporary mailing
address table C (which is different format of table A). Then after creating
these three name records in table C, using same record key information, read
Table B for any (possible multiple) records in Table B that may exist, locate
these records in sequential access (CASE_NUM_YR & CASE_NUM & SEQNO) and strip
name and address info from table B and copy into new format of Table C, thus
completing temp table of names and address in new format for mailing
envelopes and labels creation.

Can someone advise the proper way, via VB code to locate a specific record
in my Table B from key information supplied via the current record key held
in memory.

Thanks in advance..

 

[Marshall Barton]

I have two tables that are related CASE_NUM_YR & CASE_NUM fields (used as
primary key) Table A with two part primary key field (CASE_NUM_YR &
CASE_NUM) and Table B has three part primary Key field (CASE_NUM_YR &
CASE_NUM & SEQNO).

I need to use VB code to locate matching records in table B that match a
record currently in focus from table A. I would think that I could use a
recordset for table B, but at this point still unable to come up with
reasonable and working solution by myself. I need to read table A, strip
data out of A (names and address info of 3 people) building temporary mailing
address table C (which is different format of table A). Then after creating
these three name records in table C, using same record key information, read
Table B for any (possible multiple) records in Table B that may exist, locate
these records in sequential access (CASE_NUM_YR & CASE_NUM & SEQNO) and strip
name and address info from table B and copy into new format of Table C, thus
completing temp table of names and address in new format for mailing
envelopes and labels creation.

Can someone advise the proper way, via VB code to locate a specific record
in my Table B from key information supplied via the current record key held
in memory.

Using code to search for matching records and puttingthe
result in a temp table is a last resort way to do things.
How about just using a query that combines the matching
records?

SELECT A.CASE_NUM_YR, A.CASE_NUM, A.personame,
A.address, . . .,
B.SEQNO, B.amount, . . .
FROM tableA As A INNER JOIN tableB As B
ON A.CASE_NUM_YR = B.CASE_NUM_YR
AND A.CASE_NUM = B.CASE_NUM
WHERE A.CASE_NUM = Forms!yourform.txtCaseNum

I am certain that that is not exactly what you want, but
it's the best I can do with the information you provided.
If that is even a little close to what you want, post back
with more details and what about it is not ot your liking.