Jumat, 04 Maret 2016

RE: [MS_AccessPros] Requery datasheet

 

One thing to note, if the cursor is on the last record in the recordset, the rs.MoveNext will move the cursor to the EOF. Trying to get the primary key will result in an error. In that case, I would think you would want to get the previous record.

 

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

 

    ' 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.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

 

 

Darrell

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, March 04, 2016 2: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 

(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 

(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

 

 


The information contained in this communication is highly confidential and is intended solely for the use of the individual(s) to whom this communication is directed. If you are not the intended recipient, you are hereby notified that any viewing, copying, disclosure or distribution of this information is prohibited. Please notify the sender, by electronic mail or telephone, of any unintended receipt and delete the original message without making any copies.

Blue Cross Blue Shield of Michigan and Blue Care Network of Michigan are nonprofit corporations and independent licensees of the Blue Cross and Blue Shield Association.

__._,_.___

Posted by: "Embrey, Darrell" <DEmbrey@bcbsm.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (6)

.

__,_._,___

Tidak ada komentar:

Poskan Komentar