Function 1

[Mindmaster]

Hi all

Has any one got a function to their disposal to project dates into a subform. Needs to work weekly from date=(). And create records specific to value in combo box????

Thanks

 

[Mindmaster]

Please Please take a look i am desperate for any input!!

 

[JimHorton]

Mind,
You'll need to be a bit more specific, ie what type of values in combobox, what dates do you want in the subform, etc. As a general answer, to do date math you can use the DateAdd() function, to add Years, Months, etc. down to seconds to a date, then to populate the subform the syntax is Forms!MainForm!SubformNAME.Form!subformControl.
--Jim

 

[Mindmaster]

thanks jim...

specifics are i have a subform containing records of agreed payment dates. The dB is for repayment of o/s balance. The user needs to select the number of weeks then the function inputs sub records defaulted 7 days from to today and so on.

example if user selects 10 weeks the dates are weekly from today for 10 weeks other fields in subform are the amount due --which is defaulted to the o/s balance div by the number of weeks and the method defaulted to combox on main form..

the part that i am having trouble is the automatic increment of records equalling the number of weeeks selected and the corsponding dates every 7 days..

thanks so much for taking a look

 

[Mindmaster]

okay specifics are

combox- main form numbers 1-20

dates normal dd/mm/yy - default as 7 day intervals

record number must = combox number

thanks

rob

 

[Mindmaster]

Come on someone must have done this need to get it done before the weekends please help!!!!!!!!

 

[PaulF]

Something along this line might do

Private Sub ComboBoxName_AfterUpdate()
Dim db As DAO.Database, rst As DAO.Recordset
Dim x as Integer, dtNewDate as Date
Set db = CurrentDb
Set rst = db.OpenRecordset("YourTableName&quot
dtNewDate = Date
For x = 1 to ComboBoxName
dtNewDate = DateAdd("d", 7, dtNewDate)
With rst
.AddNew
!DateField = dtNewDate
.Update
End With
Next x
Set db = Nothing
Set rst = Nothing
End Sub

PaulF

 

[Mindmaster]

thanks so much i'll give it a try. i have tried alsorts my last attempted included the largest if stament i have ever seen thanks so much i'll give it a try and let you know.

thanks again

 

[braindead2]

Mindmaster,
you are still not being very specific as what you are trying to do

Here is a start based on what I think you may be asking
will add a record to a table and in the date field add the date incramenting 1 week at a time
you will need to do some serious editing to make this work but hopefully it gets you stared in the right direction

pass function on like this weeklyincrement(combo.value)

function weeklyincrement(intwks as integer)
dim rst as recordset,db as database
dim curday as variant, x as integer
curday = date()
set db = currentdb
'open recordset where subform is based on for adding
setrst = db.openrecordset("tablenamehere&quot
for x = 1 to intweeks 'loop how many weeks
rst.addnew
'this is where you add the fileds you need
rst!paymentnumber = x
rst!datefieldnamehere = dateadd("w",x,date())
rst!recordnumber = me.combobox.value
rst.update ' record added
next x
rst.close
set db = nothing
set rst = nothing

good luck

 

[braindead2]

previous must have been posted while I was writing my code.
do as PaulF says!

Gee! I hate when that happens!

 

[Mindmaster]

No i have tried the other code but again i'm getting confused...

The problem at hand is i have a main form containg customers details. on this main form i have a number of fielsds name address...etc

and one for total balance

the purpose is that we have a customer with a very outstanding balance customer is agrees to pay it of weekly currently the user enters each payment into the linked subform with the date and the amount,

most customers split the amount over say 20 weeks and pay every week on the same day at the post office or switch until the balance is cleared. As this seems to be the case it is time consuming for the user to input each date and amount so this is the reason for the projection of dates..

the agent completes the customers record the inputs total balance advises the customer what the wqeekly instalements are over say 20 weeks the customer agrees to make payments weekly . I know want the customer to push the command button and for the amount to div by the total balance (this bit not a problem) but then when say the user selects 10 weeks in a combox the function will create 10 records each 7 days apart starting from todays date. then the subform will look a bit like this:

example sorry about the roughness

Main form (linked to table 1)

Account no:89412112
Name:roberts
Address:10 anywhere
Total Balance:£200
Number of weeks : 4 (could be anything from 1-20 weeks)

Sub form (linked to table 2)

Date due | Amount due | account number

07/12/01 | £50 | 89412112
14/12/01 | £50 | 89412112
21/12/01 | £50 | 89412112
28/12/01 | £50 | 89412112


the subform is the piece i need the function to perform out of the information on the main form i need it to be triggered by a command button like (forcasted dates) these dates need to be defaulted incase of irregular customers.

thanks guys

so much i'm going to be hear late i think but would like to finish asap so i really apprieciate this i owe anyone who can understand this and me i drink!!!

regards

 

[KyleS]

Hi,

I understand what your trying to do here, but I still think Paul's code will work with some adjustments. Try this, but you have to switch every italicized section with their proper names in your db:

Private Sub ComboBoxName_AfterUpdate()
Dim db As DAO.Database, rst As DAO.Recordset
Dim x as Integer, dtNewDate as Date
Set db = CurrentDb
Set rst = db.OpenRecordset("YourTableName&quot
dtNewDate = Date
For x = 1 to ComboBoxName
dtNewDate = DateAdd("d", 7, dtNewDate)
With rst
.AddNew
.Fields("DateField&quot = dtNewDate
.Fields("AmountDueField&quot = Me.TotalDueField/ComboBoxName
.Fields("AccountNumberField&quot = Me.AccountNumberField
.Update
End With
Next x
DoCmd.Requery SubFormName
Set db = Nothing
Set rst = Nothing
End Sub

Let me know what this is doing if it's not working and we'll paly with it 'till it works

Kyle :

 

[Mindmaster]

paul's code does work but it changes the date to the date specified in the combo box but it does not add records in the process so if i select 5 weeks it gives me the date as the first record- 5 weeks from todays if it is well write but i need it to added all the other dates into the before it does this make sense. I need to include the add record so it includes each date per week up until this date.

but its definatly on the right track

 

[KyleS]

I know what you're looking for, if 4 weeks are selected you need four entries 1 week apart. I just don't have anything like that set up so I can't test this stuff.

I'm honestly not sure why it's doing that. This code should add as many records as you have weeks. We know it's reading the week number correctly because it's putting the proper final date in. The onlything I can think of is maybe it's outrunning itself...

Put ".Requery" between the ".Update" and before "End With"

I'm not guaranteeing that this will fix it, but it will tell me if I'm right about the problem or I've got to go back to the drawing board...

Kyle:

 

[Mindmaster]

Hi Kyle - your code works as did Paul's but the same problem but a little different one date flashes in the subform and if i go into the table after i have selected the number i get an interesting result and so far the best by far i have the number of weeks as blank records until the last date that is correct so it seems it is adding new records but is missing the dates out of the payments before the last one i have tried altering the code but still the same problem any ideas Kyle???

cheers

rob

thanks by the way i feel like i'm getting somewhere

 

[braindead2]

the reason it is putting in the same date is you are not changing the date

change this line to
For x = 1 to ComboBoxName
dtNewDate = DateAdd("d", 7, dtNewDate)

For x = 1 to ComboBoxName
dtNewDate = DateAdd("ww", x, dtNewDate)

 

[Mindmaster]

hi paul,

still getting the same problem i understand the change buit still i get the weeks in blank records followed by the final date????? sorry i keep bothering you

 

[Mindmaster]

Kyle

the requery between the .update and end with

gives an error so had to take that out and but sadly still no joy

 

[Mindmaster]

a bit of a break through i moved the .requery subform name again to before "end with"

and i get the following

14/08/01
blank record
28/12/01
blank record
18/01/01
Blank record
15/02/01
Blank Record
22/03/01
Blank record

i now its the smallest thing thats wrong any ideas

thanks guys...

 

[Mindmaster]

i have changed the code to this and i now get the correct dates but with a gap inbetween each record

the text is as follows:

Private Sub Combo318_AfterUpdate()
Dim db As DAO.Database, rst As DAO.Recordset
Dim x As Integer, dtNewDate As Date
Set db = CurrentDb
Set rst = db.OpenRecordset("Plan Subform&quot
dtNewDate = Date
For x = 1 To Combo318
dtNewDate = DateAdd("d", 7, dtNewDate)
With rst
.AddNew
Me.Plan_Subform_subform!DatePaymentDue = dtNewDate
.Fields("Account ID&quot = Me.AccountNo
.Update
DoCmd.Requery "Plan Subform subform"
End With
Next x

Set db = Nothing
Set rst = Nothing
------------------------

The other problem is you can't view it in the subform you have to go into the table why could this be