Record selection in subform not working 1

[Loktar]

I know this issue has been brought up here, and many times at that. However, I've read all the posts I could find on it and still can't solve my problem.

I have a subform built off a query, and when a user clicks the "remove" button next to a record, the record is deleted from the underlying table, and a .Requery is called. This part works just fine.

The problem is that my users don't want the subform to reset to the first record after removing an item from the list (which happens after the Requery). The list can be hundreds of items long, and thus very inconvienient if they have to scroll down again to remove the next item. Here is the code I have so far for the click event of my remove command button:

Private Sub cmdRemove_Click()
    On Error GoTo Err_cmdRemove_Click
    Dim strSQL As String
    Dim curRecord As String
    
    [green]' Delete all records in linktblThumbnailList[/green]
    strSQL = "DELETE tbl_FiscalGraphList.*" & _
             "FROM tbl_FiscalGraphList " & _
             "WHERE (((tbl_FiscalGraphList.Item)=""" & Me.[Item] & """));"
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL (strSQL)
    DoCmd.SetWarnings True
    
    curRecord = Me.CurrentRecord
    
    Me.Requery
    
    Me.Parent.SetFocus
    
    If curRecord <> 1 Then
        DoCmd.GoToRecord acDataForm, Me.Parent.Name, acGoTo, curRecord
    End If

Exit_cmdRemove_Click:
    Exit Sub

Err_cmdRemove_Click:
    MsgBox Err.Description
    Resume Exit_cmdRemove_Click
    
End Sub

I've done some MsgBox debugging to make sure that curRecord is getting the right value, and even hardcoded in a value that I knew would exist, but I keep getting the error "You can't go to the specified record."

Anyone have any ideas how I can fix this? Thanks a ton in advance!

 

[RoyVidar]

Hi!

I'd try to capture the PK of the record, and the issue a .findfirst, I find that among the more reliable methods.

But here, you could try moving the assigning of currentrecord to before the delete, because after a delete, perhaps you're pointing to the deleted record.

But here's a dao .findfirst sample:

[tt]dim rs as dao.recordset
dim lPK as long
lPk = Me!txtControlHoldingPK
' assigning the sql...
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
Me.Requery
set rs=me.recordsetclone
rs.findfirst "PKField = " & lPK
if not rs.nomatch then me.bookmark=rs.bookmard
set rs=nothing[/tt]

- it needs a reference to the Microsoft DAO 3.# Object Library, and here assuming the PK is numeric and one field

Roy-Vidar

 

[Loktar]

Hey Roy,

I saw that code of yours in another thread, but I didn't understand how it could findfirst when the record that would store lPK has been deleted. How would it ever find a match? Maybe I don't understand what PK means exactly.

I just tried assigning the curRecord before the delete, but it didn't work either. I did find out that the only time the code runs without error is when it is trying to go to record 1. I'm still trying to find out exactly what that's all about. Maybe it has something to do with the fact that it's a continuous form... *shrugs*

 

[Loktar]

Now I feel slightly stupid

The reason that it "worked" when it was trying to go to record 1 was because of the if statement I had that only ran if it was not equal to 1. I had needed that for part of a test I was running on it earlier and forgot to delete it afterwards.

Anyway, thanks again and sorry about that.

 

[Loktar]

Well this looks like a new record for consecutive replies in one of my own threads, but I've figured it out thanks to the help you gave me. Here's what I ended up with:

Private Sub cmdRemove_Click()
    On Error GoTo Err_cmdRemove_Click
    Dim strSQL As String
    Dim curRecord As Long
    
    Dim rs As DAO.Recordset
    Dim sPK As String
    
    Set rs = Me.RecordsetClone
    
    sPK = Me!Item
    rs.FindFirst "Item = """ & sPK & """"
    rs.MoveNext
    
    ' If last item in the rs, find previous, not next
    If Not rs.EOF Then
        sPK = rs.Fields("Item")
    Else
        ' Already moved next, so move back 2 to get previous
        rs.Move -2
        sPK = rs.Fields("Item")
        MsgBox ("looking for " & sPK)
    End If
    
    ' Delete all records in linktblThumbnailList
    strSQL = "DELETE tbl_FiscalGraphList.*" & _
             "FROM tbl_FiscalGraphList " & _
             "WHERE (((tbl_FiscalGraphList.Item)=""" & Me.[Item] & """));"
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL (strSQL)
    DoCmd.SetWarnings True
    
    Me.Requery

    Me.Recordset.FindFirst "Item = """ & sPK & """"

Exit_cmdRemove_Click:
    Exit Sub

Err_cmdRemove_Click:
    MsgBox Err.Description
    Resume Exit_cmdRemove_Click
    
End Sub

Thanks again for your help Roy! As I'm working off of 2 hours of sleep last night it took me a while to figure out PK was primary key... Thanks for pointing me in the right direction, it's much appreciated!