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

It is confusing as too much codes，that's true .I will upload codes as less as possible. Most of codes just to repoduce the environment，and I guess merely a function might not explain clearly。

Thanks for your advice，I will try to post concisely.

Answer 2

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

Opens the SignOut form.  All controls were bound to the Transactions table, so I thought that a new record would be added with: TransType, Qty, MasterNum, UserInitials, and Date.

Re the bolded part, you thought wrong. When you open a form in Add mode NOTHING is automatically populated. Even if you set default values, nothing is automatically populated until you start the record by entering data into a control. If you use the code in the On Load (it should be On Load, not On Open, sorry) event that I gave you:

If Not IsNull(Me.OpenArgs) Then

     Me.MasterNum = Me.OpenArgs

End if

That will "dirty" the record, creating a new record, but the user will have to enter values in the controls unless you do so programmatically. All the controls should be bound. I do similar things all the time. Now there are some things that you can default. For example date can have the Default property set to =Date(), TransType may also be defaults as it would be a sign out. And, depending on how you capture the user initials, you may be able to default that as well. 

I put a little demo of this on my One Drive: http://1drv.ms/1SmMmiX

Open frmParts, then hit the sign out button.

Aside to peiyezhu:

Not everything should be solved by throwing gobs of code at it. You also need to take into account that many of the people asking questions here are not coders and won't have a clue what all that code means.

Answer 3

Option Compare Database

Option Explicit

Const Cm2Twips = 566.93

'pass args directly by peiyezhu

Sub CreateTblParts()

Dim dbs As DAO.Database

Dim strSql As String

Set dbs = CurrentDb

dbs.Execute "DROP TABLE tblTransactions"

dbs.Execute "DROP TABLE tblParts"

dbs.Execute "CREATE TABLE tblParts (MastNumber TEXT PRIMARY KEY, Description TEXT,CurrentQuantity DOUBLE )"

strSql = "CREATE TABLE tblTransactions (TransId INTEGER PRIMARY KEY, MastNumber TEXT , Quantity DOUBLE,CONSTRAINT FKTransMasterNumber FOREIGN KEY (MastNumber) REFERENCES tblParts) "

dbs.Execute strSql

dbs.Close

End Sub

Sub InsertValues4tblParts()

Dim dbs As DAO.Database

Dim strSql As String

Set dbs = CurrentDb

strSql = "INSERT INTO tblParts(MastNumber,Description,CurrentQuantity) "

strSql = strSql & " VALUES ("

strSql = strSql & "'P0001',"

strSql = strSql & "'Part1',"

strSql = strSql & "100"

strSql = strSql & ")"

dbs.Execute strSql

strSql = "INSERT INTO tblParts(MastNumber,Description,CurrentQuantity) "

strSql = strSql & " VALUES ("

strSql = strSql & "'P0002',"

strSql = strSql & "'Part2',"

strSql = strSql & "300"

strSql = strSql & ")"

dbs.Execute strSql

End Sub

Sub InsertValues4tblTransactions()

Dim dbs As DAO.Database

Dim strSql As String

Set dbs = CurrentDb

strSql = "INSERT INTO tblTransactions(TransId, MastNumber, Quantity) "

strSql = strSql & " VALUES ("

strSql = strSql & "1,"

strSql = strSql & "'P0001',"

strSql = strSql & "100"

strSql = strSql & ")"

dbs.Execute strSql

strSql = "INSERT INTO tblTransactions(TransId,MastNumber,Quantity) "

strSql = strSql & " VALUES ("

strSql = strSql & "2,"

strSql = strSql & "'P0002',"

strSql = strSql & "300"

strSql = strSql & ")"

dbs.Execute strSql

End Sub

Sub CreateFrmParts()

Dim f As Form

Set f = Application.CreateForm

'Set f = Screen.ActiveForm

Dim lst As ListBox

Set lst = Application.CreateControl(f.Name, acListBox, acDetail, , , 1 * Cm2Twips, 0.5 * Cm2Twips, 3 * Cm2Twips, 0.5 * Cm2Twips)

'Set lst = Screen.ActiveControl

With lst

.Left = 0.1 * Cm2Twips

.Height = 5 * Cm2Twips

.Width = 8 * Cm2Twips

.RowSource = "SELECT MastNumber,Description,CurrentQuantity FROM tblParts"

.ColumnCount = 3

.ColumnHeads = True

.ColumnWidths = "2cm;2.6cm;0.6cm"

.Name = "lstMastNumber"

End With

Dim cmd As CommandButton

Set cmd = Application.CreateControl(f.Name, acCommandButton, acDetail, , , lst.Left + lst.Width + 1 * Cm2Twips, lst.Top, 2 * Cm2Twips, 1 * Cm2Twips)

cmd.Name = "cmdSignOut"

cmd.OnClick = "=fSignOut()"

cmd.Caption = "SignOut"

Dim strName As String

strName = f.Name

DoCmd.Save acForm, f.Name

DoCmd.Restore

DoCmd.Close acForm, f.Name

DoCmd.Rename "Parts", acForm, strName

DoCmd.OpenForm "Parts"

End Sub

Sub CreateFrmSignOut()

Dim f As Form

' DoCmd.Close acForm, "SignOut"

' DoCmd.DeleteObject acForm, "SignOut"

Set f = Application.CreateForm

f.OnLoad = "=fSignOutOnLoad()"

'txtPartNumber

Dim dblLeft, dblWidth, dblHeight, dblTop

Dim txt As TextBox

Dim lbl As Label

Set txt = Application.CreateControl(f.Name, acTextBox, acDetail, , , 3 * Cm2Twips, 0.5 * Cm2Twips, 2 * Cm2Twips, 0.5 * Cm2Twips)

txt.Name = "txtMastNumber"

dblLeft = txt.Left

dblWidth = txt.Width

dblHeight = txt.Height

dblTop = txt.Top

Set lbl = Application.CreateControl(f.Name, acLabel, acDetail, txt.Name, , dblLeft - dblWidth - 0.1 * Cm2Twips, dblTop, dblWidth, dblHeight)

lbl.Name = "lblMastNumber"

lbl.Caption = "MastNumber"

'txtDate

dblLeft = txt.Left

dblWidth = txt.Width

dblHeight = txt.Height

dblTop = txt.Top + txt.Height + 0.1 * Cm2Twips

Set txt = Application.CreateControl(f.Name, acTextBox, acDetail, , , dblLeft, dblTop, dblWidth, dblHeight)

txt.Name = "txtDate"

dblLeft = dblLeft - dblWidth - 0.1 * Cm2Twips

dblWidth = txt.Width

dblHeight = txt.Height

dblTop = txt.Top

Set lbl = Application.CreateControl(f.Name, acLabel, acDetail, txt.Name, , dblLeft, dblTop, dblWidth, dblHeight)

lbl.Name = "lblTransDate"

lbl.Caption = "TransDate"

'quantity

dblLeft = txt.Left

dblWidth = txt.Width

dblHeight = txt.Height

dblTop = txt.Top + txt.Height + 0.1 * Cm2Twips

Set txt = Application.CreateControl(f.Name, acTextBox, acDetail, , , dblLeft, dblTop, dblWidth, dblHeight)

txt.Name = "txtQantity"

dblLeft = dblLeft - dblWidth - 0.1 * Cm2Twips

dblWidth = txt.Width

dblHeight = txt.Height

dblTop = txt.Top

Set lbl = Application.CreateControl(f.Name, acLabel, acDetail, txt.Name, , dblLeft, dblTop, dblWidth, dblHeight)

lbl.Name = "lblQantity"

lbl.Caption = "Qantity"

Dim cmd As CommandButton

Set cmd = Application.CreateControl(f.Name, acCommandButton, acDetail, , , txt.Left + txt.Width + 1 * Cm2Twips, txt.Top, 2 * Cm2Twips, 1 * Cm2Twips)

cmd.Name = "cmdOK"

'cmd.OnClick = "=fSignOutOK()" 'need further development

cmd.Caption = "OK"

Dim strName As String

strName = f.Name

DoCmd.Save acForm, f.Name

DoCmd.Restore

DoCmd.Close acForm, f.Name

DoCmd.Rename "SignOut", acForm, strName

DoCmd.OpenForm "SignOut"

End Sub

Function fSignOut()

If Nz(Forms("Parts").Controls("lstMastNumber")) = "" Then

MsgBox "Select a MastNumber First!"

Else

DoCmd.OpenForm "SignOut"

End If

End Function

Function fSignOutOnLoad()

With Forms("SignOut")

.Controls("txtMastNumber") = Forms("Parts").Controls("lstMastNumber") 'pass args directly

.Controls("txtDate") = Format(Now, "dd-mmm-yyyy")

.Controls("txtQantity").SetFocus

End With

End Function

Answer 4

I did mention earlier that I could not get the OpenArgs with OnOpen event to work.

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

Opens the Parts Display form, all necessary data is there.  All controls on this form are bound. 

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

Opens the SignOut form.  All controls were bound to the Transactions table, so I thought that a new record would be added with: TransType, Qty, MasterNum, UserInitials, and Date.

I unbound the MasterNum textbox, but the code still does not work as you described.  There are no filters for the three forms. 

Not sure what else could interfere with this.  The Main form (search form) is housed in a navigation form, but that should not interfere with the event code for the forms.