Possible? Return a value from a new record created with an append query

[DepricatedZero]

I need to have a query create a new record, and then somehow get the primary key for the record it just created.

Right now I'm doing it through a DAO.RecordSet to open an existing query, add a new record, and then with the recordset at the new record, assign the autogenerated key back out to the form. The problem with this is that doing queries through code in this fashion seems to take exponentially longer than simply using DoCmd.OpenQuery "query"

I'm trying to find ways to speed up the application, and the biggest drag is the heavy use of RecordSets for this purpose.

[June7]

Why using a query to create record? Where does this data for new record come from? Why not move to new record on form and populate fields? Want to show some code for analysis?

[rpeare]

http://stackoverflow.com/questions/6...499393#6499393

Check out this thread. Take note of the warning though, in multi user environments determining the PK of a 'next' record is risky.

In order to identify the record you just added you'd have to refresh the contents of your database (probably) then do a dlookup("max([PK_Fiedl])", "TableName") to return the largest PK after you've added a record. Again in a multi user environment this would be risk in that if you add your record then someone adds a record immediately after you your code may pick up the wrong 'new' record.

[DepricatedZero]

That's pretty much the problem rpeare. I'm building for a multi user environment - so I need to reliably get the key. The way I'm doing it now works, it's just slow and I was hoping to speed it up.

June7 - to explain what I'm trying to do:

I have 3 inter-related tables. We'll call them Process 1, Process 2, and Incident. Incidents are the central point - any time a Process is created, an Incident needs to be created. 1-to-1-to-1. Any Process MUST have an Incident, but it doesn't necessarily require the other process to be initiated. An Incident can have one of each Process active.

So Form for Process 1 is loaded, it runs this code (it has been edited so only the really relevant parts are present):

Private Sub Form_Load()
    Dim key As String
    key = IIf(IsNull(Me.OpenArgs), 341, Me.OpenArgs)
    If LoadIMSection(key) Then
 End Sub  
Private Function LoadIMSection(key As String) As Boolean
    LoadIMSection = False
    If key = "New" Then
        SetupNewProcess
        LoadIMSection = True
        Exit Function
    End If

(lots more code here, I'll post below when it's relevent)

End Function

Private Sub SetupNewProcess()
    SetDefaultNew
    CreateNewRecord
    
    
End Sub
 
Private Sub CreateNewRecord()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim def As DAO.QueryDef
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("qryIMProcess_NEW")

    rs.AddNew
    
    Me.txtIMID.value = rs!lngIMIDCnt
    
    cboIM = GetName
    cboCA = GetName
    cboScribe = GetName
    
    rs!strIM = GetName
    rs!strCA = GetName
    rs!strScribe = GetName
    rs.update
    rs.Close
End Sub

This creates the initial record that most of the form handles. At this point, not far different from having it bound to a query. However, I can't bind the form because of record locking - I need multiple people to access the same record through the same form.

So then whenever I save the form, a lot of data is aved to the Incident table instead of the Process table. Here's how that's handled:

 Private Function NoAssociatedIncident() As Boolean
    If Me.cboIncidentID.value = "" Or IsNull(cboIncidentID.value) Then
        Debug.Print "no associated incident"
        NoAssociatedIncident = True
    End If
End Function


Private Function SaveIMSection(key As String) As Boolean
On Error GoTo ErrorHandler
    If NoAssociatedIncident Then
        CreateIncident
    End If

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim def As DAO.QueryDef
    
       
    Set db = CurrentDb
    Set def = db.QueryDefs![qryIMProcess_PARAM]
    
    def.Parameters![IMID] = key
    
    Set rs = def.OpenRecordset
    If rs.RecordCount = 0 Then
        Exit Function
    End If

    rs.Edit

    rs!strIM = cboIM.value
    'TO SAVE EYE BLEED I'M CUTTING OUT THE ASSIGNMENTS
    'Theres a bunch like this, like 30 items that get assigned rs!field = control.value

rs.update
    rs.Close
    SetIncidentManager

ErrorHandlerExit:
    Exit Function
    
ErrorHandler:
        If Err = 3021 Then
        'No Current Recordset
        MsgBox "There is no current record. Either the record has been deleted or an error occurred. Please check with the Incident Manager for further details."
        rs.Close
        DoCmd.Close "frmIncidentManagement"
        Exit Function
    End If
    
End Function

Private Sub CreateIncident()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("qryNewIncident")
    
    rs.AddNew
    rs!lngIMIDCnt = Me.txtIMID
    Me.cboIncidentID.value = rs!lngIncidentID
    Me.cboIncidentID.Locked = True
    
    rs.update
    rs.Close
    
    Dim def As DAO.QueryDef
    Set def = db.QueryDefs![qryIMOnly_tblIMProcess_PARAM]
    
    def.Parameters![IMID] = txtIMID
    
    Set rs = def.OpenRecordset
    rs.Edit
    rs!lngIncidentID = cboIncidentID.value
    
    rs.update
    rs.Close
    
End Sub

So if it attempts to save to the Incident Table but finds no associated record, it creates one this way.

[June7]

Multiple people editing same record at same time? I can't even imagine situation that would happen in.

[DepricatedZero]

Well, I'm working on breaking away from access and into VB.NET with MSSQL, which handles concurrent users in a record, but for now I have to fake it :/

[rpeare]

if you are creating an incident every time you create a create a process you don't need to identify the 'new' record at all. You'd just have to make sure all processes have an incident which can probably be done with a simple append query and no need to do anything fancy. You'd just have count your records by PROCESS and anything that had a 0 count in the incident table, create a record.

[DepricatedZero]

The problem I'm running in to seems to be that the Incident is the parent table of both Process types. Even if I build a form based on a query that combines the two tables, it only creates the record in the Process table and then throws an error about not having an associated record in the Incident table.

[rpeare]

That fact still remains you are trying to synchronize two tables. So... let's say during your data entry you create a process, what you're really doing is creating an incident first, then pulling that 'new' incident and generating a process with the 'new' incident number.

If every incident has a process you don't need to create a record with the 'new' incident number.

Create a new incident. (that part works)
refresh your data if necessary
Run an append query that adds a new records to your process table if one does not exist for the incident number. It does not have to be tied to the incident number you just created.

Once both queries have run and data been refreshed (as needed) you should be good.

[DepricatedZero]

Hm. I'll see what I can manage with that. I was going about it by trying to create the Process first.