Search code examples
vbams-accessadorecordset

Populate field in ms access form from field in ado recordset record of the same name

I have a form in Access that I am trying to populate during it's Current event using the fields from an ADO Recordset. I am using Sql Server for the database and am using the recordset to try to populate to corresponding fields on the form with what is in the recordset. Right now it works like this:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_FormOpen

    Set rst As new ADODB.Recordset
    With rst
        .ActiveConnection = CurrentProject.AccessConnection
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Source = "SELECT * FROM [Outage Summary] ORDER BY OutageSummaryID"
        .Open
    End With
    Set Me.Recordset = rst

End Sub


Private Sub Form_Current()
    OutageSummaryID.Value = Me.Recordset!OutageSummaryID
    Dispatcher.Value = Me.Recordset!Dispatcher
    StartDateTime.Value = Me.Recordset!StartDateTime
    Location.Value = Me.Recordset!Location
    CityRelated.Value = Me.Recordset!CityRelated
    Scheduled.Value = Me.Recordset!Scheduled
    CustomerEquip.Value = Me.Recordset!CustomerEquip
    DispatchBusHrs.Value = Me.Recordset!DispatchBusHrs
    TotalCount.Value = Me.Recordset!TotalCount
    Substation.Value = Me.Recordset!Substation
    CompletionDateTime.Value = Me.Recordset!CompletionDateTime
    CustCallDateTime.Value = Me.Recordset!CustCallDateTime
    FirstRespDateTime.Value = Me.Recordset!FirstRespDateTime
    Feeder.Value = Me.Recordset!Feeder
    SwitchingSchedule.Value = Me.Recordset!SwitchingSchedule
    Cause.Value = Me.Recordset!Cause
    ActionTaken.Value = Me.Recordset!ActionTaken
    Me.ME.Value = Me.Recordset!ME
    MI.Value = Me.Recordset!MI
End Sub

But I would like the current subroutine to work something like this:

Dim fld As ADODB.Field
Dim nam As String
For Each fld In Me.Recordset.Fields
    Debug.Print fld
    nam = fld.Name
    Me.Controls(nam).Value = fld.Value
Next

With the code as it stands I am getting an error "The recordset is not updatable" Thanks for any help!

Solution

  • This is because you are not binding to the recordset. It would be better if you stored your query and attached it to the form as the recordsource so that way it will bind it to the form. Then you set the record source of each field in design mode and no code is needed and it will be updateable depending on your permission to the SQL Server.