Sales Manager Call Cylce Auto "lookup"? not sure of terminology

D

Danimoco

trying to incorporate a call cycle where i already have used a calendar for
current dates and future follow up of task dates. This means i just want to
put 2 weekly visits from a drop down box and the db calculates and records
all future visits - how can i do this?

I know basic access but have no idea how to do this - hope i have all
terminology and explained myself well enough.

Thanks heaps in advance for any help i may get!! :)
 
K

Ken Sheridan

You'll need to execute a loop in which the date is incremented by the visit
interval at each iteration for whatever number of visit dates you want to
project into the future.

Lets say you have a table CustomerVisits which includes columns CustomerID
and VisitDate. One approach would be to have an unbound dialogue form with a
combo box, cboCustomer say whose value would be the CustomerID of the
selected customer (but showing their name), a txtFirstVisitdate text box into
which the date of the first visit can be entered, a txtInterval text box into
which the interval in days between visits can be entered, a txtNumberOfVisits
into which the number of visits can be entered and a 'Confirm' button. The
code in the button's Click event procedure would be:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim n As Integer
Dim dtmdate As Date

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText


For n = 0 to Me.txtNumberOfVisits - 1
dtmDate = CDate(Me.txtFirstVisitDate) + (Me.txtInterval * n)

strSQL = "INSERT INTO CustomerVisits(CustomerID,VisitDate) " & _
"VALUES(" & Me.cboCustomer & ",#" & _
Format(dtmDate,"mm/dd/yyyy") & "#)"

cmd.CommandText = strSQL
cmd.Execute
Next n

So if you want to insert rows into the table for 20 visits at fortnightly
intervals you'd select the customer in question from the combo box, enter the
date of the first visit into txtFirstVisitdate (or get the date from a
calendar control), 20 into txtNumberOfVisits and 14 into txtInterval, then
click the button.

Ken Sheridan
Stafford, England
 
Top