Hi all.

SLX LAN v7.2.1.

The code below determines if the end user can have access to a pop menu when right clicking on the datagrid. I'm getting an "Either BOF or EOF is True" error. The issue is with the line where it sees if that record already has an entry, if it does then the end user can not see the pop menu when they right click. The problem is when it reaches a record that meets the requirements for the first 2 if statements, but errors in the third if statement.

How can I change my code to only allow when the current ticket doesn't already have an entry in the C_TicketReturns table?

Thank you in advance.

<p>Sub AXFormChange(Sender) 

Dim sql_Status
Dim rs_Status
Dim sql_Status1
Dim rs_Status1
 
sql_Status = "Select AREA,STATUSCODE FROM sysdba.TICKET Where TICKETID = '" & GetCurrentTicketID & "'"
Set rs_Status = Application.GetNewConnection.Execute(sql_Status) 

sql_Status1 = "SELECT TICKETID AS TicketID, COUNT(TICKETID) AS [Count] FROM  sysdba.C_TICKETRETURNS GROUP BY TICKETID HAVING   (COUNT(TICKETID) = 1) AND TICKETID ='" & GetCurrentTicketID & "'"
Set rs_Status1 = Application.GetNewConnection.Execute(sql_Status1)

 If rs_Status.Fields("AREA").Value <> "Returns" Then
    Datagrid1.PopupMenu.Items(0).Enabled = False
ElseIf rs_Status.Fields("STATUSCODE").Value = "k6UJ9A0003JZ" Then
   Datagrid1.PopupMenu.Items(0).Enabled = False
ElseIf rs_Status1.Fields("Count").Value = 1 Then
    Datagrid1.PopupMenu.Items(0).Enabled = False
Else
    Datagrid1.PopupMenu.Items(0).Enabled = True

End If

 

End Sub

Where are you setting or getting the value for:

GetCurrentTicketID ??

put a MSGBOX sql_Status just before you get the RS and prove it has a value.

That's a function I have in the script. And yes it does bring up the TicketID.

<p>Function GetCurrentTicketID
 

 

    GetCurrentTicketID = ""

    If Application.MainViews.Count > 0 Then

       If Not Application.MainViews.ActiveView Is Nothing Then

          If UCase(Application.MainViews.ActiveView.BaseTable) = "TICKET" Then 'DNL

             GetCurrentTicketID = Application.MainViews.ActiveView.CurrentID

          End If

       End If

    End If

 

End Function

Ok I got it to work by changing the count > 1 line of code to this:

ElseIf rs_Status1.EOF or rs_Status1.BOF Then

    Datagrid1.PopupMenu.Items(0).Enabled = True

The Main Problem here is that your SQL Statement may return no rows, and you should check the Number of Records returned before working on the Recordset.

Set rs_Status1 = Application.GetNewConnection.Execute(sql_Status1)

If rs_Status1.RecordCount > 1

'You have Data. Work with it

' Your code goes here

Else

' Do something if no records found

End If

By doing it this way, you don't try to Read Data when there is no Data returned.

* A Regular Count statement would always return one row, but this is a Group statement, so if there are no matching records, you would get no results.

Thanks!