Solved

How to pass a variable from one form to another conditionally

Posted on 2014-01-02
7
1,230 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 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 167 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 167 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 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 166 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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 47

Expert Comment

by:Dale Fye (Access MVP)
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 57
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

861 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