Simple VBA to SQL problem

[UnsolvedCoding]

Hey all -

I am working in Excel and am trying to get a simple indicator to work and am having a brain fry. All I want is to be able to determine if the Status_CD is P and based on that make a decision. For the life of me can't remember how.


' SQL Code
sSQL = "Select 1 from ER_CB where STATUS_CD = 'P' and CB_NBR ='" & sCurrCBNbr & "' "

' Connect to SQL
SQL_Connection
If Warning_Flag = 1 Then Exit Sub

' Create Record set
Recordset_Connection
If Warning_Flag = 1 Then Exit Sub

If rsERDB("STATUS_CD") <> "P" Then Cells(i, "L").Value = "Not Pending"


I have tried While Not rsERDB.EOF and While rsERDB.EOF, put in a cell value as listed above, using a msgbox, changed SQL etc but am simply stuck.

Anyone know what I am missing? I know its simple but it escapes me at the moment.

 

[PHV]

So, I guess the SQL should be like this:
sSQL = "Select STATUS_CD from ER_CB where CB_NBR ='" & sCurrCBNbr & "' "

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[UnsolvedCoding]

I will have to check if it pulls any other status's but it looks promising.

 

[Andrzejek]

I don't see anywhere where your connection to your database is established and where your [tt]rsERDB[/tt] is declared or populated.

Have fun.

---- Andy

 

[UnsolvedCoding]

After doing research it was deteremined that there could be more then one status_cd so the code was re-written it now looks like this -

i = 2

Do

' Select starting point
sCurrCBNbr = Cells(i, "F").Value

' Connect to SQL
SQL_Connection
If Warning_Flag = 1 Then Exit Sub

' Establish Recordset with active connection
Set rsERDB = New ADODB.Recordset
rsERDB.ActiveConnection = cnERDB
rsERDB.CursorLocation = adUseClient

' SQL Code
sSQL = "Select Status_CD from ER_CB where replace(cb_nbr,'P','') = replace('" & sCurrCBNbr & "','P','')"

rsERDB.Open sSQL, cnERDB, 3, 1, 1

While Not rsERDB.EOF

' Verify if Status_CD is P
If rsERDB("STATUS_CD") <> "P" Then

MsgBox sCurrCBNbr & " is not pending. Cell will turn blue."

' Color the cell light blue
Not_Pending_Color

End If

' Go to the next record in SQL
rsERDB.MoveNext

Wend

' Go down one row
i = i + 1

' If the cell is empty exit the loop
If IsEmpty(Cells(i, "F")) Then Exit Do

Loop

 

[Andrzejek]

I don't know how many rows you have to process, but if you have 1000 rows, you create 1000 new record sets with 1000 connections to your data base. Is that what you wanted?

How about (code not tested)'

[green]
' Connect to SQL[/green]
SQL_Connection
If Warning_Flag = 1 Then Exit Sub
[green]
' Establish Recordset with active connection[/green]
Set rsERDB = New ADODB.Recordset
rsERDB.ActiveConnection = cnERDB
rsERDB.CursorLocation = adUseClient

[green]' SQL Code[/green]
sSQL = "Select Status_CD, cb_nbr from ER_CB "[green]
'where replace(cb_nbr,'P','') = replace('" & sCurrCBNbr & "','P','')"[/green]

rsERDB.Open sSQL, cnERDB, 3, 1, 1

i = 2

Do
[green]    ' Select starting point[/green]
    sCurrCBNbr = Cells(i, "F").Value
[blue]
rsERDB.Filter = "Some filter here"
[/blue]
    While Not rsERDB.EOF
[green]        ' Verify if Status_CD is P[/green]
        If rsERDB("STATUS_CD") <> "P" Then
            MsgBox sCurrCBNbr & " is not pending. Cell will turn blue."
[green]          ' Color the cell light blue[/green]
            Not_Pending_Color
        End If
[green]        ' Go to the next record in SQL[/green]
        rsERDB.MoveNext
    Wend
[blue]
rsERDB.Filter = ADODB.FilterGroupEnum.adFilterNone
[/blue] 
[green]    ' Go down one row[/green]
    i = i + 1
[green]    ' If the cell is empty exit the loop[/green]
    If IsEmpty(Cells(i, "F")) Then Exit Do
Loop
[blue]
rsERDB.Close
Set rsERDB = Nothing
cnERDB.Close
Set cnERDB = Nothing
[/blue]

Have fun.

---- Andy