Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old February 5th, 2009, 07:42 PM
Registered User
 
Join Date: Feb 2009
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Smile Find Last Record and Add new keyed record

I want to write VBA code (Access 2007) triggered from a double click Event on a field in a Form. The table is an accdb format.
I want to find the last key in the table displayed with this Form and then add 1 to that key and write a new blank record with that key. This key is the Primary Key field. I want to refresh the Form so that the new blank record is the current record and is displayed in the Form.
I can get the code to write the new record with the proper key number but I have to click the last record key in the Form before I double click the event and I have to click the Refresh All icon in the ribbon to after the event.
I want the code to do all of the process without requiring the user to click items before and after the event.
I am new to VBA coding but have used Access 2002 some. I have purchased 4 books and still canít figure it out.
Thanks for any help.
This is my first attempt at forums.
Reply With Quote
  #2 (permalink)  
Old February 6th, 2009, 02:55 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

The easiest thing to do is to make the field an autonumber field since that does everything you asked for automatically, and the user doesn't need to provide any input or see the PK.

Can you post the code you are using if you still want to remove this automation?
__________________
mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
  #3 (permalink)  
Old February 6th, 2009, 07:46 PM
Registered User
 
Join Date: Feb 2009
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by mmcdonal View Post
The easiest thing to do is to make the field an autonumber field since that does everything you asked for automatically, and the user doesn't need to provide any input or see the PK.

Can you post the code you are using if you still want to remove this automation?
I tried using autonumber but it doesn't work for various reasons.

I am including code with things I have tried with them commented out. It moves the form to the last record but doesn't set the current record to the last record. It does add the new record OK but also doesn't refresh the record in the form.

Code:
Private Sub SKey_DblClick(Cancel As Integer)

    Dim savekey As Integer
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SowerCensus", dbOpenTable)
        ' DoCmd.GoToRecord acDataForm, "frmSower", acLast
    DoCmd.RunCommand acCmdRecordsGoToLast
        ' DoCmd.RunCommand acCmdLastPosition
        ' DoCmd.RunCommand acCmdRefreshData
        ' rst.Bookmark = rst.LastModified   ' Go to new record
    savekey = SKey + 1
    Debug.Print "Next SOWER Key is: " & savekey
    With rst
       .AddNew             ' Add new record
       !SKey = savekey     ' Set fields
       !LastName = "New Last Name"
       .Update             ' Save changes.
        ' .Bookmark = rst.LastModified   ' Go to new record
        
       Debug.Print "Current title: "
    End With
    DoCmd.RunCommand acCmdRefresh
    rst.Close
    dbs.Close
End Sub
Thanks for your help.
Reply With Quote
  #4 (permalink)  
Old February 9th, 2009, 07:24 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I would suggest using the Button Wizard, record Operations, adding the functions you want that way, and then scavenge the code if you want to use it on your buttons. Like, add a record is:

DoCmd.GoToRecord , , acNewRec

So I think the solution is there.

I can't tell you the number of times I have had to sort out someone's application because they didn't use autonumber and there were duplicate keys. Make sure you set the field to Index Yes (No Duplicates) and do the necessary error handling.

Did that help?
__________________
mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
how to add new record as first record in dataset hunzian78@yahoo.com ASP.NET 1.0 and 1.1 Professional 4 April 21st, 2006 05:23 AM
how to find record, if not found add in form Sjackson Access 1 May 27th, 2005 10:58 AM
use SQL to find a record linmu VB.NET 2002/2003 Basics 4 May 6th, 2005 05:03 PM
Function to Find a Record me2357 Access VBA 5 March 10th, 2005 02:22 PM
Find out missing record hari-kumar-vadakkeveedu SQL Server 2000 3 October 15th, 2004 01:09 AM



All times are GMT -4. The time now is 02:16 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.