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

Q1:DLOOKUP(“QtyInStock”,“tblParts”,“MastNumber=” & Me.MastNum

To get Other Field In tblparts

Q2:

use Query To Append Transaction Table，liKe:

Insert into Transaction (Qty) select forms!frm!Quantity

Docmd.Runsql QryName

Answer 2

You probably should then have an inventory table.

Scott recommended to me that the quantities for CurrentAmount (calculated) and StockTake (last physical count) should be on the Parts tbl in another thread.

Here is the relationships table.

I have a Transactions table that records each addition or subtraction. 

The Main form is a nav form that contains the Search form, which allows the user to search and select a part.  After clicking on the part, the PartsDetail form opens so the user can see where the part is located and a picture.

Then, if satisfied, the user clicks a button which opens a third form (SignOut) where they indicate quantity, enter their initials and the date is shown (now set to =Date() ).  (Thanks!)

Here is the simplified process.

Here, the Main form allows the User to search tblParts.ID, tblParts.Desc, tblSupplierPartNum.PartNumber.  The PartsDetail form (shown as Display Selected Part) shows more detailed info from other tables, warehouse, location, quantity...

Are you suggesting that using the docmd.open form with those args is better than using a global or tempvar?  if so, why?

Answer 3

 Private Sub Form_Load()

    TempVars!tmpMaster = Me.MasterNum.Value

     MsgBox tempvars(”tmpMaster”)

   

    Me.txtFocus.SetFocus

End Sub

try this.

Answer 4

It sounds like your entire design needs some rethinking, from your tables on up.  However, as stated previously here, you don't have any need for global variables.  When you have a record that relates to another, you simply supply the referencing foreign key id to pull up the related record, or begin a new one. 

So in your circumstance, you have a parts table.  You probably should then have an inventory table.  The parts table will store only information about each part including an identification value (preferably numeric).  The identification value should be unique and will represent the part record.

Your inventory table will store information specifically about inventory only and will include at least one foreign key field relating to the parts table.  You need no other information about the part record in your inventory table.  Just as your SSN tells pretty much everything about you, so will the id that you place in the inventory table tell everything about the part.  Your inventory table should also include a date, and a quantity field, which can be either a positive or negative value.  If the quantity is positive, that is the number of the particular part added to your inventory.  If it is negative, then that is the number of the particular part removed from you inventory.  Each time inventory is added or deducted, a new record will be created for the part in question.  To determine what your current inventory is, you will sum the quantity field for each part listed.  The result will be the current quantity available in your inventory.  You will probably want another foreign key field for the agent updating the inventory.

So for your forms, you will most likely have a form that displays a list of available parts.  In that form you will likely want to open a form for updating the inventory.  So in the list form, you will have a command:

DoCmd.OpenForm "InventoryUpdate_frm",,,,acFormAdd,,Me.PartID

This will open your inventory form to a new record and will pass to the form, the PartID via the OpenArgs parameter of the OpenForm method.  Once the form is open, you will begin adding a new record by typing in the quantity value.  Your date field should be set to the default value of Date() or Now().  When the new record is started, the value will automatically be set.  In the form's BeforeInsert event, you will have a procedure to add the part id from the form's open arguments to the record's part id field.

Private Sub Form_BeforeInsert(Cancel As Integer)

    Me.PartID = Me.OpenArgs

End Sub

So after you have entered the quantity, positive or negative, and the agent, the rest of the data has been entered for you and you're ready to save the record and close the form.

To add a bit more to this, I see that you want to go through a detail view of the part before updating the inventory.  So from your parts list form you would have a command to open the detail form as below:

DoCmd.OpenForm "PartDetail_frm",,,"PartID=" & Me.PartID

This will open the part form to the particular part record.

From there you can open your inventory form as shown above.

This is pretty basic, but it should be enough to get you moving in the right direction.  I hope this will be helpful for you.  Good luck!

Answer 5

So I am using a global as the default value for the MasterNumID_FK field, which needs (?) to be bound to the Transactions table so that record is added.

I tried using the OpenArgs but found that it did not do anything.

Here is a pic of the form:

The form's DataEntry = yes.

Now, it's using the TransType to update the Parts.currentAmount.