Solved

MS Access 2003 Datasheet view and record update

Posted on 2013-01-21
11
696 Views
Last Modified: 2013-01-22
I have a (datasheet view) form that has link column with a click event.

This form may have 1000 records in it and they are sorted on a particular column (Money) descending order.

The process is for management to work their way down the list and "select" particular records for a visit.

WHen selected...an update (VBA Stored Procedure on SQL Server) takes place.

Now...ANNOYING PART
Whether I do a Me.Form.Requery or refresh doesn;t matter.
The datasheet cycles all the way to the top record.

Then management has to scroll down to where they left off

Any way to do the update and sta on that particular window/view?
0
Comment
Question by:lrbrister
  • 6
  • 5
11 Comments
 
LVL 75
ID: 38802909
try t his

dim vBM As Variant
vBM = Me.RecordsetClone.Bookmark  ' set current location

'   you stored procedure code here
' and/or requery, etc

Me.Bookmark = vBM  ' restore previous location

mx
0
 

Author Comment

by:lrbrister
ID: 38802977
DatabaseMX

I tried that but it's setting the variant = 1

I went to record 5000
Ran my update with the code below
But it sets it back to the tom.


            Dim vBM As Variant
            vBM = Me.RecordsetClone.Bookmark  ' set current location
        
            'Assuming vbYes has been clicked, proceed with attempt to insert/update record
            Dim cmd1 As New ADODB.Command
            Dim rst1 As ADODB.Recordset
            
            cmd1.ActiveConnection = CurrentProject.Connection
            cmd1.CommandType = adCmdStoredProc
            cmd1.CommandText = "usp_MarketingVisitEdit"
            cmd1.Parameters("@id") = IIf(remove = 1, Me.txtVisitID, 0)
            cmd1.Parameters("@clientID") = Me.Client_ID
            cmd1.Parameters("@AE") = Me.AE
            cmd1.Parameters("@requester") = "Management"
            cmd1.Parameters("@remove") = remove
            
            Set rst1 = cmd1.Execute()
            
            'Message will come back from sql.
            'Inserted, Updated, or "Meeting request already made today
            MsgBox rst1![replyMessage], vbOKOnly, "Submitted"
    
            Form.Requery
            Me.Bookmark = vBM  ' restore previous location

Open in new window

0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 38803025
OK ... let's assume you have an auto counter for Primary Key (?)

Dim lID as Long.
lID = Me![ID] ' get current record ID

' code, requery, etc.

With Me.RecordsetClone
    .FindFirst  "[ID] =" & lID
     Me.BookMark = .Bookmark
End With

I guess the BM gets wiped when the Requery happens

Note:  I used ID as an example field name.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:lrbrister
ID: 38803081
Going to try that.
When I set a integer variable to CurrentRecord I get the right number.

In a datasheet view is there no way to then go to that record?

The can't actually create a new record.
They're just changing the status on that particular record
0
 

Author Comment

by:lrbrister
ID: 38803099
databaseMX

Tried your last example.
Getting error message on .FindFirst

Screenprint
0
 
LVL 75
ID: 38803123
"When I set a integer variable to CurrentRecord I get the right number."
??

Why do you have the DoCmd.GoToRecord?  You don't need that.
Setting the Form Bookmark will go to the record.

Also, seems the error is occurring elsewhere ?
0
 

Author Comment

by:lrbrister
ID: 38803176
DatabaseMX
In my datasheet view, I scroll down to whatever record I wanted to change

When I clicked on it I can capture the "CurrentRecord"

After my requery I'm doing a DoCmd.GoToRecord

Is there something I'm missing and a reaso I should not do this?
I'm not the pro in MS Access by any means and just want the best solution.

Here's what's actually working now.
Should I not be doing this?

codeprint
0
 
LVL 75
ID: 38803280
Well, if it's working, I cannot argue with that :-)

It's just that I'm not sure CurrentRecord will be intact after a Requery ... but if it seems to be working, then I guess you are good to go.

Is this Form bound to a Access table or query ?

mx
0
 

Author Comment

by:lrbrister
ID: 38803292
DatabaseMX
Its bound to a SQL Server 2005 view

screenprint
0
 
LVL 75
ID: 38803449
Well, I'm not sure that RecordsetClone (etc) work in that case ... but no matter, if what you have is working ...

mx
0
 

Author Closing Comment

by:lrbrister
ID: 38807119
This pointed me in a "process" right direction.

Sometimes, just having an idea where to start works wonders.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question