Passing values through multiple forms

Question

Hi all,

I am not sure if this is the most efficient way to do this, but:

I have an inventory program, and one of the functions is the ability of employees to "sign out" an item.  After finding the part, they click on it's description in a list box, which opens frmPartDisplay to display more detailed info and offers the choice to "Sign Out" or "Cancel".  This form is opened using

 DoCmd.OpenForm "F1_PartsDisplay", , , "[MasterNum] = " & listItems.Column(0). 

When the user clicks the "Sign Out" button, I would like another form frmSignOut to open, which contains a transaction type, restock or removal, the quantity they are adjusting, the Master number, which is unique to each Part, a text box for the user's initials, and the date they sign it out.

Once the user clicks the Sign Out button, this info is going to be captured to a Transaction tbl and update the quantity on the Parts tbl.

I can get the form to open, but the Master number is not being passed to frmSignOut, using  

DoCmd.OpenForm "F1_SignOut", , , "[MasterNum_FK] = " & Me.MasterNum

I've tried using a variable to store the data from the original form, frmMain, which opens the frmPartDisplay, but that variable loses it's value, even though it's a module variable.

Thanks,

Mike

Answer 1

Your reasoning ignores the work flow of the application and makes assumptions that may not be valid.

First, the work flow is that the user selects a part, then reviews that selection in another form. Only then does the user press a sign out button to open the Sign Out form. Therefore, by this time the user will be wanting to create the Sign Out record, so the idea of giving them a further chance to back out doesn't appear necessary. Plus, it may be confusing to the user to open the form and not see the part # in place. Second, this work flow makes it unlikely that this form would be opened on its own. The check for a Null OpenArgs, accommodates that. If the OP wants he can include a Cancel option.

My preference is to use the OpenArgs. I don't consider using a form reference as bad programming because the developer will know the app's work flow and can determine whether he might need to open the form from somewhere else.

I don't like the idea of using a Tempvar or global variable because it adds another level that is superfluous. But I wouldn't consider it "bad programming".

Answer 2

Hello Scott,

If you need to bark at someone, you can bark at me.  I gave him the BeforeInsert event following opening his form using the OpenArgs parameter of the OpenForm method. 

I would have to say that I disagree with your instruction on using the Open event to set the part id (Master Num).  I say this because it automatically begins a new record when the user may decide not to add a record after all. 

The advantage of using the BeforeInsert event is that it can be canceled if for some reason the OpenArg is missing or deemed invalid.  The procedure I gave him was simple and doesn't provide additional coding for checking that, but the option is available.

As for setting the value based on a reference to the calling form, I consider that bad programming.  The developer has the option of deciding to open the inventory form from more than one place, the list or the detail form.  Setting the form's part id value to reference a specific form limits other options.

As for using a global variable or "TempVars" to manage his objective, it is in my opinion, yet another bad programming practice.

So feel free to bark at me all you want if you feel the need.  I'm a big boy, I can handle it...

I think I'll take a step back now and let some of the other chefs in the kitchen handle this...  It's getting crowed in here.

Answer 3

It might help if you followed the instructions exactly. I don't know where you got

Private Sub Form_BeforeInsert(Cancel As Integer)

    Me.MasterNum = Me.OpenArgs

End Sub

What I said was to use the ON Open event. Go back and review what I said.

The process you described makes sense. User select a part. A form opens indicating where the part is located and what it looks like, user then presses a button to sign out the part. This opens a form where the Part ID (Masternum) is filled in. There are three ways to do that. One is setting a global variable and assigning that to the control, two is passing the value using the OpenArgs, three is setting the default value on the signout form to the control on the calling form. All three methods were described to you.

As for updating the Current Amount, you don't. You don't store the current amount, you CALCULATE it in a query that starts with the last physical inventory amount and adds/subtracts the transactions since the last physical inventory.  

And I'm not sure what you mean by filling in other info. You have your transactions table to detail each transaction.

Answer 4

Could not get it to work yet, using the docmd code you suggested.

PartsDisplay

Private Sub cmdSignout_Click()

'    DoCmd.OpenForm "F1_SignOut" ', , , , , , Me.MasterNum

    DoCmd.OpenForm "F1_SignOut", , , "[MasterNum] = " & Me.MasterNum.Value, acFormAdd, , Me.MasterNum 'after third comma is where I add the [MasterNum] =

        With Forms!F1_SignOut

            .MasterNum.Enabled = False

            .MasterNum.Locked = True

        End With

    'cannot close this form with the code below, it will not open the SignOut form...

    'DoCmd.Close

End Sub

SignOut

Private Sub Form_BeforeInsert(Cancel As Integer)

    Me.MasterNum = Me.OpenArgs

End Sub

Private Sub Form_Close()

    Forms!NavForm.Visible = True

End Sub

Private Sub Form_Load()

    Dim strInitials As String

    Dim dateSignout As Date

    Dim strTransType As String

    Dim intQuantity As Integer

    'DoCmd.RunMacro "SignOutFindNext"

    DoCmd.Close acForm, "F1_PartsDisplay"

    Forms!NavForm.Visible = False

End Sub

Answer 5

Here is the code that executes when the User clicks on the Part in the list box:

Private Sub listItems_Click()

    Dim rowNum As Integer

    Dim varMaster As Integer, varItem, varPartNum

    Dim Response As Integer

    rowNum = Me.listItems.ListIndex + 1

    GBL_intMasterPass = (listItems.Column(0, rowNum))

    DoCmd.OpenForm "F1_PartsDisplay", , , "[MasterNum] = " & listItems

        With Forms![F1_PartsDisplay]

            .MasterNum.Enabled = False

            .MasterNum.Locked = True

        End With

End Sub

The DoCmd is similar to what you have, but the PartID(MasterNum) is not available on the Main form, unless I reference a value in the listbox.