Kamis, 17 Maret 2016

Re: [MS_AccessPros] Working with recordset with SQL back end

 

Adam-


Did you try the DAO code in the article I sent you?  Basically, you should be able to use the LastModified property to move back to the newly added record, then grab the Identity value:

    Set Db = DBEngine(0)(0)
    Set rst = Db.OpenRecordset("tblParentListBE", dbOpenDynaset, dbAppendOnly+dbSeeChanges)
    rst.AddNew
    rst![ParentName] = Me.ClientName
    rst.Update
    rst.BookMark = rst.LastModified
    tID = rst!ParentID
    rst.Close
    Set rst = Nothing

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)



On Mar 17, 2016, at 5:27 PM, runuphillracing@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Bill,

Can you help? Your advice in the link didn't work.

Track Autonumber new record Addnew or append qry

 


---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

This article might help:



John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)



On Mar 17, 2016, at 4:04 PM, runuphillracing@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

That gives me RT error 3021, No current record.

Anyone?



---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Adam-

Oh, I didn't notice that you were trying to capture the Identity value.  There's a way to do that, but I don't remember.  It *might* work if you move the tID = after the Update.

Anyone else want to jump in?

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)



On Mar 16, 2016, at 10:37 PM, runuphillracing@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,


I think I need to put "+ dbSeeChanges" after "dbAppendOnly"


Then, I get an error Invalid use of Null on

    tID = rst![ParentID]


rst![ParentID] evaluates to null


Adam

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Adam-

I don't see anything you need to change if you're using linked tables to SQL Server.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)



On Mar 16, 2016, at 8:43 PM, runuphillracing@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

How do I change the following code to work with a SQL back end (as opposed to an accdb BE)?


    Set Db = DBEngine(0)(0)
    Set rst = Db.OpenRecordset("tblParentListBE", dbOpenDynaset, dbAppendOnly)
    rst.AddNew
    rst![ParentName] = Me.ClientName
    tID = rst![ParentID]
    rst.Update
    rst.Close
    Set rst = Nothing


Thanks

Adam







__._,_.___

Posted by: John Viescas <johnv@msn.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (8)

Save time and get your email on the go with the Yahoo Mail App
Get the beautifully designed, lighting fast, and easy-to-use, Yahoo Mail app today. Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.


.

__,_._,___

Tidak ada komentar:

Poskan Komentar