Solved

VBA Loop not working right

Posted on 2014-09-15
15
168 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
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 33

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
 
LVL 84
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 84
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 84
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 84
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Read about achieving the basic levels of HRIS security in the workplace.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now