Save Record, open form, close form

I'm using a form with multiple subforms for data entry.  The field that ties all together is the ClientID (auto# in main table)

I'm not able to open that form to create a new record, because the Client ID doesn't yet exist.

I built a simple form (frm01_Clients_New) that does open to a new record, with just a few key fields.

I’d like to save the record, close this form and simultaneously open the main form (frm01_Clients_New2) for data entry. The query for that form looks for the ClientID field on frm01_Clients_New

here’s what I tried, but nothing happens when I click the button - and is there a better/easier way to do this?

Private Sub opnfrmnew_Click()
On Error GoTo Err_opnfrmnew_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "frm01_Clients_New2"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    stDocName = "frm01_Clients_New"

    Exit Sub

    MsgBox Err.Description
    Resume Exit_opnfrmnew_Click
End Sub
Who is Participating?
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.

"I'm not able to open that form to create a new record, because the Client ID doesn't yet exist."
To save time making assumptions, attach a sample database that recreates the issue.
schneider_ksAuthor Commented:
?? I can't send the's confidential.
Patrick MatthewsCommented:
>> ?? I can't send the's confidential.

Then obfuscate the data to protect your sensitive information.

The data set need not be large, but a sample would be very helpful.
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Helen FeddemaCommented:
Have you tried opening the form in DataEntry mode?  That should open it to a new record.  Try this syntax:

DoCmd.OpenForm FormName:="frmContacts", datamode:=acFormAdd

Open in new window

"?? I can't send the's confidential."
You should understand your database than anyone else.
To help we need to experience such issue, by creating the databae, which we no nothing about.

A sample database is not a real one, but a dummy database that reprosuces the issue. No unnessary objects, and enogh data to reflect your output.
By doing so, you reduce the time to get a solution and invite others to help. Time is an issue here.
I'm going to guess that I know what's going on, but do understand that without a copy of the database in front of me, it's only a guess.

When you open the main form to a new record, there's no ClientID, so the subforms obviously can't show any data.  I know that this can be a problem is someone tries to enter something into the subforms before the data on the main form has been saved: a great way to create orphan data.

How I've dealt with this before now is to have a check run on the Form_Current event.  Something like
    If Me.NewRecord Then
        'Take some steps to hide the subforms
    End if

Hiding them can be a simple as setting the subform control's Visible Property to False.  I've even done it by putting them all on a tab control and hiding that.

By the way, the code can them become something like:
    tabSubFormTabControl.Visible = Not Me.NewRecord.

Then, once the user has entered enough data into the main form for it to make sense to save it, have them click a "Save and Proceed" or "Apply" button.  The code behind that can save the data:
   Me.Dirty = False
will do the trick, and either have some validation beforehand or catch errors to make sure that the data is in good shape.

If it is, then you can simply go
   Me.Dirty = False
   tabSubFormsTabControl.Visible = True
and you're back in action.

Last thought, in the On Current event, make sure you set the focus to a control that is not going to be hidden otherwise you'll get an error that will make users cry.

Hope this helps, if not, it's yours for free anyhow.
schneider_ksAuthor Commented:
Sorry, I thought this would be pretty straightforward. Here's a stripped down version.
schneider_ksAuthor Commented:
Have to leave for a few hours, will check back later, Thanks.
Downloaded the database. Explain what to do, according to original question, and to expect what.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Assuming you're using Bound forms:

"I’d like to save the record, close this form and simultaneously open the main form (frm01_Clients_New2) for data entry. The query for that form looks for the ClientID field on frm01_Clients_New"

To save your record, do as Andrew_Webster suggests:

If Me.Dirty Then Me.Dirty = False

Once you do that, then open your second form:

DoCmd.OpenForm "frm01_Clients_New2", , "ClientID=" & Me.ClientID

Now, in the OPEN event or frm01_Clients_New2:

If CurrentProject.AllForms("YourOriginalForm").IsLoaded Then DoCmd.Close acForm, "YourOriginalForm"

This insures that your second form can get the necessary infom from the original form if need be.

It is far, far better if you try to implement this yourself rather than relying on others to do it for you. You'll learn a lot more, and be ready for the next time a similar issue comes up. I'm all for uploading databases when we've exhausted other means of support, but this isn't one of those times.


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
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.