Solved

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

Posted on 2014-07-24
12
399 Views
Last Modified: 2014-08-14
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?
0
Comment
Question by:-MAS
  • 6
  • 4
12 Comments
 
LVL 84
ID: 40216395
What is a "Sub Sno"?
0
 
LVL 24

Author Comment

by:-MAS
ID: 40216714
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
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 40221332
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
 
LVL 24

Author Comment

by:-MAS
ID: 40221553
-->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
 
LVL 84
ID: 40226632
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 30

Expert Comment

by:hnasr
ID: 40227638
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
 
LVL 24

Author Comment

by:-MAS
ID: 40229801
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
 
LVL 24

Author Comment

by:-MAS
ID: 40232716
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
 
LVL 84
ID: 40232955
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
 
LVL 24

Accepted Solution

by:
-MAS earned 0 total points
ID: 40251096
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
 
LVL 24

Author Closing Comment

by:-MAS
ID: 40260127
I have done it by myself
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now