Solved

MS Access 2003 Datasheet view and record update

Posted on 2013-01-21
11
698 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 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

696 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