#Name?

S

swas

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
 
S

swas

I solved my problem, by adding

me.recordsource = me.recordsource

at the end of the subroutine. Obviously refreshing the recordsource in this
manner is different to requery.

Just to stop everyone searching for a solution here... :)


swas


swas said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top