This might not be a straightforward as you think. You need to step back a
little first and consider the tables you'll need for this. Firstly a
Customers table like this:

Customers
….CustomerID (primary key, e.g. an autonumber)
….CustomerName (text)
….BillingPeriod (integer number)
….more fields for address, phone etc.

Then a Bills table:

Bills
….CustomerID (foreign key long integer number)
….BillDate (date)
….BillFrom (date/time)
….BillTo (date/time)
….BillAmount

The primary key of this table is a composite one of CsutomerID and BillDate.

Within a form based on Customers include a subform based on Bills, linking
them on CustomerID. In the main parent form add a 'Create New Bill' button.
In its Click event procedure put code along these lines:

Dim cmd As ADODB.Command
Dim strSQL As String
Dim strCriteria As String
Dim strNextDateFrom as String
Dim strNextDateTo as String
Dim dtmlastDateFrom As Date
Dim dtmlastDateTo As Date
Dim intDays As Integer

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

strCriteria = "CustomerID = " & Me.CustomerID
intDays = Me.BillingPeriod

' get latest BillFrom and Bill To dates for current customer,
' defaulting to 60 days prior to current date if no previous bill
dtmLastDateFrom = Nz(Dmax("DateFrom", "Bills", strCriteria),VBA.Date-60)
dtmLastDateTo = dtmLastDateFrom + intDays - 1

' format next dates from and to as date literals in ISO date format
strNextDateFrom = "#" & Format(dtmLastDateFrom+intDays,"yyyy-mm-dd") &
"#"
strNextDateTo = "#" & Format(dtmLastDateTo+intDays,"yyyy-mm-dd") & "#"

' insert row into table
strSQL = "INSERT INTO Bills(CustomerID, DateFrom, DateTo) " & _
"VALUES(" & Me.CustomerID & "," & strNextDateFrom & "," & _
strNextDateTo & ")"

cmd.CommandText = strSQL
cmd.Execute

' requery subform to show new row
Me.sfcBills.Requery

Where sfcBills is the name of the subform control housing the . NB this is
the name of the control in the parent form's Controls collection, not the
name of the underlying form object.

Note that you are using billing periods of a number of days the dates will
vary from month to month and, with a 30 day billing period for instance,
there will not be an exact 12 billing periods per calendar year. The dates
you have given as examples do not in fact constitute 30 day periods, and you
are overlapping the start and end dates of consecutive periods, whereas the
start date should be the day following the end date of the previous period.
The code above will avoid this but if you really want monthly billing periods
then you would need to store the number of months per customer in the
BillingPeriod column rather than days, and add months rather than days, for
which you'd need to use the DateAdd function rather than simple arithmetic.

Ken Sheridan
Stafford, England

Chiki wrote:
Thanks for the Reply. Timmons....

Yes I m using the Fields on My Form.what exactly I want is how would i
display or show the Billing period on My form.

Coming to the Example,
As i said earlier if A's Bill Is generated for the first time on 23/04/2010
for the period 22/03/2010 to 22/04/2010.The Next month bill will also be
generated for the Same Dates i.e. 22/04/2010 to 22/05/2010. Like same for
every month...

Hope i had explained it in detail.Please help me.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/201005/1