Solved Position form to bookmark (1 Viewer)

ClaraBarton

Registered User.
Local time
Today, 15:19
Joined
Oct 14, 2019
Messages
197
In the following code all the variables test right, the proper record is created and everything is right BUT the form will not move to the new record.
Code:
Private Sub btnNew_Click()
On Error GoTo btnNew_Click_Err

  Dim db As DAO.Database
  Set db = CurrentDb
  Dim rst As DAO.Recordset
  Dim intDoc As Integer
  Dim strWhere As String
  Dim strFind As String
  Dim intDrawer As Integer
  Dim intNewID As Integer
  Dim strSql As String
 
  Me.FilterOn = False
        
'get the drawer
intDrawer = PopupValue("popDrawer", "lstLocation")
Set rst = Me.RecordsetClone
strFind = "[InUse] = False And [fDrawerID] =" & intDrawer
    
    If rst.RecordCount <> 0 Then
        rst.MoveLast
        rst.MoveFirst
    End If
    
  'find an empty record to fill in
     rst.FindFirst strFind
        If Not rst.NoMatch Then
           Me.Bookmark = rst.Bookmark
        Else
            
   'If there are no empty records, tack them on the end
               With rst
                    .AddNew
                       ![Modified] = Date
                       ![Description] = "<New Item>"
    'grab the new ID for the detail table
                       intNewID = ![ItemID]
                    .Update
    'requery the form to pick up the new record
                    Me.Requery
                End With
                rst.Close
                Set rst = Nothing

     'reposition the form
            Set rst = Me.RecordsetClone
            strWhere = "ItemID = " & intNewID
                If Not rst.EOF Then
                    rst.FindFirst strWhere
                        Me.Bookmark = rst.Bookmark
                End If
          
        End If
  
   'create a matching record in the detail table
    intDoc = Nz(DMax("DocNo", "qryItems1", "[fDrawerID]=" & intDrawer), 0)
    intDoc = intDoc + 1

    strSql = "INSERT INTO tblItemDetail ([DocNo], [fDrawerID], [fItemID], [InUse]) " & _
                "VALUES (" & intDoc & "," & intDrawer & "," & intNewID & "," & -1 & ");"
      
       With CurrentDb
            .Execute strSql
       End With
  
    On Error Resume Next
    
    rst.Close
    Set rst = Nothing
    Me.Requery
    DoCmd.GoToControl "Item"

btnNew_Click_Exit:
    Exit Sub

btnNew_Click_Err:
     MsgBox Error$
     Resume btnNew_Click_Exit

End Sub
 

Minty

AWF VIP
Local time
Today, 23:19
Joined
Jul 26, 2013
Messages
9,288
Are you sure that intNewID is being set when the new record is added?
I would have thought that the ID wasn't set until after the update to the recordset was issued?
 

ClaraBarton

Registered User.
Local time
Today, 15:19
Joined
Oct 14, 2019
Messages
197
This is the record I want to go to:
Code:
?strWhere
ItemID = 208
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:19
Joined
Feb 19, 2002
Messages
36,352
The bookmark is no longer valid once you requery the form. Rather than saving the bookmark, save the primary key and reposition based on that.
 

Minty

AWF VIP
Local time
Today, 23:19
Joined
Jul 26, 2013
Messages
9,288
Okay - I always use a

If Not rst.NoMatch Then

to ensure a match is found, it might be worth adding it in?
You use it in the first find earlier in the code?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:19
Joined
May 7, 2009
Messages
16,419
on previous post i already informed you that you must Save the record
first before going back and get the newID:

Code:
'grab the new ID for the detail table
                    .Update
                    .bookmark = .lastmodified
                    intNewID = ![ItemID]
 

ClaraBarton

Registered User.
Local time
Today, 15:19
Joined
Oct 14, 2019
Messages
197
You're right, I'm still messing with the same thing. I did as you said. Repeatedly. Still won't move to new record.
Code:
Private Sub btnNew_Click()
On Error GoTo btnNew_Click_Err

  Dim db As DAO.Database
  Set db = CurrentDb
  Dim rst As DAO.Recordset
  Dim intDoc As Integer
  Dim strWhere As String
  Dim strFind As String
  Dim intDrawer As Integer
  Dim intNewID As Integer
  Dim strSql As String
 
  Me.FilterOn = False
        
'get the drawer
intDrawer = PopupValue("popDrawer", "lstLocation")
Set rst = Me.RecordsetClone
strFind = "[InUse] = False And [fDrawerID] =" & intDrawer
    
    If rst.RecordCount <> 0 Then
        rst.MoveLast
        rst.MoveFirst
    End If
    
  'find an empty record to fill in
     rst.FindFirst strFind
        If Not rst.NoMatch Then
           Me.Bookmark = rst.Bookmark
        Else
            
   'If there are no empty records, tack them on the end
               With rst
                    .AddNew
                       ![Modified] = Date
                       ![Description] = "<New Item>"
    'grab the new ID for the detail table
                    .Update
                    .Bookmark = .LastModified
                    intNewID = ![ItemID]
    'requery the form to pick up the new record
                    Me.Requery
                End With
                rst.Close
                Set rst = Nothing
    
     'reposition the form
            Set rst = Me.RecordsetClone
            strWhere = "ItemID = " & intNewID
                If Not rst.EOF Then
                    rst.FindFirst strWhere
                        Me.Bookmark = rst.Bookmark
                End If
          
        End If
  
   'create a matching record in the detail table
    intDoc = Nz(DMax("DocNo", "qryItems1", "[fDrawerID]=" & intDrawer), 0)
    intDoc = intDoc + 1

    strSql = "INSERT INTO tblItemDetail ([DocNo], [fDrawerID], [fItemID], [InUse]) " & _
                "VALUES (" & intDoc & "," & intDrawer & "," & intNewID & "," & -1 & ");"
      
       With CurrentDb
            .Execute strSql
       End With
  
    On Error Resume Next
    
    rst.Close
    Set rst = Nothing
    Me.Requery
    DoCmd.GoToControl "Item"

btnNew_Click_Exit:
    Exit Sub

btnNew_Click_Err:
     MsgBox Error$
     Resume btnNew_Click_Exit

End Sub
 

Minty

AWF VIP
Local time
Today, 23:19
Joined
Jul 26, 2013
Messages
9,288
As @Pat Hartman said - and I missed it - you are issuing a form requery right at the end of your code, which will render the bookmark obsolete!
 

ClaraBarton

Registered User.
Local time
Today, 15:19
Joined
Oct 14, 2019
Messages
197
Well there you go!!!!! Thank you so much. I commented out requery and it works like a charm. NEVER would have figured it out on my own
 

Users who are viewing this thread

Top Bottom