Solved

Code for handeling "No current record" error

Posted on 1998-09-30
14
167 Views
Last Modified: 2010-04-30
I am populating a flexgrid using records based on Select from criteria.  How can I handle errors generated from there being no records in the query?

My Code is as follows:


Private Sub FG1_GotFocus()
   
   
   
    Dim S As String

    FG1.Font.Name = "arial"
    FG1.Font.Size = "8"
    FG1.Font.Bold = True
    FG1.Cols = 11
    FG1.ColWidth(8) = 0
    FG1.ColWidth(9) = 0
    FG1.ColWidth(10) = 0
   
    S = "          |<Code                 |<Program Title                                               |<Cost      |<Sched    |<Perf       |<Remarks                                                 |<Updated      "
    FG1.FormatString = S$
    FG1.WordWrap = True

    Dim ncostcnt1 As Long
    Dim nschedcnt As Long
    Dim nperfcnt As Long
    Dim nColNum As Long
    Dim sPicFile As String


    For ncostcnt1 = 1 To FG1.Rows - 1
    nColNum = 3
    sPicFile = ""
       
             Select Case Data1.Recordset.Fields("cost")
           Case "Red"
             Select Case Data1.Recordset.Fields("coststat_2")
               Case "Red"
                 sPicFile = "redred.gif"
               Case "Yellow"
                 sPicFile = "redyel.gif"
               Case "Green"
                 sPicFile = "redgrn.gif"
               Case Else
                 sPicFile = "redball.gif"
             End Select
               Case "Yellow"
             Select Case Data1.Recordset.Fields("coststat_2")
               Case "Red"
                 sPicFile = "yelred.gif"
               Case "Yellow"
                 sPicFile = "yelyel.gif"
               Case "Green"
                 sPicFile = "yelgrn.gif"
               Case Else
                 sPicFile = "yelball.gif"
             End Select
           Case "Green"
             Select Case Data1.Recordset.Fields("coststat_2")
               Case "Red"
                 sPicFile = "grnred.gif"
               Case "Yellow"
                 sPicFile = "grnyel.gif"
               Case "Green"
                 sPicFile = "grngrn.gif"
               Case Else
                 sPicFile = "grnball.gif"
             End Select
        Case Else
            sPicFile = "white.gif"
         End Select
         
    Data1.Recordset.MoveNext
   
         sPicFile = "c:\program status tracking Database\" & sPicFile
            If sPicFile <> "" Then
                FG1.Col = nColNum
                FG1.Row = ncostcnt1
                Set FG1.CellPicture = LoadPicture(sPicFile)
                FG1.TextMatrix(ncostcnt1, nColNum) = ""
            End If
    Next ncostcnt1
    If Not FrmProgData.Data1.Recordset.BOF Then
    Data1.Recordset.MoveFirst
    For nschedcnt1 = 1 To FG1.Rows - 1
    nColNum = 4
    sPicFile = ""
       
             Select Case Data1.Recordset.Fields("sched")
           Case "Red"
             Select Case Data1.Recordset.Fields("schedstat_2")
               Case "Red"
                 sPicFile = "redred.gif"
               Case "Yellow"
                 sPicFile = "redyel.gif"
               Case "Green"
                 sPicFile = "redgrn.gif"
               Case Else
                 sPicFile = "redball.gif"
             End Select
               Case "Yellow"
             Select Case Data1.Recordset.Fields("schedstat_2")
               Case "Red"
                 sPicFile = "yelred.gif"
               Case "Yellow"
                 sPicFile = "yelyel.gif"
               Case "Green"
                 sPicFile = "yelgrn.gif"
               Case Else
                 sPicFile = "yelball.gif"
             End Select
           Case "Green"
             Select Case Data1.Recordset.Fields("schedstat_2")
               Case "Red"
                 sPicFile = "grnred.gif"
               Case "Yellow"
                 sPicFile = "grnyel.gif"
               Case "Green"
                 sPicFile = "grngrn.gif"
               Case Else
                 sPicFile = "grnball.gif"
             End Select
        Case Else
            sPicFile = "white.gif"
         End Select
     
     
         sPicFile = "c:\program status tracking Database\" & sPicFile
            If sPicFile <> "" Then
                FG1.Col = nColNum
                FG1.Row = nschedcnt1
                Set FG1.CellPicture = LoadPicture(sPicFile)
                FG1.TextMatrix(nschedcnt1, nColNum) = ""
            End If
       
    Data1.Recordset.MoveNext
   
    Next nschedcnt1
   
    Data1.Recordset.MoveFirst
    For nperfcnt1 = 1 To FG1.Rows - 1
    nColNum = 5
    sPicFile = ""
       
             Select Case Data1.Recordset.Fields("perf")
           Case "Red"
             Select Case Data1.Recordset.Fields("perfstat_2")
               Case "Red"
                 sPicFile = "redred.gif"
               Case "Yellow"
                 sPicFile = "redyel.gif"
               Case "Green"
                 sPicFile = "redgrn.gif"
               Case Else
                 sPicFile = "redball.gif"
             End Select
               Case "Yellow"
             Select Case Data1.Recordset.Fields("perfstat_2")
               Case "Red"
                 sPicFile = "yelred.gif"
               Case "Yellow"
                 sPicFile = "yelyel.gif"
               Case "Green"
                 sPicFile = "yelgrn.gif"
               Case Else
                 sPicFile = "yelball.gif"
             End Select
           Case "Green"
             Select Case Data1.Recordset.Fields("perfstat_2")
               Case "Red"
                 sPicFile = "grnred.gif"
               Case "Yellow"
                 sPicFile = "grnyel.gif"
               Case "Green"
                 sPicFile = "grngrn.gif"
               Case Else
                 sPicFile = "grnball.gif"
             End Select
        Case Else
            sPicFile = "white.gif"
           


         End Select
         
         sPicFile = "c:\program status tracking Database\" & sPicFile
            If sPicFile <> "" Then
                FG1.Col = nColNum
                FG1.Row = nperfcnt1
                Set FG1.CellPicture = LoadPicture(sPicFile)
                FG1.TextMatrix(nperfcnt1, nColNum) = ""
            End If
       
    Data1.Recordset.MoveNext
   
    Next nperfcnt1
   
   
    Call fixflex
End Sub
0
Comment
Question by:clarwc
  • 10
  • 4
14 Comments
 
LVL 3

Accepted Solution

by:
vmano earned 200 total points
ID: 1437366
Replace all your For..Next loops with this:

Do While not Data1.RecordSet.EOF
  FG1.Rows = FG1.Rows +1  
  'code here to populate the grid
 
  Data1.RecordSet.MoveNext
Loop

let me know if this helps
vmano
0
 
LVL 3

Expert Comment

by:vmano
ID: 1437367
Ofcourse you have to move to the fisrst record like this
Data1.RecordSet.MoveFirst after each Do..Loop, for processing the next Do..Loop.
 
0
 

Author Comment

by:clarwc
ID: 1437368
I'm getting confused as to what goes where.....
0
 
LVL 3

Expert Comment

by:vmano
ID: 1437369
OK Clarwc, I will replace on of your For..Next loop with Do..Loop
Here is your code:

For nschedcnt1 = 1 To FG1.Rows - 1
    nColNum = 4
    sPicFile = ""
  Select Case Data1.Recordset.Fields("sched")
      Case "Red"
           Select Case Data1.Recordset.Fields("schedstat_2")
                Case "Red"
                    sPicFile = "redred.gif"
                Case "Yellow"
                     sPicFile = "redyel.gif"
                Case "Green"
                     sPicFile = "redgrn.gif"
                Case Else
                     sPicFile = "redball.gif"
           End Select
    Data1.Recordset.MoveNext
     
    Next nschedcnt1

0
 
LVL 3

Expert Comment

by:vmano
ID: 1437370
OK Clarwc, I will replace on of your For..Next loop with Do..Loop
Here is your code:

For nschedcnt1 = 1 To FG1.Rows - 1
    nColNum = 4
    sPicFile = ""
  Select Case Data1.Recordset.Fields("sched")
      Case "Red"
           Select Case Data1.Recordset.Fields("schedstat_2")
                Case "Red"
                    sPicFile = "redred.gif"
                Case "Yellow"
                     sPicFile = "redyel.gif"
                Case "Green"
                     sPicFile = "redgrn.gif"
                Case Else
                     sPicFile = "redball.gif"
           End Select
    Data1.Recordset.MoveNext
     
    Next nschedcnt1

0
 
LVL 3

Expert Comment

by:vmano
ID: 1437371
OK Clarwc, I will replace on of your For..Next loop with Do..Loop
Here is your code:

For nschedcnt1 = 1 To FG1.Rows - 1
    nColNum = 4
    sPicFile = ""
  Select Case Data1.Recordset.Fields("sched")
      Case "Red"
           Select Case Data1.Recordset.Fields("schedstat_2")
                Case "Red"
                    sPicFile = "redred.gif"
                Case "Yellow"
                     sPicFile = "redyel.gif"
                Case "Green"
                     sPicFile = "redgrn.gif"
                Case Else
                     sPicFile = "redball.gif"
           End Select
    Data1.Recordset.MoveNext
     
    Next nschedcnt1

0
 
LVL 3

Expert Comment

by:vmano
ID: 1437372
I am sorry. I just pressed the subitt button without completing.

OK here you go...
Here is your code:

For nschedcnt1 = 1 To FG1.Rows - 1
    nColNum = 4
    sPicFile = ""
  Select Case Data1.Recordset.Fields("sched")
      Case "Red"
           Select Case Data1.Recordset.Fields("schedstat_2")
                Case "Red"
                    sPicFile = "redred.gif"
                Case "Yellow"
                     sPicFile = "redyel.gif"
                Case "Green"
                     sPicFile = "redgrn.gif"
                Case Else
                     sPicFile = "redball.gif"
           End Select
        'and you have other Case statements for Yello, Green.....
        Data1.Recordset.MoveNext
    Next nschedcnt1

Now here is how you replace.

Do While Not Data1.RecordSet.EOF
    FG1.Rows = FG1.Rows + 1
    nColNum = 4
    sPicFile = ""
    Select Case Data1.Recordset.Fields("sched")
      Case "Red"
           Select Case Data1.Recordset.Fields("schedstat_2")
                Case "Red"
                    sPicFile = "redred.gif"
                Case "Yellow"
                     sPicFile = "redyel.gif"
                Case "Green"
                     sPicFile = "redgrn.gif"
                Case Else
                     sPicFile = "redball.gif"
           End Select
    'and you have other Case statements for Yello, Green.....
    End Select
    Data1.RecordSet.MoveNext
Loop

Data.Recordset.MoveFirst

'replace your For..Loop with Do..Loop as shown above.

let me know if this helps,
vmano
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:clarwc
ID: 1437373
Using the code this way I am getting a Loop without do error

    Data1.Recordset.MoveFirst
    Do While Not Data1.Recordset.EOF
          FG1.Rows = FG1.Rows + 1
          nColNum = 4
          sPicFile = ""
       
             Select Case Data1.Recordset.Fields("sched")
           Case "Red"
             Select Case Data1.Recordset.Fields("schedstat_2")
               Case "Red"
                 sPicFile = "redred.gif"
               Case "Yellow"
                 sPicFile = "redyel.gif"
               Case "Green"
                 sPicFile = "redgrn.gif"
               Case Else
                 sPicFile = "redball.gif"
             End Select
               Case "Yellow"
             Select Case Data1.Recordset.Fields("schedstat_2")
               Case "Red"
                 sPicFile = "yelred.gif"
               Case "Yellow"
                 sPicFile = "yelyel.gif"
               Case "Green"
                 sPicFile = "yelgrn.gif"
               Case Else
                 sPicFile = "yelball.gif"
             End Select
           Case "Green"
             Select Case Data1.Recordset.Fields("schedstat_2")
               Case "Red"
                 sPicFile = "grnred.gif"
               Case "Yellow"
                 sPicFile = "grnyel.gif"
               Case "Green"
                 sPicFile = "grngrn.gif"
               Case Else
                 sPicFile = "grnball.gif"
             End Select
          Data1.Recordset.MoveNext
      Loop

      Data.Recordset.MoveFirst
        Case Else
            sPicFile = "white.gif"
         End Select
     
     
ecordset.MoveFirst
0
 
LVL 3

Expert Comment

by:vmano
ID: 1437374
I am not sure as to what you are saying about the error. It might be that you are missing one of the "Loop" statements. just check all your "Do..Loop" loops  and make sure that for every "Do" there is a "Loop" statement.

vmano
0
 

Author Comment

by:clarwc
ID: 1437375
There is a loop for every do statement. 3 do while not statements and 3 loop statements.

I am increasing the points to 200 if you would please modify the code I originally posted so that I can juct cut and paste the entire sub-routine.
0
 
LVL 3

Expert Comment

by:vmano
ID: 1437376
OK. just hang in there. i am going again through your original code.

0
 
LVL 3

Expert Comment

by:vmano
ID: 1437377
OK. one question here -- do you hard code the number of rows on the grid or should they change accordingly.????

0
 
LVL 3

Expert Comment

by:vmano
ID: 1437378
OK Clarwc, the following code should work on all conditions.Please cut and paste the code from here.

    'Code starts here
    Dim S           As String
    Dim i           As Integer
    Dim nColNum     As Long
    Dim sPicFile    As String
   
    'paste the code here for formatting the grid.    
   
    i = 0
    Do While Not Data1.Recordset.EOF
        i = i + 1
        FG1.Rows = FG1.Rows + 1
       
        'for third coulmn
        nColNum = 3
        sPicFile = ""
             
        Select Case Data1.Recordset.Fields("cost")
            Case "Red"
                Select Case Data1.Recordset.Fields("coststat_2")
                   Case "Red"
                     sPicFile = "redred.gif"
                   Case "Yellow"
                     sPicFile = "redyel.gif"
                   Case "Green"
                     sPicFile = "redgrn.gif"
                   Case Else
                     sPicFile = "redball.gif"
                 End Select
            Case "Yellow"
                 Select Case Data1.Recordset.Fields("coststat_2")
                   Case "Red"
                     sPicFile = "yelred.gif"
                   Case "Yellow"
                     sPicFile = "yelyel.gif"
                   Case "Green"
                     sPicFile = "yelgrn.gif"
                   Case Else
                     sPicFile = "yelball.gif"
                 End Select
            Case "Green"
                 Select Case Data1.Recordset.Fields("coststat_2")
                   Case "Red"
                     sPicFile = "grnred.gif"
                   Case "Yellow"
                     sPicFile = "grnyel.gif"
                   Case "Green"
                     sPicFile = "grngrn.gif"
                   Case Else
                     sPicFile = "grnball.gif"
                 End Select
            Case Else
                sPicFile = "white.gif"
        End Select
             
        sPicFile = "c:\program status tracking Database\" & sPicFile
        If sPicFile <> "" Then
            FG1.Col = nColNum
            FG1.Row = i
            Set FG1.CellPicture = LoadPicture(sPicFile)
            FG1.TextMatrix(i, nColNum) = ""
        End If

        'for fourth column
        nColNum = 4
        sPicFile = ""
         
        Select Case Data1.Recordset.Fields("sched")
               Case "Red"
                 Select Case Data1.Recordset.Fields("schedstat_2")
                   Case "Red"
                     sPicFile = "redred.gif"
                   Case "Yellow"
                     sPicFile = "redyel.gif"
                   Case "Green"
                     sPicFile = "redgrn.gif"
                   Case Else
                     sPicFile = "redball.gif"
                 End Select
            Case "Yellow"
                 Select Case Data1.Recordset.Fields("schedstat_2")
                   Case "Red"
                     sPicFile = "yelred.gif"
                   Case "Yellow"
                     sPicFile = "yelyel.gif"
                   Case "Green"
                     sPicFile = "yelgrn.gif"
                   Case Else
                     sPicFile = "yelball.gif"
                 End Select
            Case "Green"
                 Select Case Data1.Recordset.Fields("schedstat_2")
                   Case "Red"
                     sPicFile = "grnred.gif"
                   Case "Yellow"
                     sPicFile = "grnyel.gif"
                   Case "Green"
                     sPicFile = "grngrn.gif"
                   Case Else
                     sPicFile = "grnball.gif"
                 End Select
            Case Else
                sPicFile = "white.gif"
        End Select
        sPicFile = "c:\program status tracking Database\" & sPicFile
        If sPicFile <> "" Then
                FG1.Col = nColNum
                FG1.Row = i
                Set FG1.CellPicture = LoadPicture(sPicFile)
                FG1.TextMatrix(i, nColNum) = ""
        End If
       
        'for fifth column
        nColNum = 5
        sPicFile = ""
         
        Select Case Data1.Recordset.Fields("perf")
               Case "Red"
                 Select Case Data1.Recordset.Fields("perfstat_2")
                   Case "Red"
                     sPicFile = "redred.gif"
                   Case "Yellow"
                     sPicFile = "redyel.gif"
                   Case "Green"
                     sPicFile = "redgrn.gif"
                   Case Else
                     sPicFile = "redball.gif"
                 End Select
            Case "Yellow"
                 Select Case Data1.Recordset.Fields("perfstat_2")
                   Case "Red"
                     sPicFile = "yelred.gif"
                   Case "Yellow"
                     sPicFile = "yelyel.gif"
                   Case "Green"
                     sPicFile = "yelgrn.gif"
                   Case Else
                     sPicFile = "yelball.gif"
                 End Select
            Case "Green"
                 Select Case Data1.Recordset.Fields("perfstat_2")
                   Case "Red"
                     sPicFile = "grnred.gif"
                   Case "Yellow"
                     sPicFile = "grnyel.gif"
                   Case "Green"
                     sPicFile = "grngrn.gif"
                   Case Else
                     sPicFile = "grnball.gif"
                 End Select
            Case Else
                sPicFile = "white.gif"
        End Select
         
        sPicFile = "c:\program status tracking Database\" & sPicFile
        If sPicFile <> "" Then
                FG1.Col = nColNum
                FG1.Row = i
                Set FG1.CellPicture = LoadPicture(sPicFile)
                FG1.TextMatrix(i, nColNum) = ""
        End If
       
        Data1.Recordset.MoveNext
    Loop

let me know if you have more questions on this code.
0
 

Author Comment

by:clarwc
ID: 1437379
That did it.  Sorry to take so much of your time.  I am very new at this.

Warren
0

Featured Post

What Is Threat Intelligence?

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

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
vba find the last empty column 10 81
Validating VB6 Function 19 50
Determine Range to Select 5 35
Adding to a VBA? 6 49
Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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 process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

744 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

10 Experts available now in Live!

Get 1:1 Help Now