Solved

variable not set

Posted on 2014-09-18
17
82 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 45

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 45

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
 

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 45

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 45

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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 45

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 45

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

743 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

11 Experts available now in Live!

Get 1:1 Help Now