Add Sno with Date, Add 180 days added to previous record

I have an access form with few subforms.
There is a date in the main form. I want to add a Sub Sno with date in to the subform. Below is the details.
First appointment date (Mainform)
Add first record.           main form date+3months. There is a field to record their attendance in subform.
Add Second record.    subform attendance date+6 months
Add third record.         subform attendance date+6 months
I managed to add the first record with 3 months added.
When the 2nd and 3rd 6months from the attendance date is creating problem with other records if I close and open again. (i.e. adding 6 months to the other record's attendance
Any ideas?
LVL 30
MAS (MVE)EE Solution GuideAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What is a "Sub Sno"?
0
MAS (MVE)EE Solution GuideAuthor Commented:
Sno on main form (on main table)
SubSno is a serial on subform on another table which a one to one relation
Sno(PK)    Reg.Date      
101      11-3-2014    --> SubSno  PV.date           PVATT             Related Key
                                             PV1     11-6-2014       12-6-2014               100
                                             PV2     12-12-2014     13-12-2014             100
                                             PV3     13-6-2015       13-6-2015                100
101      15-2-2014    -->      PV1     15-5-2014       15-5-2014                101
                                             PV2     15-11-2014      16-5-2014               101
                                             PV2     16-11-2014      16-5-2014               101
PV1 and PV2  is the Sub Serial no which is on the subform on another table.
100 and 101 is the serial no on the main form which is on the main table.
PV1 is date after 3 months after on the main form (Registration Date)
PV2 is date after 6 months from the PV1 attended date
PV3 is date after 6 months from the PV2 attended date
PV Date is the date of appointment  given
PVATT is the date they attended


Please let me know if it is not clear
I want to automate these dates and PV numbers
PV1 date is the date after3 months of registration.
PV2 date is the date after 6 months of PV1 attended date.
PV3 date is the date after 6 months of PV2 attended date.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
So do the records on the Subform already exist? Or do you need to create those records?

If you need to create them, then under what conditions would they be created? For example, do you want to create records only when a new Mainform record is entered?

If they already exist, then you can use DateAdd to modify the dates, based on the RegistraionDate:

Me.YourSubformControl.form.PV1 = DateAdd("m", 30, Me.RegistrationDate)

You would then have to determine if the PVAtt is populated, and go from there:

If Nz(Me.PVAtt, 0) <> 0 Then
  Me.PV2 = DateAdd("m", 6, Me.PVAtt)
End If

Again, you'd have to determine when to do this - would you do this in the AfterUpdate event of the PVAtt control on the form, for example, or would you have a code routine that calculates the dates as needed?

Same basic situation for PV3 - you'd use DateAdd to add 6 Months to PV2Att Date.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MAS (MVE)EE Solution GuideAuthor Commented:
-->So do the records on the Subform already exist? Or do you need to create those records?
We have to create it.
I managed to create the first record. But problem comes with the second record.
The date is 6 months after the 1st appointment attended date (which is PVATT).
Only 1st appointment is 3 months after registration. Rest all added 6 months from the last attended date.
BTW PV1,PV2,PV3 are serial numbers
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
But you cannot create the second record until you have a value PVATT field for the first record - is that right?

If so, then you'd have to create that second record in the AfterUpdate event of the subform - so you'd check to see if the PVATT value is filled, and then further check to see if the second record has been created. If not, then you'd create the record with the correct values.
0
hnasrCommented:
If possible, upload a sample database with the objects necessary to reproduce the issue.
Give instructions of what to enter and what you get and what to expect.
0
MAS (MVE)EE Solution GuideAuthor Commented:
Uploaded sample
open the tab TC and you will see sample data entered.
sample data and its details below

Sno on main form (on main table)
SubSno is a serial on subform on another table which a one to one relation
Sno(PK)    Reg.Date      
101      11-3-2014    --> SubSno  PV.date           PVATT             Related Key
                                             PV1     11-6-2014       12-6-2014               100
                                             PV2     12-12-2014     13-12-2014             100
                                             PV3     13-6-2015       13-6-2015                100
101      15-2-2014    -->      PV1     15-5-2014       15-5-2014                101
                                             PV2     15-11-2014      16-5-2014               101
                                             PV2     16-11-2014      16-5-2014               101
PV1 and PV2  is the Sub Serial no which is on the subform on another table.
100 and 101 is the serial no on the main form which is on the main table.
PV1 is date after 3 months after on the main form (Registration Date)
PV2 is date after 6 months from the PV1 attended date
PV3 is date after 6 months from the PV2 attended date
PV Date is the date of appointment  given
PVATT is the date they attended


Please let me know if it is not clear
I want to automate these dates and PV numbers
PV1 date is the date after3 months of registration.
PV2 date is the date after 6 months of PV1 attended date.
PV3 date is the date after 6 months of PV2 attended date.
0
MAS (MVE)EE Solution GuideAuthor Commented:
I manage to add that. Now I need only guidance on how to access through network?
I need to give access to other users in the network in which 90% users are not in domain
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Now I need only guidance on how to access through network?
That is an entirely different matter, and you should ask that as a new question.
0
MAS (MVE)EE Solution GuideAuthor Commented:
Dim datetcpc
Dim tempdate
datetcpc = Parent.MST_TCPC_Date
DoCmd.GoToRecord , "", acLast
If IsNull(PVNO) Then
DoCmd.GoToRecord , "", acNewRec
Me.PVNO = "PV1"
Me.PV = DateAdd("m", 3, datetcpc)
ElseIf Me.PVNO.Value = "PV1" Then
If IsNull(ATTPV) Then
MsgBox "Please enter the last PV attended date"
GoTo lastn
End If
datetcpc = Me.ATTPV
DoCmd.GoToRecord , "", acNewRec
Me.PVNO = "PV2"
Me.PV = DateAdd("m", 6, datetcpc)
ElseIf Me.PVNO.Value = "PV2" Then
If IsNull(ATTPV) Then
MsgBox "Please enter the last PV attended date"
GoTo lastn
End If
datetcpc = Me.ATTPV
DoCmd.GoToRecord , "", acNewRec
Me.PVNO = "PV3"
Me.PV = DateAdd("m", 6, datetcpc)

Open in new window


I used this code and it worked
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MAS (MVE)EE Solution GuideAuthor Commented:
I have done it by myself
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.