Access VBA loop, with escalating field names (1 Viewer)

undertm

Registered User.
Local time
Tomorrow, 01:55
Joined
Jul 28, 2011
Messages
14
Hello All


I am writing a Timesheet Access app. about 20% into it because I could not find a good sample online. (if anyone has a good one let me know). Anyway I have the data entry screen going and it collects and populates the data into a table using SQL vb script. (see screen shot below).



I thought I would create a template for each type of worker eg electricians, instructors , mechanics etc. so that it populates the hours for the week saving them time typing it all in, but again the code is too long.



This is the code i am trying to use, but its not working.



Dim index As Integer
Dim Startvar As String

index = 1
Startvar = "Me.txtStartD" & index

Do While index <= 7 'this will do 7 rotations.

Startvar = "Me.txtStartD" & index
MsgBox Startvar
Startvar = "8"
index = index + 1
' Me.txtStartD1 = "8" original code
Me.txtLunchD1 = "0.5"
Me.txtOtherD1 = "0"
Me.txtFinishD1 = "16"

Loop


Check out the attached image, The "MsgBox Startvar" rotates and show the correct escalation of field names, (txtStartD1, txtStartD2, txtStartD3, txtStartD4, txtStartD5, txtStartD6, txtStartD7) but does not populate the form.


Any help would be appreciated. Once i get the field txtStartDx going I will do the other fields like txtlunchDx


If anyone would like to join me writing the next stages Approval, Rejection, Re-Approval and Reports let me know.


Thanking someone in advance.


Terry
 

Attachments

  • Screenshot 2019-05-23 18.31.36.png
    Screenshot 2019-05-23 18.31.36.png
    55.9 KB · Views: 63

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 14:55
Joined
Sep 12, 2006
Messages
15,614
try this sort of syntax

me.controls(varname) = value
 

isladogs

MVP / VIP
Local time
Today, 14:55
Joined
Jan 14, 2017
Messages
18,186
The first post was moderated due to the attachment. Until you have ten posts, please zip any attached files.
Posting this to trigger email notifications
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:55
Joined
May 21, 2018
Messages
8,463
Using g-t-h suggestion try something like
Code:
Private Sub SetDefaults()
  Dim i As Integer
  For i = 1 To 7
    Me.Controls("txtStartD" & i) = 8
    Me.Controls("txtLunchD" & i) = 0.5
    Me.Controls("txtOtherD" & i) = 0
    Me.Controls("txtFinishD" & i) = 16
  Next i
End Sub
 

Micron

AWF VIP
Local time
Today, 10:55
Joined
Oct 20, 2018
Messages
3,476
Are you sure you want to be doing time sheet calculations with text ("8") instead of numbers (8)?
but its not working.
Not very helpful. No error message?
For such escalating loops I use Me.Controls("txtLunchD" & i) where i or some other variable is declared as integer. Index is a reserved word.
Suggest you do not use. Just because you return what looks like a valid message doesn't mean your message will translate to a valid field reference - especially when it's declared as a string
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:55
Joined
Sep 21, 2011
Messages
14,054
The way I approached this was to have a table holding the start/end and lunch hours for each employee for each day.
The reason for that was some employees had different start & end times depending on the day, and we all finished early on a Friday.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 14:55
Joined
Sep 12, 2006
Messages
15,614
I think micron meant that your original code was assigning the hours as "text", rather than as numbers.
 

Users who are viewing this thread

Top Bottom