Open a form for a new record

I have a database that has tables for Clients and Jobs (tblClients and tblJobs)

Currently working is a form to add a new job.
This form has a query that joins tblClients and tblJobs
It is called from a button on a menu form using…
stDocName = "frmNewJob"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.GoToRecord , , acNewRec

On this form is a field cboClient.  The user selects the Client in this combo box and it populates the client details on the form.  The user then enters the new job information and saves to create the new job.
This is all working fine and has been for ages.

Now I want to do almost the same thing, but calling a copy of the above form – frmNewJobFromEditClient from the client details form.
So when the new form is opened I want it to open as a new record but with the Client details already populated (as if the user had selected the correct client).

I assumed that I would be able to put something like…
Me.cboClient = [frmEditClient].[Client ID]
in the Open event of the frmNewJobFromEditClient form.
I have tried all sorts of options of syntax without success.

Hope someone can help me out.

Regards
Richard
rltomalinAsked:
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.

Juan OcasioApplication DeveloperCommented:
I was just checking out some of the templates in Access and came across the Assets template (access 2016).  Take a look at it.  On the welcome page, it has a link to a video that shows how to use the template and it does something very similar.  It uses macros, but you can convert the macros to vba code.
0
rltomalinAuthor Commented:
Sorry, don't have Access 2016.  Currently using Access 2010.
0
Dale FyeOwner, Developing Solutions LLCCommented:
My preferred method is to pass the ClientID as the OpenArgs argument in the OpenForm method, and you can do the NewRecord aspect in the same step.  Furthermore, this method allows you to use the acDialog option in the WindowMode, which will prevent the code which follows the OpenForm method from running in the form that calls this form; I do this so that I can requery the current form after the popup closes.  It will also open the form in AddNew mode and pass it the value of the ClientID that you want to use for the new job.

So, to start with, the code you have above would be replaced with the following.  The first line will open your popup form in dialog mode (which will prevent that the rest of the code from running until the popup form is closed or hidden).  The way I generally do this is I open the popup form, and if the user actually adds a record, I simply hide that form when the user is done.  If they cancel the Add operation, then I close the form.  So the code below checks to see if the popup is still open and if so, it requeries the ClientJobs combo box and sets the value of that combo to the JobID which was just created, then it closes the popup.
docmd.OpenForm stDocName, acNormal, , , acFormAdd, acDialog, me.cbo_ClientID
if currentproject.allforms(stDocName).IsLoaded then
    me.cbo_ClientJobs.Requery
    me.cbo_ClientJobs = forms(stDocName).JobID
    docmd.close acform, stDocName
end if

Open in new window

Then, in the Popup forms Load event, I add code which sets the value of the ClientID combo box based upon the value that was passed in the OpenArgs value of the OpenForm method.  I also call the AfterUpdate event of that combo box to fill in any other controls on the form which relate directly to the contact.
Private Sub Form_Load
    me.cbo_ClientID = NZ(me.OpenArgs, 0)
    Call cbo_ClientID_AfterUpdate
End Sub

Open in new window

Finally, I set the ControlBox and CloseButton controls on the popup form to No, so the user has to click on either a 'Cancel' or 'Save' button on the form.  The code behind the Cancel button simply closes the form:
Private Sub cmd_Cancel_Click
    me.undo
    docmd.close acform, me.name
end sub

Open in new window

while the code behind the 'Save' button checks to make sure all of the required fields are filled in, and if so, saves the record and closes the form.
Private Sub cmd_Save_Click
    if PassesChecks Then
        me.dirty = false
        me.visible = false
    endif
end sub

Open in new window

The PassesChecks function (shown below is used to make sure that all of the required fields are filled in)
Private Function PassesChecks() as Boolean
    if trim(me.cbo_ClientID & "") = "" then
        msgbox "Select a client"
        me.cbo_ClientID.setfocus
        exit sub
    elseif trim(me.txt_JobTitle & "") = "" then
        msgbox "Enter a job title!"
        me.txt_JobTitle.Setfocus
        exit sub
'   elseif <= enter more criteria here to ensure that all required values for tbl_ClientJobs have been filled in
     
    else
          PassesChecks = true
    end if

End Function

Open in new window

0

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
rltomalinAuthor Commented:
Excellent response - thank you.  This was absolutely appropriate to my questions and easy to follow.
Had it working in 10 minutes.  I would never have figured this out myself.  I have done Access programming in the past but really only rarely - so need a nudge from time to time.
Regards
Richard
0
Dale FyeOwner, Developing Solutions LLCCommented:
glad to help.  Don't forget to close out the question.
0
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
Databases

From novice to tech pro — start learning today.