none
Forms RecordSet ADO RRS feed

  • Question

  • Thank you for taking the time to read my question.

    I found this page https://docs.microsoft.com/en-us/office/vba/access/concepts/activex-data-objects/bind-a-form-to-an-ado-recordset

    I can get my code to run, but when the form opens, there is no data (there is one record returned in the recordset). I have my form set to open as datasheet.

    The part I don't understand is how to align the fields in the ADO dataset to the text boxes on my form? Maybe this is why I don't see any data?

    Thanks,

    Update:

    I forgot to say that I'm applying the recordset to a subform on my form. I do this when I open the parent form.

    I added this code to the subform:

    Private Sub Form_Current()
    Dim rs As Object
        Set rs = Me.Recordset
        If TypeOf rs Is DAO.Recordset Then
            MsgBox "DAO Recordset"
        ElseIf TypeOf rs Is ADODB.Recordset Then
            MsgBox "ADO Recordset"
        End If
    End Sub

    I get "ADO Recordset"

    Update:

    I have applied the same code to just the subform which is applied on Open.

    I tried the following and when I step through the code I see:

    Me.FormulaCode.ControlSource = Me.Recordset!FormulaCode

    When the code completes, the FormuaCode field just says #Name?

    Update:

    Or do I have to do something like this and I can only see one record at a time?

    Me!FormulaCode.ControlSource = "=" & """" & Me.Recordset!FormulaCode & """"
    Me.FormulaName.ControlSource = "=" & """" & Me.Recordset!FormulaName & """"
    Me.FormulaPlant.ControlSource = "=" & """" & Me.Recordset!FormulaPlant & """"
    Me.FromModelsFormulaID.ControlSource = "=" & """" & Me.Recordset!FromModelsFormulaID & """"

    Update:

    I now have 2 records in my recordset and by doing the above I get two records/rows in my form but they are both equal to the first records values.

    • Edited by mbrad Wednesday, February 19, 2020 10:36 PM
    Wednesday, February 19, 2020 9:20 PM

Answers

  • The way to make this work is in form design view, make the control's Control Source equal to the name of the field in the ADO recordset.

    • Marked as answer by mbrad Thursday, February 20, 2020 6:25 PM
    Thursday, February 20, 2020 6:25 PM