trying to add data into a table between 2 dates. (1 Viewer)

xaavia

New member
Local time
Today, 16:39
Joined
Mar 18, 2011
Messages
1
MS ACCESS
Im making a schedule for a clinic that is run once every week. the treatment lasts for 6 weeks straight.
so a client is booked into a specific clinic from the date of 1st appointment(week 1 of treatment), till the 6th week(week 6 of treatment).

What i have is
-> the clinic's ID
-> Date for every wednesday it is run
-> Clients ID
-> Clients 1st appt date
-> Clients 6th appt date and
-> a clinic schedule table

What i want is a way to automatically add the clinic id into the clinic schedule for every wednesday from the 1st appointment to the 6th appointment.

any suggestions? please? :(
 
Last edited:

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 16:39
Joined
Jul 9, 2003
Messages
16,244
Create a table named “tblClinicSchedule” with two fields: ClinicID, ApptDate

Add the code below to a new module and call the function something like this:

Code:
Call fClinicSchedule(#3/18/2011#, 234, 6, 7)


Code:
Public Sub fClinicSchedule(datDate As Date, lngClinicID As Long, lngNoOfAppointments As Long, lngBetweenAppointments As Long)
'clinic schedule
'Call fClinicSchedule(#3/18/2011#, 234, 6, 7)
Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String

'Set Reference To:-
'VBE > Tools > Reference
'Microsoft ActiveX Data Objects (chances are anything from 2.1 - 2.8 would be fine.
'There's not a lot of reason not to choose 2.8 though really.)
'http://www.access-programmers.co.uk/forums/showpost.php?p=725103&postcount=2

Dim adoCon As ADODB.Connection
Dim adoCmd As ADODB.Command

   Set adoCon = CurrentProject.Connection
   Set adoCmd = New ADODB.Command

strSQL1 = "INSERT INTO tblClinicSchedule(ClinicID, ApptDate) VALUES ("
strSQL2 = ", "
strSQL3 = " )"

Dim X As Integer
Dim datDateOfClinic As Date
datDateOfClinic = datDate - lngBetweenAppointments

    For X = 1 To lngNoOfAppointments
        datDateOfClinic = datDateOfClinic + lngBetweenAppointments
        strSQL = strSQL1 & lngClinicID & strSQL2 & SQLDate(datDateOfClinic) & strSQL3
        'MsgBox " >>> " & strSQL
            'Use a Command Object to issue an SQL statement
                With adoCmd
                    .ActiveConnection = adoCon
                    .CommandType = adCmdText
                    .CommandText = strSQL
                    .Execute
                End With
    Next
    adoCon.Close
    Set adoCon = Nothing
    Set adoCmd = Nothing

End Sub           'fClinicSchedule
 

Users who are viewing this thread

Top Bottom