Filter a DAO.Recordset Table

Hello,
I am trying to add a filter to a recordset table but I am not sure how to write ir or in what part of the code.
This is my code:

	Code:
	Dim RS As DAO.Recordset
Set RS = CurrentDb.OpenRecordset("Mail_Reminder_RecordSet")
With RS
If .EOF And .bof Then 'IT MEANS THERE ARE NO RECORDS, WE ARE AT THE BEGINNING OF FILE AND END OF FILE (No Records found for this query.)
Else
Do Until .EOF 'DO UNTIL END OF RECORDSET FILE
Forms![Menu2]![IncidKey] = RS!IncidKey

DoCmd.SendObject acSendReport, "report", acFormatPDF, ![Mail], , , "First Alert - High Impact Incident Notification", "This is an automatic mail notification. You have received this mail because there is a high-impact First Alert incident regarding your product line. Please refer to the attached report.", False
.Edit
![Mail_Sent_Date] = Now()
.Update
.MoveNext
Loop
End If
End With
'CLEAN UP MEMORY AT END
If Not RS Is Nothing Then
RS.Close
Set RS = Nothing
End If

What I need to do is filter by mail_Sent_Date = Null so I do not resend the same email twice. I have tried this but it did not work:

	Code:
	RS.Filter = RS!mail_Sent_Date = Null

Any ideas?

Thanks in advance for your help


Post your answer or comment

comments powered by Disqus
Is it possible to query a DAO recordset once it has been created in VBA. Currently I create a recordset and use it for a few things, but It would be useful to be able to run a few more bits of SQL against it to check some other things. The table it is created from is large and it takes several seconds to creatye the recordset. I could insert the records into a table and query that, but would prefer to it in VBA as it gives me more flexibility.

The code I use is something like this:

strSQL = "SELECT * from table WHERE field = '" & ctlCombo & "';"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If Not rs.EOF Then
rs.MoveLast
End If
lngRecordCount = rs.RecordCount

What i would then like to be able to do is

select count * from rs where field 2 = 'abc' etc.

Hi,

Is it possible to move a record in a DAO recordset? I have some code that is looping through a recordset and allocating resource time to requirement. A late specification change has arisen and now on rare occasions there will be requirements that cannot be completed on the same day. I'm looking for a quick way to amend my code to deal with this. I've added a simple if statement to isolate the rare records in question, and check that they can be processed on the date about to be allocated to them. If they cannot, then I want to effectively swap the position of the current record with the next record, so that the next record can be allocated the time, and the former current record will be the first to be considered for the next available resource time. I've tried using the AbsolutePosition property, but don't seem to be able to set that. Any suggestions would be gratefully received.

Thanks,

Matthew

Is there a way in VBA to create a DAO recordset based on a table and delete certain records only from the copy and not the actual table?

I want to use a DAO Recordset copy of a table to
1) Loop through the records and delete / remove the ones I donít want
2) Use the remaining records to as the record source of a form.

Iíve tried .Clone and that doesnít work. Is this possible with DAO?

I normally would use a query, but that wonít work in this instance. Iíve tried using .Filter on the forms recordsource directly but that wonít work since I canít simply specify the criteria.

Maybe I am trying to make it harder than it needs to be. I am attempting to see if I can filter a table based on a bitmask field and display only certain records. Might also need to use the filtered records in a query. Need to see if that is possible before I attempt to use the bitmask field. I can go ahead and use several Boolean fields but could get tedious. Hereís a link to an article on bitmasking. As far as I can tell, you can only do bitwise comparisons in code which is why a query won't work (at least I couldn't get it to work).

Hello all,

"Filtering" a recordset

I have a DAO.recordset that gives me sales for a range of dates pulled from SQL Server. Once I've connected to the Recordset, I would like to apply a "filter" to the same Dataset several times to find a particular salesperson, crunch some numbers, then remove the "filter" and apply another "filter" and reuse the recordset over and over in this way. I don't know if I need a parameter query or if there's a way to filter a recordset once a connection has been established? Can someone give me suggestion as to how I might get this to work?

Thanks a whole lot!

Hello,

I have a question on DAO.

I have a Single form that is bound to a table with 15,000 records. The form has several controls that are each bound to a field in the table. I have a combo box that is bound to a query that selects the primary key of each of the 15,000 records. If I select one specific value from this combo box, I would like the current record in the recordset to jump to that specific record, thus populating all of the form's controls with that record's values.

Is there a quick way to do this in DAO?

I have thought of putting in a loop, and using Recordset.MoveNext and checking the Field in the current record against the field chosen in the combo box, but this seems tedious (esp. for 15,000 records). Any way to jump directly to a specific record in a DAO recordset, keyed off of a field value (or something else?) and not have to use Recordset.MoveNext in a loop?

Thanks,

Scott

Hi Guys. I have what I think is a simple enough question but an hour of searching hasnt come up with a direct answer. I think it is "No" but wanted to make sure.

We have an old program in VB6 written years ago that I have been keeping a float for the past several years. It has always be a VB6 front end that hits a Access (JET) database via DAO.

I would like to get the project running on a SQL Server database as the backend and avoid using Access as the middle man via Linked Tables. In other words, I was hoping to update the VB code to be able to connect directly to SQL Server but NOT have to rewrite all of the DAO code that rely on editing the database tables via Recordsets.

But, I keep running into the error that the Recordsets are not updatable when I try to do a rst.edit. Knowing that DAO was mostly designed to work with JET, I assume that it CANNOT edit via a direct connection to SQL Server?

Here is some code:


	Code:
	dbsSQL As DAO.Database
Set dbsSQL = OpenDatabase("", dbDriverNoPrompt, False, ODBC;Driver={SQL Server};" & _
   Server={FSSQLSERVER_1};UID=SQL_Editor;PWD=Edit_SQL;)

Dim rstTEMP As DAO.Recordset
Set rstTEMP = dbsSQL .OpenRecordset(strSQLQ, dbOpenDynaset, dbSeeChanges)
rstTEMP.Edit '= BIG FAT ERROR
rstTEMP("CUR_ID") = int_tdms_loc_id
rstTEMP.Update



Okay - I'm not entirely sure how to ask this question so please bear with me... I need to place the contents of a DAO Recordset into a string value that can then be inserted into a text file that is the body of an e-mail message. The DAO Recordset is a query that will have from 1 to any number of rows and I need all of the rows and all of the columns to show in the text (ideally, in some kind of orderly fashion). What I hope to be able to do is send out an e-mail that provides generic instructions from the text file along with the detailed records from the query. I can get the code to cycle through the records and assemble the list for each respondent; I just can't seem to figure out how to drop the list of records into the body of the message... Any advice would be deeply appreciated!

What would be the most optimum way to .findfirst on a dao recordset with the criteria from another dao recordset? I'll try to explain more with code that may not be right but you should get the idea.


	Code:
	Dim rs as dao.recordset, rs2 as dao.recordset, db as database, db2 as database
set db = currentdb
set db2 = opendatabase("Path to DB", False, False, ";PWD=###")
 
set rs = db.openrecordset("Table Name", dbopendynaset)
set rs2 = db2.openrecordset("Table Name", dbopendynaset)
 
'// Finds records marked for deletion. Deletion equals 20
rs.findfirst "[Record Status] = 20"
do while rs.nomatch = false
      rs2.findfirst rs
      rs2.delete
rs.movenext
loop
rs.close
rs2.close
set rs = nothing
set rs2 = nothing
set db = nothing
set db2 = nothing

Thanks,

TheChazm

Couldnt find this in search either.


Using tables to filter a query.

Example:

qry1= EmpID EmpName

tbl = Filter

EmpID = 1 to 5000
Filter = a list of 30 different numbers (say 1-15 then 20-35)

Having the query only show those ID's using the entries in the table.

Thanks a ton as always.

Hi folks,

I have a large table with the following set of fields:

Age (integer 18-65)
Series (int 1-5)
Class1 (int 1-4)
Class2 (binary - currently text)
Class3 (binary - currently text)
Class4 (int - 4 possible values)
Term (int 0-45)
Rate (float)

Basically, what I need to do is query for Rate according to a combination of some of the other fields. Not all the fields are used for each query, but every query is guaranteed to be unique. The data is fixed and will not normally change. As you can see, when fully populated this is a biggish dataset, probably 120k rows or something of that order.

At the moment, I'm using Find with a DAO recordset (called t):

Code: qry = "Age = " & Val(r!Age) qry = qry & " AND Series='xyz'" ' etc t.FindFirst qry rate = Val(t!Rate) I have to carry out about 300,000 of these lookups, and ideally I'd like something that runs *fast*. I'm using a constrained test set of about 30000 rows at the moment and the slowdown is significant. I gather that Seek rather than Find is a better choice for speed, but frankly I don't understand the documentation, not being much of a programmer.

Is there a better way to do this? I'm be no means fixated on using DAO; it was simply the first thing I got working so I've used it throughout my project.

Many thanks.

I have a few records locked in MS Access. I can go into SQL Server and update just fine, but not in Access. The way I got there was to do a DAO Recordset, and there was something wrong within the .Edit, so when it got to .Update it couldnít do it, so maybe it didnít release the Recordset?? How do I unlock these records for MS Access? Tried closing and reopening MS Access, logging off, restarting the PC, updating in SQL Server and seeing if that released the lock, killing SPIDs, all unsuccessful. When I try to .update with the Recordset, I get a ODBC call failed.
When I try to update the table by simply making a change, then down arrowing to go to the next record, it says another user is changing and gives me choices to drop changes or to copy to clipboard.
Thanks in advance!

Is the recordpatrolling (rs.movelast and rs.movefirst) after creating a DAO recordset is mandatory before going to use it? I belive I am following a wrong tradition so far. Clarifications needed. Thank you.

I would like to be able to create a dao.recordset from a .xls file, and also a .dbf file.

I use the following code to create a dao.recordset from a .csv or .txt file:


	Code:
	Dim sql as string
Dim rst as DAO.Recorset

sql = "Select * FROM [Text;;FMT=Delimited;HDR=YES;IMEX=2;DATABASE=C:Documents and SettingsOwnerDesktop].[DataIn.CSV]"

Set rst = CurrentDb.OpenRecordset(sql)

Can someone tell me how I would change this code to do the same for an .xls, .xlsx or .dbf file?

Thanks,
Sup

Hi to everyone!!

Ia have trouble with my code. It gives me "runtime error 3251: This operation is not supported by this type of object" about my Requery. I requery a DAO.recordset. What could it be??

Hi,

Ive just recently changed my database from a *.MDB to a *.ACCDB. Once i done this my ''New ADODB Recordet" no longer exists. and i ashume i need to use a DAO recordset. I need to add a new record and make the employeeId to + 1. Here is my code i had with the ADODB recordset. Can someone provide me with the code for doing the same job but from a DAO recordet? It usually gets stuck when i reach the code in bold. Any suggestions?



Private Sub AddNewRecordAndDisplayIt()
Dim vMaxRecNo As Variant
Dim MySQL As String
Dim CurrRec As New ADODB.Recordset

Me.AllowAdditions = True

'Find current max rec no.

vMaxRecNo = DMax("nEmployeeID", "EmployeeDetails")
If IsNull(vMaxRecNo) Then
nCurrRecID = 1
Else
nCurrRecID = vMaxRecNo + 1
End If
With CurrRec
.Open "EmployeeDetails", CurrentProject.Connection, adOpenStatic, adLockPessimistic
.AddNew
!nEmployeeID = nCurrRecID 'Primary Key
.Update
.Close
End With
Set CurrRec = Nothing
Me.AllowAdditions = False
'Display the new record.
MySQL = "SELECT a.* From EmployeeDetails a where a.nEmployeeID =" & nCurrRecID
Me.RecordSource = MySQL
End Sub

Hi all.

I have a ListBox which gets its data from a DAO.Recordset I created in VBA (after running a SQL query)
This DAO record set is not closed/cleared by using set DAOrecordset = nothing until the form closes - So, as I understand it, the recordset remains open and can be referenced right?

-------------------------------------

The SQL Code used is:

"SELECT TblPhoneCall.CallerName, TblPhoneCall.CompanyName, TblPhoneCall.DateTime, TblPhoneCall.Description, TblWelcomePacks.ID " & _
"FROM TblPhoneCall INNER JOIN TblWelcomePacks ON TblPhoneCall.ID = TblWelcomePacks.ID " & _
"WHERE TblPhoneCall.CallerName LIKE '" & EdCallerName.Value & "*' " & _
"ORDER BY TblPhoneCall.CallerName;"

------------------------------------
the List box is updated with the records found with the above SQL code.

So, onto my problem...

When I click on an item in the list box, I want the corresponding records' Description (as in bold in the SQL code) data to be displayed inside a TextBox located on the form.

I've experimented with the SEEK function however I kept getting errors.


Can anyone help?

Hi,

I'm trying to replace an Append query - which is pretty slow to run.
The query appends data from fields 2 tables (based on a JOIN between the 2 tables) into a 3rd table.


To achieve this, the query needs to review all records on Table 2, and find matches in Table 1.
(After reading around the net), I've tried to achieve this by creating 3 Recordsets (1 for each table), then .Add ing data from fields in the first 2 recordsets into the 3rd.


Table 1 : "JBADownload"
Fields : "Supplier Reference" (Primary, Text), "SDIV" (Text)


Table 2 : "THLFU_EFP856AYV1"
Fields : "Item#" (Non-Unique, Text), "B_Qty" (Numerical), "GRP_DLVRY" (Text).


Table 3 : "CRP_Loads" (Table that Data needs to be appended into)
Fields : "Item" (Numerical), CRP_Load (Text), Qty (Numerical) and Sub_Division (Text)


Field JBADownload!"Supplier Reference" is RIGHT JOINed to THLFU_EFP856AYV1!Item#


I hope to limit the records in the recordsets to include only those records from THLFU_EFP856AYV1 where (based on the JOIN), the SDIV field in table JBADownload are equal to the If / Elseif statements I have used.

Additionally, I need Recordset "SubDiv2" (for Table "THLFU_EFP856AYV1") to group the contents by field "GRP_DLVRY", performing a Sum on field B_Qty (called B_Qty_Total) and a Count on Field ITEM# (called ITEM#_Count).

However, when I try to run this code - I get a Run Time Error (Type 424), saying that an Object is missing on Recordset "SubDiv2".. however I can't figure it out.


Any ideas gratefuly received.
(Apologies for the lack of line breaks in the OpenRecordset lines - I have tried to add these in without success).

C99


=+=+=+=+=+=+=+=+=+=+=+=+=+=++=+=+=+=+=+=+=+=+=+=+= +
Function Recordset_856_by_Sub_Div2()

Dim DBS As DAO.Database
Dim SubDiv1 As DAO.Recordset
Dim SubDiv2 As DAO.Recordset
Dim SubDiv3 As DAO.Recordset
Set DBS = CurrentDb

'OpenRecordsets......

Set SubDiv1 = db.OpenRecordset("SELECT * FROM [JBADownload RIGHT JOIN THLFU_EFP856AYV1 ON JBADownload.[SUPPLIER REFERENCE] = THLFU_EFP856AYV1.[ITEM#]]", dbOpenDynaset)


Set SubDiv2 = db.OpenRecordset("SELECT [GRP_DLVRY], Count([Item#].Quantity) AS Item_Count, Sum([B_Qty].Quantity) As B_Qty_Total FROM [THLFU_EFP856AYV1] LEFT JOIN JBADownload ON THLFU_EFP856AYV1.[ITEM#]= JBADownload.[SUPPLIER REFERENCE]GROUP BY THLFU_EFP856AYV1_1.GRP_DLVRY]", dbOpenDynaset)


Set SubDiv3 = db.OpenRecordset("CRP_Loads", dbOpenDynaset)




If JBADownload!SDiv = "EC" Then

ElseIf JBADownload!SDiv = "EF" Then
ElseIf JBADownload!SDiv = "EQ" Then
ElseIf JBADownload!SDiv = "1C" Then
ElseIf JBADownload!SDiv = "1E" Then

Else

Subdiv3.AddNew

Subdiv3!Item = SubDiv2!Item#_Count
Subdiv3!CRP_Load = SubDiv2!GRP_DLVRY
Subdiv3!Qty = SubDiv2!B_Qty_Total
Subdiv3!Sub_Division = SubDiv1!SDiv

Subdiv3.Update

End If


SubDiv1.Close
SubDiv2.Close
Subdiv3.Close


Set SubDiv1 = Nothing
Set SubDiv2 = Nothing
Set Subdiv3 = Nothing

Set db = Nothing


MsgBox "Finished", vbInformation, "Recordset_856_by_Sub_Div"
End Function

A while back I had a question on how best to append a record without opening the entire recordset. The Better Approach SQL or DAO

I took the append approach

	Code:
	Set RSTnew = CurrentDb.OpenRecordset("SELECT * FROM dbo_InspectionTable", dbOpenDynaset, dbAppendOnly)

Everything worked fine in testing mode. Of course when I added it to the production database, it immediately crashed. As I experimented, I received two error messages. One of the errors was: "Run-time error '3001' invalid argument". The other error was "error: 3622 dbSeeChanges with SQL Server". Error 3622 Open SQL Server Table with Identity Column.

The problem is that we are using Microsoft SQL server as the production back-end and one of the fields is an auto-number field (Identity Column), which requires the dbSeeChanges option. In testing, I was using a "local" Access back-end version which worked just fine. After searching, I found the solution here. Adding a new record

The improved code is:

	Code:
	Set RSTnew = CurrentDb.OpenRecordset("SELECT * FROM dbo_InspectionTable", dbOpenDynaset, dbAppendOnly + dbSeeChanges)

I had figured that I needed to get dbAppendOnly and dbSeeChanges in the "options" block, but I was unaware of how to do it. Adding the "+" signed solved that issue.

I am trying to implement some simple code that seems to work fine from within a module, but cannot get it to work from within a form sub.


	Code:
	Dim db As DAO.Database
Dim SVUnfiltered As DAO.Recordset
Dim SV As DAO.Recordset

Set db = CurrentDb
Set SVUnfiltered = db.OpenRecordset("SELECT * FROM DAT", dbOpenDynaset)
SVUnfiltered.Filter = "SVUnfiltered![DAT1] = 'SV'"
Set SV = SVUnfiltered.OpenRecordset

DAT is a table within the database. DAT1 is a field within DAT.

All I need to do is filter my SV query. I get the error "too few parameters, expeted 2".... WHY!!!??!!

Any help would be great.

Phil

I have been trying to add records to table from a form and got a runtime error item not found in this collection. The bale is linked to another table like this: tlbSchool"one------->"many" tblContacts(table im inserting data) and bothe have a feild school and that is the line flagged up for error in this code




	Code:
	   
   Dim Contacts As DAO.Database
   Dim Cont As DAO.Recordset
    Set Contacts = CurrentDb
    Set Cont = Contacts.OpenRecordset("tblContacts")
   Cont.AddNew
   Cont("ContactFirstName").Value = Me.FirstName.Value
   Cont("ContactSurname").Value = Me.Surname.Value
   Cont("School").Value = Me.SchoolName.Value
   Cont("JobTitle").Value = Me.FirstName.Value
   Cont("Email").Value = Me.EmailAddress.Value
   Cont("Sport1Involved").Value = Me.SportInolved.Value
   Cont("Padsis").Value = Me.Padsis.Value
   Cont.Update

Any ideas how to get around this

Hello all, first of all i will have to state that i am not really a VBA person i am a little confused by this simple matter.

I have a MySQL server and am able to talk to it now via DAO. What i wanted to do is to be able to import a recordset from the MySQL server and store it in a local Access table, and also the inverse of that. I wanted to use DAO (or ADO if needed, but i dont know anything about ADO) to manage the MySQL data, and didnt want to link the tables in Access.

A simple solution would be opening both the tables in DAO and processing the recordset line by line, but i was looking for a faster smarter way using the SQL engine(s) to do the work as this solution would probabbly by way too slow.

If anyone could point me in the direction to do this i would be greatful.
Andy.

I am trying to copy some data from one table to another, but not in a simple way. I need to look up an index number, then using that record, cycle through all the fields past field 11, and for every non-null entry, copy both the entry as well as the name of the field in to my new table.

Here's my code:


	Code:
	    Dim dbsNecropsy As DAO.Database
    Dim rstGenotypes As DAO.Recordset
    Dim fld As DAO.Field
    Dim FieldName As String
    Dim Blah As Integer
    
      
    Set dbsNecropsy = CurrentDb
    Set rstGenotypes = dbsNecropsy.OpenRecordset("FacilityTyping", dbOpenTable)
    
    rstGenotypes.Index = "Animal UID"
    rstGenotypes.Seek "=", [Mouse UID]
    
    If rstGenotypes.NoMatch Then
        MsgBox "Genotypes for UID could not be found."
    Else
        [Gender] = rstGenotypes!Sex
        For Blah = 11 To rstGenotypes.Fields.Count - 1
            Set fld = rstGenotypes(Blah)
            Set FieldName = fld.Name
            If IsNull(rstGenotypes!FieldName) Then
                Next
            Else
                ["Gene" & Blah] = rstGenotypes!FieldName
                ["Allele" & Blah] = FieldName
            End If
        Next
    End If

I have no idea if it works, as it throws an error at me for this:
Set FieldName = fld.Name
I have no idea why.
Any and all help, as always, is greatly appreciated!

Dear Access Expert,

I am looking for an effecient method to transfer a custom DAO recordset to an MS Access table.


There must be a better way than using the following template.


	Code:
	Sub CreateTable()

Dim dbs As Database, tbl As TableDef, fld As Field

Set dbs = CurrentDb

Set tbl = dbs.CreateTableDef("Test")
Set fld = tbl.CreateField("test1", dbText)

tbl.Fields.Append fld
dbs.TableDefs.Append tbl
dbs.TableDefs.Refresh

End Sub


Thanks very much.

Hello Guys,
I have a user login form linked to a table tblUsers, I use code to search an existing user Name and if the user avilable then I get his password and level and then I click the button to move to another Form,
anyway what I want to do is to start using the recordset, I don't want to link the users table to the login form, what I want to do is.
build a form contains the following Unbound Controls.

txtUserName
txtUserPass
txtUserLevel

and whenever I enter the User Name in the txtUserName on the AfterUpdate event I want the RecourdSet to Search the table of tblUsers and prints in the text boxes the following Data

txtUserName = UserName
txtUserPass = UserPass
txtUserLevel = UserLevel
and if the txtUserName UserName then
Msgbox "User is not Exist"

Help will be appreciated because I think working with RecordSet is more professional for this kind of forms,

so far I know how to move to first and last record using DAO recordset, but I couldn't understand the Examples given in so many sites to how to Seek a record and get it in the active form to be a current record.

thanks in advance


Not finding an answer? Try a Google search.