Pass two values from two sources to new form (1 Viewer)

[Zydeceltico]

Hi All,

We make final products out of component parts.

I have a top level form for Inspections (frmInspectionEvent). One of the clickable buttons on that form is named "Mill Inspection."

Assuming the user has chosen a job number on the main inspection form, the code for cmdOpenMillInspection_Click() is:

Private Sub cmdOpenMillInspection_Click()
  Me.Dirty = False
  If Not IsNull(Me.InspectionEvent_PK) Then
    DoCmd.OpenForm "dsFinalProductComponents", , , , , acFormDS, Me.InspectionEvent_PK
    DoCmd.OpenForm "frmInspectMill", , , , , acDialog, Me.InspectionEvent_PK
    Me.Requery
  Else
    MsgBox "No Job Chosen. Parent Record Not Saved"
  End If
End Sub

After clicking the button, form dsFinalProductComponents opens and let's me scroll through the various components that make the product named on the main inspection form. This part works great.

One of the fields on dsFinalProductComponents is txtPart_ID which is a numerical pointer back to a parts table key.

I need to pass this value to cboPartType on frmInspectMill via a clickable form-closing button named "Select."

cboPartType could easily be a a simple text field if that helps.

It does not need to be a cbo. Right now cboPartType is based on a qryFinalProductsComponents which returns 7 fields of data. Column 2 is PartType (the actual part name) and Column 6 has Part_ID (the key).

I know I could this with OpenArgs - BUT - - - -

frmInspectMill already uses a different OpenArgs on its Load event. Here's that code:

Private Sub Form_Load()
  Dim rs As DAO.Recordset
  If Not Trim(Me.OpenArgs & " ") = "" Then
    'See if record exists
    Set rs = Me.Recordset
    'MsgBox Me.OpenArgs
    rs.FindFirst "InspectionEvent_FK = " & CLng(Me.OpenArgs)
    If rs.NoMatch Then  'it does not exist so you need to create it
      DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
      Me.InspectionEvent_FK = Me.OpenArgs
    End If
  End If
End Sub

So I don't know how to pass the value from the previous form via OpenArgs and have a different OpenArgs happening at the same time when frmInspectMill is opened.

If you do play with the forms, you'll need to use the Undo buttons to easily back out.

Thanks in advance for all of your help.

Tim

 

[MajP]

I did not look at your specific case in detail, but in general you can pass multiple arguments separated by some delimiter. I use a semicolon. Then break the string up into its parts in the load event of the called form.

I pass in my open args with a semicolon between them. Then in the called form split the string up. In this case an ID, sort string, and filter string. Something like

"12; ProductName; ProductCategory = 'SomeProductCategory'"

Private sub Form_load(0)
  Dim Args() As String
  Dim rs As DAO.Recordset
  Dim ctl As Access.Control
  If Not Trim(Me.OpenArgs & " ") = "" Then
    Args = Split(Me.OpenArgs, ";") 'breaks into three items numbered 0,1,2
    SortForm Args(1) 'A function to sort
    If Args(2) <> "" Then FilterForm Args(2)
    Set rs = Me.Recordset
    rs.FindFirst "ProductID = '" & Args(0) & "'"
  End If
end sub

Not sure if that is what you are asking.

 

[Zydeceltico]

I did not look at your specific case in detail, but in general you can pass multiple arguments separated by some delimiter. I use a semicolon. Then break the string up into its parts in the load event of the called form.
Not sure if that is what you are asking.

Thanks. No - it's not exactly. My challenge is that I need to pass a different value from two different forms to a third form.

Form 1 is always open and an ID is created for that instance that I want to pass to Form 3.

I click a button on Form 1 and two DoCmd.OpenForm lines are called at the same time.

Form 2 has another piece of data that I have to choose to pass to Form 3 after selecting on Form 2.

 

[MajP]

Since you are moving from form to form there is no way any of the forms are modal or open acdialog. So you do not need to pass anything because any form can control another form. The only time you need openargs is if you open the form ACDIALOG and stop code execution in the calling form.

So if form 2 wants to set something in form 3 it would simply call some code
forms("form3").someproperty = something

 

[Zydeceltico]

Since you are moving from form to form there is no way any of the forms are modal or open acdialog.

I changed some of the workflow and code around to make the process of having all of the forms open at the same time make more sense - because they were modal before and the final as acDialog.

So now, Form 1 is opened, a button clicked to open Form 2 and the ID is passed from Form 1 to Form 2.

Form 2 has a text control that needs to be populated from a text field that exists on Form 3 (which is based on a query that returns multiple records which I move through using the record selectors at the bottom of the form as Form 3 only shows one record at a time instead of as a spreadsheet).

What I would like to do is open Form 3 from Form 2; locate the correct record and click a button that takes the value from a specific text field on Form 3, passes it to the correct text field on Form 2 and closes Form 3.

Is this possible? I don't see how to pass OpenArgs when closing a form rather than opening one.

Or is there a way to reference and pass the value I need before simply closing Form 3?

 

[Zydeceltico]

I think I found my solution - or at least A solution and I don't know why I didn't think of it before.

I got rid of Form 3 altogether and based the text field I need to populate on a query that reaches way back upstream.

I would still like to know if it is possible to pass a value from one form to another when closing a form.

 

[MajP]

I would still like to know if it is possible to pass a value from one form to another when closing a form.

Yes there are several ways to do this; however, to do it properly and maintain flexibility you normally do not want to "hardwire" the closing form to pass something back to a single form because then you are stuck only be able to do one thing. You either want to tell the call form which form called it so that when it closes it knows what to pass back to which form called it. Or the way I like to do it is to have the calling form do all the work and pulls the value from the called form without the called form knowing anything about being called. I described that in an early thread you had
https://www.access-programmers.co.uk/forums/showthread.php?t=301508&highlight=Popup

There are some other approaches provided. However, if this is truly a one use concept (you will always pass the value when this form closes) then you could simply hard code it to pass the value to the other form when it closes. At a minimum check to ensure the other form is opening before you try to pass something to the other form.

 

[The_Doc_Man]

At this point, you have a couple of different ways to pass data around. Your original model involving 3 forms betrays a certain mindset in designing. (I am NOT saying it is a good or bad mindset - not passing judgment at ALL.) You like to divide and conquer your work by making each form narrowly constructed for a limited purpose. As it happens, I do that frequently, though sometimes I have been known to make a multi-threat form.

You might consider one of these options if you are going to design this kind of data sharing among forms. Some have been noted in other posts of this thread.

1. OpenArgs works for passing data from one form to another in a given sequence. The args must be passed as text. Since only one form will be able to open a particular instantiation of another form, OpenArgs is a single-source, single-destination method and if you want more data than that, you have to do things like high-school relay runners to "pass the baton" along the chain. The OpenArgs argument is memory resident and visible only between the calling and called forms. When the called form closes, the argument is local to that form and vanishes with it.

2. You can build a temporary table in the front end as your argument passage support method. You wouldn't put it in the back end if the data applied to the user of the FE. Just be sure to erase the table when done with it. It would be up to you as to what was stored there and how you would access it, but a DLookup would work and an in-line SQL string to erase obsolete values would be fairly easy. Could even be a one-liner in VBA. Note that this is a PERSISTENT method. The table won't be cleared by an exit or by closure of a form. If you moved the table to the back end, it is the only way that data from user X on form A could be visible to user Y on form B.

3. Again, as long as we are talking about data restricted to the user of the FE, you can pass data strictly in memory. You would need a General Module to act as a repository for the variables. If you already have a general module for support routines, you would just put the variables as PUBLIC in the declaration area of the general module. This kind of variable lasts for the life of the session but persists after closure of the receiving form.

4. There is a method to create something called a TempVar that you can look up as an Access VBA feature. You can store things in TempVars that can be accessible from anything in the FE file. Like public variables in a module, TempVars reside in memory for the life of the current session and are definitely front end features. TempVars persist until you clean them up or the session exits.

5. You can create a dictionary entry that uses a keyword to look up a single value that is associated with the keyword. Look up "Create a dictionary" to read about this. If doing a web search rather than a forum search, that would be "MS Access dictionary." A dictionary is also a memory-resident front end feature that lasts for the life of the session unless deleted.

6. IF the issue of the passed argument is that it will be used for filtration, you MIGHT also consider opening the form with an explicit filter rather than passing a value in some other way. This method only applies to values to be used for filtration of the form's .Recordsource query.

7. IF AND ONLY IF a data-source form is open, another form can simply reach into the form by going through the Forms collection to get the value from a control on that form.

There might be other, sneakier ways to share data among objects but these are the ones I can remember that are related to forms.

 

[Zydeceltico]

At this point, you have a couple of different ways to pass data around. Your original model involving 3 forms betrays a certain mindset in designing. (I am NOT saying it is a good or bad mindset - not passing judgment at ALL.) You like to divide and conquer your work by making each form narrowly constructed for a limited purpose. As it happens, I do that frequently, though sometimes I have been known to make a multi-threat form.

Thanks! Much useful information - and very clear. My knowledge-base just grew a lot. Much appreciated.

Initially I was going to go with one Main "super" form with a tab control. All of the inspection forms would have fit in the tabs with out issue - it would have also likely made data passing easier.

I didn't for two main reason. Firstly, I have at least one instance where one of the forms existing on a tab really had to link outside the tab control to a secondary form for more specific data entry. I have been struggling with passing data as it is and I, for the life of me, have not been able to figure out how to navigate and determine how to call from the tabbed form to a field on a form that exists as a popup outside of the tabbed control and main form.

Secondly, every time I post the actual DB, I am told that I haven't normalized enough . So I took some advice I heard somewhere on here and normalized the entire thing "until it hurt" then I denormalized it to the point where it works - really well for what we need at work. Or at least I know it is going to when it's done - which is a lot closer today than it was several months ago.

In the interim, I have learned a ton about using DoCmd.OpenForm and OpenArgs and also about using queries and datasheets. Still only beginning to "see the light" but definitely have increased my knowledge and skill base there. Thank you and everybody for that.