Solved

Code for handeling "No current record" error

Posted on 1998-09-30
14
180 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

 
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
 

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

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.

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

821 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