Field names in Access subform populated with vba recordset

Question

It seems the simplest of things are usually the most elusive.

In Access 2007, I have a subform (frmfacilityInventory) within a form (frmInventorySheet). Upon some selections in the main form, the user clicks a button and I create a blankrecordset in vba and run some queries in the background and add new records to this recordset. Upon completion, the new recordset is displayed in the subform - or so I want it to.

My fields all show #ERROR.

the new vba recordset has data and I even display a full record in text boxes to make sure everything else is working. The number of returned records in the subform are correct but still all showing #ERROR.

Looking at my subform, it has no "Record Source" set until the vba code completes building the new recordset and then sets the source of the subform to the new recordset. The subform has it's first field as "Facility". The Control Source for this field (at the form level) is also set to "Facility". My recordset has a "Facility" column. So why do I see #ERROR in the Facility column?

    'Create blank recordset
Dim rsPMList As ADODB.Recordset
Set rsPMList = New ADODB.Recordset
Dim Facility, SQLstr As String
Facility = txtcboecho

'Create fields in blank recordset
With rsPMList.Fields
    .Append "PMID", adNumeric, 4, adFldKeyColumn
    .Append "Facility", adChar, 7, adFldUpdatable
    .Append "Device", adChar, 4, adFldUpdatable
    .Append "BarcodeID", adChar, 7, adFldUpdatable
    .Append "Name", adVarChar, 50, adFldUpdatable
    .Append "Address", adChar, 15, adFldUpdatable
    .Append "Location", adVarChar, 75, adFldUpdatable
End With

'Open blank recordset
rsPMList.Open

'Create query for "Building Controllers"
Dim rsBCList As ADODB.Recordset
Set rsBCList = New ADODB.Recordset

SQLstr = "SELECT tblFacility.FacCode AS Facility, 'BC' AS Device, "
SQLstr = SQLstr & "tblBC.BCName AS Name, tblBC.IPAddress AS Address, "
SQLstr = SQLstr & "FROM tblFacility INNER JOIN tblBC "
SQLstr = SQLstr & "ON tblFacility.FacilityID = tblBC.Facility "
SQLstr = SQLstr & "WHERE tblFacility.FacCode = '" & Facility & "' "
SQLstr = SQLstr & "AND tblBC.ParentBC Is Null"
rsBCList.Open SQLstr, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

'Move records from query to blank recordset
Do While rsBCList.EOF <> True
    rsPMList.AddNew
        rsPMList.Fields("Facility") = rsBCList.Fields("Facility")
        rsPMList.Fields("Device") = rsBCList.Fields("Device")
        If IsNull(rsBCList.Fields("BarcodeID")) Then
            rsPMList.Fields("BarcodeID") = ""
        Else
            rsPMList.Fields("BarcodeID") = rsBCList.Fields("BarcodeID")
        End If
        rsPMList.Fields("Name") = rsBCList.Fields("Name")
        rsPMList.Fields("Address") = rsBCList.Fields("Address")
        rsPMList.Fields("Location") = rsBCList.Fields("Location")
    rsPMList.Update
rsBCList.MoveNext
Loop

'These lines shows that query data was successfully moved
'to the blank recordset.  Two text fields display two fields.
rsPMList.MoveFirst
txtShow1 = rsPMList.Fields("BarcodeID")
rsPMList.MoveNext
txtshow2 = rsPMList.Fields("BarcodeID")
rsBCList.Close
Set rsBCList = Nothing

'Set the record source of the subform to the newly created recordset
Set Me.frmFacilityInventory.Form.Recordset = rsPMList

rsPMList.Close
Set rsPMList = Nothing

HansUp HansUp · Accepted Answer · 2013-11-27T00:28:44

Set the recordset's CursorLocation and LockType properties before you Open it.

This simplified version of your recordset worked with my Access 2007 form.

With rsPMList
    .Fields.Append "PMID", adInteger, , adFldKeyColumn ' changed from adNumeric, 4
    .Fields.Append "Facility", adChar, 7, adFldUpdatable
    .Fields.Append "Device", adChar, 4, adFldUpdatable
    .CursorLocation = adUseClient
    .LockType = adLockPessimistic
    .Open
End With

Field names in Access subform populated with vba recordset

1 Answers