Find a record in a Recordset (on the SQL Server)

[Ausburgh]

How can I find a record in a recordset (connected to the SQL Server via code)

I modified my code to look like:

Private Sub List1_Click()
Dim Template As String
Template = "[ERemark] = " & Chr$(34) & List1.List(List1.ListIndex) & Chr$(34)
rs.Find Template
End Sub

Result: the list was blank

In reality I would like to be able to search with 2 variables (one is a double and the other the description (a string)) - so I can generate a list of unique items.

Private Sub List1_Click()
Dim Template As String
Dim NumTemplate as Double
Template = "[ERemark] = " & Chr$(34) & List1.List(List1.ListIndex) & Chr$(34)
NumTemplate = "[TotalExpense] = " & List1.List(List1.ListIndex) 'I'm not sure this is correct
rs.Find Template & NumTemplate 'I'm certain this is wrong
End Sub

Thanks in advance

 

[royalcheese]

Can you not make a query with the to find a record in a record set ?

 

[Andrzejek]

If you are using ADODB recordset, try Filter it:

rs.Filter = "[ERemark] = 123 AND [TotalExpense] = 987"

Substitute 123 and 987 with the values from List1

To turn the filter off:

rs.Filter = ADODB.FilterGroupEnum.adFilterNone

HTH

---- Andy

 

[BobRodes]

You can't use multiple field references in Find. You can with Filter, which I generally prefer. Also, it's unlikely that you will get anything but a blank with yours, because SQL server doesn't use double quotes to delimit strings, but single ones. (You can set it so that it does, but it doesn't by default.) So

with List1
   rs.Filter = "[ERemark] = '" & .List(.ListIndex) & "'"
end with

should work, assuming that your current selection actually exists in the rs recordset.

HTH

Bob

 

[Ausburgh]

Thanks Bob for responding.

Unfortunately it didn't work - the list is still blank. I'm sure I messed up something.

Private Sub List1_Click()
Dim Template As String
Template = "[ERemark] = '" & List1.List(List1.ListIndex) & "'"
rs.Filter Template
'With List1
   'rs.Filter = "[ERemark] = '" & .List(.ListIndex) & "'"
'End With
End Sub

I'm not sure I understand when you say "assuming ... current selection actually exists in the rs recordset"

Here's my connection, perhaps this is where my problem is:

Private Sub Form_Load()
Dim x As Variant, NumRecs As Integer
Dim i As Integer, Bookmark As String

'Set and make the connection to the database.
    Set Conn = New ADODB.Connection
    'define the recordset access statement
    Dim strSQL As String
    strSQL = "SELECT * FROM Expense ORDER BY ExpenseID"
    Set rs = New ADODB.Recordset
        
    On Error GoTo Err_Exit
    
    Conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=dbJVC;Data Source=ABC-0000001"
    Conn.Open
    
    With rs
        .ActiveConnection = Conn
        .CursorLocation = adUseServer
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Source = strSQL
        .Open
    End With

Bookmark = rs.Bookmark
rs.MoveLast
NumRecs = rs.RecordCount
rs.MoveFirst

List1.Clear

For i = 0 To NumRecs - 1
    x = rs.Fields("ERemark").Value
    If IsNull(x) Then x = ""
    rs.MoveNext
    List1.AddItem x
Next i

rs.Bookmark = Bookmark

Exit Sub

Err_Exit:
    ADOerror
End Sub

Master teach me Kung-FU. I appreciate your help.

 

[JerryKlmns]

...
Bookmark = rs.Bookmark
While Not rs.EOF
x = rs.Fields("ERemark").Value & ""
If Len(x)>0 Then List1.AddItem x
rs.MoveNext
Wend
rs.Bookmark = Bookmark
...

 

[BobRodes]

Your last looks ok, although Jerry's code is an improvement over your RecordCount solution.

Let's take a step backwards.

Can you please write a short paragraph that explains what you're attempting to accomplish? Once we have that, I'm sure we can work out the solution. My feeling is that things are getting a bit confused.

Thanks,

bob

 

[JerryKlmns]

I think he wants to view all remarks entered in that table, when selecting one find the record referring to. But what about 2 records having the same remark? May be a grid showing the records?

Having said that, the strSQL for the List1 control, should be:
"SELECT DISTINCT ERemark FROM Expense WHERE ERemark Is Not Null"

And the according strSQL for the grid control should be :
"SELECT * FROM Expense WHERE ERemark='" & List1.List(List1.ListIndex) & "'"

Ausburgh ?????

 

[BobRodes]

At this point, I dont' follow what the OP is trying to do at all. If he wants to explain, I'll be glad to attempt to solve his problem.

Bob

 

[Ausburgh]

Thanks for your responses ... I just got back from my vacation last night (5/25 - 6/2 - as you can image I'm exhausted ... not that anyone cares!).


In any case, Jerry's explanation is correct ... the problem is that there will be multiple records that do have the same remark (my search field) ... which is why I need to explore a way to find and pull up the correct record.

I did try Jerry's code (on the 24th before I had to run to catch my flight) but I couldn't get it to work.

Thanks again guys.

 

[BobRodes]

Feel free to write that short paragraph....

 

[CaptainD]

Would something like this work for you.

Select Table1.* From Table1 Where 'YourFirstValue' IN([SearchField1], [SeaqrchField2]) OR 'YourSecondValue' IN ([SearchField1], [SearchField2])