Full Version: Dynamic DataSheet Labels
UtterAccess Forums > Microsoft® Access > Access Forms
George13
I am working with a form (Access 2003) that relies upon a series of queries to create date-related columns of information. Basically, it is projecting inventory demand for the next 12 months.
The output form needs to be in a "datasheet" view because of the number of columns that need to be displayed. Believe it or not the 22" provided with the Continuous Forms view only provides enough space for 6 months of data because each month has 4 columns that are needed to display and I need to display at least 12 months of projections - so the DataSheet view works just fine, especially since I can anchor the first column as users scroll to the right. The Continuous Form view is just too narrow to get all the months across.
The issue is that I need to change the column heading label from "Month 1", "Month 2", "Month 3", etc (essentially the field names passed by the Query calculations) to display the acutal month (eg - March, April, May, etc.) - and of course this need to change each month as we move forward... next month the headings need to be April, May, June, etc.
Ocan do this just fine with Continuous Forms and Reports by calculating an unbound field in place of the field label, but how can I accomplish the same function in DataSheet view?
FOr is there some way to vary the field names created by the Query. Currently I am using the following Query Field Calculation:
Month-2: IIf([YearMonth]=Year(DateAdd("m",2,Now())) & Right(Month(DateAdd("m",2,Now()))+100,2),[QuantityExpected],0)
[YearMonth] is a previous calculation extracting the month and year from a date in the format "200805" - (i.e. May 2008)
Is there some way to either dynamically change the field name (eg "Month-2) in the Query to a calendar month (i.e. "May" or "5"), or convert "Month-2" to Month(Date())+2 on the DataSheet label?
Thanks for your help.
GEORGE
Rainlover
George
I have exactly the same problem.
I did you ever find a solution?
strive4peace
Hi Des,

yes, there are ways

post a db, specify what form to look at, and I'll put some code there
Rainlover
G'day Crystal
ong time no chat. Trust you are well.
Crystal
I wrote a solution and to my surprise it worked. The only problem is that I am using a series of "If" statements where I think "Case" would be better. But I can't get case to work. It freezes on me, or more correctly the form won't open.
This is what I have. "NextMonday" is my UDF.
Public Sub SetLabelName(ByRef objThisObject As Object)
Dim ctl As Control
For Each ctl In objThisObject
If ctl.Tag = "Dyno" Then
If ctl.Name = "LblWeek1" Then
ctl.Caption = DateAdd("d", -3, NextMonday(DLookup("sdate", "tblforecaststamp")))
End If

If ctl.Name = "LblWeek2" Then
ctl.Caption = DateAdd("d", 4, NextMonday(DLookup("sdate", "tblforecaststamp")))
End If
If ctl.Name = "LblWeek3" Then
ctl.Caption = DateAdd("d", 11, NextMonday(DLookup("sdate", "tblforecaststamp")))
End If

If ctl.Name = "LblWeek4" Then
ctl.Caption = DateAdd("d", 18, NextMonday(DLookup("sdate", "tblforecaststamp")))
End If

If ctl.Name = "LblWeek8" Then
ctl.Caption = DateAdd("d", 46, NextMonday(DLookup("sdate", "tblforecaststamp")))
End If

If ctl.Name = "LblWeek12" Then
ctl.Caption = DateAdd("d", 74, NextMonday(DLookup("sdate", "tblforecaststamp")))
End If

If ctl.Name = "LblWeek16" Then
ctl.Caption = DateAdd("d", 102, NextMonday(DLookup("sdate", "tblforecaststamp")))
End If

If ctl.Name = "LblWeek20" Then
ctl.Caption = DateAdd("d", 130, NextMonday(DLookup("sdate", "tblforecaststamp")))
End If

If ctl.Name = "LblWeek24" Then
ctl.Caption = DateAdd("d", 158, NextMonday(DLookup("sdate", "tblforecaststamp")))
End If

If ctl.Name = "LblWeek28" Then
ctl.Caption = DateAdd("d", 186, NextMonday(DLookup("sdate", "tblforecaststamp")))
End If

End If
Next
End Sub
Also on the Form Open event I have
Private Sub Form_Open(Cancel As Integer)
SetLabelName Me
End Sub
strive4peace
Hi Des,

don't forget to use CODE tags when you post code wink.gif

you should be able to do this:

CODE
Public Sub SetLabelName( pF as form)

   On Error GoTo Proc_Err

   Dim ctl As Control
   Dim NumDays as integer
  
   NumDays = 0
nbsp;  'assuming the controls are in the detail section
   For Each ctl In pF.Detail.Controls

      If ctl.Tag = "Dyno" Then

         select case ctl.Name
         case "LblWeek1"
            NumDays = -3
         case "LblWeek2"
            NumDays = 4
         '... etc
         end select

        'by wrapping the date in Format, you are returning a string
         ctl.Caption = Format( _
            DateAdd("d", NumDays, _
            NextMonday(DLookup("sdate", "tblforecaststamp"))) _
            , "mm-dd-yy")

     end if
   next ctl
Proc_Exit:
   On Error Resume Next
   set ctl = nothing
  
   Exit Sub
  
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   SetLabelName"
  
   Resume Proc_Exit

   'if you want to single-step code to find error, CTRL-Break at MsgBox
   'then set this to be the next statement
   Resume

End Sub

is there just one record in tblforecaststamp? I see you aren't using any criteria...
what is the code for NextMonday?

since this code seems to be pretty specific to this form, I would put it behind the form instead of making it a public procedure -- then you can use Me instead of the passed form object
Rainlover
Thanks for the help Crystal. I shall give it a go.
There is only one record in tblForecastStamp.
The code is used on two different Events so it needs to stay where it is.
The code for Next Monday is.
CODE

Function NextMonday(sDate As Variant) As Date
'This function finds the date of the next Monday after the date passed as sDate
' Written by Des Wells
' September 2008
On Error GoTo Err_Func
Dim DayNumber As Integer
    
    If IsDate(sDate) Then
        DayNumber = Weekday(sDate)
            If DayNumber = 1 Then
                NextMonday = DateAdd("d", 1, sDate)
            Else
                NextMonday = DateAdd("d", 9 - DayNumber, sDate)
            End If
    End If
Exit_Func:
  Exit Function
  
Err_Func:
      MsgBox Err.Number & " - " & Err.Description
Resume Exit_Func
End Function

And thanks for reminding me that I need to add some error handling.
Cheers.
strive4peace
Hi Des,
You're welcome
"The code is used on two different Events so it needs to stay where it is."
for the same form?

"error handling." sad.gif
Rainlover
Yes Crystal It is the same form.
But I think I might use it also on a Report or two but not sure at this stage.
strive4peace
Hi Des,

you might want to put a comment at the top of the code specifying where it is used ... then, if you end up not using it anywhere else, you can move the code...
George13
Thank you both. I had about given up hope for a solution. I look forward to trying your suggestions.
Thanks again...
GEORGE
strive4peace
you're welcome, George -- was really nice that Des dug up your post wink.gif ... so can you ride a unicycle and juggle? <smile>

Edited by: strive4peace2008 on Tue Oct 7 2:25:16 EDT 2008.
Rainlover
George
If you have any problems please reply to either Crystal or myself.
You might get a faster responce from Crystal as she in in a similar time zone to you.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.