If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Move feild entries from form to form using global variables
I have a form that enters products into a Bill of Materials (description and
cost). The input form allows the user to type in a product manually or click a "Add From List" cmd button. On click, another form opens that has a list box and 2 text boxes. When the user clicks an item in the list box (OnClick event) the description and cost are loaded into a global variable and then appear in the corresponding text boxes. Once the user is certain they have selected the correct product, they click a cmd button that closes the list box form and "SHOULD" load the global variables into a new record set of the Bill of Materials form. The problem is I can't work out the code to transfer the variables. My path of thinking is to set the current record of the Bill of Materials to New in the OnClick event just prior to opening the list box form. Then the OnClick event for the cmd button on the list box form should load the record set before closing itstlsef. Yes? No? How? |
#2
|
|||
|
|||
Move feild entries from form to form using global variables
You don't need the global variable. Just load them directly into the text
boxes. The new form can refer to the text boxes using the DefaultValue property: =Forms!MyFirstFormName!TextboxName You can also use a special global variable that Access maintains, called OpenArgs which stores a string variable that can be passed as the 7th argument of the OpenForm method. -- Arvin Meyer, MCP, MVP Microsoft Access Free Access downloads: http://www.datastrat.com http://www.mvps.org/access "JackCGW" wrote in message ... I have a form that enters products into a Bill of Materials (description and cost). The input form allows the user to type in a product manually or click a "Add From List" cmd button. On click, another form opens that has a list box and 2 text boxes. When the user clicks an item in the list box (OnClick event) the description and cost are loaded into a global variable and then appear in the corresponding text boxes. Once the user is certain they have selected the correct product, they click a cmd button that closes the list box form and "SHOULD" load the global variables into a new record set of the Bill of Materials form. The problem is I can't work out the code to transfer the variables. My path of thinking is to set the current record of the Bill of Materials to New in the OnClick event just prior to opening the list box form. Then the OnClick event for the cmd button on the list box form should load the record set before closing itstlsef. Yes? No? How? |
#3
|
|||
|
|||
Move feild entries from form to form using global variables
Arvin,
I can't use the OpenArgs because the Bill of Materials forms does not close when the list form is called. The =Forms!...referance is giving me trouble because the list box form closes OnClick when the user has selected the correct product. I just can't seem to wrap my head around the process of moving the information to the New Record set on a different form. I chose the Global Variable path to allow the user to change his selection and only after the correct selection has been made, the last item in the GVariables is used. I also thought I may want these variables again for another event, thus keeping this data "alive" after the list form closed seem like a good idea (at the time). Jack "Arvin Meyer [MVP]" wrote: You don't need the global variable. Just load them directly into the text boxes. The new form can refer to the text boxes using the DefaultValue property: =Forms!MyFirstFormName!TextboxName You can also use a special global variable that Access maintains, called OpenArgs which stores a string variable that can be passed as the 7th argument of the OpenForm method. -- Arvin Meyer, MCP, MVP Microsoft Access Free Access downloads: http://www.datastrat.com http://www.mvps.org/access "JackCGW" wrote in message ... I have a form that enters products into a Bill of Materials (description and cost). The input form allows the user to type in a product manually or click a "Add From List" cmd button. On click, another form opens that has a list box and 2 text boxes. When the user clicks an item in the list box (OnClick event) the description and cost are loaded into a global variable and then appear in the corresponding text boxes. Once the user is certain they have selected the correct product, they click a cmd button that closes the list box form and "SHOULD" load the global variables into a new record set of the Bill of Materials form. The problem is I can't work out the code to transfer the variables. My path of thinking is to set the current record of the Bill of Materials to New in the OnClick event just prior to opening the list box form. Then the OnClick event for the cmd button on the list box form should load the record set before closing itstlsef. Yes? No? How? |
#4
|
|||
|
|||
Move feild entries from form to form using global variables
Hi, Jack.
As per your request in your other thread, the easiest way to accomplish this task is to let the list box automate the process. I'll give you a simple example, but you'll need to replace the names in the example with your own. First, create a new query and paste the following in the SQL View pane: SELECT ID, ItemName, ItemDesc, Cost FROM tblItems ORDER BY ItemName; .. . . where ID is the primary key of the table listing the items, ItemName is the item, ItemDesc is the item's description, Cost is the item's cost, and tblItems is the name of the table. Save the query and name it qryItems. Open the form that contains the list box in Design View and open the Properties dialog window. Select the list box, and then select the Data tab, and then select the "Row Source" Property. Scroll down and select qryItems. Select the Format tab, and then select the "Column Count" Property. Type 4 in the text box. Select the "Column Widths" Property and type 0";1";0";0" in the text box. This way, only the item's name will be displayed. In the form's module in the list box's OnAfterUpdate( ) event, paste the following: Private Sub lstItems_AfterUpdate() On Error GoTo ErrHandler Me!txtItemDesc.Value = Me!lstItems.Column(2) Me!txtItemCost.Value = CCur(Me!lstItems.Column(3)) Exit Sub ErrHandler: MsgBox "Error in lstItems_AfterUpdate( ) in" & vbCrLf & _ Me.Name & " form." & vbCrLf & vbCrLf & _ "Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description Err.Clear End Sub .. . . where lstItems is the name of the list box, txtItemDesc is the text box displaying the description, and txtItemCost is the text box displaying the cost. In the close button's OnClick( ) event, paste the following: Private Sub CloseBtn_Click() On Error GoTo ErrHandler DoCmd.GoToRecord acDataForm, "frmBOM", acNewRec Forms("frmBOM")!txtItem.Value = Me!lstItems.Column(1) Forms("frmBOM")!txtDesc.Value = Me!lstItems.Column(2) Forms("frmBOM")!txtCost.Value = CCur(Me!lstItems.Column(3)) DoCmd.Close acForm, Me.Name Exit Sub ErrHandler: MsgBox "Error in CloseBtn_Click( ) in" & vbCrLf & _ Me.Name & " form." & vbCrLf & vbCrLf & _ "Error #" & Err.Number & vbCrLf & Err.Description Err.Clear End Sub .. . . where CloseBtn is the name of the button, frmBOM is the name of the BOM form, txtItem is the text box on the the BOM form displaying the item's name, txtDesc is the text box on the the BOM form displaying the item's description, and txtCost is the text box on the the BOM form displaying the item's cost. Save and compile the module. Return to the form in Design View and close the Properties dialog window, and then close this form. Open the BOM form and select the "Add From List" button to open the list box form. Select an item in the list box and you'll see the text boxes automatically populate with the correct values. Select the close button to return to the BOM form and you'll see a new record with the selected item, description, and cost. As you can see, there's no need for global variables. If these three values are needed elsewhere, then they can be retrieved from the text boxes on the BOM form. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. (Please remove ZERO_SPAM from my reply E-mail address so that a message will be forwarded to me.) - - - If my answer has helped you, please sign in and answer yes to the question "Did this post answer your question?" at the bottom of the message, which adds your question and the answers to the database of answers. Remember that questions answered the quickest are often from those who have a history of rewarding the contributors who have taken the time to answer questions correctly. "JackCGW" wrote: I have a form that enters products into a Bill of Materials (description and cost). The input form allows the user to type in a product manually or click a "Add From List" cmd button. On click, another form opens that has a list box and 2 text boxes. When the user clicks an item in the list box (OnClick event) the description and cost are loaded into a global variable and then appear in the corresponding text boxes. Once the user is certain they have selected the correct product, they click a cmd button that closes the list box form and "SHOULD" load the global variables into a new record set of the Bill of Materials form. The problem is I can't work out the code to transfer the variables. My path of thinking is to set the current record of the Bill of Materials to New in the OnClick event just prior to opening the list box form. Then the OnClick event for the cmd button on the list box form should load the record set before closing itstlsef. Yes? No? How? |
#5
|
|||
|
|||
Move feild entries from form to form using global variables
Well Gunny,
That worked; albeit I have no idea how...but that;s why I am a cabinet maker and you are a programmer. Now when I say worked I realy mean sort of worked. As a stand alone form the code works flawlessly. But if I use the form as a subform linked to the mother by ProjectID, I get an error that states: Error #2489 The object 'subformMaterialdetails' isn't open. How is that possible? Jack "'69 Camaro" wrote: Hi, Jack. As per your request in your other thread, the easiest way to accomplish this task is to let the list box automate the process. I'll give you a simple example, but you'll need to replace the names in the example with your own. First, create a new query and paste the following in the SQL View pane: SELECT ID, ItemName, ItemDesc, Cost FROM tblItems ORDER BY ItemName; . . . where ID is the primary key of the table listing the items, ItemName is the item, ItemDesc is the item's description, Cost is the item's cost, and tblItems is the name of the table. Save the query and name it qryItems. Open the form that contains the list box in Design View and open the Properties dialog window. Select the list box, and then select the Data tab, and then select the "Row Source" Property. Scroll down and select qryItems. Select the Format tab, and then select the "Column Count" Property. Type 4 in the text box. Select the "Column Widths" Property and type 0";1";0";0" in the text box. This way, only the item's name will be displayed. In the form's module in the list box's OnAfterUpdate( ) event, paste the following: Private Sub lstItems_AfterUpdate() On Error GoTo ErrHandler Me!txtItemDesc.Value = Me!lstItems.Column(2) Me!txtItemCost.Value = CCur(Me!lstItems.Column(3)) Exit Sub ErrHandler: MsgBox "Error in lstItems_AfterUpdate( ) in" & vbCrLf & _ Me.Name & " form." & vbCrLf & vbCrLf & _ "Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description Err.Clear End Sub . . . where lstItems is the name of the list box, txtItemDesc is the text box displaying the description, and txtItemCost is the text box displaying the cost. In the close button's OnClick( ) event, paste the following: Private Sub CloseBtn_Click() On Error GoTo ErrHandler DoCmd.GoToRecord acDataForm, "frmBOM", acNewRec Forms("frmBOM")!txtItem.Value = Me!lstItems.Column(1) Forms("frmBOM")!txtDesc.Value = Me!lstItems.Column(2) Forms("frmBOM")!txtCost.Value = CCur(Me!lstItems.Column(3)) DoCmd.Close acForm, Me.Name Exit Sub ErrHandler: MsgBox "Error in CloseBtn_Click( ) in" & vbCrLf & _ Me.Name & " form." & vbCrLf & vbCrLf & _ "Error #" & Err.Number & vbCrLf & Err.Description Err.Clear End Sub . . . where CloseBtn is the name of the button, frmBOM is the name of the BOM form, txtItem is the text box on the the BOM form displaying the item's name, txtDesc is the text box on the the BOM form displaying the item's description, and txtCost is the text box on the the BOM form displaying the item's cost. Save and compile the module. Return to the form in Design View and close the Properties dialog window, and then close this form. Open the BOM form and select the "Add From List" button to open the list box form. Select an item in the list box and you'll see the text boxes automatically populate with the correct values. Select the close button to return to the BOM form and you'll see a new record with the selected item, description, and cost. As you can see, there's no need for global variables. If these three values are needed elsewhere, then they can be retrieved from the text boxes on the BOM form. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. (Please remove ZERO_SPAM from my reply E-mail address so that a message will be forwarded to me.) - - - If my answer has helped you, please sign in and answer yes to the question "Did this post answer your question?" at the bottom of the message, which adds your question and the answers to the database of answers. Remember that questions answered the quickest are often from those who have a history of rewarding the contributors who have taken the time to answer questions correctly. "JackCGW" wrote: I have a form that enters products into a Bill of Materials (description and cost). The input form allows the user to type in a product manually or click a "Add From List" cmd button. On click, another form opens that has a list box and 2 text boxes. When the user clicks an item in the list box (OnClick event) the description and cost are loaded into a global variable and then appear in the corresponding text boxes. Once the user is certain they have selected the correct product, they click a cmd button that closes the list box form and "SHOULD" load the global variables into a new record set of the Bill of Materials form. The problem is I can't work out the code to transfer the variables. My path of thinking is to set the current record of the Bill of Materials to New in the OnClick event just prior to opening the list box form. Then the OnClick event for the cmd button on the list box form should load the record set before closing itstlsef. Yes? No? How? |
#6
|
|||
|
|||
Move feild entries from form to form using global variables
Hi, Jack.
As a stand alone form the code works flawlessly. But if I use the form as a subform I did mention that it's a simple example. Add subforms and you've got complexity that you need to add code for. The object 'subformMaterialdetails' isn't open. It isn't. It's now an object that the subform control is "holding" whenever the main form is open. You must refer to this subform control, not the form. Change these three lines of code: Forms("frmBOM")!txtItem.Value = Me!lstItems.Column(1) Forms("frmBOM")!txtDesc.Value = Me!lstItems.Column(2) Forms("frmBOM")!txtCost.Value = CCur(Me!lstItems.Column(3)) .. . . to the following (and watch out for word wrap): Forms("frmBOM").Controls("MyCtrl")!txtItem.Value = Me!lstItems.Column(1) Forms("frmBOM").Controls("MyCtrl")!txtDesc.Value = Me!lstItems.Column(2) Forms("frmBOM").Controls("MyCtrl")!txtCost.Value = CCur(Me!lstItems.Column(3)) .. . . where frmBOM is the name of the main form, MyCtrl is the name of the subform control (_not_ the subform's name), txtItem is the text box on the subform displaying the item's name, txtDesc is the text box on the subform displaying the item's description, and txtCost is the text box on the subform displaying the item's cost. Save and compile the module again. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. (Please remove ZERO_SPAM from my reply E-mail address so that a message will be forwarded to me.) - - - If my answer has helped you, please sign in and answer yes to the question "Did this post answer your question?" at the bottom of the message, which adds your question and the answers to the database of answers. Remember that questions answered the quickest are often from those who have a history of rewarding the contributors who have taken the time to answer questions correctly. "JackCGW" wrote: Well Gunny, That worked; albeit I have no idea how...but that;s why I am a cabinet maker and you are a programmer. Now when I say worked I realy mean sort of worked. As a stand alone form the code works flawlessly. But if I use the form as a subform linked to the mother by ProjectID, I get an error that states: Error #2489 The object 'subformMaterialdetails' isn't open. How is that possible? Jack "'69 Camaro" wrote: Hi, Jack. As per your request in your other thread, the easiest way to accomplish this task is to let the list box automate the process. I'll give you a simple example, but you'll need to replace the names in the example with your own. First, create a new query and paste the following in the SQL View pane: SELECT ID, ItemName, ItemDesc, Cost FROM tblItems ORDER BY ItemName; . . . where ID is the primary key of the table listing the items, ItemName is the item, ItemDesc is the item's description, Cost is the item's cost, and tblItems is the name of the table. Save the query and name it qryItems. Open the form that contains the list box in Design View and open the Properties dialog window. Select the list box, and then select the Data tab, and then select the "Row Source" Property. Scroll down and select qryItems. Select the Format tab, and then select the "Column Count" Property. Type 4 in the text box. Select the "Column Widths" Property and type 0";1";0";0" in the text box. This way, only the item's name will be displayed. In the form's module in the list box's OnAfterUpdate( ) event, paste the following: Private Sub lstItems_AfterUpdate() On Error GoTo ErrHandler Me!txtItemDesc.Value = Me!lstItems.Column(2) Me!txtItemCost.Value = CCur(Me!lstItems.Column(3)) Exit Sub ErrHandler: MsgBox "Error in lstItems_AfterUpdate( ) in" & vbCrLf & _ Me.Name & " form." & vbCrLf & vbCrLf & _ "Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description Err.Clear End Sub . . . where lstItems is the name of the list box, txtItemDesc is the text box displaying the description, and txtItemCost is the text box displaying the cost. In the close button's OnClick( ) event, paste the following: Private Sub CloseBtn_Click() On Error GoTo ErrHandler DoCmd.GoToRecord acDataForm, "frmBOM", acNewRec Forms("frmBOM")!txtItem.Value = Me!lstItems.Column(1) Forms("frmBOM")!txtDesc.Value = Me!lstItems.Column(2) Forms("frmBOM")!txtCost.Value = CCur(Me!lstItems.Column(3)) DoCmd.Close acForm, Me.Name Exit Sub ErrHandler: MsgBox "Error in CloseBtn_Click( ) in" & vbCrLf & _ Me.Name & " form." & vbCrLf & vbCrLf & _ "Error #" & Err.Number & vbCrLf & Err.Description Err.Clear End Sub . . . where CloseBtn is the name of the button, frmBOM is the name of the BOM form, txtItem is the text box on the the BOM form displaying the item's name, txtDesc is the text box on the the BOM form displaying the item's description, and txtCost is the text box on the the BOM form displaying the item's cost. Save and compile the module. Return to the form in Design View and close the Properties dialog window, and then close this form. Open the BOM form and select the "Add From List" button to open the list box form. Select an item in the list box and you'll see the text boxes automatically populate with the correct values. Select the close button to return to the BOM form and you'll see a new record with the selected item, description, and cost. As you can see, there's no need for global variables. If these three values are needed elsewhere, then they can be retrieved from the text boxes on the BOM form. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. (Please remove ZERO_SPAM from my reply E-mail address so that a message will be forwarded to me.) - - - If my answer has helped you, please sign in and answer yes to the question "Did this post answer your question?" at the bottom of the message, which adds your question and the answers to the database of answers. Remember that questions answered the quickest are often from those who have a history of rewarding the contributors who have taken the time to answer questions correctly. "JackCGW" wrote: I have a form that enters products into a Bill of Materials (description and cost). The input form allows the user to type in a product manually or click a "Add From List" cmd button. On click, another form opens that has a list box and 2 text boxes. When the user clicks an item in the list box (OnClick event) the description and cost are loaded into a global variable and then appear in the corresponding text boxes. Once the user is certain they have selected the correct product, they click a cmd button that closes the list box form and "SHOULD" load the global variables into a new record set of the Bill of Materials form. The problem is I can't work out the code to transfer the variables. My path of thinking is to set the current record of the Bill of Materials to New in the OnClick event just prior to opening the list box form. Then the OnClick event for the cmd button on the list box form should load the record set before closing itstlsef. Yes? No? How? |
#7
|
|||
|
|||
Move feild entries from form to form using global variables
Gunny,
I actually understand that...but...I can't seem to find the subforms control name. I have looked all through the help files but no luck(usually isn't). Jack "'69 Camaro" wrote: Hi, Jack. As a stand alone form the code works flawlessly. But if I use the form as a subform I did mention that it's a simple example. Add subforms and you've got complexity that you need to add code for. The object 'subformMaterialdetails' isn't open. It isn't. It's now an object that the subform control is "holding" whenever the main form is open. You must refer to this subform control, not the form. Change these three lines of code: Forms("frmBOM")!txtItem.Value = Me!lstItems.Column(1) Forms("frmBOM")!txtDesc.Value = Me!lstItems.Column(2) Forms("frmBOM")!txtCost.Value = CCur(Me!lstItems.Column(3)) . . . to the following (and watch out for word wrap): Forms("frmBOM").Controls("MyCtrl")!txtItem.Value = Me!lstItems.Column(1) Forms("frmBOM").Controls("MyCtrl")!txtDesc.Value = Me!lstItems.Column(2) Forms("frmBOM").Controls("MyCtrl")!txtCost.Value = CCur(Me!lstItems.Column(3)) . . . where frmBOM is the name of the main form, MyCtrl is the name of the subform control (_not_ the subform's name), txtItem is the text box on the subform displaying the item's name, txtDesc is the text box on the subform displaying the item's description, and txtCost is the text box on the subform displaying the item's cost. Save and compile the module again. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. (Please remove ZERO_SPAM from my reply E-mail address so that a message will be forwarded to me.) - - - If my answer has helped you, please sign in and answer yes to the question "Did this post answer your question?" at the bottom of the message, which adds your question and the answers to the database of answers. Remember that questions answered the quickest are often from those who have a history of rewarding the contributors who have taken the time to answer questions correctly. "JackCGW" wrote: Well Gunny, That worked; albeit I have no idea how...but that;s why I am a cabinet maker and you are a programmer. Now when I say worked I realy mean sort of worked. As a stand alone form the code works flawlessly. But if I use the form as a subform linked to the mother by ProjectID, I get an error that states: Error #2489 The object 'subformMaterialdetails' isn't open. How is that possible? Jack "'69 Camaro" wrote: Hi, Jack. As per your request in your other thread, the easiest way to accomplish this task is to let the list box automate the process. I'll give you a simple example, but you'll need to replace the names in the example with your own. First, create a new query and paste the following in the SQL View pane: SELECT ID, ItemName, ItemDesc, Cost FROM tblItems ORDER BY ItemName; . . . where ID is the primary key of the table listing the items, ItemName is the item, ItemDesc is the item's description, Cost is the item's cost, and tblItems is the name of the table. Save the query and name it qryItems. Open the form that contains the list box in Design View and open the Properties dialog window. Select the list box, and then select the Data tab, and then select the "Row Source" Property. Scroll down and select qryItems. Select the Format tab, and then select the "Column Count" Property. Type 4 in the text box. Select the "Column Widths" Property and type 0";1";0";0" in the text box. This way, only the item's name will be displayed. In the form's module in the list box's OnAfterUpdate( ) event, paste the following: Private Sub lstItems_AfterUpdate() On Error GoTo ErrHandler Me!txtItemDesc.Value = Me!lstItems.Column(2) Me!txtItemCost.Value = CCur(Me!lstItems.Column(3)) Exit Sub ErrHandler: MsgBox "Error in lstItems_AfterUpdate( ) in" & vbCrLf & _ Me.Name & " form." & vbCrLf & vbCrLf & _ "Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description Err.Clear End Sub . . . where lstItems is the name of the list box, txtItemDesc is the text box displaying the description, and txtItemCost is the text box displaying the cost. In the close button's OnClick( ) event, paste the following: Private Sub CloseBtn_Click() On Error GoTo ErrHandler DoCmd.GoToRecord acDataForm, "frmBOM", acNewRec Forms("frmBOM")!txtItem.Value = Me!lstItems.Column(1) Forms("frmBOM")!txtDesc.Value = Me!lstItems.Column(2) Forms("frmBOM")!txtCost.Value = CCur(Me!lstItems.Column(3)) DoCmd.Close acForm, Me.Name Exit Sub ErrHandler: MsgBox "Error in CloseBtn_Click( ) in" & vbCrLf & _ Me.Name & " form." & vbCrLf & vbCrLf & _ "Error #" & Err.Number & vbCrLf & Err.Description Err.Clear End Sub . . . where CloseBtn is the name of the button, frmBOM is the name of the BOM form, txtItem is the text box on the the BOM form displaying the item's name, txtDesc is the text box on the the BOM form displaying the item's description, and txtCost is the text box on the the BOM form displaying the item's cost. Save and compile the module. Return to the form in Design View and close the Properties dialog window, and then close this form. Open the BOM form and select the "Add From List" button to open the list box form. Select an item in the list box and you'll see the text boxes automatically populate with the correct values. Select the close button to return to the BOM form and you'll see a new record with the selected item, description, and cost. As you can see, there's no need for global variables. If these three values are needed elsewhere, then they can be retrieved from the text boxes on the BOM form. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. (Please remove ZERO_SPAM from my reply E-mail address so that a message will be forwarded to me.) - - - If my answer has helped you, please sign in and answer yes to the question "Did this post answer your question?" at the bottom of the message, which adds your question and the answers to the database of answers. Remember that questions answered the quickest are often from those who have a history of rewarding the contributors who have taken the time to answer questions correctly. "JackCGW" wrote: I have a form that enters products into a Bill of Materials (description and cost). The input form allows the user to type in a product manually or click a "Add From List" cmd button. On click, another form opens that has a list box and 2 text boxes. When the user clicks an item in the list box (OnClick event) the description and cost are loaded into a global variable and then appear in the corresponding text boxes. Once the user is certain they have selected the correct product, they click a cmd button that closes the list box form and "SHOULD" load the global variables into a new record set of the Bill of Materials form. The problem is I can't work out the code to transfer the variables. My path of thinking is to set the current record of the Bill of Materials to New in the OnClick event just prior to opening the list box form. Then the OnClick event for the cmd button on the list box form should load the record set before closing itstlsef. Yes? No? How? |
#8
|
|||
|
|||
Move feild entries from form to form using global variables
Hi, Jack.
Open the main form in Design View. Open the Properties dialog window by selecting the "Properties" button on the built-in toolbar. On the main form, select the subform control so that there are "boxes" around its perimeter, indicating "Selected Control." The Title Bar on the Properties dialog window changes to "Subform/Subreport: MyCtrlName." Select the "Other" tab. Check the Name Property, and it will match the same name as the one I've used as MyCtrlName as a placeholder in the Title Bar. Replace my names with your names in the following example (watch out for word wrap): Forms("frmBOM").Controls("MyCtrlName")!txtItem.Val ue = Me!lstItems.Column(1) Forms("frmBOM").Controls("MyCtrlName")!txtDesc.Val ue = Me!lstItems.Column(2) Forms("frmBOM").Controls("MyCtrlName")!txtCost.Val ue = CCur(Me!lstItems.Column(3)) .. . . where frmBOM is the name of the main form, MyCtrlName is the name of the subform control, txtItem is the text box on the subform displaying the item's name, txtDesc is the text box on the subform displaying the item's description, and txtCost is the text box on the subform displaying the item's cost. Save and compile the module again. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. (Please remove ZERO_SPAM from my reply E-mail address so that a message will be forwarded to me.) - - - If my answer has helped you, please sign in and answer yes to the question "Did this post answer your question?" at the bottom of the message, which adds your question and the answers to the database of answers. Remember that questions answered the quickest are often from those who have a history of rewarding the contributors who have taken the time to answer questions correctly. "JackCGW" wrote: Gunny, I actually understand that...but...I can't seem to find the subforms control name. I have looked all through the help files but no luck(usually isn't). Jack "'69 Camaro" wrote: Hi, Jack. As a stand alone form the code works flawlessly. But if I use the form as a subform I did mention that it's a simple example. Add subforms and you've got complexity that you need to add code for. The object 'subformMaterialdetails' isn't open. It isn't. It's now an object that the subform control is "holding" whenever the main form is open. You must refer to this subform control, not the form. Change these three lines of code: Forms("frmBOM")!txtItem.Value = Me!lstItems.Column(1) Forms("frmBOM")!txtDesc.Value = Me!lstItems.Column(2) Forms("frmBOM")!txtCost.Value = CCur(Me!lstItems.Column(3)) . . . to the following (and watch out for word wrap): Forms("frmBOM").Controls("MyCtrl")!txtItem.Value = Me!lstItems.Column(1) Forms("frmBOM").Controls("MyCtrl")!txtDesc.Value = Me!lstItems.Column(2) Forms("frmBOM").Controls("MyCtrl")!txtCost.Value = CCur(Me!lstItems.Column(3)) . . . where frmBOM is the name of the main form, MyCtrl is the name of the subform control (_not_ the subform's name), txtItem is the text box on the subform displaying the item's name, txtDesc is the text box on the subform displaying the item's description, and txtCost is the text box on the subform displaying the item's cost. Save and compile the module again. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. (Please remove ZERO_SPAM from my reply E-mail address so that a message will be forwarded to me.) - - - If my answer has helped you, please sign in and answer yes to the question "Did this post answer your question?" at the bottom of the message, which adds your question and the answers to the database of answers. Remember that questions answered the quickest are often from those who have a history of rewarding the contributors who have taken the time to answer questions correctly. "JackCGW" wrote: Well Gunny, That worked; albeit I have no idea how...but that;s why I am a cabinet maker and you are a programmer. Now when I say worked I realy mean sort of worked. As a stand alone form the code works flawlessly. But if I use the form as a subform linked to the mother by ProjectID, I get an error that states: Error #2489 The object 'subformMaterialdetails' isn't open. How is that possible? Jack "'69 Camaro" wrote: Hi, Jack. As per your request in your other thread, the easiest way to accomplish this task is to let the list box automate the process. I'll give you a simple example, but you'll need to replace the names in the example with your own. First, create a new query and paste the following in the SQL View pane: SELECT ID, ItemName, ItemDesc, Cost FROM tblItems ORDER BY ItemName; . . . where ID is the primary key of the table listing the items, ItemName is the item, ItemDesc is the item's description, Cost is the item's cost, and tblItems is the name of the table. Save the query and name it qryItems. Open the form that contains the list box in Design View and open the Properties dialog window. Select the list box, and then select the Data tab, and then select the "Row Source" Property. Scroll down and select qryItems. Select the Format tab, and then select the "Column Count" Property. Type 4 in the text box. Select the "Column Widths" Property and type 0";1";0";0" in the text box. This way, only the item's name will be displayed. In the form's module in the list box's OnAfterUpdate( ) event, paste the following: Private Sub lstItems_AfterUpdate() On Error GoTo ErrHandler Me!txtItemDesc.Value = Me!lstItems.Column(2) Me!txtItemCost.Value = CCur(Me!lstItems.Column(3)) Exit Sub ErrHandler: MsgBox "Error in lstItems_AfterUpdate( ) in" & vbCrLf & _ Me.Name & " form." & vbCrLf & vbCrLf & _ "Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description Err.Clear End Sub . . . where lstItems is the name of the list box, txtItemDesc is the text box displaying the description, and txtItemCost is the text box displaying the cost. In the close button's OnClick( ) event, paste the following: Private Sub CloseBtn_Click() On Error GoTo ErrHandler DoCmd.GoToRecord acDataForm, "frmBOM", acNewRec Forms("frmBOM")!txtItem.Value = Me!lstItems.Column(1) Forms("frmBOM")!txtDesc.Value = Me!lstItems.Column(2) Forms("frmBOM")!txtCost.Value = CCur(Me!lstItems.Column(3)) DoCmd.Close acForm, Me.Name Exit Sub ErrHandler: MsgBox "Error in CloseBtn_Click( ) in" & vbCrLf & _ Me.Name & " form." & vbCrLf & vbCrLf & _ "Error #" & Err.Number & vbCrLf & Err.Description Err.Clear End Sub . . . where CloseBtn is the name of the button, frmBOM is the name of the BOM form, txtItem is the text box on the the BOM form displaying the item's name, txtDesc is the text box on the the BOM form displaying the item's description, and txtCost is the text box on the the BOM form displaying the item's cost. Save and compile the module. Return to the form in Design View and close the Properties dialog window, and then close this form. Open the BOM form and select the "Add From List" button to open the list box form. Select an item in the list box and you'll see the text boxes automatically populate with the correct values. Select the close button to return to the BOM form and you'll see a new record with the selected item, description, and cost. As you can see, there's no need for global variables. If these three values are needed elsewhere, then they can be retrieved from the text boxes on the BOM form. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. (Please remove ZERO_SPAM from my reply E-mail address so that a message will be forwarded to me.) - - - If my answer has helped you, please sign in and answer yes to the question "Did this post answer your question?" at the bottom of the message, which adds your question and the answers to the database of answers. Remember that questions answered the quickest are often from those who have a history of rewarding the contributors who have taken the time to answer questions correctly. "JackCGW" wrote: I have a form that enters products into a Bill of Materials (description and cost). The input form allows the user to type in a product manually or click a "Add From List" cmd button. On click, another form opens that has a list box and 2 text boxes. When the user clicks an item in the list box (OnClick event) the description and cost are loaded into a global variable and then appear in the corresponding text boxes. Once the user is certain they have selected the correct product, they click a cmd button that closes the list box form and "SHOULD" load the global variables into a new record set of the Bill of Materials form. The problem is I can't work out the code to transfer the variables. My path of thinking is to set the current record of the Bill of Materials to New in the OnClick event just prior to opening the list box form. Then the OnClick event for the cmd button on the list box form should load the record set before closing itstlsef. Yes? No? How? |
#9
|
|||
|
|||
Move feild entries from form to form using global variables
Gunny,
I made the changes with high hopes...but...I was still getting the same error mesasge so I put a break point at the On Error line. Private Sub cmdAddSelectedItem_Click() On Error GoTo Err_cmdAddSelectedItem_Click DoCmd.GoToRecord acDataForm, "subformMaterialDetails", acNewRec Forms("EditProject").Controls("subformMaterialDeta ils")!MaterialDescription.Value = Me!lstMaterialAddSelect.Column(1) Forms("EditProject").Controls("subformMaterialDeta ils")!MaterialUnitPrice.Value = CCur(Me!lstMaterialAddSelect.Column(2)) Forms("EditProject").Controls("subformMaterialDeta ils")!MaterialSalesTax.Value = CCur(Me!lstMaterialAddSelect.Column(3)) DoCmd.Close acForm, Me.Name Exit Sub Err_cmdAddSelectedItem_Click: MsgBox "Error in cmdAddSelectedItem_Click() in" & vbCrLf & Me.Name & " form." & vbCrLf & vbCrLf & "Error #" & Err.Number & vbCrLf & Err.Description Err.Clear End Sub Now when I 'Step Into' the code, the error occurs at the DoCmd...AcNewRec line. I thought I could apply the same logic of calling to the control(subform) but I appearently don't know the proper "things" to type. Just to simplify these threads... The parent form is called EditProject The subform as a control is called subformMaterialDetails The lsitbox form is called MaterialAddSelectMiniform The cmdButton that gets all the clicking is on MaterialAddSelectMiniform and is called cmdAddSelectedItem Thanks for the help; We are almost there...I can feel it. Jack "'69 Camaro" wrote: Hi, Jack. Open the main form in Design View. Open the Properties dialog window by selecting the "Properties" button on the built-in toolbar. On the main form, select the subform control so that there are "boxes" around its perimeter, indicating "Selected Control." The Title Bar on the Properties dialog window changes to "Subform/Subreport: MyCtrlName." Select the "Other" tab. Check the Name Property, and it will match the same name as the one I've used as MyCtrlName as a placeholder in the Title Bar. Replace my names with your names in the following example (watch out for word wrap): Forms("frmBOM").Controls("MyCtrlName")!txtItem.Val ue = Me!lstItems.Column(1) Forms("frmBOM").Controls("MyCtrlName")!txtDesc.Val ue = Me!lstItems.Column(2) Forms("frmBOM").Controls("MyCtrlName")!txtCost.Val ue = CCur(Me!lstItems.Column(3)) . . . where frmBOM is the name of the main form, MyCtrlName is the name of the subform control, txtItem is the text box on the subform displaying the item's name, txtDesc is the text box on the subform displaying the item's description, and txtCost is the text box on the subform displaying the item's cost. Save and compile the module again. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. (Please remove ZERO_SPAM from my reply E-mail address so that a message will be forwarded to me.) - - - If my answer has helped you, please sign in and answer yes to the question "Did this post answer your question?" at the bottom of the message, which adds your question and the answers to the database of answers. Remember that questions answered the quickest are often from those who have a history of rewarding the contributors who have taken the time to answer questions correctly. "JackCGW" wrote: Gunny, I actually understand that...but...I can't seem to find the subforms control name. I have looked all through the help files but no luck(usually isn't). Jack "'69 Camaro" wrote: Hi, Jack. As a stand alone form the code works flawlessly. But if I use the form as a subform I did mention that it's a simple example. Add subforms and you've got complexity that you need to add code for. The object 'subformMaterialdetails' isn't open. It isn't. It's now an object that the subform control is "holding" whenever the main form is open. You must refer to this subform control, not the form. Change these three lines of code: Forms("frmBOM")!txtItem.Value = Me!lstItems.Column(1) Forms("frmBOM")!txtDesc.Value = Me!lstItems.Column(2) Forms("frmBOM")!txtCost.Value = CCur(Me!lstItems.Column(3)) . . . to the following (and watch out for word wrap): Forms("frmBOM").Controls("MyCtrl")!txtItem.Value = Me!lstItems.Column(1) Forms("frmBOM").Controls("MyCtrl")!txtDesc.Value = Me!lstItems.Column(2) Forms("frmBOM").Controls("MyCtrl")!txtCost.Value = CCur(Me!lstItems.Column(3)) . . . where frmBOM is the name of the main form, MyCtrl is the name of the subform control (_not_ the subform's name), txtItem is the text box on the subform displaying the item's name, txtDesc is the text box on the subform displaying the item's description, and txtCost is the text box on the subform displaying the item's cost. Save and compile the module again. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. (Please remove ZERO_SPAM from my reply E-mail address so that a message will be forwarded to me.) - - - If my answer has helped you, please sign in and answer yes to the question "Did this post answer your question?" at the bottom of the message, which adds your question and the answers to the database of answers. Remember that questions answered the quickest are often from those who have a history of rewarding the contributors who have taken the time to answer questions correctly. "JackCGW" wrote: Well Gunny, That worked; albeit I have no idea how...but that;s why I am a cabinet maker and you are a programmer. Now when I say worked I realy mean sort of worked. As a stand alone form the code works flawlessly. But if I use the form as a subform linked to the mother by ProjectID, I get an error that states: Error #2489 The object 'subformMaterialdetails' isn't open. How is that possible? Jack "'69 Camaro" wrote: Hi, Jack. As per your request in your other thread, the easiest way to accomplish this task is to let the list box automate the process. I'll give you a simple example, but you'll need to replace the names in the example with your own. First, create a new query and paste the following in the SQL View pane: SELECT ID, ItemName, ItemDesc, Cost FROM tblItems ORDER BY ItemName; . . . where ID is the primary key of the table listing the items, ItemName is the item, ItemDesc is the item's description, Cost is the item's cost, and tblItems is the name of the table. Save the query and name it qryItems. Open the form that contains the list box in Design View and open the Properties dialog window. Select the list box, and then select the Data tab, and then select the "Row Source" Property. Scroll down and select qryItems. Select the Format tab, and then select the "Column Count" Property. Type 4 in the text box. Select the "Column Widths" Property and type 0";1";0";0" in the text box. This way, only the item's name will be displayed. In the form's module in the list box's OnAfterUpdate( ) event, paste the following: Private Sub lstItems_AfterUpdate() On Error GoTo ErrHandler Me!txtItemDesc.Value = Me!lstItems.Column(2) Me!txtItemCost.Value = CCur(Me!lstItems.Column(3)) Exit Sub ErrHandler: MsgBox "Error in lstItems_AfterUpdate( ) in" & vbCrLf & _ Me.Name & " form." & vbCrLf & vbCrLf & _ "Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description Err.Clear End Sub . . . where lstItems is the name of the list box, txtItemDesc is the text box displaying the description, and txtItemCost is the text box displaying the cost. In the close button's OnClick( ) event, paste the following: Private Sub CloseBtn_Click() On Error GoTo ErrHandler DoCmd.GoToRecord acDataForm, "frmBOM", acNewRec Forms("frmBOM")!txtItem.Value = Me!lstItems.Column(1) Forms("frmBOM")!txtDesc.Value = Me!lstItems.Column(2) Forms("frmBOM")!txtCost.Value = CCur(Me!lstItems.Column(3)) DoCmd.Close acForm, Me.Name Exit Sub ErrHandler: MsgBox "Error in CloseBtn_Click( ) in" & vbCrLf & _ Me.Name & " form." & vbCrLf & vbCrLf & _ "Error #" & Err.Number & vbCrLf & Err.Description Err.Clear End Sub . . . where CloseBtn is the name of the button, frmBOM is the name of the BOM form, txtItem is the text box on the the BOM form displaying the item's name, txtDesc is the text box on the the BOM form displaying the item's description, and txtCost is the text box on the the BOM form displaying the item's cost. Save and compile the module. Return to the form in Design View and close the Properties dialog window, and then close this form. Open the BOM form and select the "Add From List" button to open the list box form. Select an item in the list box and you'll see the text boxes automatically populate with the correct values. Select the close button to return to the BOM form and you'll see a new record with the selected item, description, and cost. As you can see, there's no need for global variables. If these three values are needed elsewhere, then they can be retrieved from the text boxes on the BOM form. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. (Please remove ZERO_SPAM from my reply E-mail address so that a message will be forwarded to me.) - - - If my answer has helped you, please sign in and answer yes to the question "Did this post answer your question?" at the bottom of the message, which adds your question and the answers to the database of answers. Remember that questions answered the quickest are often from those who have a history of rewarding the contributors who have taken the time to answer questions correctly. "JackCGW" wrote: I have a form that enters products into a Bill of Materials (description and cost). The input form allows the user to type in a product manually or click a "Add From List" cmd button. On click, another form opens that has a list box and 2 text boxes. When the user clicks an item in the list box (OnClick event) the description and cost are loaded into a global variable and then appear in the corresponding text boxes. Once the user is certain they have selected the correct product, they click a cmd button that closes the list box form and "SHOULD" load the global variables into a new record set of the Bill of Materials form. The problem is I can't work out the code to transfer the variables. My path of thinking is to set the current record of the Bill of Materials to New in the OnClick event just prior to opening the list box form. Then the OnClick event for the cmd button on the list box form should load the record set before closing itstlsef. Yes? No? How? |
#10
|
|||
|
|||
Move feild entries from form to form using global variables
Hi, Jack.
DoCmd.GoToRecord acDataForm, "subformMaterialDetails", acNewRec I'm not pulling your leg. Whenever the main form is open, it _only_ has controls. There's no subform open as if it were a real form. GoToRecord must use the main form's name in this line of code, because it's _the_form_. Unless you have a main form that's configured to bypass Access 2003's bug with bound subforms, this command won't work for a subform control. My advice to you is to forget the subform idea and just apply the code to a regular form. It's possible to go in through the back door to insert a new record for the subform, but that requires more advanced coding skills and knowledge of the form structure and the underlying table structures. I can only guess on the structures, but I'll give you an example and let you decide whether you want to take the easier route by skipping the subform idea. Private Sub CloseBtn_Click() On Error GoTo ErrHandler CurrentDb().Execute "INSERT INTO tblMyTable (MaterialDescription, " & _ MaterialUnitPrice, MaterialSalesTax, ID) " & _ "VALUES ('" Me!lstMaterialAddSelect.Column(1) & "', " & _ CCur(Me!lstMaterialAddSelect.Column(2)) & ", " & _ CCur(Me!lstMaterialAddSelect.Column(3)) & ", " & _ Forms!EditProject!txtID.Value & ")", dbFailOnError Forms("EditProject")!subformMaterialDetails.Requer y DoCmd.Close acForm, Me.Name Exit Sub ErrHandler: MsgBox "Error in CloseBtn_Click( ) in" & vbCrLf & _ Me.Name & " form." & vbCrLf & vbCrLf & _ "Error #" & Err.Number & vbCrLf & Err.Description Err.Clear End Sub The code above makes a lot of assumptions. It assumes: 1.) The subform is bound to the tblMyTable table. 2.) The three fields that need values inserted from the selection in the list box are named MaterialDescription, MaterialUnitPrice, MaterialSalesTax, and that MaterialDescription is a Text data type and MaterialUnitPrice and MaterialSalesTax are both Currency data types. 3.) That the main form is named EditProject. 4.) That the main form is bound. 5.) That the subform control is named subformMaterialDetails. 6.) That the subform is bound. 7.) That there's a relationship between the table that the main form is bound to and the table that the subform is bound to, and that relationship requires that the ID field be the primary key on the one side (displayed in the main form) and is the foreign key on the many side (but it doesn't need to have the same name on the many side). 8.) That the main form's LinkMasterField Property is the ID field. 9.) That the main form's text box which displays the ID field is named txtID. 10.) That the ID field is numerical. 11.) That there are no required fields in the tblMyTable that don't have default values. In other words, the values passed from the list box and the foreign key satisfy all needs for a new record to be created. If any of these assumptions are wrong, you'll need to make adjustments to the names, or the SQL syntax (for different data types), or compensate for any additional required fields, or compensate for a different form structure, or a different relationship. We are almost there...I can feel it. If all you have to do is replace the names, then yes, we are. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. (Please remove ZERO_SPAM from my reply E-mail address so that a message will be forwarded to me.) - - - If my answer has helped you, please sign in and answer yes to the question "Did this post answer your question?" at the bottom of the message, which adds your question and the answers to the database of answers. Remember that questions answered the quickest are often from those who have a history of rewarding the contributors who have taken the time to answer questions correctly. "JackCGW" wrote: Gunny, I made the changes with high hopes...but...I was still getting the same error mesasge so I put a break point at the On Error line. Private Sub cmdAddSelectedItem_Click() On Error GoTo Err_cmdAddSelectedItem_Click DoCmd.GoToRecord acDataForm, "subformMaterialDetails", acNewRec Forms("EditProject").Controls("subformMaterialDeta ils")!MaterialDescription.Value = Me!lstMaterialAddSelect.Column(1) Forms("EditProject").Controls("subformMaterialDeta ils")!MaterialUnitPrice.Value = CCur(Me!lstMaterialAddSelect.Column(2)) Forms("EditProject").Controls("subformMaterialDeta ils")!MaterialSalesTax.Value = CCur(Me!lstMaterialAddSelect.Column(3)) DoCmd.Close acForm, Me.Name Exit Sub Err_cmdAddSelectedItem_Click: MsgBox "Error in cmdAddSelectedItem_Click() in" & vbCrLf & Me.Name & " form." & vbCrLf & vbCrLf & "Error #" & Err.Number & vbCrLf & Err.Description Err.Clear End Sub Now when I 'Step Into' the code, the error occurs at the DoCmd...AcNewRec line. I thought I could apply the same logic of calling to the control(subform) but I appearently don't know the proper "things" to type. Just to simplify these threads... The parent form is called EditProject The subform as a control is called subformMaterialDetails The lsitbox form is called MaterialAddSelectMiniform The cmdButton that gets all the clicking is on MaterialAddSelectMiniform and is called cmdAddSelectedItem Thanks for the help; We are almost there...I can feel it. Jack "'69 Camaro" wrote: Hi, Jack. Open the main form in Design View. Open the Properties dialog window by selecting the "Properties" button on the built-in toolbar. On the main form, select the subform control so that there are "boxes" around its perimeter, indicating "Selected Control." The Title Bar on the Properties dialog window changes to "Subform/Subreport: MyCtrlName." Select the "Other" tab. Check the Name Property, and it will match the same name as the one I've used as MyCtrlName as a placeholder in the Title Bar. Replace my names with your names in the following example (watch out for word wrap): Forms("frmBOM").Controls("MyCtrlName")!txtItem.Val ue = Me!lstItems.Column(1) Forms("frmBOM").Controls("MyCtrlName")!txtDesc.Val ue = Me!lstItems.Column(2) Forms("frmBOM").Controls("MyCtrlName")!txtCost.Val ue = CCur(Me!lstItems.Column(3)) . . . where frmBOM is the name of the main form, MyCtrlName is the name of the subform control, txtItem is the text box on the subform displaying the item's name, txtDesc is the text box on the subform displaying the item's description, and txtCost is the text box on the subform displaying the item's cost. Save and compile the module again. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. (Please remove ZERO_SPAM from my reply E-mail address so that a message will be forwarded to me.) - - - If my answer has helped you, please sign in and answer yes to the question "Did this post answer your question?" at the bottom of the message, which adds your question and the answers to the database of answers. Remember that questions answered the quickest are often from those who have a history of rewarding the contributors who have taken the time to answer questions correctly. "JackCGW" wrote: Gunny, I actually understand that...but...I can't seem to find the subforms control name. I have looked all through the help files but no luck(usually isn't). Jack "'69 Camaro" wrote: Hi, Jack. As a stand alone form the code works flawlessly. But if I use the form as a subform I did mention that it's a simple example. Add subforms and you've got complexity that you need to add code for. The object 'subformMaterialdetails' isn't open. It isn't. It's now an object that the subform control is "holding" whenever the main form is open. You must refer to this subform control, not the form. Change these three lines of code: Forms("frmBOM")!txtItem.Value = Me!lstItems.Column(1) Forms("frmBOM")!txtDesc.Value = Me!lstItems.Column(2) Forms("frmBOM")!txtCost.Value = CCur(Me!lstItems.Column(3)) . . . to the following (and watch out for word wrap): Forms("frmBOM").Controls("MyCtrl")!txtItem.Value = Me!lstItems.Column(1) Forms("frmBOM").Controls("MyCtrl")!txtDesc.Value = Me!lstItems.Column(2) Forms("frmBOM").Controls("MyCtrl")!txtCost.Value = CCur(Me!lstItems.Column(3)) . . . where frmBOM is the name of the main form, MyCtrl is the name of the subform control (_not_ the subform's name), txtItem is the text box on the subform displaying the item's name, txtDesc is the text box on the subform displaying the item's description, and txtCost is the text box on the subform displaying the item's cost. Save and compile the module again. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. (Please remove ZERO_SPAM from my reply E-mail address so that a message will be forwarded to me.) - - - If my answer has helped you, please sign in and answer yes to the question "Did this post answer your question?" at the bottom of the message, which adds your question and the answers to the database of answers. Remember that questions answered the quickest are often from those who have a history of rewarding the contributors who have taken the time to answer questions correctly. "JackCGW" wrote: Well Gunny, That worked; albeit I have no idea how...but that;s why I am a cabinet maker and you are a programmer. Now when I say worked I realy mean sort of worked. As a stand alone form the code works flawlessly. But if I use the form as a subform linked to the mother by ProjectID, I get an error that states: Error #2489 The object 'subformMaterialdetails' isn't open. How is that possible? Jack "'69 Camaro" wrote: Hi, Jack. As per your request in your other thread, the easiest way to accomplish this task is to let the list box automate the process. I'll give you a simple example, but you'll need to replace the names in the example with your own. First, create a new query and paste the following in the SQL View pane: SELECT ID, ItemName, ItemDesc, Cost FROM tblItems ORDER BY ItemName; . . . where ID is the primary key of the table listing the items, ItemName is the item, ItemDesc is the item's description, Cost is the item's cost, and tblItems is the name of the table. Save the query and name it qryItems. Open the form that contains the list box in Design View and open the Properties dialog window. Select the list box, and then select the Data tab, and then select the "Row Source" Property. Scroll down and select qryItems. Select the Format tab, and then select the "Column Count" Property. Type 4 in the text box. Select the "Column Widths" Property and type 0";1";0";0" in the text box. This way, only the item's name will be displayed. In the form's module in the list box's OnAfterUpdate( ) event, paste the following: Private Sub lstItems_AfterUpdate() On Error GoTo ErrHandler Me!txtItemDesc.Value = Me!lstItems.Column(2) Me!txtItemCost.Value = CCur(Me!lstItems.Column(3)) Exit Sub ErrHandler: MsgBox "Error in lstItems_AfterUpdate( ) in" & vbCrLf & _ Me.Name & " form." & vbCrLf & vbCrLf & _ "Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description Err.Clear End Sub . . . where lstItems is the name of the list box, txtItemDesc is the text box displaying the description, and txtItemCost is the text box displaying the cost. In the close button's OnClick( ) event, paste the following: Private Sub CloseBtn_Click() On Error GoTo ErrHandler DoCmd.GoToRecord acDataForm, "frmBOM", acNewRec Forms("frmBOM")!txtItem.Value = Me!lstItems.Column(1) Forms("frmBOM")!txtDesc.Value = Me!lstItems.Column(2) Forms("frmBOM")!txtCost.Value = CCur(Me!lstItems.Column(3)) DoCmd.Close acForm, Me.Name Exit Sub ErrHandler: MsgBox "Error in CloseBtn_Click( ) in" & vbCrLf & _ Me.Name & " form." & vbCrLf & vbCrLf & _ "Error #" & Err.Number & vbCrLf & Err.Description Err.Clear End Sub . . . where CloseBtn is the name of the button, frmBOM is the name of the BOM form, txtItem is the text box on the the BOM form displaying the item's name, txtDesc is the text box on the the BOM form displaying the item's description, and txtCost is the text box on the the BOM form displaying the item's cost. Save and compile the module. Return to the form in Design View and close the Properties dialog window, and then close this form. Open the BOM form and select the "Add From List" button to open the list box form. Select an item in the list box and you'll see the text boxes automatically populate with the correct values. Select the close button to return to the BOM form and you'll see a new record with the selected item, description, and cost. As you can see, there's no need for global variables. If these three values are needed elsewhere, then they can be retrieved from the text boxes on the BOM form. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips. (Please remove ZERO_SPAM from my reply E-mail address so that a message will be forwarded to me.) - - - If my answer has helped you, please sign in and answer yes to the question "Did this post answer your question?" at the bottom of the message, which adds your question and the answers to the database of answers. Remember that questions answered the quickest are often from those who have a history of rewarding the contributors who have taken the time to answer questions correctly. "JackCGW" wrote: I have a form that enters products into a Bill of Materials (description and cost). The input form allows the user to type in a product manually or click a "Add From List" cmd button. On click, another form opens that has a list box and 2 text boxes. When the user clicks an item in the list box (OnClick event) the description and cost are loaded into a global variable and then appear in the corresponding text boxes. Once the user is certain they have selected the correct product, they click a cmd button that closes the list box form and "SHOULD" load the global variables into a new record set of the Bill of Materials form. The problem is I can't work out the code to transfer the variables. My path of thinking is to set the current record of the Bill of Materials to New in the OnClick event just prior to opening the list box form. Then the OnClick event for the cmd button on the list box form should load the record set before closing itstlsef. Yes? No? How? |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Report will not access public variables from form | Lee | Setting Up & Running Reports | 4 | May 5th, 2005 04:51 PM |
Global Variables in Add-Ins | Doug B. | Using Forms | 1 | April 25th, 2005 10:17 PM |
strategy for data entry in multiple tables | LAF | Using Forms | 18 | April 25th, 2005 04:04 AM |
automatically fill field with form information | Tina L. | Using Forms | 2 | January 14th, 2005 07:18 AM |
auto entry into second table after update | Tony | New Users | 13 | July 9th, 2004 10:42 PM |