MS Access - Form - Pass value from one form to multiple forms using condition

Hi,

I have five core table say as tbl1,tbl2,tbl3,tbl4 and tbl5, they all are linked with one table say as tblMain. There are five forms as frm_tbl1,frm_tbl2,frm_tbl3,frm_tbl4 and frm_tbl5, in all of these there is a field Sou that need to get value from frm_tblMain. frm_tblMain open when the click event occour in Sou field which open this form, user select the Sou and that value have to pass to previous form. As there are five form is there a way to pass value from one main form to all of this depending upon which form is open. If frm_tble1 is open it should pass to that form ,if other is open to other so on.

Any help?

Thank you.
LVL 1
WSStudentAsked:
Who is Participating?
 
WSStudentAuthor Commented:
Thank you everyone for your help.

It's solved.
Wsm93--1-.accdb
0
 
Dale FyeCommented:
when you open a form, you use the OpenForm method, which contains an argument (the last one in the list) named OpenArgs.  If you pass a value as this argument, it the form that is being opened will be able to read that argument.  In the example below, it would be 23, or in the second example, it would be the value of the textbox control 'txt_Sou'
docmd.OpenForm "formName", acNormal, , , , acDialog, "23"
docmd.openform "formname", acNormal, , , , acDialog, me.txt_Sou

Open in new window

You can also reach back to that main form from within Form1 using the Load event
Private Sub Form_Load

    me.txt_Sou = Forms("frmMain").txt_Sou

End Sub

Open in new window

0
 
WSStudentAuthor Commented:
But how to pass value using condition, like if the main form is open using form_tbl1 it should pass value to form_tbl1, if it is open using frm_tbl2 then pass value to frm_tbl2 and so on?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Dale FyeCommented:
WS,

It really depends.  Are you opening frm_Main first, then opening the form_tbl1, tbl2, ... , and then trying to pass a value back to frm_Main, which is already open, or is that form closed?  It would be helpful to understand the flow of your application better.
0
 
WSStudentAuthor Commented:
first i will open frm_tbl1,frm_tbl2,.... and then from those forms frm_Main will be open (frm_Main is basically a search form), will select the ID and then ID then should pass back to previous form.

The problem i am having is at this point that while passing value back how the access will know to pass value back to which form,either to frm_Tbl1,frm_tbl2 ...?
0
 
Fabrice LambertFabrice LambertCommented:
There are many way to achieve communication between forms:
- Open arguments.
- Referencing the calling form (assuming it is open).
- Equip the calling form with getters and / or setters.
- Raise events.
- Share data trough a file. ect ect ....
0
 
Fabrice LambertFabrice LambertCommented:
A simple way to achieve your desired result, is as Dale suggested, to first equip your calling forms (frm_tbl1,frm_tbl2,frm_tbl3 ect ...) with a property let procedure (this will alow you to write back search results).
Second, open your "search form" with an argument identifying wich form opened it (form name is a good candidate).

In the search form load event, grab the openArgs property, and set a reference to the calling form via the forms collection.
Perform your search (criterias will depend on the form referenced since you now have access to its controls).
Call the property let procedure to write your search result back to the calling form.
1
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The Tempvar collection is also an option. Your first form can set a value, and other forms can reference it:

Tempvar.Add "NameOfYourTempVar", ValueOfYourTempVar

To retrieve it:

Tempvar!NameOfYourTempVar
0
 
Dale FyeCommented:
another technique I use is to open the search form using the acDialog windowmode argument.  This causes the remaining code in the form that calls that form to pause until frm_Main gets closed (or hidden).  I then take advantage of the fact that the search form, once hidden, returns program flow to the calling form, so it would look something like:

Private Sub cmd_Search_Click

    docmd.OpenForm "frm_Main", acNormal, , , , acDialog    
    'use of acDialog in the previous line stops this code from proceeding until the called form (frm_Main) is closed or hidden.
    set frm = forms("frm_Main")
    if frm.Tag = "Continue" Then
        'insert code here to capture values from frm_Main which you need to search or filter in the calling form
    end if
    docmd.close acform, "frm_Main"

End Sub

Open in new window

With this technique, I would use whatever buttons you have on frm_Main to set the value of the Tag property of that form to either Cancel, or Continue; then hide the form, something like:
Private Sub cmd_Continue_Click

    me.Tag = "Continue"
    me.visible = false

end if

Open in new window

0
 
WSStudentAuthor Commented:
The suggestion all have given i'm working on that but i got one error in my code in implementing this which i am not able to understand, any help?

Error 2447: There is an invalid use of the .(dot) or ! operator or invalid parentheses

Private Sub Command193_Click()

If Me.txtAddRec = "Cap" Then
Forms!frmAddRecord!Source = Me.txtCap
DoCmd.Close
ElseIf Me.txtAddRec1 = "CapEdit" Then
Forms!frmEditRecord!Source = Me.txtCap1
DoCmd.Close


End If

End Sub

Open in new window

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Try Forms!frmAddRecord.Source
0
 
Dale FyeCommented:
re: your post? value?

What is Command193, and what form is it on?  where do the values for txtAddRec and txtAddRec1 come from?
0
 
WSStudentAuthor Commented:
@Scoot, not working.

@Dale, Command193 is a button, it is on the frmMain ( the second form that open from frm_tbl1,frm_tbl2,...) txtAddRec and txtAddRec1 are   textboxs in frmMain (these are the textboxes that are getting value from previous form,if it is Cap then it's the first form frm_tbl1 else if there is CapEdit it's the frm_tbl2).
0
 
WSStudentAuthor Commented:
Attach is a sample DB also. frmFirst is the first to open and then click Edit Record where Source field click that go to Search Source and then click on Add Source there the error is.

In Add Record it is working but in Edit Record is the one where ahead it gives error.
Wsm93--1-.accdb
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Your code works fine IF your form named "Table1" is open. If not, then your code does not work.

I'm not sure what you're trying to accomplish, so it's hard to give suggestions to fix this. Can you provide more details?
0
 
WSStudentAuthor Commented:
What i am trying to achieve in this is that when Add Record is click in First form which is named as "frmFirst" , it open Add Record form and then in field Source click on that text box and open the Search Source form name as "frmSourceSearch", there the user select some ID from subform click on Add Source and that ID value should pass to previous form Add Record field Source. (This is working as you can see in the sample DB).

Similarly i want to achieve for Edit Record.Click On First form which is named as "frmFirst" , it open eDIT Record form and then in field Source click on that text box and open the Search Source form name as "frmSourceSearch", there the user select some ID from subform click on Add Source and that ID value should pass to previous form Edit Record field Source. (This is where i am getting this error).

The overall logic behind this is that when the user click add or edit record their form open up either add or edit form and from there with Source field the same form open in both which is frmSourceSearch, user select the Source and then that value should pass back to previous form, if this frmSourceSearch is open then it should pass value to that if it is open from edit then it should pass value to that.

(frmFirst -> Click Add Record -> Table1(form) -> Click Source (field) -> frmSourceSearch (Open) -> Select ID (from Subform) -> Click Add Source -> Pass value back to Add Record form)

(frmFirst -> Click Edit Record -> Table1(form) -> Click Source (field) -> frmSourceSearch (Open) -> Select ID (from Subform) -> Click Add Source -> Pass value back to Edit Record form)
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Your Edit Form macro opens a form named "Table1_1", not "Table_1". Could you change that macro to open Table_1 instead of Table1_1?
0
 
WSStudentAuthor Commented:
That's the answer.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.