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?

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?

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?

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?

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?

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?

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?

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?

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?

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?