How do I query all the records showing in the datasheet view of split form

[scooteman]

Hello,

I have a split form with the datasheet at the bottom. On my form I have a search box that I borrowed from Microsoft's Contact Management database template. The box searches on each of my fields so I can filter base on location or job classification, etc. The form is for setting up a mail merge and labels. I have a checkmark field (Yes/No) for setting the query criteria for the mail merge. I have a button to select all records and one to clear all records of the checkmarks. I would like to make a button to checkmark all the records selected by the search.

I tried to set the criteria for the update query to the Employee_ID on the form. This, however, finds and changes only the first record. How can I query all the records showing in the datasheet portion of the form?

 

[boblarson]

The form's record source is the source of the records. The single record view is just a view of the currently selected record. So there is no difference in them. If you want something in the data sheet it is the form's record source that needs to change.

 

[scooteman]

The form's record source is the source of the records. The single record view is just a view of the currently selected record. So there is no difference in them. If you want something in the data sheet it is the form's record source that needs to change.

I have my form setup so that I can only see the datasheet portion. Very similar to the way it is setup in MS Contacts database. If my record source has 20 records and I use the filter search box to see all the employees in one location and it narrows it down to 4, How do I setup the Update query to update only those 4 recordsr?

The filtering is being done by a macro. So the original records source [a query for this form] doesn't appear to be affected.

 

[boblarson]

Here's an example of code I used for a command button to update the price of the selected records to 110% of the listed price:

Private Sub Command22_Click()
Dim rst As DAO.Recordset
Dim strList As String

Set rst = Me.RecordsetClone
Do Until rst.EOF
    strList = strList & rst("OrderID") & ", "
    rst.MoveNext
Loop
strList = Left(strList, Len(strList) - 2)

strSql = "UPDATE [Order Details] SET [Order Details].UnitPrice = [UnitPrice]*1.1 " & _
              "WHERE OrderID In (" & strList & ");"
CurrentDb.Execute strSql, dbFailOnError

End Sub

It works because the recordset clone changes to reflect the filtered values.

 

[scooteman]

Boblarson,

I tried but I must have done something wrong. The code updates all the records and not just the ones showing from the filter. Here is how a changed the code:

Dim rst As DAO.Recordset
Dim strList As String
Set rst = Me.RecordsetClone
Do Until rst.EOF
strList = strList & rst("Checkmark") & ", "
rst.MoveNext
Loop
strList = Left(strList, Len(strList) - 2)
strSql = "UPDATE [Query_SelectForLetter] SET [Query_SelectForLetter].Checkmark = yes " & _
"WHERE checkmark In (" & strList & ");"
CurrentDb.Execute strSql, dbFailOnError

The name of my form is: Mail Merge Setup
The underlying query is: Query_SelectForLetter
The field I want to update is: Checkmark

What did I do wrong in the coding?

 

[boblarson]

You don't capture the checkmark when iterating through. You need the PRIMARY KEY of the records. Then you will update only those records:

Dim rst As DAO.Recordset
Dim strList As String
Set rst = Me.RecordsetClone
Do Until rst.EOF
strList = strList & rst("[COLOR="Red"]YourPKFieldNameHere[/COLOR]") & ", "
rst.MoveNext
Loop
strList = Left(strList, Len(strList) - 2)
strSql = "UPDATE [Query_SelectForLetter] SET [Query_SelectForLetter].Checkmark = yes " & _
"WHERE [COLOR="red"]PKFieldNameHere [/COLOR]In (" & strList & ");"
CurrentDb.Execute strSql, dbFailOnError

 

[scooteman]

Thanks, that worked like a charm! Does just what I wanted!!!!

 

[boblarson]

Glad to hear.

 

[scooteman]

Bob,

I've noticed an error message if I click my button a second time without re-filtering. If I use it to select all records, then clear all the checkmarks then press the button again without re-filtering the script will throw the error "Run-Time error "5": Invalid call, procedure or argument". The Debug button takes me to this line in the code:

strList = Left(strList, Len(strList) - 2)

Is there a way to stop or suppress this error?

It goes away if I re-filter.

 

[boblarson]

Check in the Debug window to see what the value of strList is at that point. I am hazarding a guess that it is having issues with the RecordsetClone being at the end of the file and not moving back to the beginning. So, you might need to add the

rst.MoveFirst

just at the beginning (prior to the Do Until rst.EOF line).

 

[scooteman]

Yea! that did the trick. All fixed now!!! Thanks so much.

 

[boblarson]

Good, glad to hear.