Solved

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

Posted on 2014-07-24
12
401 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 25

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 25

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
 
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 25

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 25

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 25

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 25

Author Closing Comment

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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

786 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