Trouble creating a RecordSource for an unbound subform

I have looked at many posts here and on other sites and still cannot resolve my problem. I get multiple error messages when trying to dynamically add a recordsource to an unbound subform. Here is my latest attempt:

    Dim strUser As String
    Dim frm As Form
    strUser = Forms.frmLogin.txtPassword
   
    MsgBox strUser
    Me.frmSelectWeek.Form.RecordSource = ""
   
    Me.frmSelectWeek.Form.Visible = False
   
    'DoCmd.SetWarnings False
    DoCmd.DeleteObject acTable, "tblSelectWeek_Temp"
       
    DoCmd.OpenQuery "qrySelectWeek"
   
    Dim strSQL, strSQL1 As String
    Dim strTableName As String
   
    strTableName = strUser & "_ACTIVITY"
    strSQL = "CREATE TABLE " & strTableName & "(ACTDATE Date, CLIENT  Int, ACTIVITY Int, COMPLETE date, HOURS Int, COMMENT char(225)) "

    strSQL1 = "INSERT INTO " & strTableName & "( ACTDATE, CLIENT, ACTIVITY, COMPLETE, HOURS, COMMENT ) " & _
    "SELECT tblLog.ACTDATE, lkpClient.CLIENT, lkpActivity.ACTIVITY, tblLog.COMPLETE, tblLog.HOURS, tblLog.COMMENT " & _
    "FROM (lkpActivity INNER JOIN tblLog ON lkpActivity.ACTIVITYID = tblLog.ACTIVITYID) INNER JOIN lkpClient ON tblLog.CLIENTID = lkpClient.CLIENTID " & _
    "WHERE (((tblLog.ACTDATE) Between [forms].[frmActivityLog].[txtBegDate] And [forms].[frmActivityLog].[txtEndDate]) AND ((tblLog.USERID)=[forms].[frmLogin].[txtPassword]))"

    DoCmd.RunSQL strSQL
    DoCmd.RunSQL strSQL1

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

    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset

    rs.Open strTableName, cn

        Me.frmSelectWeek.Form.RecordSource = rs '------------- Error Here - Type Mismatch
        Me.frmSelectWeek.Form.CLIENT.ControlSource = "CLIENT"
        Me.frmSelectWeek.Form.ACTDATE.ControlSource = "ACTDATE"
        Me.frmSelectWeek.Form.ACTIVITY.ControlSource = "ACTIVITY"
        Me.frmSelectWeek.Form.COMPLETE.ControlSource = "COMPLETE"
        Me.frmSelectWeek.Form.HOURS.ControlSource = "HOURS"
        Me.frmSelectWeek.Form.COMMENT.ControlSource = "COMMENT"
               
    Me!frmSelectWeek.Form.Requery
    Me!frmSelectWeek.Form.Visible = True
RR345Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
Try setting the ControlSource of your controls in design view - OR - setting the Recordset AFTER you set the ControlSources. I don't use the Recordset approach much anymore, but it'll work.

Also, if this is going to a local Access table, you can simply create the table as you're doing and then just set the RecordSOURCE of your subform like this:

Me.YourSubform.Form.RecordSOURCE = "SELECT * FROM YourTablename"

About the only reason to use a RecordSET when doing this is if you must get data from an external source. For example, if you're running a local Access app (on your dekstop) and you need to pull relevant information from a SQL Server on your network, you can build a connection to the server, open a recordset, and then SET the form's Recordset property to your new recordset. Otherwise you're much better off using the RecordSOURCE method.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you're going to use a Recordset for the Form, you must set the Recordset property, not the Recordsource property:

Set Me.frmSelectWeek.Form.RecordSET = rs

Note also that you need to set the CursorLocation before you apply the Recordset:

rs.CursorLocation = adUseClient

Now set the Recordset.

0
 
RR345Author Commented:
Makes mores sense and now getting error Operation is Not allowed when the object is open. Let me expand more on what I am doing. I have a Tab Control on an unbound form. In one of the Tabs, I would like the user to select a date range and the information will show in datasheet view. I put my code on an onclick event of a command button. Any idea?
0
 
RR345Author Commented:
I had to play with it as I am using a temporary table named with a variable (User Login) for the RecordSource. So, final: Me.subform.Form.RecordSource = "SELECT * FROM " & strTableName

Thank you
0
All Courses

From novice to tech pro — start learning today.