DAO Recordset problem

[LucaBrasi]

I am using a DAO recordset based on a table on the back end, and the
table has almost 11,000 rows. Is there a way to 'filter' the table to
use only the rows that meet my criteria ? I know I could base the
recordset on a query, but I need the criteria to be based on 3 text
boxes on my main form. A command button on the main form, opens the
recordset and performs the actions on that recordset, but I think it
would be much more efficient to just loop through the records that
already meet the criteria. If I base the query on, for example, the
field strLocCode, where strLocCode = forms!frmMain!text1 (which is
the form containing the command button) and 2 other criteria, I get a
run-time error, and the message 'Parameter expected. 3 missing' (not
verbatim, but something close to that). Any help would be greatly
appreciated.

[Allen Browne]

Build the query statement dynamically, using just the criteria the user
entered. Your OpenRecordset() will then give you just the records you want.

Here's an example of how to build the SQL string:

Dim strSql As String
Dim strWhere As String
Dim lngLen As Long
Const strcStub = "SELECT * FROM Table1 "
Const strcTail = " ORDER BY Field1;"

If Not IsNull(Me.txtSurname) Then
strWhere = strWhere & "(Surname = """ & Me.txtSurname & """) AND "
End If
If Not IsNull(Me.txtBirthDate) Then
strWhere = strWhere & "(BirthDate = #" & Me.txtBirthDate & "#) AND "
End If
'etc for other boxes.

lngLen Len(strWhere) - 5 'Without trailing " AND ".
If lngLen <= 0 Then
strSql = strcStub & strcTail
Else
strSql = strcStub & "WHERE " & Left(strWhere, lngLen) & strcTail
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"LucaBrasi" <Lu*******@FishNap.com> wrote in message
news:449aeec6.3116484@news-server...

I am using a DAO recordset based on a table on the back end, and the
table has almost 11,000 rows. Is there a way to 'filter' the table to
use only the rows that meet my criteria ? I know I could base the
recordset on a query, but I need the criteria to be based on 3 text
boxes on my main form. A command button on the main form, opens the
recordset and performs the actions on that recordset, but I think it
would be much more efficient to just loop through the records that
already meet the criteria. If I base the query on, for example, the
field strLocCode, where strLocCode = forms!frmMain!text1 (which is
the form containing the command button) and 2 other criteria, I get a
run-time error, and the message 'Parameter expected. 3 missing' (not
verbatim, but something close to that). Any help would be greatly
appreciated.

[LucaBrasi]

Thank you very much..but I'm confused. What would the syntax for the
OpenRecordset be ? The query doesn't have a name, so how do I use
OpenRecordset ? I'm sorry if this is a stupid question, but I'm kind
of new to this, and it is 3:45 am here. Thanks again.

On Wed, 21 Jun 2006 15:35:59 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote:

Build the query statement dynamically, using just the criteria the user
entered. Your OpenRecordset() will then give you just the records you want.

Here's an example of how to build the SQL string:

Dim strSql As String
Dim strWhere As String
Dim lngLen As Long
Const strcStub = "SELECT * FROM Table1 "
Const strcTail = " ORDER BY Field1;"

If Not IsNull(Me.txtSurname) Then
strWhere = strWhere & "(Surname = """ & Me.txtSurname & """) AND "
End If
If Not IsNull(Me.txtBirthDate) Then
strWhere = strWhere & "(BirthDate = #" & Me.txtBirthDate & "#) AND "
End If
'etc for other boxes.

lngLen Len(strWhere) - 5 'Without trailing " AND ".
If lngLen <= 0 Then
strSql = strcStub & strcTail
Else
strSql = strcStub & "WHERE " & Left(strWhere, lngLen) & strcTail
End If

[Keith Wilby]

"LucaBrasi" <Lu*******@FishNap.com> wrote in message
news:449aeec6.3116484@news-server...

A command button on the main form, opens the
recordset and performs the actions on that recordset, but I think it
would be much more efficient to just loop through the records that
already meet the criteria.

Couldn't you just open the filtered recordset in code, "perform the actions"
then requery your form?

Regards,
Keith.
www.keithwilby.com

[LucaBrasi]

Well, that was my question, 'How to open the filtered recordset'

On Wed, 21 Jun 2006 08:47:10 +0100, "Keith Wilby" <he**@there.com>
wrote:

"LucaBrasi" <Lu*******@FishNap.com> wrote in message
news:449aeec6.3116484@news-server...

A command button on the main form, opens the
recordset and performs the actions on that recordset, but I think it
would be much more efficient to just loop through the records that
already meet the criteria.

Couldn't you just open the filtered recordset in code, "perform the actions"
then requery your form?

Regards,
Keith.
www.keithwilby.com

[Allen Browne]

You can OpenRecordset directly on a SQL string:
Dim rs As DAO.Recordset
Set rs = dbEngine(0)(0).OpenRecordset(strSql)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"LucaBrasi" <Lu*******@FishNap.com> wrote in message
news:4498f8e6.5708546@news-server...

Thank you very much..but I'm confused. What would the syntax for the
OpenRecordset be ? The query doesn't have a name, so how do I use
OpenRecordset ? I'm sorry if this is a stupid question, but I'm kind
of new to this, and it is 3:45 am here. Thanks again.

On Wed, 21 Jun 2006 15:35:59 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote:

Build the query statement dynamically, using just the criteria the user
entered. Your OpenRecordset() will then give you just the records you
want.

Here's an example of how to build the SQL string:

Dim strSql As String
Dim strWhere As String
Dim lngLen As Long
Const strcStub = "SELECT * FROM Table1 "
Const strcTail = " ORDER BY Field1;"

If Not IsNull(Me.txtSurname) Then
strWhere = strWhere & "(Surname = """ & Me.txtSurname & """) AND "
End If
If Not IsNull(Me.txtBirthDate) Then
strWhere = strWhere & "(BirthDate = #" & Me.txtBirthDate & "#) AND "
End If
'etc for other boxes.

lngLen Len(strWhere) - 5 'Without trailing " AND ".
If lngLen <= 0 Then
strSql = strcStub & strcTail
Else
strSql = strcStub & "WHERE " & Left(strWhere, lngLen) & strcTail
End If

[LucaBrasi]

Last question..promise ! Even though I am not directly using the
table, can I still do edits and updates ? such as

rst1.edit
rst1!strField1 = "Shoelace"
rst1.update

etc..

On Wed, 21 Jun 2006 15:54:44 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote:

You can OpenRecordset directly on a SQL string:
Dim rs As DAO.Recordset
Set rs = dbEngine(0)(0).OpenRecordset(strSql)

[Allen Browne]

Sure. If the query statement represents an editable query, you can edit it.

This technique (dynamic SQL strings, and WHERE clauses) underlies most of
the code I write in Access databases.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"LucaBrasi" <Lu*******@FishNap.com> wrote in message
news:449afb9a.6399937@news-server...

Last question..promise ! Even though I am not directly using the
table, can I still do edits and updates ? such as

rst1.edit
rst1!strField1 = "Shoelace"
rst1.update

etc..

On Wed, 21 Jun 2006 15:54:44 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote:

You can OpenRecordset directly on a SQL string:
Dim rs As DAO.Recordset
Set rs = dbEngine(0)(0).OpenRecordset(strSql)

[LucaBrasi]

Thank you, very helpful. And I appreciate your patience.
On Wed, 21 Jun 2006 16:15:43 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote:

Sure. If the query statement represents an editable query, you can edit it.

This technique (dynamic SQL strings, and WHERE clauses) underlies most of
the code I write in Access databases.

[Keith Wilby]

"LucaBrasi" <Lu*******@FishNap.com> wrote in message
news:4499f9dd.5955609@news-server...

Well, that was my question, 'How to open the filtered recordset'

Something like (off the top of my head and untested):

Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String
Set db = CurrentDb
strSQL = "Select * from tblMyTable where [MyField] = "whatever" and ...etc"

Set rs = db.OpenRecordset(strSQL)

With rs
.MoveFirst
Do Until .EOF
.Edit
![MyField] = "whatever"
.Update
.MoveNext
Loop
End With

rs.Close
Set rs = Nothing

Me.Refresh

[Lyle Fairfield]

LucaBrasi wrote:

Well, that was my question, 'How to open the filtered recordset'

I think your question was, "How to open and edit a filtered recordset?"

The answer is, "Don't!"

Update the Table by executing a dynamic SQL statement:

DbEngine(0)(0).Execute "UPDATE GoodPractice SET WhentoUseRecordsets =
'Never' WHERE Descriptor = 'KISS'"