Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 411
  • Last Modified:

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?
0
MAS
Asked:
MAS
  • 6
  • 4
2 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What is a "Sub Sno"?
0
 
MASTechnical Department HeadAuthor 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
MASTechnical Department HeadAuthor 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
 
MASTechnical Department HeadAuthor 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
 
MASTechnical Department HeadAuthor 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
 
MASTechnical Department HeadAuthor 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
 
MASTechnical Department HeadAuthor Commented:
I have done it by myself
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now