Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-07-24
12
Medium Priority
?
410 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
12 Comments
 
LVL 85
ID: 40216395
What is a "Sub Sno"?
0
 
LVL 27

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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 27

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 85
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 31

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 27

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 27

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 85
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 27

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 27

Author Closing Comment

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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

660 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