in memory ADO-recorset: no current record

[ino_mart]

All

I use MS Access 2010.

I have code below and created a form with two textboxes where property "control set" is set to "first" and "last" respectively. I enabled the reference "Microsoft ActiveX Data Object 2.8 Library".

Private Sub Addnew(ByRef rstADO As ADODB.Recordset, strFirst As String, strLast As String)
rstADO.Addnew
rstADO.Fields("First") = strFirst
rstADO.Fields("Last") = strLast
rstADO.Update
End Sub
 
Private Sub Form_Load()
Dim cn As ADODB.Connection
Dim strSQL As String
Dim rstADO As ADODB.Recordset
Set rstADO = New ADODB.Recordset
With rstADO
    .Fields.Append "ID", adInteger, , adFldKeyColumn
    .Fields.Append "First", adVarChar, 100, adFldMayBeNull
    .Fields.Append "Last", adVarChar, 100, adFldMayBeNull
 
    .CursorType = adOpenDynamic
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open
End With
Addnew rstADO, "John", "Little"
Addnew rstADO, "Bill", "Smith"
Set Me.Recordset = rstADO
End Sub

When I open the form, it displays the names. I am able to edit these two records. I can go to a third row to add a new record. However, as soon I type something in this third row, I get error message "no current record".

How can this be solved?

Regars
Ino

 

[spikepl]

I had played with this and like so many others got stuck on adding new records and gave up.

Check out this link varey carefully: http://stackoverflow.com/questions/10458418/in-memory-stand-alone-disconnected-ado-recordset

 

[ino_mart]

Thanks, the referenced article contained the solution. I did not had the code in the "before insert"-sub. I can now add records in the form without problems.

 

[spikepl]

Thanks for the feed back. That's interesting - I might take this up again.

 

[ino_mart]

There is actually one more thing: the code in the referenced article works except if all of your input fields are comboboxes (which is true in my case). In my previous test, these were still text boxes.

In this case it seems you have to select your value twice as from the second record you try to create. If you only select the value once, the record is not written and you get errors.

However, I found an easy solution by adding an "ON ENTER"-event on the comboboxes

'Me.txtID should be replaced by the field which stores the unique ID
If IsNull(Me.txtID) Then
    Form_BeforeInsert False
End If