How to insert date to and from including dates in between date in database table using vba in ms access?

Question

Using MS Access is quite new to me. I am making a leave application form where user inputs the leave date from and to then the data that will be inserted on the table should be the leave date from , then date in between from and to, and up to date. For example, leave date from 11 July up to 16 July. The data on the table should be like this: LEAVE_DATE 7/11/2016 7/12/2016 7/13/2016 7/14/2016 7/15/2016 7/16/2016

I have looked at this post How to insert values into the database table using VBA in MS access but i don't know how to do the loop for the dates.

here's my code for reference. please help me on this. thank you!

Private Sub btnSubmit_Click()

Dim FromDate As Date
Dim DateDiff As Integer
Dim strSQL As String

If Len(Nz(Trim(Me.txtLeaveFrom), "")) = 0 Then
    Call MessageBox("Please enter Leave Date From.", ExclamationIcon, OKOnly)
    Me.txtLeaveFrom.SetFocus

ElseIf Len(Nz(Trim(Me.txtLeaveTo), "")) = 0 Then
    Call MessageBox("Please enter Leave Date To.", ExclamationIcon, OKOnly)
    Me.txtLeaveTo.SetFocus

ElseIf Len(Nz(Trim(Me.cmbLeaveType), "")) = 0 Then
    Call MessageBox("Please select Type of Leave", ExclamationIcon, OKOnly)
    Me.cmbLeaveType.SetFocus

Else
    FromDate = Me.txtLeaveFrom

    strSQL = "INSERT INTO LEAVE_RECORD (EMP_ID, LEAVE_DATE, LEAVE_TYPE, APPROVED_FLG, REMARKS)" & _
                    "VALUES (EMPLOYEE_ID, '" & FromDate & "', '" & Me.cmbLeaveType & "' , 0, me.txtReason)"


    Call MessageBox("Leave Application Submitted. Please wait for approval from management.", InformationIcon, OKOnly)
End If

End Sub

KyloRen · Accepted Answer · 2016-07-11 03:50:13Z

0

To loop through dates you can use the DateAdd function along with a do loop.

I think I understand what you are saying, so try this code below to see if that is what you were trying to do.

     FromDate = Me.txtLeaveFrom

     Do Until FromDate = Me.txtLeaveTo

        ' your code
        strSQL = "INSERT INTO LEAVE_RECORD (EMP_ID, LEAVE_DATE, LEAVE_TYPE, APPROVED_FLG, REMARKS)" & _
                    "VALUES (EMPLOYEE_ID, '" & FromDate & "', '" & Me.cmbLeaveType & "' , 0, me.txtReason)"

         FromDate = DateAdd("d", 1, FromDate)
         Debug.Print FromDate

     Loop

I put a Debug.Print there for you to check that the dates are incrementing. You will have to make any adjustments to the code to enter the data you want alone with each corresponding date.

edited Jul 11, 2016 at 3:50

answered Jul 11, 2016 at 3:44

KyloRen

2,7715 gold badges33 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

KyloRen Over a year ago

You will need to use INSERT for one record at a time. So you should have the DoCmd.RunSQL strSQL inside the loop. But, I don't know if that will cause you any problems, you will have to run it and see.

Collectives™ on Stack Overflow

How to insert date to and from including dates in between date in database table using vba in ms access?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related