Build/Export File - Loop through table records

[outofpractice]

A coworker has a set of test files (txt format) which they use consistently - sometimes the test region they are using changes so they have to open up each file and change a small portion of it to point to the new test region. (There will be other changes done as well sometimes, or additions/deletion of these tests). So to try and help her with this I've made a database which does the following, but I know it is not the right way or very efficient:

There are 3 tables:

Part1 -> This has a memo field with just text, it is always the exact same information

Part2 -> This is the one that changes for the test system. It has 5 records which are strings of data that will point the test file to the appropriate test region.

Part3 -> This is another memo field which is the actual test data. Currently there are 40 records on this table

So what I have done as of right now is: Created 40 reports - one for each separate "Part3" row - I have a macro that will run each report and then export to a location as a txt file. This is inelegant, but it works.

The problem - something I did not take into consideration was for Part1 -> there will actually be two options there (Two independent test system) and each of these options can go to one of 5 test regions. Each of the test systems will have their own set of test data contained in Part3 of the report. So, to continue with how I am doing this I would then continue to duplicate the reports, so now I am up to at least 80 reports

I could continue with what I'm doing, or just copy the database and have one for each test system - but I am sure there has to be a way to only have one report and have it reference the correct information.

What I have in mind and am asking for help is:

Have a form where there user selects: Test System (Part1), and Test Region (Part2) -> A query would use this information to determine the appropriate information for the Part3 data and each report would be dynamically built and exported based on the number of rows for a particular Test System at that time.

If I could do this it would eliminate any maintenance I need to do when a new test for Part3 is added (which my current way I would have to make a new report and add it to the export macro).

The current structure I have in place now for the tables are:

tbl_Part1:
Part1_Text -> the text that is exported in the file
System -> added this part this morning to identify which Test System the text is affiliated with

tbl_Part2:
Part2_Text -> the text that is exported in the file
TestSystem -> identifies which text is needed to be sent in the file

tbl_Part3:
Part3_Text -> the text that is exported in the file
TestID -> just a field to help the user know which test she is looking at/can search by if there is an update needed to one or if one is deleted - i.e. she could tell me she is deleting "Test7" so I can delete that report and remove it from the macro
NEEDED? -> Test system field to know which test system (Part1) the text is affiliated with

The 40 reports I have are all the same, only referencing the three different parts:
Part1 -> =DLookUp("Part1_Text","tbl_Part1")
Part2 -> =DLookUp("Part2_Text","TestSystem")
Part3 -> =DLookUp("[Part3_Text]","tbl_Part3","[TestID] = 'Test1' ")


I think what I need to do is write some VB that will: Loop through Part3 to build export a report - use the "TestID" from Part3 as the file name in the export so I don't hand key each one. My VB is very rusty though so I'm having a hard time getting started on this.

This is really long, so I hope my explanation of what I have am am trying to do makes sense. Thanks!

 

[The_Doc_Man]

If there is a way to build a query to only select the desired information (using some key such as the specific TestSystem number) then you can build a report based on the query rather than the underlying tables. You might (and I emphasize MIGHT) not need any VBA at all. Wouldn't need the DLookup calls either if you can put the right fields in your table to force them to be associated by the TestSystem ID number.

Just be careful with MEMO fields because they are treated specially in Access, not actually stored with the main record (if I recall correct). If your record with the MEMO field has some other discriminator then you can search or link related data based on that "other" field. You cannot, however, use MEMO fields as keys and you will have other issues in parsing or testing the contents of MEMO data. They are just "raw strings" for the most part but their size makes them intractable for any functions that are (normally) designed for use with SHORT TEXT fields (255 character limit).

 

[outofpractice]

OK - fourth time trying to write this reply. I keep getting stuck in a sign in loop where I sign in then get the sign in page over and over!

Since my first post I have been playing around and have it doing what you said - I added a table with the two different test systems, affiliated each of the test info on tbl_Part3 with a test system.

I also set up a query/report which uses this information to bring back only the relevant information.

However, the issue is this is all one long report - I need to be able to export each of these rows from tbl_Part3 into its own text file with a unique name.

For the memo fields - the information in tbl_Part3 is more than 255 characters is why I went with memo, I am not aware of a workaround to that.

Thanks

 

[outofpractice]

I'm getting closer - but still am not there so hopefully someone can review this code and let me now where's I'm wrong.

When this code runs, it looks like it is looping through all of the records on tbl_Part3 - but it is only writing the first result. It brings up a report view as well and it contains all of the relevant records. So I have an issue with either the looping or the writing of the files - or maybe even the Set qdf part, not entirely sure where I'm messing up (probably all of it)

I just have it dynamically writing the date, but what I want it to do is to use a field called "TestID" on tbl_Part3 as the last part of the file name.

Private Sub Command11_Click()
Dim qdf As DAO.QueryDef
Dim MyRs As DAO.Recordset

Set qdf = CurrentDb.CreateQueryDef("", "SELECT ID FROM tbl_Part3") '"qry_ReportInfo")
Set MyRs = qdf.OpenRecordset 'CurrentDb.OpenRecordset("rpt_ReportInfo")

With MyRs
.MoveFirst
Do While Not .EOF
' open report hideen and filtered
DoCmd.OpenReport "rpt_ReportInfo", acPreview, , "[ID] = " & !ID, acHidden
' save the hidden report as a PDF
DoCmd.OutputTo acOutputReport, "Test1", "MS-DOSText(*.txt)", "C:\Users\User\Desktop\AccessExport\SystemA\" & Format(Date, "yyyy\_mm\_dd") & ".txt", False, "", 0, acExportQualityPrint

.MoveNext
Loop
End With
End Sub

 

[pbaldy]

2 problems: you don't close the report after the OutputTo, and the file name won't be unique to each ID.

 

[outofpractice]

2 problems: you don't close the report after the OutputTo, and the file name won't be unique to each ID.

Thanks for that - I realized I had another problem and that is why it was only putting one report out there - I was referencing an old report as well. I updated with your suggestion to close the file and was able to get to the point where it puts each file out there dynamically named based on the appropriate field on the table - TestID

However each file was blank. So I modified some more and got to one point where it put all the files in my location, but each file had every single record on the table - I assume this is because I was only using the table.

So now I am trying to modify so that this is referencing the query I had set up (vs just the table in general - although would it be better to reference the table and write a SQL statement to retrieve the appropriate info?)

The query I have set up to retrieve the appropriate records is based on user inputs on two combo boxes on frm_Test (These combo boxes select the Test System and Test Environment to build the files from - Combo0 and Combo9)

However - now that I have changed the query to reference the report - I am now getting an error of: Runtime error 3061 - Too few parameters. Expected 1on the line in red below. I believe that I need to add the combo box controls on the frm_Test to this line but am unsure of the appropriate syntax for this.


Private Sub Command11_Click()

Dim MyRs As DAO.Recordset
Dim rpt As Report

Set MyRs = CurrentDb.OpenRecordset("qry_ReportInfo")
DoCmd.OpenReport "rpt_ReportInfo", acPreview, , , acHidden
Set rpt = Reports("rpt_ReportInfo")

With MyRs
.MoveFirst
Do While Not .EOF

rpt.Filter = "[ID] = " & !ID
rpt.FilterOn = True


DoCmd.OutputTo acOutputReport, "rpt_ReportInfo", "MS-DOSText(*.txt)", "C:\Users\User\Desktop\AccessExport\SysA\" & MyRs![TestID] & ".txt", False, "", 0, acExportQualityPrint

DoCmd.Close acReport, "rpt_ReportInfo"

.MoveNext
Loop
End With
End Sub

 

[pbaldy]

I think you were closer before. There's more wrong with that than the error you're getting. If you just open one with this, does it open correctly?

DoCmd.OpenReport "rpt_ReportInfo", acPreview, , "[ID] = " & !ID

If you know valid ID's are coming through, you can hard-code one to test:

DoCmd.OpenReport "rpt_ReportInfo", acPreview, , "[ID] = 123"

 

[outofpractice]

The first example I receive: Invalid or Unqualified reference

When I used your second example it worked to pull back the record specified by the number I entered.

 

[pbaldy]

The first would have to be done with the recordset. Since the second worked, I'd expect your original loop to work, tweaked by closing the report after creating the file and making the file names unique.

 

[outofpractice]

I'll plug that back in and see what happens.

Thanks!

 

[outofpractice]

It worked! Thank you so much for walking through this with me, I appreciate it!

 

[pbaldy]

Happy to help!