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