ACCESS FEED: Re: [MS_AccessPros] Requery datasheet

Troy-

Note that I do a MoveNext to get to record 201 in your example, then save that value to do the FindFirst. I wonder if the FindFirst is failing. You could put a halt in the code, then step through it to see what it's doing.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Mar 5, 2016, at 7:39 PM, Troy Sherven tsherven@mcd.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

I just noticed you wrote the book "SQL Queries for Mere Mortals"…I love that book. I have it sitting next to my desk!

Thanks for the suggestion. I added the statement below but am getting the same result. I am using a datasheet. If I highlight row 200, and click check13, the record is removed but the focus moves to row 1 and is highlighted. My ideal would be if row 201 or row 199 was highlighted after update. It wouldn't even have to highlight another row, even if the view wouldn't go to the top of the screen, that would be a big help.

Here's what I'm confused about: Once the code hits the Me.Requery line, the record I checked will be gone from the recordset. Check13 is bound to a field in my table. When a task is completed, I'm populating check13 and it puts a value of -1 in my "completed" field in my DB table. When I requery, I'm eliminating any record from the recordset that has a value of -1. When the code you gave me hits requery, it gets ride of the record. Isn't the "findfirst" line trying to find the IDNUMBER of the record we just eliminated from the recordset?

Here's my updated code:

Private Sub Check13_AfterUpdate()

Dim rs As DAO.Recordset, lngID As Long

' Skip if there's an error

On Error Resume Next

' Get a copy of the current recordset

Set rs = Me.Recordset

' Go to the current row

rs.Bookmark = Me.Bookmark

' Try to go to the next row

rs.MoveNext

' Grab the Primary Key of the record

lngID = Me.IDNUMBER ' NOTE: Fix this to save the key using correct field name

' Requery to get rid of the checked row

Me.Requery

' Attempt to reposition

Me.Recordset.FindFirst "idnumber = " & lngID

Me.SelTop = Me.Recordset.AbsolutePosition

End Sub

Also, here's the code for the entire form:

Option Compare Database

Private Sub Form_Load()

Me.Requery

Me.Refresh

Me.Job_.ColumnWidth = -2

Me.Company.ColumnWidth = 3000

Me.Description.ColumnWidth = -2

Me.Process.ColumnWidth = -2

Me.Proctime.ColumnWidth = -2

Me.Check13.ColumnWidth = -2

Me.Check15.ColumnWidth = -2

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me![Datetime] = Now()

End Sub

'Me.Requery

'Form_Completed.Requery

Private Sub Check13_AfterUpdate()

Dim rs As DAO.Recordset, lngID As Long

' Skip if there's an error

On Error Resume Next

' Get a copy of the current recordset

Set rs = Me.Recordset

' Go to the current row

rs.Bookmark = Me.Bookmark

' Try to go to the next row

rs.MoveNext

' Grab the Primary Key of the record

lngID = Me.IDNUMBER ' NOTE: Fix this to save the key using correct field name

' Requery to get rid of the checked row

Me.Requery

' Attempt to reposition

Me.Recordset.FindFirst "idnumber = " & lngID

Me.SelTop = Me.Recordset.AbsolutePosition

End Sub

Private Sub form_timer() ' NOTE: time interval is 10 minutes

Me.Requery

End Sub

Thanks for all of your help!

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Saturday, March 05, 2016 4:09 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Requery datasheet

Troy-

Ah, this is a Datasheet or Continuous form, right? My code will move the selected record, but the screen doesn't scroll. If you look at the record selector, it should not be highlighted for the first row. You need one more statement just after the FindFirst to (I hope) do what you want:

Me.SelTop = Me.Recordset.AbsolutePosition

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Mar 4, 2016, at 11:56 PM, Troy Sherven tsherven@mcd.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I have tried both solutions and get the same result. The focus goes back to the top of the datasheet after I click Check13.

Here's John's solution:

Private Sub Check13_AfterUpdate()

Dim rs As DAO.Recordset, lngID As Long

' Skip if there's an error

On Error Resume Next

' Get a copy of the current recordset

Set rs = Me.Recordset

' Go to the current row

rs.Bookmark = Me.Bookmark

' Try to go to the next row

rs.MoveNext

' Grab the Primary Key of the record

lngID = Me.IDNUMBER ' NOTE: Fix this to save the key using correct field name

' Requery to get rid of the checked row

Me.Requery

' Attempt to reposition

Me.Recordset.FindFirst "idnumber = " & lngID

End sub

Here's Darrell's solution:

Private Sub Check13_AfterUpdate()

Dim rs As DAO.Recordset, lngID As Long

' Skip if there's an error

On Error Resume Next

' Get a copy of the current recordset

Set rs = Me.Recordset

' Go to the current row

rs.Bookmark = Me.Bookmark

' Try to go to the next row

rs.MoveNext

' Check for EOF

If rs.EOF Then

' Move to the previous record

rs.Move -1, rs.Bookmark

End If

' Grab the Primary Key of the record

lngID = Me.IDNUMBER ' NOTE: Fix this to save the key using correct field name

' Requery to get rid of the checked row

Me.Requery

' Attempt to reposition

Me.Recordset.FindFirst "idnumber = " & lngID

End Sub

Any ideas?

Thanks for your help!

Troy

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, March 04, 2016 3:10 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Requery datasheet

Bad "air code" on my part. I think someone else has already pointed out that it should be:

SET rs = Me.Recordset

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Mar 4, 2016, at 9:02 PM, Troy Sherven tsherven@mcd.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Thanks for the help. I'm trying to use what you sent me, but am getting a "compile error – invalid use of property" on the line rs=Me.Recordset (the rs is highlighted). I did change the name of the primary key field (the field name is idnumber).

Any ideas?

Private Sub Check13_AfterUpdate()

Dim rs As DAO.Recordset, lngID As Long

' Skip if there's an error

On Error Resume Next

' Get a copy of the current recordset

rs = Me.Recordset

' Go to the current row

rs.Bookmark = Me.Bookmark

' Try to go to the next row

rs.MoveNext

' Grab the Primary Key of the record

lngID = Me.idnumber ' NOTE: Fix this to save the key using correct field name

' Requery to get rid of the checked row

Me.Requery

' Attempt to reposition

Me.Recordset.FindFirst "idnumber = " & lngID

End Sub

Thanks!

Troy

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, March 04, 2016 1:55 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Requery datasheet

Troy-

Ah, now I understand why you're doing the second Requery. Yes, my code should work as long as you add the second Requery and modify the code to search on the actual Primary Key field name.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Mar 4, 2016, at 8:50 PM, Troy Sherven tsherven@mcd.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hi John,

Think of my project as a task list with a couple hundred tasks. I'm a standard form that includes 2 datasheet subforms. On subform for tasks and the second subform for completed tasks. When I check the completed checkbox, I want the record to move from the task list to the completed task list. I'm using 2 different queries as the datasource for each datasheet. The only difference between the two queries is the value of the checkbox (task list showing records where the checkbox value not like -1). That's why I'm requerying two forms. I want the record to disappear from the current form, show up on my completed form, and the focus to be on the next record instead of the top of the datasheet.

Do you think what you have below would accomplish what I'm trying to do?

Thanks,

Troy

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, March 04, 2016 1:31 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Requery datasheet

Troy-

If you've removed the record that has the focus (I assume because there's a filter to not show checked rows), where do you want the focus to go? Next record? Previous record? Also, why are you requerying two objects? Don't you just need to requery the current form?

You could try something like:

Private Sub Check13_AfterUpdate()

Dim rs As DAO.Recordset, lngID As Long

' Skip if there's an error

On Error Resume Next

' Get a copy of the current recordset

rs = Me.Recordset

' Go to the current row

rs.Bookmark = Me.Bookmark

' Try to go to the next row

rs.MoveNext

' Grab the Primary Key of the record

lngID = Me.PKey ' NOTE: Fix this to save the key using correct field name

' Requery to get rid of the checked row

Me.Requery

' Attempt to reposition

Me.Recordset.FindFirst "PKey = " & lngID

End Sub

The above code tries to find the "next" record and save its Primary Key. You'll have to modify to use the real name (and data type) of the unique key for the recordset. After a Requery, it then uses the key value to reposition the form.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Mar 4, 2016, at 8:16 PM, Troy Sherven tsherven@mcd.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I'm using Access 2010 and have a question about refreshing a datasheet form. I've created a checkbox in the datasheet (for each record). This checkbox is setup to remove a record from the datasheet. I've created an event to requery the datasheet "after update" of the checkbox. This removes the record from the datasheet. Here's the event:

Private Sub Check13_AfterUpdate()

Me.Requery

Form_Completed.Requery

End Sub

The event works fine. The problem I have is that if I'm working in the datasheet and am scrolled down to the bottom of the datasheet, if I click the checkbox it will requery the datasheet, and focus moves to the top of the datasheet again. Is there any way to requery the datasheet but stay in the same position on the form? It's annoying to have to scroll down again after every update.

Any ideas?

Thanks,

Troy Sherven