VB Script help

[Beerman3436]

I am trying to use this code to print in a report but it give me the following error.

The Value you entered isn't valid for this field

It is because I am getting the top 3 values from the query and only 1 is showing up on the report. I need the other fields to just be blank if there is only 1 value passed.

Here is the code:

Set rst = New ADODB.Recordset
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CodeProject.Connection
.Source = "Select TOP 3 [District Name],[OLDEoy],[NewEOY] From qryCover WHERE [Star2] = '*' ORDER BY [District Name]"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open , , adCmdText
End With
If IsNull(rst![District Name]) Then
Me.LargeOver1 = Null
Else
Me.LargeOver1 = rst![District Name]
Me.Large1 = rst!OldEOY
Me.New1 = rst!NewEOY
End If
rst.MoveNext
If IsNull(rst![District Name]) Then
Me.LargeOver2 = Null
Else
Me.LargeOver2 = rst![District Name]
Me.Large2 = rst!OldEOY
Me.New2 = rst!NewEOY
End If
rst.MoveNext
If IsNull(rst![District Name]) Then
Me.LargeOver3 = Null
Else
Me.LargeOver3 = rst![District Name]
Me.Large3 = rst!OldEOY
Me.New3 = rst!NewEOY
End If
rst.Close
Set rst = Nothing

 

[dcx693]

First, take out one of those 2 set commands at the start. Then, maybe you can use a rst.RecordCount to see how many records are returned so you don't try to assign non-existent values to your report fields.

 

[Beerman3436]

VB Script Help

Can you modify the code for me and repost it?

 

[namliam]

possible solution:

    Set rst = New ADODB.Recordset
'Notice the Like in your Source
'allthough i cannot see the logic of it...
    With rst
        .ActiveConnection = CodeProject.Connection
        .Source = "Select TOP 3 [District Name],[OLDEoy],[NewEOY] " & _
                  "From qryCover " & _
                  "WHERE [Star2] Like '*' ORDER BY [District Name]"
        .LockType = adLockOptimistic
        .CursorType = adOpenKeyset
        .Open , , adCmdText
    End With
    If IsNull(rst![District Name]) Then
        Me.LargeOver1 = Null
    Else
        Me.LargeOver1 = rst![District Name]
        Me.Large1 = rst!OldEOY
        Me.New1 = rst!NewEOY
    End If
    rst.MoveNext
    If IsNull(rst![District Name]) Then
        Me.LargeOver2 = Null
    Else
        Me.LargeOver2 = rst![District Name]
        Me.Large2 = rst!OldEOY
        Me.New2 = rst!NewEOY
    End If
    rst.MoveNext
    If IsNull(rst![District Name]) Then
        Me.LargeOver3 = Null
    Else
        Me.LargeOver3 = rst![District Name]
        Me.Large3 = rst!OldEOY
        Me.New3 = rst!NewEOY
    End If
    rst.Close
    Set rst = Nothing

Possible improvement:

    Set rst = New ADODB.Recordset
    Dim I As Integer
    With rst
        .ActiveConnection = CodeProject.Connection
        .Source = "Select TOP 3 [District Name],[OLDEoy],[NewEOY] " & _
                  "From qryCover " & _
                  "WHERE [Star2] Like '*' ORDER BY [District Name]"
        .LockType = adLockOptimistic
        .CursorType = adOpenKeyset
        .Open , , adCmdText
    End With
    For I = 1 To 3
        If IsNull(rst![District Name]) Then
            Me.Control("LargeOver" & I) = Null
        Else
            Me.Control("LargeOver" & I) = rst![District Name]
            Me.Control("Large" & I) = rst!OldEOY
            Me.Control("New" & I) = rst!NewEOY
        End If
        rst.MoveNext
    Next I
    rst.Close
    Set rst = Nothing

Hope it helps you

Regards

The Mailman