How to change field name in code 1

[data59]

I have a form with text boxes that represent the 52 weeks of the year. I would like to put hourly data representing a workweek of 40 hr in the appropriate box based on a start date determined by the user. My question is it possible to change the field name based on the date selected. In my case week1 of the build may start in week 12 of the year and the rest of the build with key off that start point.

If (Me.DesignHr.Value > 0 And Me.DesignHr.Value < 41) Then
Phase1 = Me.DesignHr.Value
Me.Build_Week1 = Phase1 (I would like to be able to change this based on date seleted i.e Me.Build_Week12)


Example of data

YearWeek-------------12-------13-------14
BuildWeek-------------1--------2--------3

DesignHr--------------40-------40-------10
CNCHr-----------------0---------0-------40

Code so far:

 Option Compare Database
Option Explicit


Private Sub Command6_Click()

'Var holder for Hour information'
Dim Phase1 As Integer

'Massage Box Var'
Dim iResponse As Integer

'Week Number Conversion'
Dim DesignComplete As Integer

DesignComplete = Format(Me.DesignCompDate.Value(), "ww")
Me.WeekNum = DesignComplete

'Design Schedule up to 200 hours'

   If (Me.DesignHr.Value > 0 And Me.DesignHr.Value < 41) Then
   Phase1 = Me.DesignHr.Value
   Me.Build_Week1 = Phase1
 

   Else
   If (Me.DesignHr.Value > 40 And Me.DesignHr.Value < 80) Then
   Phase1 = Me.DesignHr
   Me.Build_Week1 = 40
   Me.Build_Week2 = Phase1 - 40
   
   Else
   If (Me.DesignHr.Value > 0 And Me.DesignHr.Value < 121) Then
   Phase1 = Me.DesignHr
   Me.Build_Week1 = 40
   Me.Build_Week2 = 40
   Me.Build_Week3 = Phase1 - 80
   
   Else
   
    If (Me.DesignHr.Value > 0 And Me.DesignHr.Value < 161) Then
   Phase1 = Me.DesignHr
  
   Me.Build_Week1 = 40
   Me.Build_Week2 = 40
   Me.Build_Week3 = 40
   Me.Build_Week4 = Phase1 - 120
   
   Else
   
       If (Me.DesignHr.Value > 0 And Me.DesignHr.Value < 201) Then
   Phase1 = Me.DesignHr
    
   Me.Build_Week1 = 40
   Me.Build_Week2 = 40
   Me.Build_Week3 = 40
   Me.Build_Week4 = 40
   Me.Build_Week5 = Phase1 - 160
   
   Else
   If (Me.DesignHr.Value > 200) Then
    Me.DesignHr = 0
iResponse = MsgBox("Can not Schedule over 200 Hours", vbOK)

   End If
   End If
   End If
   End If
   End If
   End If
   End Sub
   '================================================================================='
   '================================END OF Design Code==============================='

 

[Zion7]

No, I don't believe you can change the name of a textbox, during run time.
At least I haven't been able to, using the name property of the text box.

Private Sub Command79_Click()
DoCmd.OpenForm "frmSolarSystem", acDesign ', , , , acHidden
Me.txtAxisTilt.Name = "xxx"
DoCmd.OpenForm "frmSolarSystem", acNormal
End Sub

I keep getting Error, "expresion refers to an object that is closed, or doesn't exist."

But, you can change the name, using a tableDef object.

Set tbl = CurrentDb.TableDefs("tblService")

Set fld = tbl.Fields("chkCPickUp")
fld.Name = "chkCompleted"

You'll have to incorporate your Date calculation procedure with this, prior to opening the form in normal view.

 

[data59]

Thanks Zion7 I guess I wasn't clear, what I'm trying to do is have the date point to the field on my form that corresponds to the week number in the year. Example if the job required 7 weeks to build and needed to start on march 1 (wk 9 of the year). I would want 40hr in week 9, 40hr in week 10 ect. My Me.Build_Week1 would need to put 40hr in Year_week 9 to start and I would key off that for following weeks.

 

[Zion7]

Hi Data59, sorry, that was my fault.
I'm still not entirely sure, i've grasped your concept but, an idea closely related would be to use the controls collection or a loop of some sort.


If (Me.DesignHr.Value > 0 And Me.DesignHr.Value < 41) Then
y = Me.DesignHr.Value
ElseIf If (Me.DesignHr.Value > 40 And Me.DesignHr.Value < 80) Then
y = Me.DesignHr.Value - 40
ElseIf (Me.DesignHr.Value > 0 And Me.DesignHr.Value < 121) Then
y = Me.DesignHr.Value - 80
....
End If




For x = 1 to 52
If x < y Then
Me("Build_Week" & x) = 40
Else
Me("Build_Week" & x) = Me("Build_Week" & y)
End If
Next x

...again, this is just a template, I don't think I really follow the data structure yet.
I guess the point is, you can refer to controls in various ways. Using variables with the above syntax, makes for very dynamic referencing.

 

[data59]

Zion7, Thanks for your sugestions it got me going down the right path. The code below does what I was asking. Now I have to figuerout what to do at the end of the year when jobs will wrap into the next year. Thank for your help

Option Compare Database
Option Explicit

Private Sub Command6_Click()

'Var holder for Hour information'
Dim Phase1 As Integer

'Massage Box Var'
Dim iResponse As Integer

'Week Number Conversion'
Dim x As Integer

x = Format(Me.DesignCompDate.Value(), "ww")


'Design Schedule up to 200 hours'

   If (Me.DesignHr.Value > 0 And Me.DesignHr.Value < 41) Then
   Phase1 = Me.DesignHr.Value
   Me("Build_Week" & x) = Phase1
 

   Else
   If (Me.DesignHr.Value > 40 And Me.DesignHr.Value < 80) Then
   Phase1 = Me.DesignHr
   Me("Build_Week" & x) = 40
   Me("Build_Week" & x + 1) = Phase1 - 40
   
   Else
   If (Me.DesignHr.Value > 0 And Me.DesignHr.Value < 121) Then
   Phase1 = Me.DesignHr
   Me("Build_Week" & x) = 40
   Me("Build_Week" & x + 1) = 40
   Me("Build_Week" & x + 2) = Phase1 - 80
   
   Else
   
    If (Me.DesignHr.Value > 0 And Me.DesignHr.Value < 161) Then
   Phase1 = Me.DesignHr
  
   Me("Build_Week" & x) = 40
   Me("Build_Week" & x + 1) = 40
   Me("Build_Week" & x + 2) = 40
   Me("Build_Week" & x + 3) = Phase1 - 120
   
   Else
   
       If (Me.DesignHr.Value > 0 And Me.DesignHr.Value < 201) Then
   Phase1 = Me.DesignHr
    
   Me("Build_Week" & x) = 40
   Me("Build_Week" & x + 1) = 40
   Me("Build_Week" & x + 2) = 40
   Me("Build_Week" & x + 3) = 40
   Me("Build_Week" & x + 4) = Phase1 - 160
   
   Else
   If (Me.DesignHr.Value > 200) Then
    Me.DesignHr = 0
iResponse = MsgBox("Can not Schedule over 200 Hours", vbOK)

   End If
   End If
   End If
   End If
   End If
   End If
   End Sub

 

[Zion7]

data59, Have you considered DateAdd()?

I don't mean to be presumptuous but, here's a possible revamp, of your code.

Dim hr As date
hr = Me.DesignHr.Value

Select Case hr
Case 0 To 41
Phase1 = hr
y = 1
Case 40 To 80
Phase1 = hr - 40
y = 2
Case 0 To 121
Phase1 = hr - 80
y = 3
Case 0 To 161
Phase1 = hr - 120
y = 4
Case Else
Me.DesignHr = 0
iResponse = MsgBox("Can not Schedule over 200 Hours", vbOK)
End Select

For x = 1 to y
If x < y Then
Me("Build_Week" & x) = 40
Else
Me("Build_Week" & x) = Phase1
End If
Next x

 

[TheAceMan1]

How are ya data59 . . .

How about:

[blue] Me(Build_Week & Format([purple][b]YourDate[/b][/purple], "ww")) = Phase1[/blue]

See Ya! . . . . . .

 

[data59]

Zion7, Sorry I have not responded until know. But thanks for your excellent suggestion on my code. Your suggestions worked great. However I’ve determined that my approach to this problem was flawed so I have decided to start fresh. I will try to simplify my question. Suppose I have a table that has the following fields

[Job#]---[Mach Operation Type]--[Startdate(Monday of week)--[ Job duration-(Weeks)]

I would like to display the data in a format like this:

[Job#]---[Mach Operation Type]--[Total weekly Hours of Job Function]

I would like to display this in a form or report that would show this information and show a week number for each week of the year. I would like the formula to be the total hours for the job divided by the job duration, and deposited the product in to the week number. The start week to deposit the product would be keyed off the start date i.e. total job duration 3 weeks , total hours for the job 300hr = wk1-100hr, wk-2 100hr, wk-3 100hr

Report example:
[Job#]---[Mach Operation Type]--[Total weekly Hours of Job Function] [Wk1]-[Wk2 ]-[Wk3]-ect----


It would be similar to the way a calendar program work I suppose, but I’ cant seem to get my head around it and I am still pretty new to coding in VB. Any ideas would be greatly appreciated. Thanks