0

I have a userform in Access 2013 and I'd like to bind the controls to a ADO recordset when it is opened.

So far, I have the following code:

Private Sub UserForm_Activate()

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cn = New ADODB.Connection

    With cn
        .CursorLocation = adUseClient
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Properties("Data Source").Value = CurrentProject.FullName
        .Properties("Mode").Value = adModeReadWrite
        .Properties("Persist Security Info").Value = False
        .Open
    End With

       Set rs = New ADODB.Recordset

   With rs
      Set .ActiveConnection = cn
      .Source = "SELECT SerialNumber FROM SerialPlateData"
      .LockType = adLockOptimistic
      .CursorType = adOpenKeyset
      .Open
   End With

    'bind textbox to ado recordset at runtime
    txtSerialNumber.ControlSource = rs.Fields("SerialNumber").Name

    Set rs = Nothing
    Set cn = Nothing

End Sub

I get an error at this line txtSerialNumber.ControlSource = rs.Fields("SerialNumber").Name

My aim is to bind the data retrieved from the recordset to the controls on the userform and when finished, I'll be updating the data table with any changes.

How am I best to fix this?

Thanks

cosmarchy
  • 686
  • 3
  • 9
  • 21
  • 1
    What is the exact error message? – June7 Jun 27 '19 at 21:07
  • 2
    Have to bind form to data set. https://stackoverflow.com/questions/5641760/ms-access-form-bound-to-ado-disconnected-recordset – June7 Jun 27 '19 at 21:14
  • Yup, you can't bind individual controls to different recordsets unless you're willing to do really complicated stuff. I do have code lying around for something similar, but it's very lengthy – Erik A Jun 28 '19 at 05:19
  • 1
    Don't use an Office/Excel Userform. Use an Access form and bind it to the table and the control to the field. _Zero code_. – Gustav Jun 28 '19 at 07:53

0 Answers0