Iterating through dates in a For loop (1 Viewer)

tcneo

Member
Local time
Tomorrow, 06:40
Joined
Dec 7, 2021
Messages
68
Hi,

I have a Form with the following:
comboBox that allows the user to select their choice of Analyst - cboAvGenAnalyst
2 textboxes that allows the user to select the start and end date - txtAvGenFromDate & txtAvGenTillDate
a textbox that allows the user to enter the duration in minutes - txtAvGenDuration
a command button for user to click after entering the required information above.

I want to create a record in a table with the Date, Analyst and Duration.

So for example, if the user input:
"Analyst 1"
1-Feb-2022
3-Feb-2022
100

There will be 3 records created in the table "Analyst_Availability":
Analyst 1 / 1-Feb-2022 / 100
Analyst 1 / 2-Feb-2022 / 100
Analyst 1 / 3-Feb-2022 / 100

This is my first time trying to write VBA code that involves dates and loop.

I wrote the following to be embedded into the Click event of the Command button.

Thankfully, it works!

But I would like to get feedback on how it can be improved.

Side question: how do I end the routine early (or prevent it from running) if any of the fields are empty/Null?
Side question: how do I print both a number and a string in the same MsgBox? (see code with the Comment)

***** VBA code below *****
Code:
Private Sub cmdAvGen_Click()
    ' Declaring variables
    Dim sAvGenFromDate As String
    Dim sAvGenTillDate As String
    Dim intAvGenDuration As Integer
    Dim dateCounter As Date
    Dim myR As Recordset
   
    Set myR = CurrentDb.OpenRecordset("Analyst_Availability")
       
    ' MsgBox to display output of the various controls
    If IsNull(Me.cboAvGenAnalyst) Then
        MsgBox ("You need to choose an Analyst!")
    Else
        MsgBox ("Hi! Analyst is " + Me.cboAvGenAnalyst)
    End If
       
    'sAvGenFromDate = Me.txtAvGenFromDate
    sAvGenFromDate = Format(Me.txtAvGenFromDate, "d-mmm-yyyy")
    sAvGenTillDate = Format(Me.txtAvGenTillDate, "d-mmm-yyyy")
    MsgBox ("So you want to generate availability from " + sAvGenFromDate + " to " + sAvGenTillDate + ", right?")

   ' Here I print the duration only. How do I print "100 mins each day, right?"
    intAvGenDuration = Me.txtAvGenDuration
    MsgBox (intAvGenDuration)

    For dateCounter = Me.txtAvGenFromDate To Me.txtAvGenTillDate
        myR.AddNew
        myR![Work_Date] = dateCounter
        myR![Analyst_ID] = Me.cboAvGenAnalyst
        myR![Available_Duration] = Me.txtAvGenDuration
        myR.Update
    Next dateCounter

    myR.Close
   
    Set myR = Nothing
   
End Sub
 
Last edited:

June7

AWF VIP
Local time
Today, 14:40
Joined
Mar 9, 2014
Messages
4,318
Please post code between CODE tags to retain indentation and readability.

Why bother user with message box if not doing anything with response? Why the difficulty with duration MsgBox since you already know how to concatenate? Why not just one MsgBox? Ampersand character is preferred for concatenation.
Code:
If MsgBox ("So you want to generate availability from " & sAvGenFromDate & " to " & sAvGenTillDate _
                  & vbCrLf & " and for " & intAvGenDuration & " minutes each day, right", vbYesNo) = vbYes Then
    'code to save records
    dateCounter = Me.txtAvGenFromDate
    Do While dateCounter <= Me.txtAvGenTillDate
        myR.AddNew
        myR![Work_Date] = dateCounter
        myR![Analyst_ID] = Me.cboAvGenAnalyst
        myR![Available_Duration] = Me.txtAvGenDuration
        myR.Update
        dateCounter = dateCounter + 1
     Loop
End If
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:40
Joined
Feb 19, 2002
Messages
35,935
Just FYI, the + is an arithmetic operator which can under certain circumstances serve double duty as a concatenation operator. However, it has different properties than the & so you should understand the difference to take advantage of the properties.

The & ignores nulls so for
fldA = abc
fldB = xyz
You get abcxyz regardless of whether you use & or +

for
fldA = abc
fldB = null
You get abc for & but Null for +. The + returns null if one of the operators is null

for
fldA = 123
FldB = 456
You get 123456 for & but 579 for the +. The & concatenates but the + adds.

The + is used in particular when concatenating name or address parts where one or more might be null

FirstName & " " & MiddleName & " " & LastName
if MiddleName is null, you end up with two spaces between First and Last names

But FirstName & " " & (Middlename + " ") & LastName
gives you only a single space when Middlename is null because Null + space = null
 

tcneo

Member
Local time
Tomorrow, 06:40
Joined
Dec 7, 2021
Messages
68
Just FYI, the + is an arithmetic operator which can under certain circumstances serve double duty as a concatenation operator. However, it has different properties than the & so you should understand the difference to take advantage of the properties.

The & ignores nulls so for
fldA = abc
fldB = xyz
You get abcxyz regardless of whether you use & or +

for
fldA = abc
fldB = null
You get abc for & but Null for +. The + returns null if one of the operators is null

for
fldA = 123
FldB = 456
You get 123456 for & but 579 for the +. The & concatenates but the + adds.

The + is used in particular when concatenating name or address parts where one or more might be null

FirstName & " " & MiddleName & " " & LastName
if MiddleName is null, you end up with two spaces between First and Last names

But FirstName & " " & (Middlename + " ") & LastName
gives you only a single space when Middlename is null because Null + space = null
Dear Pat,

Thanks for explaining the difference between the + and the & operator. I learnt a lot!
 

tcneo

Member
Local time
Tomorrow, 06:40
Joined
Dec 7, 2021
Messages
68
Please post code between CODE tags to retain indentation and readability.

Why bother user with message box if not doing anything with response? Why the difficulty with duration MsgBox since you already know how to concatenate? Why not just one MsgBox? Ampersand character is preferred for concatenation.
Code:
If MsgBox ("So you want to generate availability from " & sAvGenFromDate & " to " & sAvGenTillDate _
                  & vbCrLf & " and for " & intAvGenDuration & " minutes each day, right", vbYesNo) = vbYes Then
    'code to save records
    dateCounter = Me.txtAvGenFromDate
    Do While dateCounter <= Me.txtAvGenTillDate
        myR.AddNew
        myR![Work_Date] = dateCounter
        myR![Analyst_ID] = Me.cboAvGenAnalyst
        myR![Available_Duration] = Me.txtAvGenDuration
        myR.Update
        dateCounter = dateCounter + 1
     Loop
End If
Dear June,

I will take note about the CODE tags. I didn't know about it prior to this.

I agree with you that the message boxes are not neccessary. They are a relic of when I was just starting out with VBA and practising a few lines of simple code. They will most probably be removed in the final product.
 

tcneo

Member
Local time
Tomorrow, 06:40
Joined
Dec 7, 2021
Messages
68
How do I prevent the For Loop from running if any of the needed controls are blank/null? I tried using a IF THEN statement with a End Sub inside it to end the routine early but got an error message.
 

June7

AWF VIP
Local time
Today, 14:40
Joined
Mar 9, 2014
Messages
4,318
If Not IsNull(Me.controlname1) And Not IsNull(Me.conrolName2) Then
'code save records
End If
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:40
Joined
Feb 19, 2002
Messages
35,935
Using IsNull() is not sufficient when working with form controls since users can replace a value with a ZLS which visually is indistinct from a null but logically is different. So, when checking controls on a form, always allow for ZLS

if Me.controlname1 & "" <> "" And Me.conrolName2 & "" <> "" Then

Or you could use the Len() function. Both handle ZLS and Null with one test.
 

tcneo

Member
Local time
Tomorrow, 06:40
Joined
Dec 7, 2021
Messages
68
Using IsNull() is not sufficient when working with form controls since users can replace a value with a ZLS which visually is indistinct from a null but logically is different. So, when checking controls on a form, always allow for ZLS

if Me.controlname1 & "" <> "" And Me.conrolName2 & "" <> "" Then

Or you could use the Len() function. Both handle ZLS and Null with one test.
Thanks for letting me know about the ZLS issue. How do I use LEN() to test?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:40
Joined
May 7, 2009
Messages
16,129
You can also test if there is date on both controls:

If IsDate([txtAvGenFromDate]) And IsDate([txtAvGenTillDate]) Theb
' all dates are entered
Else
End If
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:40
Joined
Feb 19, 2002
Messages
35,935
f Len(Me.controlname1) <> 0 AND Len(Me.conrolName2) <> 0 Then

IsDate() is fine but it, of course ONLY works for dates, The two methods I showed work regardless of the data type.
 

moke123

AWF VIP
Local time
Today, 18:40
Joined
Jan 11, 2013
Messages
2,802
Another test for null or ZLS uses the Nz().

If Nz(yourcontrol,"") <> "" then
 

tcneo

Member
Local time
Tomorrow, 06:40
Joined
Dec 7, 2021
Messages
68
If Not IsNull(Me.controlname1) And Not IsNull(Me.conrolName2) Then
'code save records
End If
i just learnt about EXIT SUB today.

is it a good idea to use exit sub rather than nesting the code in the THEN block? especially, if there are many conditions i need to check before proceeding with the code.

For example:

Code:
SUB button_click()

If condition1 THEN
    EXIT SUB
END IF

If condition2 THEN
    EXIT SUB
END IF

main chunk of code now that conditions are checked

END SUB
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:40
Joined
Feb 19, 2002
Messages
35,935
I prefer to use exit sub because it makes the code much easier to expand. At some point, the indenting gets silly when you nest too deeply and if you don't nest, the code is muddled and hard to understand.
 

tcneo

Member
Local time
Tomorrow, 06:40
Joined
Dec 7, 2021
Messages
68
I prefer to use exit sub because it makes the code much easier to expand. At some point, the indenting gets silly when you nest too deeply and if you don't nest, the code is muddled and hard to understand.
Will there be any issues with exit sub with regards to the releasing of recordsets, variable, memory etc.
 

June7

AWF VIP
Local time
Today, 14:40
Joined
Mar 9, 2014
Messages
4,318
VBA releases variables from memory automatically when procedure ends but never hurts to close and set objects to nothing if you want. So instead of Exit Sub in each If would need a GoTo to branch to a block with the clean up code.
 

MarkK

bit cruncher
Local time
Today, 15:40
Joined
Mar 17, 2004
Messages
7,907
I would do...
Code:
SUB button_click()

If Not (condition1 OR condition2) THEN
    main chunk of code now that conditions are checked
END IF

END SUB
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:40
Joined
Feb 19, 2002
Messages
35,935
Will there be any issues with exit sub with regards to the releasing of recordsets, variable, memory etc.
It is highly unlikely that you would have a recordset open in the form's BeforeUpdate event but if you do, you should instead of using Exit Sub, use GoTo an ExitProc so you can close open objects. Access handles the rest of the cleanup itself.
 

tcneo

Member
Local time
Tomorrow, 06:40
Joined
Dec 7, 2021
Messages
68
I would do...
Code:
SUB button_click()

If Not (condition1 OR condition2) THEN
    main chunk of code now that conditions are checked
END IF

END SUB
i was doing that initially but then it got too lengthy. condition 1 was checking for empty fields and i wanted to tell the user to fill up all fields before clicking on the button. condition 2 was checking for valid dates (eg Till date is after From date), and i wanted to tell the user to key in the appropriate date range before trying again.
 

Users who are viewing this thread

Top Bottom