Solved

Code for handeling "No current record" error

Posted on 1998-09-30
14
176 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

809 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