Open recordset question

[John E. Fox]

Dear All,

I am trying to update data in a table using a button on a form.

the following bit of code does what I want:
Dim rst As Recordset, db As Database

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tablename _
WHERE [fieldname] = 'Smith123'")

etc.
What I really want to do is to replace the 'Smith123' with either a
variable or preferably the contents of a field on the form.
I have tried all permutations of brackets and punctuation but can't get
it to work.

Any ideas please.
Thanks in advance

John Fox
--

Dr John E. Fox
Director

All prices quoted are in UK pounds and do not include any taxes.
************************************************** ****************
Alta Bioscience Email: al******@bham.ac.uk
The University of Birmingham Phone: [phone]
Edgbaston, Birmingham Fax: [phone]
B15 2TT, UK URL: www.altabioscience.bham.ac.uk

[Ace Calhoon]

Hm, should be:

Dim rst As Recordset, db As Database

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tablename _
WHERE [fieldname] = '" & myVar &"'")

[Arno R]

"John E. Fox" <al******@bham.ac.uk> schreef in bericht news:d7**********@redhat2.bham.ac.uk...

Dear All,

I am trying to update data in a table using a button on a form.

the following bit of code does what I want:


Dim rst As Recordset, db As Database

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tablename _
WHERE [fieldname] = 'Smith123'")

etc.


What I really want to do is to replace the 'Smith123' with either a
variable or preferably the contents of a field on the form.
I have tried all permutations of brackets and punctuation but can't get
it to work.

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tablename _
WHERE [fieldname] = '" & textvar & "'") ' text var needs quotes (look carefully !!)
OR
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tablename _
WHERE [fieldname] = " & numvar ) ' numeric var does not need quotes

Replace textvar or numvar with Forms!YourFom!YourConntrol if needed

Arno R

[John E. Fox]

Thank you, it worked!
I'm sure I had tried that in one of my many attempts but must have had
something else wrong at the time.

John
Ace Calhoon wrote:

Hm, should be:

Dim rst As Recordset, db As Database

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tablename _
WHERE [fieldname] = '" & myVar &"'")

--

Dr John E. Fox
Director

All prices quoted are in UK pounds and do not include any taxes.
************************************************** ****************
Alta Bioscience Email: al******@bham.ac.uk
The University of Birmingham Phone: [phone]
Edgbaston, Birmingham Fax: [phone]
B15 2TT, UK URL: www.altabioscience.bham.ac.uk

[John E. Fox]

Thank you, it worked!
I'm sure I had tried that in one of my many attempts but must have had
something else wrong at the time.

John
Arno R wrote:

"John E. Fox" <al******@bham.ac.uk> schreef in bericht news:d7**********@redhat2.bham.ac.uk...

Dear All,

I am trying to update data in a table using a button on a form.

the following bit of code does what I want:
Dim rst As Recordset, db As Database

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tablename _
WHERE [fieldname] = 'Smith123'")

etc.
What I really want to do is to replace the 'Smith123' with either a
variable or preferably the contents of a field on the form.
I have tried all permutations of brackets and punctuation but can't get
it to work.

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tablename _
WHERE [fieldname] = '" & textvar & "'") ' text var needs quotes (look carefully !!)
OR
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tablename _
WHERE [fieldname] = " & numvar ) ' numeric var does not need quotes

Replace textvar or numvar with Forms!YourFom!YourConntrol if needed

Arno R

--

Dr John E. Fox
Director

All prices quoted are in UK pounds and do not include any taxes.
************************************************** ****************
Alta Bioscience Email: al******@bham.ac.uk
The University of Birmingham Phone: [phone]
Edgbaston, Birmingham Fax: [phone]
B15 2TT, UK URL: www.altabioscience.bham.ac.uk

[John E. Fox]

Thanks folks,
John


John E. Fox wrote:

Dear All,

I am trying to update data in a table using a button on a form.

the following bit of code does what I want:
Dim rst As Recordset, db As Database

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tablename _
WHERE [fieldname] = 'Smith123'")

etc.
What I really want to do is to replace the 'Smith123' with either a
variable or preferably the contents of a field on the form.
I have tried all permutations of brackets and punctuation but can't get
it to work.

Any ideas please.
Thanks in advance

John Fox

--

Dr John E. Fox
Director

All prices quoted are in UK pounds and do not include any taxes.
************************************************** ****************
Alta Bioscience Email: al******@bham.ac.uk
The University of Birmingham Phone: [phone]
Edgbaston, Birmingham Fax: [phone]
B15 2TT, UK URL: www.altabioscience.bham.ac.uk