Solved

variable not set

Posted on 2014-09-18
17
87 Views
Last Modified: 2014-09-20
I get an error that variable is not set on line 226 "PopulateFlexGrid".  This happens when the following routine is executed.  It was not happening before, but now its an issue.  How do I resolve?


Private Sub Command3_Click()


If Token = 0 Then

    If Combo1 = "" Then
    MsgBox "Please choose an Agency", vbOKOnly, "Please Select"
    Exit Sub
    End If
    
    If Combo2 = "" Then
    MsgBox "Please select a Month", vbOKOnly, "Please Select"
    Exit Sub
    End If
    
    SelectedMonth = Combo2

End If


'Inserting Progress and Setbacks or Updating Progress and Setbacks-----------------------------------------
If rec7.State = adStateOpen Then
       
                   
                  
                  
        rec7.MoveFirst
       i = 1
        Do While Not rec7.EOF
        
                  esql = "SELECT * from tblOrgMonthlyProgressSetbacks where PIID = " & MSFlexGrid1.TextMatrix(i, 4) & " And MonthlyID = " & MonthlyID & " And Month = " & SelectedMonth & " And Fiscal = " & Fiscal
                        
                  rec9.CursorType = adOpenStatic
                  rec9.CursorLocation = adUseClient
                  rec9.LockType = adLockOptimistic
                  rec9.Open esql, conn, , , adCmdText
                         
                  If rec9.RecordCount = 0 Then
                    If MSFlexGrid1.TextMatrix(i, 10) <> "" Then
                    rec9.AddNew
                    rec9!AgencyID = AgencyID
                    rec9!PIID = MSFlexGrid1.TextMatrix(i, 4)
                    rec9!MonthlyID = MonthlyID
                    rec9!ProgressSetback = MSFlexGrid1.TextMatrix(i, 10)
                    rec9!Month = SelectedMonth
                    rec9!Fiscal = Fiscal
                    rec9.Update
                    GoTo Process1
                    End If
                  End If
                  
                  If rec9.RecordCount > 0 Then
                    rec9!ProgressSetback = MSFlexGrid1.TextMatrix(i, 10)
                    rec9.Update
                    GoTo Process1
                  End If
Process1:
                rec9.Close
                i = i + 1
        rec7.MoveNext
        Loop
        
End If
        
        
MSFlexGrid1.Clear






        If conn.State = adStateClosed Then
        conn.Open "Provider=sqloledb;Data Source=" & ConnectionIP & ",1433;Network Library=DBMSSOCN;Initial Catalog= " & CAPDB & "; User ID=xx;Password=xxxxxxx"
        End If

'This section uses an IF/THEN clause because of the Monthly Report and Progress Grid separation
    If Token = 0 Then
        'Get AgencyID--------
       esql = "select AgencyID from tblOrgProfile where [System Name] ='" & Combo1 & "'"
       rec9.Open (esql), conn, adOpenDynamic, adLockOptimistic
       
       AgencyID = rec9!AgencyID
       
       If Not rec9.EOF Then rec9.MoveNext
       rec9.Close
       
        'Get MonthlyID--------
       esql = "select MonthlyID from tblOrgMonthlyReport where AgencyID = " & AgencyID & " And Month like '" & SelectedMonth & "%' And Fiscal = " & Fiscal
       rec9.Open (esql), conn, adOpenDynamic, adLockOptimistic
       
       If rec9.EOF And rec9.BOF Then
        MsgBox "No records Found", vbOKOnly, "No records"
        rec9.Close
        Exit Sub
       End If
       
       MonthlyID = rec9!MonthlyID
       
       If Not rec9.EOF Then rec9.MoveNext
       rec9.Close
       
    End If

'Pulling all the JOINED Data into the recordset7 --------------------------
esql = "select distinct g.Agency,g.Goal, o.Objective,p.PI,p.PIID,P.Method,a.[ActivityName],A.ActivityID,'' as Count, '' as Hours,'' as ProgressSetback, '' as Month " & _
"from tblOrgGoals G " & _
" Join tblOrgObjectives O on G.GoalID = O.GoalID and g.agencyid=o.agencyid " & _
" Join tblOrgActivities A on O.ObjectiveID = A.ObjectiveID and g.agencyid=a.agencyid " & _
" Join tblOrgPI P on P.ActivityID = A.ActivityID and g.agencyid=p.agencyid " & _
"LEFT Join tblOrgMonthlyProgressSetbacks S on P.PIID = S.PIID and g.agencyid=s.agencyid " & _
"Where G.AgencyID = " & AgencyID & " And A.Fiscal = " & Fiscal & " order by G.Agency,G.Goal"

If rec7.State = adStateOpen Then
        rec7.Close
End If

      rec7.CursorType = adOpenStatic
      rec7.CursorLocation = adUseClient
      rec7.LockType = adLockOptimistic
      rec7.Open esql, conn, , , adCmdText


If rec7.EOF And rec7.BOF Then
    
    MsgBox "There are no records for this report", vbOKOnly, "No Data Found"
    
    rec7.Close
    Exit Sub
    
End If



  'Creating the FlexGrid and putting values into the table----------------------------------

    MSFlexGrid1.FixedRows = 1
    MSFlexGrid1.FixedCols = 0
    
    If Not rec7.EOF Then
    
        MSFlexGrid1.Rows = rec7.RecordCount + 1
        MSFlexGrid1.Cols = rec7.Fields.Count
        
        GridRows = rec7.RecordCount + 1

        'Name the columns--------------------
        For i = 0 To rec7.Fields.Count - 1
            MSFlexGrid1.TextMatrix(0, i) = rec7.Fields(i).Name
            MSFlexGrid1.ColAlignment(i) = 0
        Next
    
        'Insert values into the table------------------
        i = 1
        Do While Not rec7.EOF
    
            For j = 0 To rec7.Fields.Count - 1
                If Not IsNull(rec7.Fields(j).Value) Then
                    MSFlexGrid1.TextMatrix(i, j) = rec7.Fields(j).Value
                End If
            Next
                
                
                'Insert ProgressSetbacks, Month, Fiscal from the database-------------------------------------------------
                esql8 = "SELECT ProgressSetback, Month from tblOrgMonthlyProgressSetbacks where PIID = " & MSFlexGrid1.TextMatrix(i, 4) & " And MonthlyID = " & MonthlyID & " And Month = " & SelectedMonth & " And Fiscal = " & Fiscal
                        
                  rec8.CursorType = adOpenStatic
                  rec8.CursorLocation = adUseClient
                  rec8.LockType = adLockOptimistic
                  rec8.Open esql8, conn, , , adCmdText
                
                
                If rec8.BOF = True And rec8.EOF = True Then
                MSFlexGrid1.TextMatrix(i, 10) = ""
                MSFlexGrid1.TextMatrix(i, 11) = ""
                Else
                MSFlexGrid1.TextMatrix(i, 10) = rec8!ProgressSetback
                MSFlexGrid1.TextMatrix(i, 11) = rec8!Month
                End If
                
                rec8.Close
                
        
                'Total Participant Count for the Month-------------------------------------------------
                esql8 = "SELECT count(distinct RegID) from tblOrgHours where ActivityID = " & MSFlexGrid1.TextMatrix(i, 7) & " And Month(activitydate) = " & SelectedMonth & " And ParticipantHour = 1 And AgencyID = " & AgencyID & " And Fiscal = " & Fiscal
                        
                  rec8.CursorType = adOpenStatic
                  rec8.CursorLocation = adUseClient
                  rec8.LockType = adLockOptimistic
                  rec8.Open esql8, conn, , , adCmdText
                  
                If Not IsNull(rec8.Fields(0)) Then
                MSFlexGrid1.TextMatrix(i, 8) = rec8.Fields(0)
                Else
                MSFlexGrid1.TextMatrix(i, 8) = 0
                End If
                
                rec8.Close
        
        
                'Total Participant Hours for the Month-------------------------------------------------
                esql8 = "SELECT SUM(Hours) from tblOrgHours where ActivityID = " & MSFlexGrid1.TextMatrix(i, 7) & " And Month(activitydate) = " & SelectedMonth & " And ParticipantHour = 1 And AgencyID = " & AgencyID & " and Fiscal = " & Fiscal
                        
                  rec8.CursorType = adOpenStatic
                  rec8.CursorLocation = adUseClient
                  rec8.LockType = adLockOptimistic
                  rec8.Open esql8, conn, , , adCmdText
                        
                If Not IsNull(rec8.Fields(0)) Then
                MSFlexGrid1.TextMatrix(i, 9) = rec8.Fields(0)
                Else
                MSFlexGrid1.TextMatrix(i, 9) = 0
                End If
                rec8.Close
        
                'Row Height----------
                MSFlexGrid1.RowHeight(i) = 1000
                
        i = i + 1
        rec7.MoveNext
        Loop
    

    End If

PopulateFlexGrid = True

'-----------------------------------------------------

        
        If Token = 0 Then
        MSFlexGrid1.ColWidth(4) = 550
        MSFlexGrid1.ColWidth(5) = 1000
        MSFlexGrid1.ColWidth(7) = 0
        MSFlexGrid1.ColWidth(8) = 550
        MSFlexGrid1.ColWidth(9) = 550
        MSFlexGrid1.ColWidth(10) = 7000
        End If
        
        If Token = 1 Then
        MSFlexGrid1.ColWidth(0) = 0
        MSFlexGrid1.ColWidth(1) = 2500
        MSFlexGrid1.ColWidth(2) = 2500
        MSFlexGrid1.ColWidth(3) = 1500
        MSFlexGrid1.ColWidth(4) = 0
        MSFlexGrid1.ColWidth(5) = 0
        MSFlexGrid1.ColWidth(6) = 1250
        MSFlexGrid1.ColWidth(7) = 0
        MSFlexGrid1.ColWidth(8) = 550
        MSFlexGrid1.ColWidth(9) = 550
        MSFlexGrid1.ColWidth(10) = 5500
        
        End If

        Call FlexGridColumnColor(MSFlexGrid1, 10, &HFFFFC0)

Open in new window

0
Comment
Question by:al4629740
  • 9
  • 6
  • 2
17 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40330671
What error do you get?
What is PopulateFlexGrid? If it's a Sub or Function is it Private and in another form or module?
0
 

Author Comment

by:al4629740
ID: 40330828
variable not set
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40330838
So then I assume PopulateFlexGrid is a variable defined as Boolean. Where is it defined and how (Public? Private? Dim?) is it defined?

It might be easier if you could zip up and attach your project.
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:al4629740
ID: 40330881
Its not defined at all.

What should I put?
0
 

Author Comment

by:al4629740
ID: 40330882
What is the purpose of PoplulateFlexGrid
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40330895
What is the purpose of PoplulateFlexGrid
Without seeing your project I have no idea.

Please make a zip file from your project and attach it.
0
 

Author Comment

by:al4629740
ID: 40330905
Can I zip just one of the forms and send it.  Its a huge project
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40330916
No, the whole project is best. If the zip is too large to attach here then upload it to https://www.mediafire.com and post the resulting URL so that I can download the zip.
0
 

Author Comment

by:al4629740
ID: 40330932
My project has login information peppered through it since it is accessed over the internet.  Unfortunately I will have to blank those out for security purposes before sending it....
0
 

Author Comment

by:al4629740
ID: 40330952
I also realize that if I blank it out, then you won't be able to run the project since you won't be able to access the DB...
0
 

Author Comment

by:al4629740
ID: 40331015
I hope this suffices considering what I just said...
form.rtf
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40331054
I'm going out for a while but will get back to you later today.
0
 

Author Comment

by:al4629740
ID: 40331086
Ok
0
 
LVL 15

Expert Comment

by:ChloesDad
ID: 40331183
PopulateFlexGrid is only referenced once in the file and it is set to true

Do you use Option Explicit in your coding as this will stop you from having undeclared variables in your code. Is this code that you have written, or has it been copied from elsewhere?
0
 

Author Comment

by:al4629740
ID: 40331209
I copied the code from elsewhere. What purpose does populateflexgrid serve?
0
 
LVL 15

Expert Comment

by:ChloesDad
ID: 40331260
We don't know, it obviously did something in the other code, probably a flag to indicate that the grid should be populated.

This is the danger of copying code and then not fully understanding what you have copied and how it links into other features of the program.
0
 
LVL 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40331425
Your form.rtf attachment doesn't help very much so here's the best I can suggest.

In your project type Ctrl+F and in 'Find What:" enter "PopulateFlexGrid" and then select the 'Current Project' option and click 'Find Next' at least twice to see if there's more than one. If there is only one then just comment out the line that's giving you the problem.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

861 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