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 *****
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: