Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MS Access 2003 Datasheet view and record update

Posted on 2013-01-21
11
Medium Priority
?
704 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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 …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

604 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