Solved Position form to added record

[ClaraBarton]

I know how to get the ID (DMax) of a new record but I fill in (Update) unused records that aren't in the form recordset until the checkbox inuse is positive.
I'm stumped as to how to get the ID if it isn't a new record. These records aren't included in the forms recordset until the table is requeried.
I need to know the ID to position to the added (updated) record.

Public Sub AddItem(ID As Long, strITPath As String)
    Dim strCriteria               As String
    Dim lDocNo                  As Long
    Dim strSql                     As String
    Dim rst                         As DAO.Recordset
    Dim strBookmark        As String
 
    lDocNo = AddDocNo(ID)
    strCriteria = "fLocID = " & ID & " AND DocNo = " & lDocNo
 
    If DCount("ItemID", "tblItems", strCriteria) = 0 Then
           strSql = StringFormatSQL("INSERT INTO tblItems (fLocID, DocNo, ITPath, Item, InUse)" & _
                    " values ({0}, {1}, {2}, '(New)', True);", _
                    ID, lDocNo, strITPath)
      Else
          strSql = StringFormatSQL("UPDATE tblItems" & _
                                " SET ITPath = {0}," & _
                                " Item = '(New)'," & _
                                " InUse = True" & _
                                " WHERE flocID = {1} and" & _
                                " DocNo = {2};", _
                                strITPath, ID, lDocNo)
    End If

SQLExecute (strSql)

Set rst = Me.RecordsetClone
    strBookmark = rst.Bookmark  'isn't in recordset before requery (no current record)
    Me.Requery
    Me.Bookmark = strBookmark
 Set rst = Nothing

End Sub

 

[theDBguy]

I could be wrong, but it looks like the ID is passed to the function, so maybe you can just search for it? For example, use FindFirst?

Sent from phone…

 

[ClaraBarton]

No, that's an ID that connects it as a subform. It's a parent ID and there are many subrecords. The record ID is created on the INSERT or it's located on the UPDATE

 

[ClaraBarton]

What I need to know is how can I grab the record ID right after the ExecuteSQL.. whether it's a new record or an update of a previous record.

 

[theDBguy]

What I need to know is how can I grab the record ID right after the ExecuteSQL.. whether it's a new record or an update of a previous record.

Did you post the code for ExecuteSQL? You might/should be able to use the LastModified property in it.

 

[arnelgp]

maybe you can test this:

Public Sub AddItem(ID As Long, strITPath As String)
    Dim strCriteria               As String
    Dim lDocNo                  As Long
    Dim strSql                     As String
    Dim rst                         As DAO.Recordset
    Dim strBookmark        As String
      
    lDocNo = AddDocNo(ID)
    strCriteria = "fLocID = " & ID & " AND DocNo = " & lDocNo
 
    
    If DCount("ItemID", "tblItems", strCriteria) = 0 Then
           strSql = StringFormatSQL("INSERT INTO tblItems (fLocID, DocNo, ITPath, Item, InUse)" & _
                    " values ({0}, {1}, {2}, '(New)', True);", _
                    ID, lDocNo, strITPath)
      Else
          strSql = StringFormatSQL("UPDATE tblItems" & _
                                " SET ITPath = {0}," & _
                                " Item = '(New)'," & _
                                " InUse = True" & _
                                " WHERE flocID = {1} and" & _
                                " DocNo = {2};", _
                                strITPath, ID, lDocNo)
    End If

SQLExecute (strSql)



'Set rst = Me.RecordsetClone
    'strBookmark = rst.Bookmark  'isn't in recordset before requery (no current record)
    'Me.Requery
    'Me.Bookmark = strBookmark
 'Set rst = Nothing

'arnelgp
'requery the recordset
With Me.Recordset
    .Requery
    'find record (old or new)
    .FindFirst strCriteria & " and Item = 'New' and ITPath = '" & strITPath & "' and InUse = -1"
End With

End Sub

 

[ClaraBarton]

Of Course! Why couldn't I think of that? Thank you!
I tried setting the recordset from the table and then bookmarking the .last modified before requerying the form but I couldn't get a valid bookmark that way. Why is that?

 

[arnelgp]

When you Requery the form, all Bookmarks are reset (invalidated), and the Bookmark that
you saved will not guarantee to put you on correct record.

 

[ClaraBarton]

Oh! The things I don't know!
Actually just strCriteria works. Thank you so much.