Solved

MS Access 2003 Datasheet view and record update

Posted on 2013-01-21
11
693 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 - Access MVP) 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
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.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

778 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