Solved

Copying access form field data to another fields

Posted on 2009-05-07
7
200 Views
Last Modified: 2013-12-25
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![frmPrescreenDataEntry]!SubjectID.Value = Forms![frmPrescreenDataEntry]!txtNewFamilyID & "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:
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

Open in new window

0
Comment
Question by:ksingh0311
  • 3
  • 2
  • 2
7 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 24333768
This appears to be a duplicate of:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Forms/Q_24391233.html
Please use that one for responses.
0
 

Author Comment

by:ksingh0311
ID: 24335822
I am sorry for the duplicate questions.. I think I pressed submit twice by mistake..
0
 
LVL 77

Expert Comment

by:peter57r
ID: 24335894
No problem; just delete it - or 'request attention'.
0
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.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24349124
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.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 24349199
Here is a sample of my version of that code, and a sample database:

Private Sub cmdDuplicateRecord_Click()

'Duplicates confirmantion
Dim bytConfirm As Byte
'Ask for delete confirmation and display a descriptive Field.
bytConfirm = MsgBox("Duplicate current Record " & "(" & Me.MovieTitle & ")" & "?", vbQuestion + vbYesNo)
    'If No is selected, then Exit the code
    If bytConfirm = vbNo Then
        Exit Sub
    End If
   
    'Select the Current Record
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    'Copy the Current Record
    DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
    'Paste, Append the new Record
    DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
    'Clear the Year Field
    Me.Year = ""
    'Move to the Year Field for data entry
    Me.Year.SetFocus
   
End Sub



Access-EEQ-24391236DuplicateReco.mdb
0
 

Author Comment

by:ksingh0311
ID: 24387349
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!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24387716
;-)

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

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

863 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

22 Experts available now in Live!

Get 1:1 Help Now