SubForm Question

[LOP]

I have a subform that has a combo box containing a result set of a query
based on a criteria from a textbox in the main form. The goal is if a user
clicks on a value from the combo box it should update values on two other
textboxes in the main form. Here's my code in the AfterUpdate event of the
subform:
Dim LSQL As String
LSQL = "SELECT [Master_SoftwareProduct].[ID],
[Master_SoftwareProduct].[Standard Product Name], "
LSQL = LSQL & " [Master_SoftwareProduct].[Manufacturer] FROM
Master_SoftwareProduct WHERE "
LSQL = LSQL & "[Product]= " & txtbxProduct.Value

cbxStandardProductName.RowSource = LSQL

Me!frmMainProduct!txtbxStdProductName.Value =
Me!cbxStandardProductName.Column(1)
Me!frmMainProduct!txtbxStdMfgName.Value =
Me!cbxStandardProductName.Column(2)

I get an error when saving the column values into the textboxes in the main
form saying it could not recognize frmMainProduct (main form). Secondly, how
do I reset the combo box so that when I use the main form's navigation bar it
will clear what was selected previously? Any help is appreciated.

 

[Wayne Morgan]

To refer to the main form from the subform, you can either use the Full name
(Forms!frmMainProduct) or you can refer directly to the main form's
relationship with the subform (Me.Parent). Using this, your first statement
would become:

Me.Parent!txtbxStdProductName.Value = Me!cbxStandardProductName.Column(1)

 

[LOP]

Thanks, Wayne. I got past the error but why am I getting a Null value in
Me!cbxStandardProductName.Column(1)? I can see the value in the combo box
itself but when passed into Me.Parent!txtbxStdProductName.Value it is null.
Any thoughts? By the way, I also corrected the line
cbxStandardProductName.RowSource = LSQL
and added the object Me prior to the control name
(Me.cbxStandardProductName.RowSource = LSQL)

To refer to the main form from the subform, you can either use the Full name
(Forms!frmMainProduct) or you can refer directly to the main form's
relationship with the subform (Me.Parent). Using this, your first statement
would become:

Me.Parent!txtbxStdProductName.Value = Me!cbxStandardProductName.Column(1)

--
Wayne Morgan
MS Access MVP

I have a subform that has a combo box containing a result set of a query
based on a criteria from a textbox in the main form. The goal is if a user
clicks on a value from the combo box it should update values on two other
textboxes in the main form. Here's my code in the AfterUpdate event of the
subform:
Dim LSQL As String
LSQL = "SELECT [Master_SoftwareProduct].[ID],
[Master_SoftwareProduct].[Standard Product Name], "
LSQL = LSQL & " [Master_SoftwareProduct].[Manufacturer] FROM
Master_SoftwareProduct WHERE "
LSQL = LSQL & "[Product]= " & txtbxProduct.Value

cbxStandardProductName.RowSource = LSQL

Me!frmMainProduct!txtbxStdProductName.Value =
Me!cbxStandardProductName.Column(1)
Me!frmMainProduct!txtbxStdMfgName.Value =
Me!cbxStandardProductName.Column(2)

I get an error when saving the column values into the textboxes in the
main
form saying it could not recognize frmMainProduct (main form). Secondly,
how
do I reset the combo box so that when I use the main form's navigation bar
it
will clear what was selected previously? Any help is appreciated.

 

[Wayne Morgan]

It appears that you have 3 columns in the combo box. The value from the
first visible column will be the one displayed in the combo box once you've
made a selection. The bound column will return the value of the combo box.
To refer to the other columns, you need to use the Column property, as
you've done. Then index value for the column property is zero based, so the
first column is 0, the second is 1, and the third is 2. With what you have,
you are referring to the second and third columns in your code. You may want
to put a Debug.Print statement in your code to print the value to the
immediate window before you send it to the parent form to see if you get the
same result in both places.

Example:
Debug.Print Me!cbxStandardProductName.Column(1)

Also, you say that you are doing this in the AfterUpdate event of the
subform, not the AfterUpdate event of the combo box. If that is the case,
what are you doing to make the subform update? Are you going to the next
record? If so, then you are probably getting the value of the combo box from
the next record. Has anything been selected in that record?

--
Wayne Morgan
MS Access MVP

Thanks, Wayne. I got past the error but why am I getting a Null value in
Me!cbxStandardProductName.Column(1)? I can see the value in the combo box
itself but when passed into Me.Parent!txtbxStdProductName.Value it is
null.
Any thoughts? By the way, I also corrected the line
cbxStandardProductName.RowSource = LSQL
and added the object Me prior to the control name
(Me.cbxStandardProductName.RowSource = LSQL)

To refer to the main form from the subform, you can either use the Full
name
(Forms!frmMainProduct) or you can refer directly to the main form's
relationship with the subform (Me.Parent). Using this, your first
statement
would become:

Me.Parent!txtbxStdProductName.Value = Me!cbxStandardProductName.Column(1)

--
Wayne Morgan
MS Access MVP

I have a subform that has a combo box containing a result set of a query
based on a criteria from a textbox in the main form. The goal is if a
user
clicks on a value from the combo box it should update values on two
other
textboxes in the main form. Here's my code in the AfterUpdate event of
the
subform:
Dim LSQL As String
LSQL = "SELECT [Master_SoftwareProduct].[ID],
[Master_SoftwareProduct].[Standard Product Name], "
LSQL = LSQL & " [Master_SoftwareProduct].[Manufacturer] FROM
Master_SoftwareProduct WHERE "
LSQL = LSQL & "[Product]= " & txtbxProduct.Value

cbxStandardProductName.RowSource = LSQL

Me!frmMainProduct!txtbxStdProductName.Value =
Me!cbxStandardProductName.Column(1)
Me!frmMainProduct!txtbxStdMfgName.Value =
Me!cbxStandardProductName.Column(2)

I get an error when saving the column values into the textboxes in the
main
form saying it could not recognize frmMainProduct (main form).
Secondly,
how
do I reset the combo box so that when I use the main form's navigation
bar
it
will clear what was selected previously? Any help is appreciated.