473,706 Members | 7,148 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Keeping the focus on the next record when deleting a record in asubform (Access 2003)

I have a list of records in a subform that a user can either edit or
delete. This is an unbound form. If the user deletes a record, I
want to refresh the form, and then position the cursor on the next
record on that subform. This seems like that this should be a fairly
easy thing to accomplish, but I cannot get it to reposition the cursor
on the next record. The following is my code segment:

Any help would be greatly appreciated!!!

Thanks.

Stuart

----------------------------------------------
(The record source is a query that returns all the records from the
Card1 table)

Set rst = Forms![frmedit]![card1 subform].[Form].RecordsetClone

rst.Bookmark = Forms![frmedit]![card1 subform].[Form].Bookmark

RecordPos = rst.AbsolutePos ition
NCID = rst![NCID]

db.Execute "Delete * from Card1 where NCID = " & "'" & NCID & "'",
dbFailOnError

'Commit the database changes
wsp.CommitTrans

Forms![frmedit]![card1 subform].[Form].Requery

rst.MoveFirst
rst.Move RecordPos, rst.Bookmark

----------------------------------------------------

Nov 19 '07 #1
1 2473
On Mon, 19 Nov 2007 14:03:25 -0800 (PST), stuart <st***********@ ncmail.net>
wrote:
>I have a list of records in a subform that a user can either edit or
delete. This is an unbound form. If the user deletes a record, I
want to refresh the form, and then position the cursor on the next
record on that subform. This seems like that this should be a fairly
easy thing to accomplish, but I cannot get it to reposition the cursor
on the next record. The following is my code segment:

Any help would be greatly appreciated!!!

Thanks.

Stuart

----------------------------------------------
(The record source is a query that returns all the records from the
Card1 table)

Set rst = Forms![frmedit]![card1 subform].[Form].RecordsetClone

rst.Bookmark = Forms![frmedit]![card1 subform].[Form].Bookmark

RecordPos = rst.AbsolutePos ition
NCID = rst![NCID]

db.Execute "Delete * from Card1 where NCID = " & "'" & NCID & "'",
dbFailOnErro r

'Commit the database changes
wsp.CommitTrans

Forms![frmedit]![card1 subform].[Form].Requery

rst.MoveFirst
rst.Move RecordPos, rst.Bookmark

----------------------------------------------------
Bookmarks are destroyed and recreated when a form or recordset is requeried and
so are useless for navigation in this scenario.

The most reliable way to move to a specific record after a form (and it's
associated RecordsetClone) is requeried, is to store the PK value or a unique
identifier from the record you wish to move to before you do the delete and
requery, and then use this value in a FindFirst call on the recordsetClone after
it has been requeried.

'get the PK of the next record
rst.MoveNext
NextPK = rst!PkField
rst.MovePreviou s

NCID = rst![NCID]
db.Execute "Delete * from Card1 where NCID = " & "'" & NCID & "'",dbFailOnErr or

wsp.CommitTrans
Forms![frmedit]![card1 subform].[Form].Requery

rst.FindFirst "[PKField]=" & NextPK

If Not rst.NoMatch Then
Forms![frmedit]![card1 subform].[Form].Bookmark = rst.Bookmark
End If

Wayne Gillespie
Gosford NSW Australia
Nov 20 '07 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
12550
by: aaj | last post by:
Hi all I have a small but rather annoying problem with continuos forms, and am wondering if anyone can suggest a method of getting over it. The front end is Access 2002 with the BE being SQL Server 2000. I have a continuous bound form that is populated from a small recordset when the form opens. The form has lots of other bits, but my problem is with a single text box and an on-click event.
13
7489
by: Manuel Lopez | last post by:
I have a puzzling form timer problem that I didn't experience prior to Access 2003 (though I'm not sure access 2003 is to blame). Here's the situation: a computer has two access 2003 databases on it, a frontend and a backend. Case 1: If vba code on the frontend updates many rows (360,000) on the backend, a form's timer event (from the frontend) will stop firing until the user gives the form focus. (Note that the update itself always...
0
1327
by: Wayne | last post by:
Can anyone cast any light on the following. I have a database that I have written in Access 2003 using 2000 file format. Everything was working OK when running it under Access 2003 but when I tried running it under Access 2000 if fell over when I tried to open some of the reports. I then tried opening it in Access 2003 again and it brought up the message about saving a copy and repairing - but it wouldn't repair. I then decompiled,...
1
2597
by: yevgenia | last post by:
Hi there, I am developing an application in Access 2003 and need to send an email through Outlook based on the information entered on the form. Everything works except that the email is being sent as plain text so none of the formatting is retained. Also, I can't figure out how to get Outlook to pick up the autosignature. The script I'm using is: Private Sub cmdSendEmail_Click() On Error GoTo StartError Dim stLinkCriteria As String...
6
4183
by: clloyd | last post by:
I have a database with over 20 tables. I need to delete one to many record(s) in all tables by using a loop code. Some tables with have one record, multiple records or no records for the criteria given. I can not do cascade delete because there are to many tables and I don't want to write 20 plus delete queries. Does anyone have any ideas how to accomplish this.
5
2440
Dököll
by: Dököll | last post by:
Hey Fans! Perhaps you can help me... I am using MS Access 2003. I have tried a number of things to get rid of the text #Error from appearing on form fields when no data present. Tried the NZ function, also tried the IIF function, told it to give me zero if no data found, so far it seems to lead to nowhere land. What are your thoughts? Hopefully it's not very simple and I missed the boat, hee hee... Thanks for your help!
5
10535
by: Lysander | last post by:
My collegue had to buy a new laptop that came with Office 2007 already installed. She had Access 2003 installed on top, in a different directory. None of our 2003 databases will run on her laptop, all giving the error "Form Open- Event, property does not exist" or something like that. This happens with every form. We can access the database, but not open any form. Going into a module of code, we can compile ok, no errors. Checking...
4
1747
by: Queen Soso | last post by:
hi all, I have this error when I click delete link in the gridview: 'unable to cast object of type 'ASP.WebForm1_aspx' to type "system.Web.UI.WebControls.GridviewRow' I really need help as soon as possible :(
0
8778
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8693
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9281
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9150
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
6613
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5935
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4444
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4707
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
2087
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.