Current record is not the correct one --Flexibility 2

[atlanticdit]

Hi,

Fairly new to using VB6 without using data controls. I was able to code a connection to one of our Macola SQL Server 2000 database through Flexibility 7.6.3c (we're using Macola 7.6.3c). I did this on our test company first. In the CustNo_Lose_Focus event in Order Entry | Enter Orders screen, I have it looking at a lfield in the ARCUSFIL_SQL table to see if it has a certain value. If so, then a message box will pop up with an alert.

Within the test company, the code does exactly what it is supposed to. I then went into our live company and the coding, references, etc. were already there. I made sure to double check to see if there were any differences between the test and live companies or databases and found none. When I ran the same code in the live company, I did not get the result I expected when the customer number lost focus. Whereas in the test company, the record moved to the record referenced by the value put into the Customer Number field within the Enter Orders screen, the live company stayed at the first record in the ARCUSFIL_SQL table. I know this because I have MsgBox's popping up with the information.

Any ideas for why the record will automatically move to the record the customer number references in the customer number box in the test company but not in the live company? I'm using Supervisor as my username. I was, also, the only one in the test company when running the code while I was one of many in the live company (not sure if that's the issue). If you don't know why this is happening, could you please tell me how to programically move to a record referenced by the Customer Number field. Thanks.

Private Sub CustNo_LoseFocus(AllowLoseFocus As Boolean)
    
    Dim connString As String
    Dim connARCUSFIL As New ADODB.Connection
    Dim rsARCUSFIL As New ADODB.Recordset
    Dim strCustNo As String
    
    
    
    connString = "Provider=SQLOLEDB.1;Persist Security Info=False;Data Source=servername;Initial Catalog = database;User ID=xxx;password=xxx"
    
    With connARCUSFIL
        .ConnectionString = connString
        .Open
    End With
    
    strCustNo = Me.CustNo.Text
    MsgBox strCustNo
       
    rsARCUSFIL.Open "Select * FROM ARCUSFIL_SQL", connARCUSFIL
    
    MsgBox rsARCUSFIL.Fields(0).Value
    If rsARCUSFIL.Fields(79).Value = "Y" Then
        MsgBox "This Customer Requires RoHS parts!"
        rsARCUSFIL.Close
    Else
        rsARCUSFIL.Close
    End If
    
End Sub

 

[NEmacGuy]

Would you want to include a where statement of where the cust_no = '" & StrCustNo & "'

Does your connection need to update records, if not I would use a readonly connection. Some of the programmer can tell you why.

 

[abaldwin]

NEmacGuy is correct to point out that you need a where clause. The SQL is returning all records and you are simply looking at the first record returned.

It is also VERY important that at the end of the code you kill all your connection information. You are correct to close them but to remove the object from memory would also require a

conArcusfil.close
set conarcusfil = nothing
set rsarcusfil = nothing

This will kill the two created objects. It does not effect your original problem of not returning the proper record but will free up memory when the code is completed.

How familiar are you with SQL statements?


Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[atlanticdit]

Thank you NEmacguy and Andy. I had put in a WHERE clause originally but, obviously, had not coded it correctly. I don't think I was putting in the &'s. I used your nothing statements too, Andy, since we have had issues of slowness.