Visual Basic Help

I am in the process of converting an old Access 95 database to Access 2003. At this point in time I am running into some issues with the print routine that was developed. It prints multiple times and it doesn't print in the correct order of page numbers. I am not programmer by any means and was wondering if someone could help me fugure this out. I will wait to see if anyone would like to help before I post any code or get into specifics.

Thank you

Newbie

 

I got a chance to copy the code... not exactly sure what it does or how it works but I would like try and figure it out.

Thank you

 

I'm not sure, but it looks like your VBA (Visual Basic for Applications) code is generating and printing reports. As much as I love programming, sometimes shortcuts are there for a reason. Why aren't you using Access's built-in Reports function? While you're upgrading from 95, why not just go all the way to 2007?

 

or better yet, learn some programming and ditch access, it's horrible!

 

Har har. Now now, let's not get too carried away with the newbie. Though I do prefer me a web (PHP) frontend with a MySQL/SQLite backend. I think one step at a time is needed here.

 

I know that access has its limitations but so far this application has worked great for what we have wanted it to do. I don't know why it was set up using VB code instead of the print functions inside of access. The problem in the code that I am having issues with at this point in time is the sorting of the pages that it reads into the temp tables and then for some reason it prints alot of copies. I was hoping that someone could see what the code is doing wrong and give me a hand fixing it.

The reason I am only upgrading to 2003 at this point in time is because that is what is installed on our PCs. At some point I would like to switch to Access 2007 just not right now.

If any one has any good advice on this problem I would appreciate it!!

Thank you

 

Without having access to the files it is processing, or the Forms where the functions are called from, it is very hard to debug VBA. In general, it is very hard to debug code when you can't test it. More importantly, if this is for a company, I'd be concerned about sensitive information. If you can't share with us the files, I would recommend trying to recreate the reports using Access' Report Wizard.

I can look at the code and try to debug it, but without having the same resources at your disposal, I can't promise anything.

 

I understand what you are saying about the sensitive information, and the fact that it is hard to debug without being able to test. But as far as I know the reports are developed in Access. Where the problem lies is in the code that tells those reports how to sort and what order to print in. It seems that I run into problems in the routine that first sorts the pages nad then tells them what order to print in.

I belive the routines that run before that open up temp. tables that read the reports in. After that it is supposed to sort them depending on the page number that was input. Then it is supposed to go in and print one copy in the correct order. I can not get it to sort the pages correctly. It will print all the pages from one temp table and then the pages from another temp table. It should be taking all the pages in all the temp tables and putting them in order and printing them out based on page #. It also prints multiple copies.

I may be redundant and it is probably hard to understand without actually seeing. I do appreciate all your ideas and you help thus far.

Thank you

 

I understand that you believe the problem is in the code; you are probably right! I've read through most of the code, it still seems to me like all it is doing is needlessly complicating the process of creating a report. Have you tried to recreate the report using the Report Wizard and the saved queries that the code is currently using?

The routines that you have given us do not seem to ask for a Page Number, but rather a "KNumber", which is supposedly the name of a table. It then runs 5 queries "by K Number". The arrays are having their 0 indexes ignored, unless you've set Option Base 1 (works in VB6, NOT in VB2005; I think Office 2003 uses VB6 or VB2003?).

One potential problem I can see is if your queries return more than 1000 records total, in which case your Report array is running out of space. You can check that manually in your query tables, or by making a query to count the records (counting manually is faster, in this case, just check the number of records in each table).

Why do you have a nested FOR loop for sorting? I can't tell just from looking at it.

I haven't quite finished looking at the "Print Report" section.

 

Our reports that we produce from this database have a Fan sheet, Traverse sheet, Pump sheet ect... When selecting a K-Number (which is our job number) when printing an entire report it goes to each table for these particular sheets and searches for those K-Numbers

Each Type of sheet is then put into a temporary table For example the code below Queries on the K-Number and finds all Traverse sheets in the Traverse table that match that K-Number. It opens a temporary table called "Fans For Report". The pages inside this report have already been given a page number by the user. I am assumming that page number is a field that is read into this table and set as an "Active Control". It then goes through and counts how many records it has in that individual table. It looks to me like it might be assigning the page number a variable. Not really sure....
DoCmd.OpenQuery "qryFansByKNumber"
Count = DCount("*", "Fans For Report")
DoCmd.OpenTable "Fans For Report"
If Count >= 1 Then
DoCmd.GoToRecord acTable, "Fans For Report", acFirst
DoCmd.GoToControl "Page"
For x = 1 To Count
Report(a, 1) = 1
Set PageNumber = Screen.ActiveControl
Report(a, 2) = PageNumber.Value
a = a + 1
DoCmd.GoToRecord acTable, "Fans For Report", acNext
DoCmd.GoToControl "Page"
Next x
End If
DoCmd.Close acTable, "Fans For Report"

After that it runs through all the tables and stores the values of the page numbers as variable it should go through and sort with this code. I'm assumming that this moves the pages around depending on the vale of X or Y... Once again not really sure.
' Sort the records by page

For x = 1 To (a - 1)
For Y = 1 To (a - 1)
If Report(Y, 2) > Report((Y + 1), 2) Then
Temp = Report(Y, 2)
Report(Y, 2) = Report((Y + 1), 2)
Report((Y + 1), 2) = Temp
Temp = Report(Y, 1)
Report(Y, 1) = Report((Y + 1), 1)
Report((Y + 1), 1) = Temp
End If
Next Y
Next x


Once it hits this part of the code it determines what pages to print and in what order. This goes through and checks which tables have records in them depending on the Value of X when we originally searched for a KNumber in the beginning. When X=1 I can get it to print the fan sheets and the correct page numbers are displayed but it doesn't look at the rest of the tables and put them in the correct order. So if I have Fan Sheets with page # 3, 5, 7 and Traverse sheets with Page # 1, 2, 4, and 6. It will print all of the fan sheets first and then the traverse sheets.

'Print the Entire Report in the correct order

If reporttype <> 4 Then
DoCmd.RunMacro "REPORT MACROS.PRINT TITLE REPORT"
End If
For x = 1 To a
DoCmd.OpenTable "KNumber"
DoCmd.GoToRecord acTable, "KNumber", acFirst
DoCmd.GoToControl "PageNum"
Set K = Screen.ActiveControl
K.Value = Report(x, 2)
DoCmd.Close acTable, "KNumber"

If Report(x, 1) = 1 Then
If reporttype = 1 Or reporttype = 2 Then
DoCmd.RunMacro "REPORT MACROS.PRINT FAN REPORT"
ElseIf reporttype = 3 Then
DoCmd.RunMacro "REPORT MACROS.PRINT METRIC FAN REPORT"
ElseIf reporttype = 4 Then
DoCmd.RunMacro "REPORT MACROS.PRINT FIELD FAN REPORT"
End If
ElseIf Report(x, 1) = 2 Then
If reporttype = 1 Or reporttype = 2 Then
DoCmd.RunMacro "REPORT MACROS.PRINT FBP REPORT"
ElseIf reporttype = 3 Then
DoCmd.RunMacro "REPORT MACROS.PRINT METRIC FBP REPORT"
ElseIf reporttype = 4 Then
DoCmd.RunMacro "REPORT MACROS.PRINT FIELD FBP REPORT"
End If
ElseIf Report(x, 1) = 3 Then
If reporttype = 1 Or reporttype = 2 Then
DoCmd.RunMacro "REPORT MACROS.PRINT OUTLET REPORT"
ElseIf reporttype = 3 Then
DoCmd.RunMacro "REPORT MACROS.PRINT METRIC OUTLET REPORT"
ElseIf reporttype = 4 Then
DoCmd.RunMacro "REPORT MACROS.PRINT FIELD OUTLET REPORT"
End If
ElseIf Report(x, 1) = 4 Then
If reporttype = 1 Or reporttype = 2 Then
DoCmd.RunMacro "REPORT MACROS.PRINT PUMP REPORT"
ElseIf reporttype = 3 Then
DoCmd.RunMacro "REPORT MACROS.PRINT METRIC PUMP REPORT"
ElseIf reporttype = 4 Then
DoCmd.RunMacro "REPORT MACROS.PRINT FIELD PUMP REPORT"
End If
ElseIf Report(x, 1) = 5 Then
If reporttype = 1 Then
DoCmd.RunMacro "REPORT MACROS.PRINT TRAVERSE REPORT"
ElseIf reporttype = 2 Then
DoCmd.RunMacro "REPORT MACROS.PRINT PG TRAVERSE REPORT"
ElseIf reporttype = 3 Then
DoCmd.RunMacro "REPORT MACROS.PRINT METRIC TRAVERSE REPORT"
ElseIf reporttype = 4 Then
DoCmd.RunMacro "REPORT MACROS.PRINT FIELD TRAVERSE REPORT"
End If
End If
Next x
DoCmd.DeleteObject acTable, "Fans For Report"
DoCmd.DeleteObject acTable, "Flow By Pressures For Report"
DoCmd.DeleteObject acTable, "Outlets For Report"
'DoCmd.DeleteObject acTable, "Pump For Report"
DoCmd.DeleteObject acTable, "Traverses For Report"
End If

DoCmd.SetWarnings True
DoCmd.Echo True


I hope some of this makes sense. I am going to try what you said with Access but it has been such a long time since I have worked with any of this stuff that it is some what difficult to unbderstand. I would like to do this on my own so I can continue to support this application in the future.

Thanks again

 

They're not put into a Table at all. A bunch of number are stored in a giant Integer Array (called Report). "Fans for Report" is probably the result produced by the Query "qryFansByKNumber". "Page Number" isn't really a field, as that would mean it's actually stored in the database. In this case, it appears to be a Control of sorts, possibly in a Form, that calculates and tracks page numbers.

You'll note that "x" is doing nothing here to affect the inner workings. If it is supposed to be doing something, your problem is likely right here. Though, why/how it ever worked in the past is beyond me.

What happens if you have Fan Sheets with page #1, 2, 4, 6 and Traverse Sheets with page #1, 2, 3, 5? I do not believe the code accounts for such a collision.

The variable "a" keeps track of the TOTAL number of records processed. The variable "Y" is used to manipulate "Reports"' first dimension during sorting. The variable "x" is used EVERYWHERE, usually for the index of a FOR loop.

It looks to me like the "Sorting" section is the culprit. It is not sorting by report type and page number (page number is stored in the second dimension, second index of Reports), but only by page number (Report(Y, 2)). What is the intended product of this sorting/printing? Should all the Fan Pages be printed first, followed by the next type?

The "Printing" section of code relies on the assumption that the report page numbers will be stored in the Reports array together grouped by the query that produced them. This will not always be the case, thanks to the sorting code, which could very easily intermix the report types since it is only comparing page numbers.

Your dedication is admirable, but sometimes you have to rebuild from scratch. No offense, but if you think you're going to have trouble with the Report Wizard, how do you expect to maintain 200+ lines of cobbled together VBA? I really highly strongly recommend you try to recreate this code in REAL reports. It is easier than you think it is; that's why it's a wizard.

As you said, you're not a programmer by any means, so why try to debug and fix code that someone else threw together? You could likely maintain a series of Access reports that you made yourself better than you could someone else's code.