G
Guest
Hi,
I am trying to develop a form in continuous form datasheet style view based
on a dynamically created crosstab query. I have posted several questions
recently and received some good help - thanks, as this is cutting edge
programming (For me...).
Most of my initial problems have been overcome except one that occasionally
pops up, and I can't work out how to solve it.
Basically, the form has all the field controls placed on it, and when
required I dynamically recreate the querydef crosstab form recordsource,
cycle through the form controls setting their controlsource to the
appropriate field, then requery and resresh the form.
All this works fine except I get some occasional fields displayed as #Name?,
which I understand to mean the field isn't available for the controlsource
setting. Yet looking at the form properties, the controlsource is set
correctly and the field is available. Look at the crosstab query directly and
it is there with data. Restart the form and it works perfectly. Try again
later and no good.
Those fields displaying correctly seem to stay correct, but new fields seem
to be where the #Name? happens. My best thinking is when the querydef is
recreated in code it isn't refreshed within the db somehow, but
CurrentDb.QueryDefs.Refresh doesn't help. As I say, the next time I try it,
every test passes...
Sorry for pasting a huge bunch of code, but at least it's there for a browse.
Thanks in advance for any help.
swas
Public Sub RebuildDatasheet(FromDate As Date, ToDate As Date, ShowSat As
Boolean, ShowSun As Boolean)
'Predefined 62 textboxes txtCol0 - txtCol61 (ie. Max 2 months schedule)
placed on datasheet form.
'On rebuild form these textboxes etc... are set to the required controlsource
Dim strSql As String
Dim intTmp As Integer
Dim strDate As String
Dim strDateDay As String
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim strTemp As String
Dim objConFormat As FormatCondition
Const strQName As String = "qrydefRunCrosstab"
On Error GoTo RebuildDatasheet_Error
DoCmd.Hourglass True
If Not IsDate(FromDate) Or Not IsDate(ToDate) Then
GoTo RebuildDatasheet_Exit
ElseIf ToDate < FromDate Then
GoTo RebuildDatasheet_Exit
ElseIf ToDate - FromDate > 61 Then
GoTo RebuildDatasheet_Exit
End If
Application.Echo False
'Reset form general
Me.RowHeight = 300
ProfileID.ColumnHidden = False
ProfileID.ColumnWidth = 1400
ProfileID.ColumnOrder = 1
ProfileID.SetFocus 'Can't have focus on date columns while resetting so put
it here
CommentsPPC.ColumnHidden = Not Me.Parent.chkDisplayComments
CommentsPPC.ColumnWidth = 3000
CommentsPPC.ColumnOrder = 2
For intTmp = 0 To 61
With Me.Controls.Item("txtCol" & intTmp)
.Enabled = False
.ControlSource = ""
.ColumnHidden = True
.ColumnOrder = intTmp + 3
'Reset conditional formatting in case changed
.FormatConditions.Delete
End With
Next
'Build ColumnHeaders for crosstab query, also setting datasheet column
controlsource and label
strSql = ""
For intTmp = 0 To ToDate - FromDate
strDate = Format(FromDate + intTmp, "dd/mm/yyyy")
strDateDay = Format(strDate, "ddd")
If Not ShowSat And strDateDay = "Sat" _
Or Not ShowSun And strDateDay = "Sun" Then
'Don't add column for these
Else
'Setup column
With Me.Controls.Item("txtCol" & intTmp)
.ControlSource = strDate 'Tried with "[" & strDate & "]" but
makes no difference
.ColumnHidden = False
.ColumnWidth = 650
.Enabled = True
'Add conditional formatting
Set objConFormat = .FormatConditions.Add(acExpression, , _
"Forms!frmrunschedule.Form.pID =
Forms!frmrunschedule.frmRunScheduleDatasheet.Form.ProfileID And " & _
"Forms!frmrunschedule.Form.txtControlFocus = " &
Quote(strDate))
.FormatConditions(0).BackColor = RGB(255, 255, 0)
End With
'Create label
Me.Controls.Item("lblCol" & intTmp).Caption = Left(strDateDay, 2) &
Format(strDate, "dd")
'Add field to sql build for columnheaders
strSql = strSql & ", " & Quote(strDate)
End If
Next
'Strip initial "' "
strSql = Right(strSql, Len(strSql) - 2)
strSql = "TRANSFORM First(tblRunSchedule.FillerCode) AS FirstOfFillerCode "
& _
"SELECT tblRunSchedule.pID " & _
"FROM tblRunSchedule " & _
"GROUP BY tblRunSchedule.pID " & _
"ORDER BY tblRunSchedule.pID " & _
"PIVOT Format([DateOfFill]," & Quote("dd/mm/yyyy") & ") In (" &
strSql & ");"
'Now create querydef so the new crosstab can be used in the recordsource
along with other columns needed
Set dbs = CurrentDb
dbs.QueryDefs.Delete strQName
Set qdf = dbs.CreateQueryDef(strQName, strSql)
qdf.Close
dbs.Close
CurrentDb.QueryDefs.Refresh 'Tried this thinking it may help #Name? problem
but doesn't
RebuildDatasheet_Exit:
Me.Requery
Me.Refresh 'Tried this as well but still get #Name? error occasionally
Set dbs = Nothing
Set qdf = Nothing
Application.Echo True
DoCmd.Hourglass False
Exit Sub
RebuildDatasheet_Error:
MsgBox Err.Number & Err.Description
Resume RebuildDatasheet_Exit
End Sub
I am trying to develop a form in continuous form datasheet style view based
on a dynamically created crosstab query. I have posted several questions
recently and received some good help - thanks, as this is cutting edge
programming (For me...).
Most of my initial problems have been overcome except one that occasionally
pops up, and I can't work out how to solve it.
Basically, the form has all the field controls placed on it, and when
required I dynamically recreate the querydef crosstab form recordsource,
cycle through the form controls setting their controlsource to the
appropriate field, then requery and resresh the form.
All this works fine except I get some occasional fields displayed as #Name?,
which I understand to mean the field isn't available for the controlsource
setting. Yet looking at the form properties, the controlsource is set
correctly and the field is available. Look at the crosstab query directly and
it is there with data. Restart the form and it works perfectly. Try again
later and no good.
Those fields displaying correctly seem to stay correct, but new fields seem
to be where the #Name? happens. My best thinking is when the querydef is
recreated in code it isn't refreshed within the db somehow, but
CurrentDb.QueryDefs.Refresh doesn't help. As I say, the next time I try it,
every test passes...
Sorry for pasting a huge bunch of code, but at least it's there for a browse.
Thanks in advance for any help.
swas
Public Sub RebuildDatasheet(FromDate As Date, ToDate As Date, ShowSat As
Boolean, ShowSun As Boolean)
'Predefined 62 textboxes txtCol0 - txtCol61 (ie. Max 2 months schedule)
placed on datasheet form.
'On rebuild form these textboxes etc... are set to the required controlsource
Dim strSql As String
Dim intTmp As Integer
Dim strDate As String
Dim strDateDay As String
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim strTemp As String
Dim objConFormat As FormatCondition
Const strQName As String = "qrydefRunCrosstab"
On Error GoTo RebuildDatasheet_Error
DoCmd.Hourglass True
If Not IsDate(FromDate) Or Not IsDate(ToDate) Then
GoTo RebuildDatasheet_Exit
ElseIf ToDate < FromDate Then
GoTo RebuildDatasheet_Exit
ElseIf ToDate - FromDate > 61 Then
GoTo RebuildDatasheet_Exit
End If
Application.Echo False
'Reset form general
Me.RowHeight = 300
ProfileID.ColumnHidden = False
ProfileID.ColumnWidth = 1400
ProfileID.ColumnOrder = 1
ProfileID.SetFocus 'Can't have focus on date columns while resetting so put
it here
CommentsPPC.ColumnHidden = Not Me.Parent.chkDisplayComments
CommentsPPC.ColumnWidth = 3000
CommentsPPC.ColumnOrder = 2
For intTmp = 0 To 61
With Me.Controls.Item("txtCol" & intTmp)
.Enabled = False
.ControlSource = ""
.ColumnHidden = True
.ColumnOrder = intTmp + 3
'Reset conditional formatting in case changed
.FormatConditions.Delete
End With
Next
'Build ColumnHeaders for crosstab query, also setting datasheet column
controlsource and label
strSql = ""
For intTmp = 0 To ToDate - FromDate
strDate = Format(FromDate + intTmp, "dd/mm/yyyy")
strDateDay = Format(strDate, "ddd")
If Not ShowSat And strDateDay = "Sat" _
Or Not ShowSun And strDateDay = "Sun" Then
'Don't add column for these
Else
'Setup column
With Me.Controls.Item("txtCol" & intTmp)
.ControlSource = strDate 'Tried with "[" & strDate & "]" but
makes no difference
.ColumnHidden = False
.ColumnWidth = 650
.Enabled = True
'Add conditional formatting
Set objConFormat = .FormatConditions.Add(acExpression, , _
"Forms!frmrunschedule.Form.pID =
Forms!frmrunschedule.frmRunScheduleDatasheet.Form.ProfileID And " & _
"Forms!frmrunschedule.Form.txtControlFocus = " &
Quote(strDate))
.FormatConditions(0).BackColor = RGB(255, 255, 0)
End With
'Create label
Me.Controls.Item("lblCol" & intTmp).Caption = Left(strDateDay, 2) &
Format(strDate, "dd")
'Add field to sql build for columnheaders
strSql = strSql & ", " & Quote(strDate)
End If
Next
'Strip initial "' "
strSql = Right(strSql, Len(strSql) - 2)
strSql = "TRANSFORM First(tblRunSchedule.FillerCode) AS FirstOfFillerCode "
& _
"SELECT tblRunSchedule.pID " & _
"FROM tblRunSchedule " & _
"GROUP BY tblRunSchedule.pID " & _
"ORDER BY tblRunSchedule.pID " & _
"PIVOT Format([DateOfFill]," & Quote("dd/mm/yyyy") & ") In (" &
strSql & ");"
'Now create querydef so the new crosstab can be used in the recordsource
along with other columns needed
Set dbs = CurrentDb
dbs.QueryDefs.Delete strQName
Set qdf = dbs.CreateQueryDef(strQName, strSql)
qdf.Close
dbs.Close
CurrentDb.QueryDefs.Refresh 'Tried this thinking it may help #Name? problem
but doesn't
RebuildDatasheet_Exit:
Me.Requery
Me.Refresh 'Tried this as well but still get #Name? error occasionally
Set dbs = Nothing
Set qdf = Nothing
Application.Echo True
DoCmd.Hourglass False
Exit Sub
RebuildDatasheet_Error:
MsgBox Err.Number & Err.Description
Resume RebuildDatasheet_Exit
End Sub
