?
Solved

VBA Loop not working right

Posted on 2014-09-15
15
Medium Priority
?
184 Views
Last Modified: 2014-09-24
I have some code that should fill in an Excel spreadsheet with information from an Access query.

Admittedly, it isn't mine. A very nice person on this site has been helping me. Of course, as soon as I try to go out on my own, I fall out of the next like a baby bird that was not ready to fly. On the sidewalk. Face down.

Anyway, I think my problem is with the looping. When the code runs, the first row that shows up in excel (not the headers) is correct. But then every line under it is different. It is like the change I made to the code made the first round go fine, but just stopped there.  Here is what I have:

iNumCols = 4

theRow = 3
TheTO = rs!To
oSheet.Cells(3, 1).Value = TheTO
TheSTOname = rs!STO
oSheet.Cells(3, 2).Value = TheSTOname
TheStaffName = rs!TEAMName
oSheet.Cells(3, 3).Value = TheStaffName
TheDesc = rs!actdesc
oSheet.Cells(3, 4).Value = TheDesc

Do Until rs.EOF
    If rs!ToDesc = TheTO Then
        If rs!STOName = TheSTOname Then
            If TheStaffName = rs!TEAMName Then

                rs.MoveNext
            Else
                theRow = theRow + 1
                oSheet.Cells(theRow, 3).Value = rs!TEAMName
                TheStaffName = rs!TEAMName
                rs.MoveNext
            End If
        Else
            theRow = theRow + 1
            oSheet.Cells(theRow, 2).Value = rs!STOName
            TheSTOname = rs!STOName
            rs.MoveNext
        End If
    Else
        theRow = theRow + 1
        oSheet.Cells(theRow, 1).Value = rs!ToDesc
        TheTO = rs!ToDesc
        rs.MoveNext
    End If
skip:
Loop

Open in new window

0
Comment
Question by:Megin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
15 Comments
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 40324176
is there a reason why you're not increasing the variable 'theRow' by one when TheStaffName = rs!TEAMName?
0
 
LVL 34

Expert Comment

by:Norie
ID: 40324201
What's the logic behind all If/Else/End Ifs in the loop?

Also, does CopyFromRecordset not work for what you are trying to do?
0
 

Author Comment

by:Megin
ID: 40324212
This link might help:

http://www.experts-exchange.com/Database/MS_Access/Q_28514101.html#a40323934


This is where my previous question about building this code was originally.

I closed it because my initial question was answered. The background as to why certain things are the way they are is there.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 85
ID: 40325017
What changes did you make? That may help us pinpoint the trouble ...
0
 
LVL 12

Expert Comment

by:James Elliott
ID: 40325105
have you tried:

oSheet.Cells(3, 1).CopyFromRecordset rs

Open in new window

0
 

Author Comment

by:Megin
ID: 40326693
Hi there!

Sorry it has taken me a while to get back to everyone.

I changed two of the references in the code.

The code was this:
iNumCols = 4

		theRow = 3
		TheTO = rs!ToDesc
		oSheet.Cells(2, 1).Value = TheTO
		TheSTOname = rs!STOName
		oSheet.Cells(3, 2).Value = TheSTOname
		TheStaffName = rs!TEAMName
		oSheet.Cells(3, 3).Value = TheStaffName

		Do Until rs.EOF
			If rs!ToDesc = TheTO Then
				If rs!STOName = TheSTOname Then
					If TheStaffName = rs!TEAMName Then
						rs.MoveNext
					Else
						theRow = theRow + 1
						oSheet.Cells(theRow, 3).Value = rs!TEAMName
						TheStaffName = rs!TEAMName
						rs.MoveNext
					End If
				Else
					theRow = theRow + 1
					oSheet.Cells(theRow, 2).Value = rs!STOName
					TheSTOname = rs!STOName
					rs.MoveNext
				End If
			Else
				theRow = theRow + 1
				oSheet.Cells(theRow, 1).Value = rs!ToDesc
				TheTO = rs!ToDesc
				rs.MoveNext
			End If
		skip:
		Loop

Open in new window


I added a couple of fields in the query that concatenate different fields to make them look the way I want them to look. The names of those fields are TO and STO.  I also added a column for theActDesc.  Here is what I have:

iNumCols = 4

theRow = 3
TheTO = rs!To
oSheet.Cells(2, 1).Value = TheTO
TheSTOname = rs!STO
oSheet.Cells(3, 2).Value = TheSTOname
TheStaffName = rs!TEAMName
oSheet.Cells(3, 3).Value = TheStaffName
theActDesc = rs!ActDesc
oSheet.Cells(3, 4).Value = theActDesc

Do Until rs.EOF
    If rs!ToDesc = TheTO Then
        If rs!STOName = TheSTOname Then
            If TheStaffName = rs!TEAMName Then
                rs.MoveNext
            Else
                theRow = theRow + 1
                oSheet.Cells(theRow, 3).Value = rs!TEAMName
                TheStaffName = rs!TEAMName
                rs.MoveNext
            End If
        Else
            theRow = theRow + 1
            oSheet.Cells(theRow, 2).Value = rs!STOName
            TheSTOname = rs!STOName
            rs.MoveNext
        End If
    Else
        theRow = theRow + 1
        oSheet.Cells(theRow, 1).Value = rs!ToDesc
        TheTO = rs!ToDesc
        rs.MoveNext
    End If
skip:
Loop

Open in new window


I am attaching the DB as well.

The button that triggers this in on the reports form. It says "Export Combined Report to Excel"


James Elliott: Where should I try adding that to the code?
0
 

Author Comment

by:Megin
ID: 40326695
And here is the attachment.
Export-v1.accdb
0
 
LVL 85
ID: 40327589
I'm not sure what is "wrong" with the output, so it's hard to help. From what I can tell, the code outputs an Excel worksheet and does some formatting.

Here's the Export from the process when I ran it:

Excel OutputWhat's wrong with the output, and what changes do you need to make?
0
 

Author Comment

by:Megin
ID: 40330709
If you look at the very top line, it is different than the others. It says AA-Task Order AA. The other items look different, too. Also, there is an item in the Details column.

Those items are being pulled from the Query fields TO, STO, and actdesc.  I need that to be repeated in the loop so that all of the rest of the items are from those fields.

But it is only getting the data from those fields one time. I am not sure I understand how to make this happen. I thought I had changed the code enough to do this, but apparently I didn't.
0
 
LVL 85
ID: 40331043
There's still not enough to help.

Could you perhaps mockup an Excel sheet that would show the same information above, but formatted the way you need it?
0
 

Author Comment

by:Megin
ID: 40331192
I am attaching an example.
Example.xlsx
0
 
LVL 85
ID: 40331442
Sorry Megin, but you'd have to give an example that goes along with the sample data you posted earlier. I _sort_ of see what you're after, but be aware that you can't do that with standard outputs and such only. You'll have to automate Excel to introduce the row spans and merges and such, as well as the formatting.

In other words - this is not really about looping in VBA, but rather about exporting data to Excel and formatting it.
0
 

Author Comment

by:Megin
ID: 40331663
I understand that the formatting will have to be a separate thing. I don't understand why the code pulls the information for rows 2 and 3 from certain fields in the qry_report query and then goes to other fields for the rest of the process.

In cell A2 it is showing "AA- Task Order AA." That is a field I set up in the query to concatenate two other fields in order to show both "AA" and "Task Order AA" in one field. I named this field "TO."

In cell B3 it is showing "AA02 Sub Task Order AA01."  I did the same thing with this field, concatenating different fields to produce one in the query that looks the way I want it to look. I named this field "STO."

I also added into the code to add the information from the query's "actdesc" field into the "Details" column.

Originally, the code didn't reference those concatenated fields or the "Actdesc" field, and all that showed up in the excel spreadsheet was everything you see in rows 4 and down.

I want the code to do what it is doing, but continue to use the concatenated fields I created and not the fields it is pulling information from in rows 4 and down.

When I changed the code to do this, it just added what you see in rows 2 and 3.

Does that make anymore sense? I apologize if I am doing a poor job of explaining this.
0
 

Accepted Solution

by:
Megin earned 0 total points
ID: 40333875
I found my solution!

I didn't understand exactly what the code saying and only changed the field reference in one location and not all of the others. So, this is what I had:


Do Until rs.EOF
    If rs!ToDesc = TheTO Then
        If rs!STOName = TheSTOname Then
            If TheStaffName = rs!TEAMName Then
                rs.MoveNext
            Else
                theRow = theRow + 1
                oSheet.Cells(theRow, 3).Value = rs!TEAMName
                TheStaffName = rs!TEAMName
                rs.MoveNext
            End If
        Else
            theRow = theRow + 1
            oSheet.Cells(theRow, 2).Value = rs!STOName
            TheSTOname = rs!STOName
            rs.MoveNext
        End If
    Else
        theRow = theRow + 1
        oSheet.Cells(theRow, 1).Value = rs!ToDesc
        TheTO = rs!ToDesc
        rs.MoveNext
    End If
skip:
Loop

Open in new window


And I changed it to this:

Do Until rs.EOF
    If rs!To = TheTO Then
        If rs!STOName = TheSTOname Then
            If TheStaffName = rs!TEAMName Then
                rs.MoveNext
            Else
                theRow = theRow + 1
                oSheet.Cells(theRow, 3).Value = rs!TEAMName
                TheStaffName = rs!TEAMName
                rs.MoveNext
            End If
        Else
            theRow = theRow + 1
            oSheet.Cells(theRow, 2).Value = rs!STO
            TheSTOname = rs!STO
            rs.MoveNext
        End If
    Else
        theRow = theRow + 1
        oSheet.Cells(theRow, 1).Value = rs!To
        TheTO = rs!To
        rs.MoveNext
    End If
skip:
Loop

Open in new window


It is now behaving the way I wanted it to behave.

But thank you to everyone for taking the time to look at this!
0
 

Author Closing Comment

by:Megin
ID: 40341113
I figured out that I hadn't changed all of the query references to match up. It is working fine now with changes I made, so I put my own answer as the solution.
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question