Link to home
Create AccountLog in
Avatar of Rick Danger
Rick DangerFlag for United Kingdom of Great Britain and Northern Ireland

asked on

How to pass a variable from one form to another conditionally

I have a form, which can be accessed 2 different ways.

The first is from another form, which will pass the SupplierID to it, by creating a filter. This will list all items for that Supplier. Then when I add a record in that form, I need to reference that SupplierID to use as a foreign key.

But I also access that form direct from a menu, so no data is passed to it. Therefore, if I want to add a record, then user will need to enter the SupplierID.

So, how do I let the form know where it was accessed from, so that it knows whether to take the SupplierID from another form or by user input? Do I check whether that form is open and use that form's SupplierID, or is there a neater way?
SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Something like:
Dim myArray() as String

myArray = Split(";" & me.OpenArgs, ";")

Open in new window

I like to add a semi-colon to the beginning of me.OpenArgs to do two things.
1.  I don't have to deal with me.OpenArgs being NULL
2.  It results in myArray(0) being blank, so I can use a 1 based array, not zero

The key is to 1) know the sequence of the arguments you are passing to the form so that you can reference specific elements of the array.  Using Jim's example of:

"ADD;SETCTRLTODATA=txtCustomerID:" & NewData & ";EXITTOFORM=frmLoad"

This might actually look like the following with an actual CustomerID

"ADD;SETCTRLTODATA=txtCustomerID:123;EXITTOFORM=frmLoad"

Split() would return:
myArray(0): this would be an empty string
myArray(1) : ADD
myArray(2): SETCTRLTODATA=txtCustomerID:123
myArrY(3): EXITTOFORM=frmLoad

you could do something like the following to identify the control that you want to push data into, and the value for that control.
intCharPos1 = instr(myArray(2), "=")    '14
intCharPos2 = instr(intCharPos1 + 1, myArray(2), ":")   '28
strCtrlName = mid(myArray(2), intCharPos1 + 1, intCharPos2-intCharPos1-1)
me.controls(strCtrlName) = Mid(myArray(2), intCharPos2 + 1)

Open in new window

This can be a bit more complicated, which is why I like to use the method I described.  You could also use a combination of the two, by simply passing in the name of the form used to call the new form.  To open the second form, the syntax might look like:

DoCmd.OpenForm "frmCustomerAddOnly", acNormal, , , acFormEdit, acDialog, me.name

Then, in the Form_Load event, you might use:
Private Sub Form_Load

    if NZ(me.OpenArgs, "") = "form1" Then
         me.txt_SupplierID.DefaultValue = forms("form1").txt_SupplierID
    end if

End Sub

Open in new window

Simple enough:

 Dim strOpenArguments() as string

 strOpenArguments = Split(NZ(Me.OpenArgs,""),"|")

 Here "|" being the delimiter, which is what most often use as a vertical pipe is not typically found in data.

 You can use any delimiter you want however.

Jim.
Avatar of Rick Danger

ASKER

Thanks for all the help!