[RESOLVED] Beacon's Tutorial - Part 5 ????

[JohnSavage]

Continuing my quest to move away from the evil of bound controls I have hit another brick wall



Using bound controls have some code

AdodcNeedles.RecordSource = "SELECT * FROM Needles WHERE NeedleName = '" & Combo8.Text & "'"
AdodcNeedles.Refresh
RefA = AdodcNeedles.Recordset.Fields(2).Value
RefB = AdodcNeedles.Recordset.Fields(3).Value
RefC = AdodcNeedles.Recordset.Fields(4).Value
RefD = AdodcNeedles.Recordset.Fields(5).Value
RefE = AdodcNeedles.Recordset.Fields(6).Value
RefF = AdodcNeedles.Recordset.Fields(7).Value
RefG = AdodcNeedles.Recordset.Fields(8).Value

This code currently goes to a line in a database table and then gets the values of the other fields in that line.
Now I need to change this code to something like this

Set RS11 = New ADODB.Recordset
 RS11.Open "Needles", ConnectNeedles, adOpenKeyset, adLockPessimistic, adCmdTable

RS11.???? = "SELECT * FROM Needles WHERE NeedleName = '" & Combo8.Text & "'"
RS11.Update

RefA = RS11.Fields(2).Value
RefB = RS11.Fields(3).Value
RefC = RS11.Fields(4).Value
RefD = RS11.Fields(5).Value
RefE = RS11.Fields(6).Value
RefF = RS11.Fields(7).Value
RefG = RS11.Fields(8).Value

Its just that I cannot find an equivalent action for the select statement, any ideas

[JohnSavage]

Now it’s getting scary, I fixed this myself

RS11.Find "[NeedleName] = '" & Combo8.Text & "'"

[si_the_geek]

I'd recommend still using the Select statement, as it will be quicker and take less memory.

Instead of opening a full table like this:

 RS11.Open "Needles", ConnectNeedles, adOpenKeyset, adLockPessimistic, adCmdTable

..put your SQL statement into a variable, and change the open line, like this:

dim strSQL as String
 strSQL = "SELECT * FROM Needles WHERE NeedleName = '" & Combo8.Text & "'" 
 RS11.Open strSQL, ConnectNeedles, adOpenKeyset, adLockPessimistic, adCmdText

(note that the .Update line is for writing and Edits you have made to the fields, so is not relevant where you have it)

[JohnSavage]

Many Thanks