Solved

How to pass a variable from one form to another conditionally

Posted on 2014-01-02
7
1,224 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 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

759 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now