Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Getting primary key value after insert (2002)

    I have a question that's really from a VBA "best practices" standpoint.

    A customer record is inserted into a table via DoCmd.RunSQL, the table contains an autonumber field (ID) that is the primary key. Currenty after the record is inserted, a new recordset is opened via db.OpenRecordset and the value of the primary key is selected based on the customer value that was just inserted. The value of the primary key is then later inserted into a child table. Is there a better method to get the value of the primary key other than opening an additional recordset? I pasted the code below. Any help would be appreciated.

    Code sample:
    ...
    DoCmd.RunSQL ("INSERT INTO tblCustomer (CstmrNmbr) '" & strCstrNmbr & "'")

    Set rst = db.OpenRecordset("tblCustomer")

    'Get ID value for inserted customer
    With rst
    .Index = "CustomerNmbr"
    .Seek "=", strCstrNmbr
    If .RecordCount > 0 Then

    intStgCount = .RecordCount
    strCstrID = .Fields("ID").Value
    End If
    .Close
    End With

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting primary key value after insert (2002)

    Since you're opening a recordset anyway, you could use it to insert a record too:
    <code>
    Set rst = db.OpenRecordset("tblCustomer", dbOpenDynaset)
    With rst
    .AddNew
    !CstmrNmbr = strCstrNmbr
    strCstrID = !ID
    .Update
    intStgCount = .RecordCount
    End With
    </code>
    Alternatively, you could avoid the recordset entirely:
    <code>
    DoCmd.RunSQL "INSERT INTO tblCustomer (CstmrNmbr) VALUES ('" & strCstrNmbr & "')"
    strCstrID = DLookup("ID", "tblCustomer", "CstmrNmbr='" & strCstrNmbr & "'")
    intStgCount = DCount("*", "tblCustomer")
    </code>
    Note that I modified the SQL statement slightly, I don't think the original version was correct.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting primary key value after insert (2002)

    Thanks for the reply Hans. Couple of other questions.

    I'm going to have to do a bulk load of several thousand files, so performance is a definite consideration. From a performance standpoint is my best solution to use the recordset?

    I just assumed the ID value wouldn't available until the .Update method committed the record. So, does that means the ID (autonumber data type) value is available right after the .AddNew method, correct? I have additional fields (other than CstrNmbr) that I'll populate as well. Let's say I hit a data type error (text value to a long data type) is the ID value incremented? Which means I could have non-sequential ID values? Just thinking about potential issues.

    The type of recordset was dbOpenDynaset. What's the difference between that and dbOpenTable? Just curious.

    Not specifically for the piece of code, but in general. This database will eventually be split, keeping the backend as Jet. The current version of the mdb uses Domain aggregate functions frequently, I've read some other posts that indicate it can create performance problems. Again, from a best practices and performance standpoint, should I could consider writing VBA functions using a recordset?

    Thanks again...

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting primary key value after insert (2002)

    If you're going to add lots of records, I'd open the recordset once, add all the records you need, then close it. That will be faster than executing lots of SQL statements and using lots of domain aggregate functions.

    An AutoNumber value is available immediately after AddNew. So if the Update fails, the AutoNumber seed has already been increased, but that shouldn't matter. If a gapless sequence is essential (I cannot imagine why, but...), you shouldn't use an AutoNumber field.

    Opening a recordset with dbOpenDynaset works best if you want to be able to move around the recordset and if you want to be able to work with linked tables (i.e. in a frontend/backend design); dbOpenTable may cause errors with linked tables.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting primary key value after insert (2002)

    The user will select a folder that contains multiple source files. I'll loop through all the source files in the folder, executing this code for each file. So, I don't think there's a way I can open the recordset only once? Unless you've got a suggestion.

    I don't need a gapless sequence, was just curious at what point the autonumber was incremented.

    In general then, should I always use dbOpenDynaset? Any scenario where it wouldn't work?.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting primary key value after insert (2002)

    You could declare the recordset as a public variable (at the top of a standard module) and use it repeatedly.

    dbOpenDynaset is usually the best option, unless you need to open the recordset read-only, then you'd use dbOpenReadOnly.

  7. #7
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting primary key value after insert (2002)

    I have some other questions related to recordsets. My plan is to write VBA functions that return a desired recordset (DAO, dbOpenDynaSet as we discussed for the split .mdb environment). I would then like to use the functions(s) to assign the form/report recordset property.

    I’ve found some examples on how to do this with forms using the OpenForm method with acHidden, assigning the recordset and then setting the form to visible. Many of the forms are going to be subforms in a tab control. Should I expect any problems using this method to assign the recordset in conjution with tab controls and subforms? Any better methods to accomplish this?

    My other problem is I can’t find a way to accomplish this with reports. The OpenReport method doesn’t have appear to have the acHidden option. I found some report examples using Filtering from a pop-up form, but I don’t think that will work for me. All the reports are printed directly to .pdf. How can I accomplish this same method with reports?

    I did find a post here with code below, but it mentions that this method may not work.

    > and in the code module of the report:

    >Private Sub Report_Open(Cancel As Integer)
    > Me.RecordSource = Forms!frmTask.Recordset.Name
    >End Sub

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting primary key value after insert (2002)

    You can set the RecordSource property of the report to a table name, query name or SQL string in the On Open event of the report.

  9. #9
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting primary key value after insert (2002)

    I can assign a form's recordset property to a recordset and a report recordsource I could assign to a string? Both the recordset and string could be return values from a function correct? Are there any performance considerations assigning the recordset or recordsource properties to a DAO recordset or a string containing the SQL? How would the Jet parsing work between the two? Sorry for so many questions, I just want to understand exactly what's going on.

    I'm trying to accomplish two goals. One, I'd like one VBA module that contains all the data selection source code. Two, I want to make sure I'm following best practices from a development and performance perspective. The problem is, I'm not sure if I'm on task with item two and I can't seem to find much in the way of reference material. Any suggestions are appreciated.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting primary key value after insert (2002)

    You can set the RecordSource property of a form or report to a string that is one of the following:
    - The name of a table in the database.
    - The name of a query in the database.
    - An SQL string that selects records (SELECT ... FROM ... etc.)

    You can set the RecordSource property of a form at any time you want, but you can set the RecordSource property of a report only in design view or in the On Open event of the report; you can *not* set the RecordSource property of a report once the report has been opened.

    You can set the RecordSet property of an open form, but not that of a report. I never use this, I always use the Record Source property, or set filters. So i cannot tell you whether setting the Recordset property of a form is more or less efficient.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •