Create ADO or DAO recordset for form without DNS

[damienwjking]

I would just like to create a recordset that I can search and manipulate data via SQL for a local Access Database. I have tried creating a recordset on the form load event.
I want the fields to then be loaded into the form text boxes.
I have look at FAQ, FORUMS etc but can only find DNS ADO stuff. What code should I use?

 

[scriverb]

Here is an example:

Dim db as DAO.Database
Dim rs as DAO.Recordset
Set db = currentdb
Set rs = db.OpenRecordset("tblTargetTable", dbOpenDynaset)
rs.movefirst
me![Field1] = rs("Field1&quot
me![Field2] = rs("Field2&quot
rs.Edit
rs("Field3&quot = rs("Field3&quot * 50
rs.Update
rs.close
db.close

Now this is a very simplistic example to get started. It opens a recordset of a table either in the currentdb or a linked table in the currentdb.

Let me know if you need more.

Bob Scriver

 

[damienwjking]

Thanks alot Bob. Thats a great start. How would I use SQL to create a recordset rather than just a single table.
Cheers

 

[scriverb]

Well I don't understand your question. This code creates a recordset of an existing table(tblYourTable). I don't understand what you mean by creating a recordset rather than a single table.

Please explain. Bob Scriver

 

[cmmrfrds]

The RecordSource is what gets displayed on the Form. On the Data tab for your Form you can make the record source a table or query or sql select statement. Also, the record source will accept any valid sql select statement. For example.

OnOpen Event of Form

Me.RecordSource = "select * from table"

This will return a recordset that gets displayed on your Form.

 

[damienwjking]

Barry,
What I meant is I would like the recordset to be of any SQL statement (i.e. An Inner Join of multiple tables) not just one table.

The code I have used is

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Contacts", dbOpenDynaset)

Rather than using the Contacts table I wanted to use a SQL statment. Below is a simple example.

SELECT Table1.Field, Table2.Field * from Table1,Table2 WHERE Table1.Field = "bla bla"

I Tried using Me.RecordSource = "Select * from table"
but it comes up with invalid use of property.

Any Ideas

 

[GHolden]

In your

Set rs = db.OpenRecordset("Contacts", dbOpenDynaset)

replace the table name with your sql

eg.

Dim strSQL as String

strSQL = "SELECT * FROM tblEmployee WHERE EmployeeID = '" & txtID & "'"

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

'note txtID is a string