asked on

Pass multiple arguments to open a form

I have a form(CAD) where I click to add a person. After clicking the button to add a person it opens a search form(personsearch) to see if that person already exists. After entering a last name and clicking a button a form opens(matchingnames) with a subform showing the matching last names in datasheet view. I double click the name in the subform(newpersonssubform1) to open a new form (persons) to a new record with the matching records.

I want the complaintnumber field from the CAD form to also carry over to the persons form when it is open. This is what is in the double click event of the subform.

Dim Tlastname, Tfirstname, Tmiddle, TDOB

Tlastname = Me!LastName
Tfirstname = Me!FirstName
Tmiddle = Me!Middle
TDOB = Me!DOB

DoCmd.OpenForm "Persons", , , "[LastName] = '" & Me.LastName & "' AND [FirstName] = '" & Me.FirstName & "'"
Me!LastName = Tlastname
Me!FirstName = Tfirstname
Me!Middle = Tmiddle
Me!DOB = TDOB

This works fine but I don't want them to have to enter a new complaint number on the "persons" form. It should come from the "CAD" form.

I am not sure if I understand opening the form based on multiple fields. I can get it to match one, two with help, but I do not understand how to add three or four or five.

dan_vella

You just need to reference the first form if it is still open:

me!complaintnumber = forms!CADform!complaintnumber

or you will need to use a global variable that will hold a value for as long as the application is open.

Leigh Purvis

A global variable is one possibility, but there is much oddness in what you're attempting (setting variables to control values - and having opened the other form - setting the same control names back to those same variables.)

I guess that you're attempting to set those values in the called form's (Persons) controls?
i.e.

With Forms!Persons
!LastName = Tlastname
!FirstName = Tfirstname
!Middle = Tmiddle
!DOB = TDOB
End With

(As you, naturally, can't use Me to refer to anything other than the current object - i.e. Form - in which the code is running)
But that smacks of duplication of data.
There should be one Person record for each valid Person - and you relate to that. Not using the same details again and again.

If this search form that you have returns a result that is a match and you want to use that - then you'd pass on the Primary Key of that record.
If not - and you want to go ahead and create a new record - you'd not (surely) be passing Date of Birth and the entire name (the likelyhood of two individuals sharing that being very low).

If you wanted to pass on the PK to open the correct record - and also pass on this new value which forms part of some detail row (a new complaint or order etc - whatever CAD relates to) then you can of course pass that to the called form - perhaps using a Global variable as suggested - though the OpenArgs parameter is a more common alternative (with no permanent memory allocation).

So perhaps instead of
DoCmd.OpenForm "Persons", , , "[LastName] = '" & Me.LastName & "' AND [FirstName] = '" & Me.FirstName & "'"
You'd have

DoCmd.OpenForm "Persons", Wherecondition:="PersonID = " & Me.PersonID, OpenArgs:=varCADPassed

Then in the called Person form - you can use the Me.OpenArgs value to refer to any passed value - and be at the correct record using the PK.

ASKER CERTIFIED SOLUTION

royce2020

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial