VBA Access - Empty Recordset (error 3021) in Form.Current event

0
votes

I am attempting to perform some actions based on the current value of fields in the Form.Recordset in the Form.Current event.

An example would be If Me.RecordSet("LastUpdate") < "#1 Jan 2020#" Then MsgBox "Last year's Comment"

Me.RecordSet could be something like "SELECT ID, LastUpdate, Comment FROM Comment WHERE ID = 12" or it could also return several records, e.g. "SELECT ID, LastUpdate, Comment FROM Comment WHERE ID < 12"

I get the Error 3021: No current record on the above line. It appears that although the Form.Current event has launched and the Me.Recordset Property correctly gives the underlying SQL, the recordset has not been populated yet?

If in the immediate pane when when debugging this line I do Me.Recordset.MoveFirst it then works. But of course, resetting the current record to the first record every time the Form.Current event fires is not the way forward...

It seems strange to me that in the Form.Current event, the Form.Recordset has not yet been populated? Is this right? Any thoughts on the correct way to do what I am trying to do?

The underlying tables are on a MySql server, although that should be irrelevant?

Edit: It appears that the Form.Current event fires 3 times... and only the last time triggers this error. So ignoring the error could be viable, but clearly I'd like to understand why that event is fired 3 times... I will need to look at the calling code.

Still not sure I understand how/why Form.Recordset would become invalidated.

2
mysqlvbams-access
On error resume next would mean that the code will not be run. I am trying o get the code to run (correctly). - Morten
I do not see how LastUpdate can help me get the value out of the recordset bound to the form? The values I am retrieving is in the Form.Recorset but not bound to a control. Using the Recordset to access the bound Recordset works well but it appears to not be populated at the time the Form.Current Event is triggered. - Morten
Please comment on the answer you are referring to. Did you try Me.LastUpdate.Value ? - Andre
And please read the doc page I linked. It explains what to do and what not to do with a Form.Recordset. The form's current record follows its .Recordset - but not the other way around, which is exactly your problem. - Andre
@Andre Me.LastUpdate.Value in the immediate pane gives 'Method or data member not found'. LastUpdate does not appear to be recognised by Access VBA. Did you mean something else?. The Form.Recordset doc page you linked to says "Use the property to use methods with the Recordset object that aren't directly supported on forms" which is what I am trying to do. - Morten

2 Answers

1
votes

Don't use Me.RecordSet("...") to retrieve values. You mainly need this to set the RecordSet property of the form: https://docs.microsoft.com/en-us/office/vba/api/access.form.recordset

To get values of the current record, use

Me!LastUpdate.Value     ' for the control
Me("LastUpdate").Value  ' same
Me.LastUpdate.Value     ' for the bound column
0
votes

Either you use the field link directly from the form

Me!LastUpdate

Or use the linked control (if control is bound to the field)

Me.BoundControl

Or if you need to use the recordset then you need to use the recordset clone and synchronize it to the form

Me.RecordsetClone.Bookmark = Me.Bookmark
Me.RecordsetClone.Fields("LastUpdate") = ...