edit recordset

myrt

Registered User.
Local time
Today, 05:10
Joined
Apr 22, 2015
Messages
34
Ok, so I seem to always make stupid mistakes, and I imagine this not to be a different case. However posting speeds up the process of finding the misspell. So, at the cost of annoying you, I'll risk my hide. Hope you take pity in me and try to give me a hand :rolleyes:

I need to update/edit the current recordset. The code I wrote works the first time I set focus to the record, afterward it says "error 3021: no current recordset".
If I set focus on another record and then return to edit this record, it newly works.

What I'm trying to do:
I have a continuous subform with a list of materials. On the side of each there's a button that opens a popup window with all the materials. From this window I double click and update the record.
I'm doing it through a recordset because I absolutely need to add a foreign key to the record that otherwise I wouldn't be able to (with a variable).

Code:
Private Sub cmdInsert_Click()
Dim varIDArt As Single
Dim varIDDett As Single
Dim rst As Recordset
Set rst = Me.Recordset

If Me.Parent.lstArticles.ListIndex <> -1 Then
varIDArt = Me.Parent.lstArticles.Column(1) 
varIDDett = Me.Parent.lstArticles.Column(0)
Else 'at the beginning there's no selection
varIDArt = Me.Parent.lstArticles.Column(1, 0)
varIDDett = Me.Parent.lstArticles.Column(0, 0)
End If

DoCmd.OpenForm "RisultsMaterials" 'from where I choose the Material

Do While CurrentProject.AllForms("RisultsMateriali").IsLoaded
    DoEvents
Loop

If Me.NewRecord Then
rst.AddNew
rst!MaterialID = varIDMAT  'this variable gets its value in the popup window
rst!DettLavID = varIDDett
rst!ArticoloID = varIDArt
Else
rst.Edit '----> this is the line underlined that says "no current recordset"
rst!MaterialID = varIDMAT
End If

rst.Update
Me.Requery

End Sub
 
I'm doing it through a recordset because I absolutely need to add a foreign key to the record that otherwise I wouldn't be able to (with a variable).
I'm surprised that no one has replied to your post yet, it seems everyone is busy this morning. ;)

You can do what you want using a bound form; the rationale behind using a recordset for this sort of simple task isn't very strong, so perhaps you want to consider doing things differently.

In any case, try setting the bookmark before editting the record:
Code:
rst.Bookmark = Me.Bookmark
rst.Edit
 
Thanks!!! It worked!

I am using a bound (sub)form (that runs a SQL recordsource with some variables), it's just that I need to edit/insert some info in fields that don't have corrisponding bound controls. So I thought of accessing those field directly through a recordset. Was I wrong?

Also, if I may ask, how come that the code runs the first time around but not immediately thereafter? What happens? I mean, what's the reason behind this behaviour?

I'm surprised that no one has replied to your post yet, it seems everyone is busy this morning. ;)

You can do what you want using a bound form; the rationale behind using a recordset for this sort of simple task isn't very strong, so perhaps you want to consider doing things differently.

In any case, try setting the bookmark before editting the record:
Code:
rst.Bookmark = Me.Bookmark
rst.Edit
 
Without using a recordset, you can still access the fields using this notation Me!FieldName

Regarding the bookmark, from memory, it's bound to the recordset but it's not synched with the current record on the form. The bookmark property ensures that it's in synch.
 
Thanks again! Well, today I learned sth. new. :)) I'll keep it in mind for next time
 

Users who are viewing this thread

Back
Top Bottom