By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,471 Members | 2,264 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,471 IT Pros & Developers. It's quick & easy.

"No current Record" after form requery

P: 25
Hi,

after I requery a form, if I do



Expand|Select|Wrap|Line Numbers
  1.    Me.Requery
  2.    Dim rs As DAO.Recordset
  3.  
  4.  
  5.    Set rs = Forms!frmMain.Recordset
  6.           With rs                    
  7.               .Edit
  8.                     !fkLocationID = Me.lbxLocation
  9.                 .Update
  10.               End With
This throws an error at .Edit. Why?

if I do
Dim rs As DAO.Recordset

Expand|Select|Wrap|Line Numbers
  1. Set rs = Forms!frmMain.Recordset
  2.                  With rs
  3.                      .Bookmark = Forms!frmMain.Bookmark
  4.                     .Edit
  5.                         !fkLocationID = Me.lbxLocation
  6.                     .Update
  7.                  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?
Nov 15 '17 #1

✓ answered by 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

Share this Question
Share on Google+
9 Replies


PhilOfWalton
Expert 100+
P: 1,430
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
Nov 15 '17 #2

P: 25
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.
Nov 15 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
Until you click on a record, it isn't the current record - just the one that is showing.

Phil
Nov 15 '17 #4

P: 25
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
Nov 15 '17 #5

NeoPa
Expert Mod 15k+
P: 31,186
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.
Nov 15 '17 #6

P: 25
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.
Expand|Select|Wrap|Line Numbers
  1.     Me.Requery
  2.     Dim rs As DAO.Recordset
  3.  
  4.     Set rs = Forms!frmMain.Recordset
  5.     With rs                    
  6.         .Edit
  7.         !fkLocationID = Me.lbxLocation
  8.         .Update
  9.     End With
Nov 18 '17 #7

NeoPa
Expert Mod 15k+
P: 31,186
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.
Nov 18 '17 #8

NeoPa
Expert Mod 15k+
P: 31,186
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.
Nov 18 '17 #9

P: 25
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):



Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdTableAdd_Click()
  2.     'On Error GoTo ErrorHandler
  3.  
  4.     Dim rst As DAO.Recordset
  5.     Dim targetForm As Form
  6.  
  7. 'open form to add table in dialog mode
  8.     DoCmd.OpenForm "frmTableAdd", acNormal, , , , acDialog, Nz(Me!fkLocationID, 0)
  9.  
  10. 'check if frmTableAdd is loaded or the Cancel button was pressed
  11.     If CurrentProject.AllForms("frmTableAdd").IsLoaded = True Then
  12.         Set targetForm = Forms("frmTableAdd")
  13.  
  14. 'determine if the user wants to change or add a new table
  15.         If targetForm!tbTableManagement.Value = 0 Then   
  16.             DoCmd.OpenQuery "qry_frmMainAppend"
  17.             Me.Requery                   
  18.             comboLocation.Requery
  19.             comboLocation = comboLocation.ItemData(0)
  20.         Else                                              'change Table location
  21.             Set rst = Me.Recordset
  22.                 With rst
  23.         error ->    .Edit
  24.                         !fkLocationID = targetForm!lbxLocation
  25.                     .Update
  26.                 End With
  27.                 Me.cmdTableSelect.Caption = Me!LocationName
  28.                 comboLocation.Requery
  29.         End If
  30.  
  31.         DoCmd.Close acForm, "frmTableAdd"
  32.  
  33.     End If
  34.  
  35. ExitSub:
  36.     Set rst = Nothing
  37.     Exit Sub
  38. ErrorHandler:
  39.     MsgBox "Error No: " & Err.Number & vbNewLine _
  40.          & "Error Details: " & Err.Description & vbNewLine _
  41.          & "Error in Sub: frmTableAdd\cmdTableAdd_Click"
  42.     Resume ExitSub
  43.  
  44. End Sub
Nov 19 '17 #10

Post your reply

Sign in to post your reply or Sign up for a free account.