How do I make a record search in Access VB? Not VB6...

[TSur]

Here is what I do for VB but how would I write this if I was puting this in a button in Access itself?



Set CN = New ADODB.Connection
CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & App.Path & "\Database\Database.mdb"
Set SetConnection = CN
ConnectVar = True


strSearchFor = List1.Text

Do while rsPriceList Not EOF

Set rsPriceList = New ADODB.Recordset
rsPriceList.Open "SELECT * FROM PriceList WHERE PriceID = " & strSearchFor & "", CN, , adLockOptimistic

rsPriceList.Fields("Name").Value = NameTxt.Text


rsPriceList.next

loop

[TSur]

I figure it is different since you are already in that database so do you need to connect to it?

If not that will change this right?

Set rsPriceList = New ADODB.Recordset
rsPriceList.Open "SELECT * FROM PriceList WHERE PriceID = " & strSearchFor & "", CN, , adLockOptimistic

[Bruce Fox]

If your Form is assigned to a Table, you can use Me.RecordSet.MoveNext etc.

Or,
You can connect (similar to VB6) like:

Dim sql As String
    Dim rs As New ADODB.Recordset
 
On Error GoTo Err_Handler
 
  
  sql = "SELECT * FROM PriceList WHERE PriceID = " & strSearchFor & ""
  rsPriceList.Open sql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
  Do [b]Until[/b] rsPriceList.EOF    'No need to test for EOF / BOF, as Do Until will perform the same thing 
    rsPriceList.Fields("Name").Value = NameTxt.Text 
    rsPriceList.MoveNext
  Loop
  rs.Close
  Set rs = Nothing
 
'Your Error Handler Here

[TSur]

Thanks for your help! Today and lastnight.

I am doing a project in just access and I haven't really used code in just that before...

[Bruce Fox]

No probs,

Just note that I modified my original post to better reflect your code.



Normaly you need to test for EOF and BOF when moving a Recordset,
However, using Do Until rsPriceList.EOF seems to do a great job



Bruce.