Changing my ADO Recordsource

[sum1unimportant]

I have created a form that displays fields from a table. I've done this using an ADO controller that has its connection string as Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\...\database.mdb;Persist Security Info=False. And it has the recordsource of "SELECT * FROM Employees".

Then I've created text boxes that have this ado controller as a data source and then then refer to the fields, standard.

What I want to do it on the click of a button change the recordsource of the ado controller so that it will display different records...I want to change the recordsource to "SELECT * FROM Employees WHERE Name LIKE ""%" & txtEmpSearch.Text & "%"";"

I can enter this recordsource in manually and the form loads and shows the correct records, but nothing happens when running the following code from a button:

Private Sub cmdSearch_Click()
adoEmployee.RecordSource = "SELECT * FROM Employees WHERE Name LIKE ""%" & txtEmpSearch.Text & "%"";"
End Sub


It appears the recordsource is unchanged....what do I need to do here? Somehow reload the form? How? If I am going about this the wrong way what do I need to do?



Thanks very much in advance.

[sum1unimportant]

plllease help, still insanely stuck on this

[Veritas2.0]

Personally I would recommed getting rid of the Data Control. Way to cumbersome.

To use ADO without the controller you would create two objects:

1. Your first object would be an ADO connection object like:

myConnection As ADODB.connection

You would then use

Set myConnection = New ADODB.connection
myConnection.open with Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\...\database.mdb;Persist Security Info=False

to open a connection to the database.

2. Your second object would be an ADO recordset like:

myRecordSet As ADODB.recordset

then you would connect to the database with something like:

Set myRecordSet = New ADODB.recordset
myRecordSet.open "SELECT * FROM TABLE", myConnection, adOpenStatic,adLockOptimistic

adOpenStatic is the Cursor type for the recordset and adLockOptimistic is the lock type. Probably be a good idea to look up those two in the help to figure out what you really need.

NOTE:If you do it this way you are going to have to manual set the value for all the fields on the form. However switching recordsets is much easier because all you would have to do is:

myRecordSet.Close
myRecordSet.Open "SELECT NEW QUERY",myConnection, CURSOR TYPE, LOCK TYPE

If you need any of this explained in more detail just ask.

Oh and welcome to the forums.

[sum1unimportant]

Thanks a lot for that, got the controller set up now I beleive. How Do I manually set my fields to that of the recordset? I have a text box, for example, named txtName, and in that I wish to show the field from my table called "Name". Please help me out. Thanks.

[Veritas2.0]

Using your example it would just be.

If Not myRecordSet.BOF and NOT myRecordSet.EOF Then txtName.text = myRecordSet("name")

The first part of the code is checking to make sure that your recordset isn't empty, because if the recordset is empty the program will throw an error when it tries to read non-existant data.

[sum1unimportant]

Yeah, I'm definately getting there now...this is so much better than my original way. How do I go about adding a new record to the db from what has been typed into the fields?

[si_the_geek]

I'd recommend taking a look at the "ADO Tutorial" link in my signature, as it contains code (and explanations) for the usual kind of features people implement - including adding/editing/searching/etc.

That is just one of the articles in the ADO section of our Database Development FAQs/Tutorials (at the top of the Database Development forum), which you will probably find useful for other parts of your program (and/or later programs).

[sum1unimportant]

cheers mate, thats awesome.

[cmccolgan27]

You guys that put your 2 cents in and help us that arnt to crash hot with VB, your great, thank you guys.