Form swapping process does not update to new record when called from first form ACCESS 2010

I am trying out something I have not done before, swapping subforms on a master form dynamically.  I got it to where the buttons do switch forms, but I am having trouble getting it to go to a new record when called from the first form, frmChildIntake. For test purposes use the name Branson, Jack as there are children for him.  (I have attached a copy of the relevant objects).  On the right is "Child Intake" button which, if there is not a previous record for the child, it should go to a new record.  But I can't seem to get the code correct.  I do not want to use a tabbed form as I do not want to load all the forms at one, but would like to solve this issue.
KIN-DEVELOPMENTChildIntakeForms.accdb
Sandra SmithRetiredAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
I am afraid that form swapping is rather the least of your problems...your database has design issues and requires a decent amount of work to get it in  a working state...
If i am not mistaken this is a transfer from an Excel Spreadsheet ...and it quite informative but Excel and Access are two different worlds...
For your specific trouble you should open your "frmChildIntakeDetailsTEST" like this
DoCmd.OpenForm "frmChildIntakeDetailsTEST", , , , , , 1  ' From frmChildIntake

Open in new window

and then on the "frmChildIntakeDetailsTEST" on the OpenEvent something like this
  If Me.OpenArgs = 1 Then
    DoCmd.GoToRecord , , acNewRec
    Me.AllowAdditions = True
    Me.AllowEdits = True
End If

Open in new window

Sandra SmithRetiredAuthor Commented:
I inherited this mess from what I was told were five other developers who created it so yes, I know it has issues (removed over 50 redundant, useless tables as well as almost 100 queries and still finding things that can be eliminated.  The prior developers did not both see what was there, just kept building on top).  There is no data coming in from Excel, I think I missed getting rid of that in the sample.  All the data is contained in the tables.  If it cannot be done, then so be it and I will go on to other processes.
Sandra SmithRetiredAuthor Commented:
I don't think made myself clear. The frmChildIntake works.  It reflects records when the user makes a Caregiver Search Choice.  However, when you hit the Child Intake button to the right of the record on this form, the frmChildIntakeDetailsTEST does not show the correct record.  It seems to always go to the first record on the table rather than displaying information for the child selected, if there is data, or go to a new record for data entry if there is no previous record.  So, that is the problem, how to get the parent/child and swapped forms on the frmChildIntakeDetailsTEST to reflect (1) proper data if there is existing record for the child on the frmChildIntake form that called this form or (2) go to a new record if there is no record for the child on the frmChildIntake form.  Then, when the user clicks the buttons on the Child Intake Details form, the correct form should be swapped into the container form with relevant data or ability to enter new data.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

John TsioumprisSoftware & Systems EngineerCommented:
Have you used my code....?
Sandra SmithRetiredAuthor Commented:
Hello John

It keeps telling me "Error Number 2105 You can't go to the specified record.

Sandra
Sandra SmithRetiredAuthor Commented:
Actually, solve myself.  Could not get your code to work, then realized I simply needed to get a record in the underlying table first and save it, then call the form.  The below is in the calling form and workers as I want.
Private Sub cmdChildIntakeDetails_Click()
On Error GoTo ErrorHandler
    Dim iCount As Integer
    Dim lngCaseId As Long
    Dim strPersonID As String
   
    strPersonID = Me.PersonID
    lngCaseId = Me.txtCaseID
    iCount = DCount("PersonID", "tblCaseChildIntake", "PersonID = '" & Me.PersonID & "' ")
   
   If iCount > 0 Then
     DoCmd.OpenForm "frmChildIntakeDetailsTEST", acNormal, , "PersonID = '" & Me.PersonID & "' AND CaseID = " & lngCaseId & " "
  Else
    DoCmd.RunSQL "INSERT INTO tblCaseChildIntake (CaseID, PersonID) VALUES (" & lngCaseId & ", '" & strPersonID & "' ) "
    DoCmd.OpenForm "frmChildIntakeDetailsTEST", acNormal, , "PersonID = '" & strPersonID & "' AND CaseID = " & lngCaseId & " "
    Forms!frmChildIntakeDetailsTest.txtFullName = Me.ChildName
  End If
 
Exit_ErrorHandler:
    Exit Sub
ErrorHandler:
    MsgBox "Error Number: " & Err.Number & " Description: " & Err.Description
    Resume Exit_ErrorHandler
End Sub

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sandra SmithRetiredAuthor Commented:
Solved myself once I realized the sequence of events was important.  The called form now has a record it can use.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.