I have a form that I am opening using a filter. On that form I have a button click that performs an Append and Update Query. When those queries run, they run on all the records from the table and I want them to run only on the filtered data. This saves me from making 6 different forms, and 12 queries, when it can be simplified with a little coding. Every code i've tried seems to have a mismatch with VBA and DAO? Hopefully someone can help me out!! The form is being opened with the following code...

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "OT"
stLinkCriteria = "(team='c' or team='5&2') AND (compound='west')"
DoCmd.OpenForm stDocName, , , stLinkCriteria

This is fairly simple, matching multiple column and criteria, but I cannot get the action query to only run on the filtered data. I can do it by making a query for each individual filtered criteria, but what can I put in the "WHERE" section to only run on the filtered data from the form? Here is the code if I wrote out the current filter...

UPDATE tblMain SET tblMain.[Date Called] = Null, tblMain.[Time Called] = Null, tblMain.[OT Date] = Null, tblMain.Answer = Null
WHERE (((tblMain.TEAM) Like "C" Or (tblMain.TEAM) Like "5&2") AND ((tblMain.COMPOUND) Like "West"));

Hi,

You can even get rid of the queries and just use the form to perform the updates. For example:

Dim rs As Object
Set rs = Me.RecordsetClone
With rs
Do While Not .EOF
.Edit
![Date Called] = Null
![Time Called] = Null
![OT Date] = Null
![Answer] = Null
.Update
.MoveNext
Loop
End With
Set rs = Nothing
Me.Requery

(untested)
Just my 2 cents...

Works perfectly! Thanks! Now that I know how to do that, I can adapt some of my other forms to get rid of a lot of these queries. If that can be done for an update query, i'm sure it can be done for an Append query. For instance, if I used this same form, and want to insert the last name and first name into another table, how would that go? Currently, part of the code is written as:

INSERT INTO tblOT ( [Last Name], [First Name])
SELECT tblMain.[LAST NAME], tblMain.[FIRST NAME],
FROM tblMain

Searching around i've found various ways it can be done, but i'd rather be consistant. Thanks a ton!

Hi,

Glad to hear it worked for you. To use this technique for inserting records, you will need two recordsets. For example:

Dim dbs As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Set dbs = CurrentDb()

Set rs1 = Me.RecordsetClone
Set rs2 = dbs.OpenRecordset("tblOT", dbOpenDynaset)

With rs1
Do While Not .EOF
With rs2
.AddNew
![Last Name] = rs1![Last Name]
![First Name] = rs1![First Name]
.Update
End With
.MoveNext
Loop
End With

rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set dbs = Nothing

(untested)
Just my 2 cents...

The code did work! Now I figure i'd put it all together, but having a few issues with it looping. I'm not real familiar with vba or dao, and usually just rely on basic commands, which can become timely and require a lot of extra unneeded queries, so I prefer what you have showed me. I've tried several different ways, and the best i've done so far is getting the fields to insert and updating only the first record of the field. I'm guessing that i'm putting the .movenext or loop in the wrong spot.

Hi,

Sorry for the delay... Can you show us what you came up with and the errors you're getting? Thanks.