Open Record Set

[danian]

I have a form that needs to enter some data into a form. I can not do this by bounding the the textbox to the table, therefore i am having to use ORs. I am using the following code in VBA but is not working:

'Save Payment Type
Dim oRS As New ADODB.Recordset
oRS.Open "SELECT PaymentType FROM tblTransMain;", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
oRS.AddNew
oRS!DetailLink = txtIDRef
oRS!PaymentType = TEST
oRS.Update
oRS.Close
Set oRS = Nothing

What i want the above todo is Select the field PaymentType from tblTrainsMain Where IDRef = txtIDRef. It will then place TEST in that field only.

At the moment the above creats a new record, which i do not want it todo.

Thanks,
D

 

[Banana]

First, it's helpful to tell us what kind of errors you receive with this. Just saying "this isn't working" doesn't really help as there's a zillion way it could go wrong.

That said, did you set recordset to the current database:

Dim db as ADODB.Database
Dim rst as ADODB.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordSet ("YourSQLstring here")
....

Also, if this didn't fix the error, check your references (in VBA editor, Tools -> References) and ensure you have latest ADO checked and no earlier version checked. That said, I should caution you that ADO are more likely to cause versioning problems; DAO 3.6 is much more reliable. If you're still having problem, trying setting both database and recordset as DAO instead of ADODB.