?
Solved

MS Access 2003 Datasheet view and record update

Posted on 2013-01-21
11
Medium Priority
?
701 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

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!

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Suggested Courses

777 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