Solved

Trouble creating a RecordSource for an unbound subform

Posted on 2011-02-20
4
467 Views
Last Modified: 2013-11-28
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
0
Comment
Question by:RR345
  • 2
  • 2
4 Comments
 
LVL 84
ID: 34939178
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
 

Author Comment

by:RR345
ID: 34943121
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
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 300 total points
ID: 34943668
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
 

Author Closing Comment

by:RR345
ID: 34943871
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

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question