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

How would I open the form to a new record if I don't use the docmd.openform?

Answer 2

Ok, I ditched the Column(0). 

Before reading your post, found out how to use a global var to store the MasterNum, and call a function in the Control Source of frmSignOut to get the MasterNum.

Q1: How does the PartsDisplay form know what data to populate into the rest of the fields?  The MasterNum is given from the Main form, but I am not sure how it gets the other data from the Parts table.

Q2: When I go to sign out a part, how do I update a calculated field on Parts table (CurrentAmount), and capture the date, initials and TransType of Restock/Removal?  Based on the TransType, the Parts.CurrentAmount needs to increase or decrease, and the other data (date, initials...) needs to be recorded on another table, which I believe is the Transactions table, if I understood one of your earlier posts on another thread.

Answer 3

First get rid of the .Column(0). It is totally superfluous ans that is the default. 

Second, your code is opening the form FILTERED for selected Masternum, but if I'm understanding you, you are ADDING a new record with this transaction. So that's not what you want to do. 

Third, the Form Load event code doesn't work because Masternum has no value at the time the form loads. 

You have two options:

1. Set the Default value of the foreign key control to read from the calling form:

=Forms!formname!MasterNum

2. Use the OpenArgs argument.

DoCmd.OpenForm "F1_Signout",,,,,,Me.Masternum

then in the ON Open event:

If Me.NewRecord Then

     Me.Masternum = Me,OpenArgs

End If

Answer 4

Not sure why this doesn't work:

 Private Sub Form_Load()

    TempVars!tmpMaster = Me.MasterNum.Value

    MsgBox tmpMaster

    Me.txtFocus.SetFocus

End Sub

This form is populated from another form, using the

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

This data comes from a list box.

The tempvar does not get its value.

Answer 5

Hi Mike,

better of variables you have TempVars that you can reference  in form, report and query.

Mimmo