loop within loop

[CortoMaltes]

I have created an application in Access 97 for cataloguing of bibliographical information and the data is exported into a text file using VBA, in order to upload the data into our main library management system database.
There is a MAIN table that contains the basic bibliographical data and another table that contains the data that users can choose for a given field, e.g. Authors (within the two there is a join table that links both).
My export code loops through the MAIN recordset and exports everything and then loops through the linked table to export the related data. What I am trying to do is to export the data in an ordered way, to make sure that one full record is exported alltogether before exporting the next record from both the main table and related table(s).
Can anybody help me with the construct of the loop, to have have the data exported in such an order way? Thank you in advance.

 

[Aivars]

Hi!
Create query based on both tables.
Aivars

 

[CortoMaltes]

The query is there. Both recordsets are created using SQL. rstMain, rstAuthors

with rstMain
while not .eof
[data]
wend
end with
with rstAuthors
while not .eof
[data]
wend
end with
This results in:
MAIN record 1
MAIN record 2
...
1st Author MAIN record 1
2nd Author MAIN record 1
1st Author MAIN record 2
...
And I would like to have:
MAIN record 1
1st Author MAIN record 1
2nd Author MAIN record 1
MAIN record 2
1st Author MAIN record 2
...
Thanks for advising

 

[Aivars]

Hi again!

Probably it's possibly to solve your problem with method which use you, but

with rstMain
while not .eof
[data]

with rstAuthors 'Include selection criteria in your recordset
while not .eof
[data]
wend
end with

wend
end with

Other way is creating query based on both tables.

Aivars

 

[CortoMaltes]

In the case that I continue using the current method, what would then be the sequence for the recordset.movenext command? Using the query method seems slightly problematic, as I have many different related tables (about 10 and I cannot reduce the number) and there is the limitation to the length of the SQL string. I'm feeling quite silly in finding a proper solution...

 

[mymou]

The complexity of the queries shouldn't be too much of a problem (conceptually) as you can easily create complex 'views' by generating queries that use queries.

I would advise that you do not use such complex sql in code (if possible) as it tends to get out of hand very easily - and is difficult to maintain.

Try using something like
Set oRS = oConn.Execute("qryQueryName&quot

I think this also has advantages in that the query should also be complied. Also, it allows the safer use of field index numbers.

Not sure what you mean by 'sequence for the recordset.movenext command' - if you have two separate recordsets - is there a problem?

Hope you find the easy solution.


Stew

 

[CortoMaltes]

My code works fine in what concerns exporting all the data from both the main and related tables. Nevertheless, by writing to the file all data from the main recordset and only then the data from the related recordsets poses problems, by changing the order of the original input. There are two conditions that I must meet:
All the data for one single record must be exported alltogether from the main and related tables, otherwise our library system is not able to place all the information together (it assumes that one record always has the same ID. Nevertheless, even if the same ID is found later in the file, it is not considered as belonging to the same record). What I have done to get around this problem is to sort the file before importing, in order to put together in the sequence all lines starting with the same ID.
2. The problem of sorting the file later is that it changes the order in which e.g. the authors were input and therefore the main author of a book, which should be the first Author line might no longer appear as the first line after sorting (usually, it becomes sorted in alphabetical order).
To better understand, here is an example:
Current export:
0006109 2420 L $$a(A new high school-leaving exam :$$bthe vocational school-leaving exam)
0006109 24505 L $$aEine "neue" Matura$$bdie Berufsreifeprüfung
0006111 2420 L $$a(An information brochure of College Study Programs in Austria)]
0006111 24505 L $$aEine Informationsbroschüre zu Fachhochschulstudiengängen in Österreich
0006109 1001 L $$aReiter W
0006109 1001 L $$aKastenhuber B
0006111 7001 L $$aGruber E
0006111 7001 L $$aJurij J
Ideal export:
0006109 2420 L $$a(A new high school-leaving exam :$$bthe vocational school-leaving exam)
0006109 24505 L $$aEine "neue" Matura$$bdie Berufsreifeprüfung
0006109 1001 L $$aReiter W
0006109 1001 L $$aKastenhuber B
0006111 2420 L $$a(An information brochure of College Study Programs in Austria)]
0006111 24505 L $$aEine Informationsbroschüre zu Fachhochschulstudiengängen in Österreich
0006111 7001 L $$aGruber E
0006111 7001 L $$aJurij J

I hope I'm sounding logic and that I am not misunderstanding your suggestions...

 

[CortoMaltes]

My code works fine in what concerns exporting all the data from both the main and related tables. Nevertheless, by writing to the file all data from the main recordset and only then the data from the related recordsets poses problems, by changing the order of the original input. There are two conditions that I must meet:
All the data for one single record must be exported alltogether from the main and related tables, otherwise our library system is not able to place all the information together (it assumes that one record always has the same ID. Nevertheless, even if the same ID is found later in the file, it is not considered as belonging to the same record). What I have done to get around this problem is to sort the file before importing, in order to put together in the sequence all lines starting with the same ID.
2. The problem of sorting the file later is that it changes the order in which e.g. the authors were input and therefore the main author of a book, which should be the first Author line might no longer appear as the first line after sorting (usually, it becomes sorted in alphabetical order).
To better understand, here is an example:
Current export:
0006109 2420 L $$a(A new high school-leaving exam :$$bthe vocational school-leaving exam)
0006109 24505 L $$aEine "neue" Matura$$bdie Berufsreifeprüfung
0006111 2420 L $$a(An information brochure of College Study Programs in Austria)]
0006111 24505 L $$aEine Informationsbroschüre zu Fachhochschulstudiengängen in Österreich
0006109 1001 L $$aReiter W
0006109 1001 L $$aKastenhuber B
0006111 7001 L $$aGruber E
0006111 7001 L $$aJurij J
Ideal export:
0006109 2420 L $$a(A new high school-leaving exam :$$bthe vocational school-leaving exam)
0006109 24505 L $$aEine "neue" Matura$$bdie Berufsreifeprüfung
0006109 1001 L $$aReiter W
0006109 1001 L $$aKastenhuber B
0006111 2420 L $$a(An information brochure of College Study Programs in Austria)
0006111 24505 L $$aEine Informationsbroschüre zu Fachhochschulstudiengängen in Österreich
0006111 7001 L $$aGruber E
0006111 7001 L $$aJurij J

I hope I'm sounding logic and that I am not misunderstanding your suggestions...

 

[Aivars]

Use .FindFirst, FindNext, FindLast etc.

with rstMain
while not .eof
[data]

with rstAuthors 'Include selection criteria in your recordset
do
.FindNext "RelFieldToMain=" & SelectCriteria
If .NoMatch Then
Exit Do
else
[data]
End If
loop
end with

wend
end with

Aivars

 

[CortoMaltes]

It seems my mouse is repeating clicks. I am sorry for that. Also, please note that in the example given the codes between ID and author names should always be "1001"

 

[CortoMaltes]

I'm gonna try your suggestion now. I have learned access and its programming while developing this particular application and I'm still some basics. I'll let you know of my success or else I'll be getting back to it here. A million thanks for your hint!

 

[Aivars]

with rstMain
while not .eof
[data]
SelectCriteria="0006109"'Data or part of it of rstMain
with rstAuthors
do
.FindNext "RelFieldToMain like '" & _
SelectCriteria & "*'"
If .NoMatch Then
Exit Do
else
[data]
End If
loop
end with

wend
end with

Aivars

 

[Aivars]

Lot of questions what participants post to forum can be found in help in simple way: click on interested you word on VBA window then press <F1>

Aivars

 

[CortoMaltes]

I really appreciate your help. Your example also gave me other ideas and I have been able to find another solution that implies less re-doing of the current code (I am fighting against time to deliver the application). All the best!