VBA Loop not working right

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

MeginAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony BerenguelCommented:
is there a reason why you're not increasing the variable 'theRow' by one when TheStaffName = rs!TEAMName?
0
NorieVBA ExpertCommented:
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
MeginAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What changes did you make? That may help us pinpoint the trouble ...
0
James ElliottManaging DirectorCommented:
have you tried:

oSheet.Cells(3, 1).CopyFromRecordset rs

Open in new window

0
MeginAuthor Commented:
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
MeginAuthor Commented:
And here is the attachment.
Export-v1.accdb
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
MeginAuthor Commented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
MeginAuthor Commented:
I am attaching an example.
Example.xlsx
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
MeginAuthor Commented:
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
MeginAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MeginAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.