If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#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 |
#2
|
|||
|
|||
QL
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? |
#3
|
|||
|
|||
#NAME Issue with Cross Tab sub form
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? |
Thread Tools | |
Display Modes | |
|
|