[AccessD] FW: Set Recordsource of sub Form

Edward S Zuris edzedz at comcast.net
Tue Apr 15 23:26:36 CDT 2008


 Maybe more examples will help.

 Here is some old school code from an old programmer.
 This style isn't Microsoft standard, but it has severed
 me well for more than ten years.  Some old timers will
 recognize it.

 = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
   If sub-form is within current form.
 = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

    ' ****************************************************
    ' sSQL for Viewing
    '
    sSQL = ""
    sSQL = sSQL & " SELECT "
    sSQL = sSQL & " * "
    sSQL = sSQL & " FROM "
    sSQL = sSQL & " JP01Mast "
    sSQL = sSQL & " WHERE "
    sSQL = sSQL & " JP01Mast.zJobID = "
    sSQL = sSQL & Chr$(34) & Trim$(Me.txtzJobID.Value & " ") & Chr$(34)
    sSQL = sSQL & " And "
    sSQL = sSQL & " JP01Mast.zSSN = "
    sSQL = sSQL & Chr$(34) & Trim$(Me.txtzSSN.Value & " ") & Chr$(34)
    sSQL = sSQL & " ORDER BY "
    sSQL = sSQL & " JP01Mast.zWordDoc, "
    sSQL = sSQL & " JP01Mast.zSysDate desc, "
    sSQL = sSQL & " JP01Mast.zStartTime desc, "
    sSQL = sSQL & " JP01Mast.JobPositionID "
    sSQL = sSQL & " ; "

    ' *******************************************************
    '  Requery the recordsource for subform subSelectDoc.
    '
    Me.subSelectDoc.Form.RecordSource = sSQL
    Me![subSelectDoc].Form.Requery
    Me![subSelectDoc].Form.Refresh


 = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
   Here is another method if the VBA code is in module or
   a form opening another form.
 = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

Private Sub cmdPayRange_Click()

    Dim sSQL                        As String
    Dim zzJobId                     As String
    Dim zzSSN                       As String

    Dim sTmp0                       As String
    Dim sTmp1                       As String
    Dim sQryName                    As String
    Dim zzForm                      As Form

    Dim dbsV2H                      As Database
    Dim qryDef                      As DAO.QueryDef

    sTmp0 = ""

    On Error Resume Next
       qryDef.Close
       dbsV2H.Close
    On Error GoTo 0

    zzJobId = ""
    zzJobId = Trim$(Me.txtzJobID.Value & " ")


    ' *****************************************************
    '   Set up the DataBase Variable, use current database.
    '
    Set dbsV2H = CurrentDb()

    ' ****************************************************
    ' @ Range Table
    '
    sSQL = ""
    sSQL = sSQL & " SELECT "
    sSQL = sSQL & " * "
    sSQL = sSQL & " FROM "
    sSQL = sSQL & " JP17PayRange "
    sSQL = sSQL & " WHERE "
    sSQL = sSQL & " zJobID = "
    sSQL = sSQL & Chr$(34) & zzJobId & Chr$(34)
    sSQL = sSQL & " ORDER BY "
    sSQL = sSQL & " zStartDate DESC "
    sSQL = sSQL & " , "
    sSQL = sSQL & " zOrder "
    sSQL = sSQL & " ; "

    ' *****************************************************
    '  save sSQL
    '
    sQryName = ""
    sQryName = "qm758h_Range"

    ' *****************************************************
    ' *****************************************************
    ' Store Qry Def
    '
    On Error Resume Next
    dbsV2H.QueryDefs.Delete sQryName
    On Error GoTo 0

    Set qryDef = dbsV2H.CreateQueryDef(sQryName, sSQL)

    On Error Resume Next
       qryDef.Close
       dbsV2H.Close
    On Error GoTo 0

    Set qryDef = Nothing

    DoEvents

    sTmp0 = ""

    ' *****************************************************
    '  Open the form
    '
    DoCmd.OpenForm "zJP714Range", , , sTmp0

    ' *******************************************************
    '  Requery the recordsource for subform ("subzBook").
    '
    [Forms]![zJP714Range]("subzBook").Form.RecordSource = sSQL
    [Forms]![zJP714Range].Form.Requery
    [Forms]![zJP714Range].Form.Refresh
    [Forms]![zJP714Range].Form.Repaint

    DoEvents
    DoCmd.Close 2, "zJP710JobDB"

End Sub

 = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
 = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Rocky Smolin
at Beach Access Software
Sent: Tuesday, April 15, 2008 12:40 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] FW: Set Recordsource of sub Form


John:

It doesn't get to the right side of the =.

Using:

Forms!ClientPersons!ClientPersonsSubFrm.RecordSource =

I get object doesn't support this property or method.

Rocky



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Ruff
Sent: Tuesday, April 15, 2008 12:07 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] FW: Set Recordsource of sub Form

If strRecordSource is a string, should it not be surrounded by quotes?
Forms!ClientPersons!ClientPersonsSubFrm.RecordSource = "'" & strRecordSource
& "' AND MailingList = True " & strOrderBy

John V. Ruff

Applications Support Analyst

SolutionsIQ


----- Original Message -----
From: "Rocky Smolin at Beach Access Software" <rockysmolin at bchacc.com>
To: "'Access Developers discussion and problem solving'"
<accessd at databaseadvisors.com>
Sent: Tuesday, April 15, 2008 11:59 AM
Subject: [AccessD] FW: Set Recordsource of sub Form


> P.S.    Forms!ClientPersons!ClientPersonsSubFrm.RecordSource =
> strRecordSource & " AND MailingList = True " & strOrderBy
>
> doesn't work either. :(
>
> Rocky
>
>
>
>
>
>
>
>
>  _____
>
> From: Rocky Smolin at Beach Access Software
> [mailto:rockysmolin at bchacc.com]
> Sent: Tuesday, April 15, 2008 11:54 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Set Recordsource of sub Form
>
>
> Dear List:
>
> I can't seem to get the syntax on this right.  From a main form I want to
> set the recordsource of a sub form.  I'm trying:
>
>    Me.ClientPersonsSubFrm.RecordSource = strRecordSource & " AND
> MailingList = True " & strOrderBy
>
> but it won't compile - says 'Method of Datamember no found'.
>
> What is the correct syntax to set the record source on a sub-form?
>
> MTIA
>
> Rocky
>
>
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

No virus found in this incoming message.
Checked by AVG.
Version: 7.5.519 / Virus Database: 269.22.13/1377 - Release Date: 4/14/2008
9:26 AM


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list