Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to pass a variable from one form to another conditionally

Posted on 2014-01-02
7
Medium Priority
?
1,242 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 48

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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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 48

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

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.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

610 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