"No current Record" after form requery

[Neruda]

Hi,

after I requery a form, if I do

   Me.Requery
   Dim rs As DAO.Recordset
 
 
   Set rs = Forms!frmMain.Recordset
          With rs                    
              .Edit
                    !fkLocationID = Me.lbxLocation
                .Update
              End With

This throws an error at .Edit. Why?

if I do
Dim rs As DAO.Recordset

Set rs = Forms!frmMain.Recordset
                 With rs
                     .Bookmark = Forms!frmMain.Bookmark
                    .Edit
                        !fkLocationID = Me.lbxLocation
                    .Update
                 End With

It works fine but i was under the impression that after a form requery the first record is current and the recordset pointer too?

[PhilOfWalton]

I think your problem is you can edit a record within a recordset. In your first example Access hasn't the foggiest idea which record needs changing. The bookmark defines which record you are talking about.

Phil

[Neruda]

ok but the form shows me the first record in the form textboxes. Isn't that the current record?
I thought the recordset cursor moves together with the form and the recordsetclone is indipendent.

[PhilOfWalton]

Until you click on a record, it isn't the current record - just the one that is showing.

Phil

[Neruda]

should't it happen also when a form loads for the first time too? doesn't seem to happen when it is loaded. i also get the error when i close the form using the X button, but it seems random. Not sure what I am doing wrong

[NeoPa]

PhilOfWalton:
Until you click on a record, it isn't the current record - just the one that is showing.

I'm sorry Phil but that's not correct. The Form_Current() event occurs after the form's opened when the first record is selected.

[Neruda]

So I am still not clear.
every time a form is opened or a form is required, the Form_Current()fires. In both cases the first record in the recordset is selected.
So why is Access complaining that there is not current record if I do (after a requery) but not when loading the form.

    Me.Requery
    Dim rs As DAO.Recordset
 
    Set rs = Forms!frmMain.Recordset
    With rs                    
        .Edit
        !fkLocationID = Me.lbxLocation
        .Update
    End With

[NeoPa]

Neruda:
So why is Access complaining that there is not current record if I do (after a requery) but not when loading the form.

If you do what Neruda? Your post is unclear and the code posted is a mess. How can you expect intelligent responses when you take no care over what you post.

I also had to reset your selection of Best Answer. It is not appropriate to click that unless it answers your question. As that particular post is inaccurate, and you are continuing to request help, then it clearly didn't. It's not fair on anyone to have a Best Answer post reset due to your carelessness.

[NeoPa]

It's possible to have a form where no record is current. However, this is not the case when there are any records available and the form is just opened. More I can't say as what you describe is so unclear. I don't even know where in that code - if even anywhere in that code - that you get a message saying no current record.

[Neruda]

That code looks brutal indeed. Apologies to anyone trying to help/reading this. I admit I wrote it in a hurry.

Phil answered the question, I was trying to understand why I need to bookmark the recordset after requerying a form if I want to edit the recordset. I assumed that, after a form requery, the record shown in the form is the current one.


This is the code in which the error occurred ( .Edit):

Private Sub cmdTableAdd_Click()
    'On Error GoTo ErrorHandler
 
    Dim rst As DAO.Recordset
    Dim targetForm As Form
 
'open form to add table in dialog mode
    DoCmd.OpenForm "frmTableAdd", acNormal, , , , acDialog, Nz(Me!fkLocationID, 0)
 
'check if frmTableAdd is loaded or the Cancel button was pressed
    If CurrentProject.AllForms("frmTableAdd").IsLoaded = True Then
        Set targetForm = Forms("frmTableAdd")
 
'determine if the user wants to change or add a new table
        If targetForm!tbTableManagement.Value = 0 Then   
            DoCmd.OpenQuery "qry_frmMainAppend"
            Me.Requery                   
            comboLocation.Requery
            comboLocation = comboLocation.ItemData(0)
        Else                                              'change Table location
            Set rst = Me.Recordset
                With rst
        error ->    .Edit
                        !fkLocationID = targetForm!lbxLocation
                    .Update
                End With
                Me.cmdTableSelect.Caption = Me!LocationName
                comboLocation.Requery
        End If
 
        DoCmd.Close acForm, "frmTableAdd"
 
    End If
 
ExitSub:
    Set rst = Nothing
    Exit Sub
ErrorHandler:
    MsgBox "Error No: " & Err.Number & vbNewLine _
         & "Error Details: " & Err.Description & vbNewLine _
         & "Error in Sub: frmTableAdd\cmdTableAdd_Click"
    Resume ExitSub
 
End Sub