Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to pass a variable from one form to another conditionally

Posted on 2014-01-02
7
Medium Priority
?
1,248 Views
Last Modified: 2014-01-07
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?
0
Comment
Question by:rick_danger
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 668 total points
ID: 39751266
<<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?>>

  Best way to do this is to use the forms openarg parameter.  With that, you can pass an argument into the form.

  Often though, what many do is use a delimiter to pass multiple arguments.  Here's an example:

      DoCmd.OpenForm "frmCustomerAddOnly", acNormal, , , acFormEdit, acDialog, "ADD;SETCTRLTODATA=txtCustomerID:" & NewData & ";EXITTOFORM=frmLoad"

 with:

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

 being what I'm passing in the openarg argument. I used a semi-colon to break apart the main arguments and a colon for value seperation.  So I'm telling this form:

1. 'ADD' - Jump into add mode
2. 'SETCTRLTODATA' - Set the following control(s) to a default value
3. 'EXITTOFORM' - when the user is done, set focus back to this form.

 I have a set of routines to split apart the string based on a delimiter, but you can use SPLIT() to put everything into an array.

 The 'tags' you use and the format are up to you.

Jim.
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 668 total points
ID: 39751274
using the code from the menu, add an openargs option to open the form

docmd.openform "formx", openargs:="menu"

you can write this also like this

doCmd.openform "formx", , , , acFormAdd, , "Menu"



in the open or load event of "formx", check if the openargs has value

private sub form_load()

if me.openargs & ""=""  then  'this form was open from the other form
   'do the normal routine
   else  
   ' form was open from menu
end if


end sub
0
 
LVL 49

Assisted Solution

by:Dale Fye
Dale Fye earned 664 total points
ID: 39751287
Although I generally do it similar to Jim's method, there is another method that I have also used.

In the Form_Load event, check to see which of the other forms is loaded.

Private Sub Form_Load

    if currentproject.allforms("form1").isloaded then
        me.txt_SupplierID.DefaultValue = forms("form1").txt_SupplierID
    End If

End Sub

This will work if you know the sequence that the forms can be loaded and if there is sufficient definition that if "form1" is loaded, you know that it is the only form that could currently call your input form.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Accepted Solution

by:
rick_danger earned 0 total points
ID: 39751298
OK, I understand these 2 solutions, and they make sense to me.

However, is there a clever way of splitting the args back into the string or number that I want? Or do I just use string handling?

Split() was mentioned, but how do I use that?
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 39751346
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

0
 
LVL 58
ID: 39751349
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.
0
 

Author Closing Comment

by:rick_danger
ID: 39761632
Thanks for all the help!
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question