ksingh0311
asked on
Copying access form field data to another fields
I inherited a database, which I have to maintain.. The issue is this: This is a database that stores data on children suffering from some disease. The disease has a high chance of affecting children's sibling also. So we collect information on kid as well as their brother/sister also. When we call a kid's mother, we ask them if they have another kid also. If the mother says yes, the access form pops up a messagebox asking the question: Does the second kid resides at the same address?, and if answered yes, a new subject ID is supposed to be created (the original subject has a preassigned subject ID created based on conditional logic coded into the form), and some details such as subject'sd address etc. are copied over to the next subject ID (the sibling). The Subject ID is the primary key, but is not autonumber. New IDs are created by the following logic:
For new subjects: Forms![frmPrescreenDataEnt ry]!Subjec tID.Value = Forms![frmPrescreenDataEnt ry]!txtNew FamilyID & "01"
This creates new subjects IDs such as 2201, 2301, 2401 etc.
For siblings of children, following code is being used to create new subject IDs: NextID = DLookup("MaxID", "qryCurrentMaxID") + 1
This creates new subject IDs for siblings such as 2202, 2203,, 2302, 2303 etc.
If it worked fine, then it was perfect. However I am having two errors: First, The address is not being copied over to the next Subject ID (child's sibling), and the Second: If the original subject in question is a pre-existing record (i.e., not a new record), the new subject ID is created without any problems. However if the original subject ID is a new record being created, whenever the siblings question comes up, a blank form comesup, but new Subject ID for the sibling is not created, rather an error message pops up: This primary key cannot contain a null value. After the above error message box pops up, it opens up the VB debugger, with the "Me.AllowAdditions = False" highlighted. So I tried to change False to True. After doing that, the new blank form still comes up, and there is no error message box. But there is no new subject ID (for siblings) either..
Mind it, the second issue happens when the original subject ID in question is a new record (works fine when the original Subject ID is a pre-existing record)... The first issue (address filelds being copied over) never work at all.. This is the complete code used:
For new subjects: Forms![frmPrescreenDataEnt
This creates new subjects IDs such as 2201, 2301, 2401 etc.
For siblings of children, following code is being used to create new subject IDs: NextID = DLookup("MaxID", "qryCurrentMaxID") + 1
This creates new subject IDs for siblings such as 2202, 2203,, 2302, 2303 etc.
If it worked fine, then it was perfect. However I am having two errors: First, The address is not being copied over to the next Subject ID (child's sibling), and the Second: If the original subject in question is a pre-existing record (i.e., not a new record), the new subject ID is created without any problems. However if the original subject ID is a new record being created, whenever the siblings question comes up, a blank form comesup, but new Subject ID for the sibling is not created, rather an error message pops up: This primary key cannot contain a null value. After the above error message box pops up, it opens up the VB debugger, with the "Me.AllowAdditions = False" highlighted. So I tried to change False to True. After doing that, the new blank form still comes up, and there is no error message box. But there is no new subject ID (for siblings) either..
Mind it, the second issue happens when the original subject ID in question is a new record (works fine when the original Subject ID is a pre-existing record)... The first issue (address filelds being copied over) never work at all.. This is the complete code used:
Private Sub framePrescreeningOfSibling_AfterUpdate()
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Dim NextID, HomePhone, Address1, Address2, City, State As String
Dim Zip As Variant
'Calculate the Next Sibling ID for the Family
NextID = DLookup("MaxID", "qryCurrentMaxID") + 1
'MsgBox NextID
'Store the Current SubjectID's Contact Info
HomePhone = Me.txtHomePhone
Address1 = Me.txtAddress1
Address2 = Me.txtAddress2
City = Me.txtCity
State = Me.cboState
Zip = Me.txtZip
'Msgbox Data
Msg = "Does this sibling reside at the same address?"
Style = vbYesNo
Title = "Adding a Sibling"
Help = ""
Ctxt = 1000
'Detect a Yes Value and Start a New Record for the Sibling
If Me.framePrescreeningOfSibling.Value = -1 Then
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then
Me.AllowAdditions = True
DoCmd.GoToRecord , , acNewRec
Me.SubjectID.Value = NextID
'Map Same Conact Data to New Sibling
Me.txtHomePhone = HomePhone
Me.txtAddress1 = Address1
Me.txtAddress2 = Address2
Me.txtCity = City
Me.cboState = State
Me.txtZip = Zip
Me.txtSource.SetFocus
Me.AllowAdditions = False
Else
Me.AllowAdditions = True
DoCmd.GoToRecord , , acNewRec
Me.SubjectID.Value = NextID
Me.txtSource.SetFocus
Me.AllowAdditions = False
End If
End If
End Sub
ASKER
I am sorry for the duplicate questions.. I think I pressed submit twice by mistake..
No problem; just delete it - or 'request attention'.
ksingh0311,
Access has a "Duplicate Record" button that you can create using the button wizard.
It will duplicate the current record.
If there are fields that you did not want duplicated, you can simply clear them out.
Now, this is by no means the most elegant system, but it should do it your needs are fairly basic.
Access has a "Duplicate Record" button that you can create using the button wizard.
It will duplicate the current record.
If there are fields that you did not want duplicated, you can simply clear them out.
Now, this is by no means the most elegant system, but it should do it your needs are fairly basic.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,
Sorry for the long delay responding.. I was out and just checked the responses. Yes, this solution helped, and all the fields that I wanted copied and working just fine. Thanks!
Sorry for the long delay responding.. I was out and just checked the responses. Yes, this solution helped, and all the fields that I wanted copied and working just fine. Thanks!
;-)
https://www.experts-exchange.com/questions/24391233/Copying-access-form-field-data-to-another-fields.html
Please use that one for responses.