#NAME Issue with Cross Tab sub form

Hi All,

I have a form with a sub form that is based on a cross tab query. This is a
month order report for all models. Depending on the time of the month some
models may be at zero. Because the form is based on a Cross Tab query the
Model name doesn't yet exsist until there is data.

So what happens on the form is it shows up as #NAME in the feild. How can I
set my form so that if it see #NAME it puts a zero in that feild?

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via http://www.accessmonster.com

subform SourceObject, MakeReportSQL -- useful for Crosstabs
---

Hi Matt,

to replace a subform recordsource while the mainform is open, do this:

1. make the subform control invisible
2. replace the subform sourceobject with a "dummy form" (make a form
with a label on it that says 'dummy' -- it will never show)
3. replace the SQL or query that the subform is based on
4. set the subform sourceobject back to the real subform
5. make the subform control visible


here is an analogy using reports -- if you have trouble adapting the
ideas for a form, we can help

you can use a report as the sourceobject of a subform -- you cannot edit
the results of a crosstab anyway

~~~~~~~~~~~
Change the RecordSource of a report before it runs

base your report on a query that is based on your crosstab. Build the
SQL for the report recordsource on the OPEN event of the report.

name your controls (and labels) in the report:
C03, c04, ...
L03, L04, ...

where 03 would be the first field for the crosstab in the query

~~~

loop through the fieldnames of your crosstab query in code and assign
the contents of the column and the label for the column

number of fields in query:

CurrentDb.QueryDefs("Queryname").Fields.Count

first fieldname:

CurrentDb.QueryDefs("Queryname").Fields(0).Name

last fieldname:

CurrentDb.QueryDefs("Queryname").Fields(CurrentDb. QueryDefs("Queryname").Fields.Count-1).Name

indexing starts with 0, so the last field is the Count-1

If your fields have spaces or special characters, you will need to
dlimit them with square brackets

[fieldname with a space or #special (character)]

~~~

then, to construct the SQL:

~~~
Private Sub Report_Open(Cancel As Integer)

'crystal
'strive4peace2007 at yahoo dot com

'NEEDS REFERENCE TO
'Microsoft DAO Library

'set up Error Handler
On Error GoTo Proc_Err

dim mCtrlname as string _
, mLblname as string, _
, mStartControlNumber as integer
, mLastControlNumber as integer
, i as integer _
, mQueryname as string _
, strSQL as string

dim db as dao.database _
qdf as dao.querydef

mQueryname = "The_name_of_your_crosstab_query"

strSQL = "SELECT firstfield, secondfield "

mStartControlNumber = 6 'wherever you want to start
mLastControlNumber = 15 'wherever you want to end

set db = currentdb
set qdf = db.QueryDefs("Queryname")

with qdf
for i = mStartControlNumber to

mCtrlname = "C" & format(i,"00")
mLblname = "L" & format(i,"00")

if i .Fields.Count

strSQL = strSQL & ", [" & .Fields(1).name & "]" _
& " AS " & mCtrlname _
& ", '" & .Fields(1).name & "'" _
& " AS " & mLblname

else

strSQL = strSQL & ", ''" _
& " AS " & mCtrlname _
& ", ''" _
& " AS " & mLblname

end if

next i

end with

strSQL = strSQL _
& " FROM [" & mQueryname & "]"
& ";"

'-------------------------------- CHOOSE ONE
'replace SQL for report
MakeQuery strSQL, "ReportRecordSourceQuery"
'OR ---
'you can just replace the record recordsource directly
'me.Rowsource = strSQL
'--------------------------------

Proc_Exit:
On Error Resume Next
'close and release object variables
set qdf = nothing
set db = nothing
Exit Sub

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& me.name & " Report_Open"

'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume

Resume Proc_Exit

End function

(NOT TESTED -- expect some debugging smile)

if you have criteria, add a WHERE clause before the terminating semi-colon

'~~~~~~~~~~~~~~~~~~~~~~~~~~

Sub MakeQuery( _
ByVal pSql As String, _
ByVal qName As String)

'modified 6-29-06
'crystal
'strive4peace2007 at yahoo dot com

On Error GoTo Proc_Err

'if query already exists, update the SQL
'if not, create the query

If Nz(DLookup("[Name]", "MSysObjects", _
"[Name]='" & qName _
& "' And [Type]=5"), "") = "" Then
CurrentDb.CreateQueryDef qName, pSql
Else
CurrentDb.QueryDefs(qName).sql = pSql
End If

Proc_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

Proc_error:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " MakeQuery"

'Press F8 to step through code and find problem
'comment next line out when program is debugged
Stop: Resume

Resume Proc_exit
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



mattc66 via AccessMonster.com wrote:
Hi All,

I have a form with a sub form that is based on a cross tab query. This is a
month order report for all models. Depending on the time of the month some
models may be at zero. Because the form is based on a Cross Tab query the
Model name doesn't yet exsist until there is data.

So what happens on the form is it shows up as #NAME in the feild. How can I
set my form so that if it see #NAME it puts a zero in that feild?

subform SourceObject, MakeReportSQL -- useful for Crosstabs
---

Hi Matt,

to replace a subform recordsource while the mainform is open, do this:

1. make the subform control invisible
2. replace the subform sourceobject with a "dummy form" (make a form
with a label on it that says 'dummy' -- it will never show)
3. replace the SQL or query that the subform is based on
4. set the subform sourceobject back to the real subform
5. make the subform control visible


here is an analogy using reports -- if you have trouble adapting the
ideas for a form, we can help

you can use a report as the sourceobject of a subform -- you cannot edit
the results of a crosstab anyway

~~~~~~~~~~~
Change the RecordSource of a report before it runs

base your report on a query that is based on your crosstab. Build the
SQL for the report recordsource on the OPEN event of the report.

name your controls (and labels) in the report:
C03, c04, ...
L03, L04, ...

where 03 would be the first field for the crosstab in the query

~~~

loop through the fieldnames of your crosstab query in code and assign
the contents of the column and the label for the column

number of fields in query:

CurrentDb.QueryDefs("Queryname").Fields.Count

first fieldname:

CurrentDb.QueryDefs("Queryname").Fields(0).Name

last fieldname:

CurrentDb.QueryDefs("Queryname").Fields(CurrentDb. QueryDefs("Queryname").Fields.Count-1).Name

indexing starts with 0, so the last field is the Count-1

If your fields have spaces or special characters, you will need to
dlimit them with square brackets

[fieldname with a space or #special (character)]

~~~

then, to construct the SQL:

~~~
Private Sub Report_Open(Cancel As Integer)

'crystal
'strive4peace2007 at yahoo dot com

'NEEDS REFERENCE TO
'Microsoft DAO Library

'set up Error Handler
On Error GoTo Proc_Err

dim mCtrlname as string _
, mLblname as string, _
, mStartControlNumber as integer
, mLastControlNumber as integer
, i as integer _
, mQueryname as string _
, strSQL as string

dim db as dao.database _
qdf as dao.querydef

mQueryname = "The_name_of_your_crosstab_query"

strSQL = "SELECT firstfield, secondfield "

mStartControlNumber = 6 'wherever you want to start
mLastControlNumber = 15 'wherever you want to end

set db = currentdb
set qdf = db.QueryDefs("Queryname")

with qdf
for i = mStartControlNumber to

mCtrlname = "C" & format(i,"00")
mLblname = "L" & format(i,"00")

if i .Fields.Count

strSQL = strSQL & ", [" & .Fields(1).name & "]" _
& " AS " & mCtrlname _
& ", '" & .Fields(1).name & "'" _
& " AS " & mLblname

else

strSQL = strSQL & ", ''" _
& " AS " & mCtrlname _
& ", ''" _
& " AS " & mLblname

end if

next i

end with

strSQL = strSQL _
& " FROM [" & mQueryname & "]"
& ";"

'-------------------------------- CHOOSE ONE
'replace SQL for report
MakeQuery strSQL, "ReportRecordSourceQuery"
'OR ---
'you can just replace the record recordsource directly
'me.Rowsource = strSQL
'--------------------------------

Proc_Exit:
On Error Resume Next
'close and release object variables
set qdf = nothing
set db = nothing
Exit Sub

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& me.name & " Report_Open"

'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume

Resume Proc_Exit

End function

(NOT TESTED -- expect some debugging smile)

if you have criteria, add a WHERE clause before the terminating semi-colon

'~~~~~~~~~~~~~~~~~~~~~~~~~~

Sub MakeQuery( _
ByVal pSql As String, _
ByVal qName As String)

'modified 6-29-06
'crystal
'strive4peace2007 at yahoo dot com

On Error GoTo Proc_Err

'if query already exists, update the SQL
'if not, create the query

If Nz(DLookup("[Name]", "MSysObjects", _
"[Name]='" & qName _
& "' And [Type]=5"), "") = "" Then
CurrentDb.CreateQueryDef qName, pSql
Else
CurrentDb.QueryDefs(qName).sql = pSql
End If

Proc_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

Proc_error:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " MakeQuery"

'Press F8 to step through code and find problem
'comment next line out when program is debugged
Stop: Resume

Resume Proc_exit
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



mattc66 via AccessMonster.com wrote:
Hi All,

I have a form with a sub form that is based on a cross tab query. This is a
month order report for all models. Depending on the time of the month some
models may be at zero. Because the form is based on a Cross Tab query the
Model name doesn't yet exsist until there is data.

So what happens on the form is it shows up as #NAME in the feild. How can I
set my form so that if it see #NAME it puts a zero in that feild?