Runtime Error 3021

[Guest]

I have created a form and subform with the wizard. After creating, I created
a combobox in my subform with the wizard. This combo box lists all products
available. My goal is to use the main form "Quote" to create quotes for
variuos customers. The subform is where all products and the statistics for
those products will be chosen. I have the subform in datasheet view.

The issue is with the combobox. When I click on it, all products are
listed, however when I choose one I get runtime error 3021. When I click
Debug, the following line is in yellow:

Me.Bookmark = rs.Bookmark

1. I have no idea what this line means.
2. I think I may need to edit my record source for some part of the form or
subform, but I do not know where or how.
3. This is my first subform with a combo box.
4. I have never had any training in coding, I have gotten this far with
books and this forum.

Any suggestions/detailed information would be appreciated. Please give me
step-by-step. Still pretty new with the lingo.

I love reading all of the posts with solutions, they have been most helpful
to me. I hope someone out there is able to help a newbie like me!

Thanks in advance!

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
Me.Bookmark = rs.Bookmark
End Sub

 

[Ken Snell [MVP]]

Change the code to this:

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
End Sub


The error that you're getting is because the code is not finding a match in
the recordset for the value you selected in Combo26. I'm guessing this is
because the combo box is based on a lookup field in the table, and that what
you think you're selecting is not what the value of the combo box is.

What is the Row Source query for the Combo26 control?

 

[Guest]

Ken,

The row source query is:

SELECT [Products Query].[UPC] FROM [Products Query];

Thank you!

Sara

Change the code to this:

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
End Sub


The error that you're getting is because the code is not finding a match in
the recordset for the value you selected in Combo26. I'm guessing this is
because the combo box is based on a lookup field in the table, and that what
you think you're selecting is not what the value of the combo box is.

What is the Row Source query for the Combo26 control?

--

Ken Snell
<MS ACCESS MVP>

I have created a form and subform with the wizard. After creating, I
created
a combobox in my subform with the wizard. This combo box lists all
products
available. My goal is to use the main form "Quote" to create quotes for
variuos customers. The subform is where all products and the statistics
for
those products will be chosen. I have the subform in datasheet view.

The issue is with the combobox. When I click on it, all products are
listed, however when I choose one I get runtime error 3021. When I click
Debug, the following line is in yellow:

Me.Bookmark = rs.Bookmark

1. I have no idea what this line means.
2. I think I may need to edit my record source for some part of the form
or
subform, but I do not know where or how.
3. This is my first subform with a combo box.
4. I have never had any training in coding, I have gotten this far with
books and this forum.

Any suggestions/detailed information would be appreciated. Please give me
step-by-step. Still pretty new with the lingo.

I love reading all of the posts with solutions, they have been most
helpful
to me. I hope someone out there is able to help a newbie like me!

Thanks in advance!

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
Me.Bookmark = rs.Bookmark
End Sub

 

[Ken Snell [MVP]]

OK, then I was wrong about this being based on a lookup field.

Did you try the new code I'd posted? What happens with it? Do you still get
the error?

--

Ken Snell
<MS ACCESS MVP>

Ken,

The row source query is:

SELECT [Products Query].[UPC] FROM [Products Query];

Thank you!

Sara

Change the code to this:

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
End Sub


The error that you're getting is because the code is not finding a match
in
the recordset for the value you selected in Combo26. I'm guessing this is
because the combo box is based on a lookup field in the table, and that
what
you think you're selecting is not what the value of the combo box is.

What is the Row Source query for the Combo26 control?

--

Ken Snell
<MS ACCESS MVP>

I have created a form and subform with the wizard. After creating, I
created
a combobox in my subform with the wizard. This combo box lists all
products
available. My goal is to use the main form "Quote" to create quotes
for
variuos customers. The subform is where all products and the
statistics
for
those products will be chosen. I have the subform in datasheet view.

The issue is with the combobox. When I click on it, all products are
listed, however when I choose one I get runtime error 3021. When I
click
Debug, the following line is in yellow:

Me.Bookmark = rs.Bookmark

1. I have no idea what this line means.
2. I think I may need to edit my record source for some part of the
form
or
subform, but I do not know where or how.
3. This is my first subform with a combo box.
4. I have never had any training in coding, I have gotten this far
with
books and this forum.

Any suggestions/detailed information would be appreciated. Please give
me
step-by-step. Still pretty new with the lingo.

I love reading all of the posts with solutions, they have been most
helpful
to me. I hope someone out there is able to help a newbie like me!

Thanks in advance!

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
Me.Bookmark = rs.Bookmark
End Sub

 

[Guest]

Ken,

I no longer get the error. However, now when I select a product from my
combo box, nothing happens. The rest of my text boxes do not populate with
any information.

Sara

OK, then I was wrong about this being based on a lookup field.

Did you try the new code I'd posted? What happens with it? Do you still get
the error?

--

Ken Snell
<MS ACCESS MVP>

Ken,

The row source query is:

SELECT [Products Query].[UPC] FROM [Products Query];

Thank you!

Sara

Change the code to this:

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
End Sub


The error that you're getting is because the code is not finding a match
in
the recordset for the value you selected in Combo26. I'm guessing this is
because the combo box is based on a lookup field in the table, and that
what
you think you're selecting is not what the value of the combo box is.

What is the Row Source query for the Combo26 control?

--

Ken Snell
<MS ACCESS MVP>


I have created a form and subform with the wizard. After creating, I
created
a combobox in my subform with the wizard. This combo box lists all
products
available. My goal is to use the main form "Quote" to create quotes
for
variuos customers. The subform is where all products and the
statistics
for
those products will be chosen. I have the subform in datasheet view.

The issue is with the combobox. When I click on it, all products are
listed, however when I choose one I get runtime error 3021. When I
click
Debug, the following line is in yellow:

Me.Bookmark = rs.Bookmark

1. I have no idea what this line means.
2. I think I may need to edit my record source for some part of the
form
or
subform, but I do not know where or how.
3. This is my first subform with a combo box.
4. I have never had any training in coding, I have gotten this far
with
books and this forum.

Any suggestions/detailed information would be appreciated. Please give
me
step-by-step. Still pretty new with the lingo.

I love reading all of the posts with solutions, they have been most
helpful
to me. I hope someone out there is able to help a newbie like me!

Thanks in advance!

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
Me.Bookmark = rs.Bookmark
End Sub

 

[Ken Snell [MVP]]

So this suggests that your form's recordset (recordsource query) does not
contain a record that has a UPC value equal to the one you are selecting in
the combo box.

Is UPC a text field or a numeric field? If it's a text field, then your code
is correct. If it's a numeric field, then change your code to this:

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = " & Me![Combo26]
If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
End Sub

--

Ken Snell
<MS ACCESS MVP>

Ken,

I no longer get the error. However, now when I select a product from my
combo box, nothing happens. The rest of my text boxes do not populate
with
any information.

Sara

OK, then I was wrong about this being based on a lookup field.

Did you try the new code I'd posted? What happens with it? Do you still
get
the error?

--

Ken Snell
<MS ACCESS MVP>

Ken,

The row source query is:

SELECT [Products Query].[UPC] FROM [Products Query];

Thank you!

Sara

:

Change the code to this:

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
End Sub


The error that you're getting is because the code is not finding a
match
in
the recordset for the value you selected in Combo26. I'm guessing this
is
because the combo box is based on a lookup field in the table, and
that
what
you think you're selecting is not what the value of the combo box is.

What is the Row Source query for the Combo26 control?

--

Ken Snell
<MS ACCESS MVP>


I have created a form and subform with the wizard. After creating, I
created
a combobox in my subform with the wizard. This combo box lists all
products
available. My goal is to use the main form "Quote" to create quotes
for
variuos customers. The subform is where all products and the
statistics
for
those products will be chosen. I have the subform in datasheet
view.

The issue is with the combobox. When I click on it, all products
are
listed, however when I choose one I get runtime error 3021. When I
click
Debug, the following line is in yellow:

Me.Bookmark = rs.Bookmark

1. I have no idea what this line means.
2. I think I may need to edit my record source for some part of the
form
or
subform, but I do not know where or how.
3. This is my first subform with a combo box.
4. I have never had any training in coding, I have gotten this far
with
books and this forum.

Any suggestions/detailed information would be appreciated. Please
give
me
step-by-step. Still pretty new with the lingo.

I love reading all of the posts with solutions, they have been most
helpful
to me. I hope someone out there is able to help a newbie like me!

Thanks in advance!

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
Me.Bookmark = rs.Bookmark
End Sub

 

[Guest]

Ken,

No, it is a text field.

Could this be a Relationship problem?

Thank you,

Sara

So this suggests that your form's recordset (recordsource query) does not
contain a record that has a UPC value equal to the one you are selecting in
the combo box.

Is UPC a text field or a numeric field? If it's a text field, then your code
is correct. If it's a numeric field, then change your code to this:

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = " & Me![Combo26]
If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
End Sub

--

Ken Snell
<MS ACCESS MVP>

Ken,

I no longer get the error. However, now when I select a product from my
combo box, nothing happens. The rest of my text boxes do not populate
with
any information.

Sara

OK, then I was wrong about this being based on a lookup field.

Did you try the new code I'd posted? What happens with it? Do you still
get
the error?

--

Ken Snell
<MS ACCESS MVP>


Ken,

The row source query is:

SELECT [Products Query].[UPC] FROM [Products Query];

Thank you!

Sara

:

Change the code to this:

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
End Sub


The error that you're getting is because the code is not finding a
match
in
the recordset for the value you selected in Combo26. I'm guessing this
is
because the combo box is based on a lookup field in the table, and
that
what
you think you're selecting is not what the value of the combo box is.

What is the Row Source query for the Combo26 control?

--

Ken Snell
<MS ACCESS MVP>


I have created a form and subform with the wizard. After creating, I
created
a combobox in my subform with the wizard. This combo box lists all
products
available. My goal is to use the main form "Quote" to create quotes
for
variuos customers. The subform is where all products and the
statistics
for
those products will be chosen. I have the subform in datasheet
view.

The issue is with the combobox. When I click on it, all products
are
listed, however when I choose one I get runtime error 3021. When I
click
Debug, the following line is in yellow:

Me.Bookmark = rs.Bookmark

1. I have no idea what this line means.
2. I think I may need to edit my record source for some part of the
form
or
subform, but I do not know where or how.
3. This is my first subform with a combo box.
4. I have never had any training in coding, I have gotten this far
with
books and this forum.

Any suggestions/detailed information would be appreciated. Please
give
me
step-by-step. Still pretty new with the lingo.

I love reading all of the posts with solutions, they have been most
helpful
to me. I hope someone out there is able to help a newbie like me!

Thanks in advance!

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
Me.Bookmark = rs.Bookmark
End Sub

 

[Ken Snell [MVP]]

Perhaps.... post the RecordSource of the form that contains the combo box.
Let's see what the form's data are.
--

Ken Snell
<MS ACCESS MVP>

Ken,

No, it is a text field.

Could this be a Relationship problem?

Thank you,

Sara

So this suggests that your form's recordset (recordsource query) does not
contain a record that has a UPC value equal to the one you are selecting
in
the combo box.

Is UPC a text field or a numeric field? If it's a text field, then your
code
is correct. If it's a numeric field, then change your code to this:

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = " & Me![Combo26]
If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
End Sub

--

Ken Snell
<MS ACCESS MVP>

Ken,

I no longer get the error. However, now when I select a product from
my
combo box, nothing happens. The rest of my text boxes do not populate
with
any information.

Sara



:

OK, then I was wrong about this being based on a lookup field.

Did you try the new code I'd posted? What happens with it? Do you
still
get
the error?

--

Ken Snell
<MS ACCESS MVP>


Ken,

The row source query is:

SELECT [Products Query].[UPC] FROM [Products Query];

Thank you!

Sara

:

Change the code to this:

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
End Sub


The error that you're getting is because the code is not finding a
match
in
the recordset for the value you selected in Combo26. I'm guessing
this
is
because the combo box is based on a lookup field in the table, and
that
what
you think you're selecting is not what the value of the combo box
is.

What is the Row Source query for the Combo26 control?

--

Ken Snell
<MS ACCESS MVP>


message
I have created a form and subform with the wizard. After
creating, I
created
a combobox in my subform with the wizard. This combo box lists
all
products
available. My goal is to use the main form "Quote" to create
quotes
for
variuos customers. The subform is where all products and the
statistics
for
those products will be chosen. I have the subform in datasheet
view.

The issue is with the combobox. When I click on it, all products
are
listed, however when I choose one I get runtime error 3021. When
I
click
Debug, the following line is in yellow:

Me.Bookmark = rs.Bookmark

1. I have no idea what this line means.
2. I think I may need to edit my record source for some part of
the
form
or
subform, but I do not know where or how.
3. This is my first subform with a combo box.
4. I have never had any training in coding, I have gotten this
far
with
books and this forum.

Any suggestions/detailed information would be appreciated.
Please
give
me
step-by-step. Still pretty new with the lingo.

I love reading all of the posts with solutions, they have been
most
helpful
to me. I hope someone out there is able to help a newbie like
me!

Thanks in advance!

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
Me.Bookmark = rs.Bookmark
End Sub

 

[Guest]

Ken,

Record Source for the subform/Subreport is Quote Subform
When I click of the upper left hand corner of the subform and click
properties, I get a propert window called Form and that Record Source is

: Products Query.

Sara

Perhaps.... post the RecordSource of the form that contains the combo box.
Let's see what the form's data are.
--

Ken Snell
<MS ACCESS MVP>

Ken,

No, it is a text field.

Could this be a Relationship problem?

Thank you,

Sara

So this suggests that your form's recordset (recordsource query) does not
contain a record that has a UPC value equal to the one you are selecting
in
the combo box.

Is UPC a text field or a numeric field? If it's a text field, then your
code
is correct. If it's a numeric field, then change your code to this:

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = " & Me![Combo26]
If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
End Sub

--

Ken Snell
<MS ACCESS MVP>

Ken,

I no longer get the error. However, now when I select a product from
my
combo box, nothing happens. The rest of my text boxes do not populate
with
any information.

Sara



:

OK, then I was wrong about this being based on a lookup field.

Did you try the new code I'd posted? What happens with it? Do you
still
get
the error?

--

Ken Snell
<MS ACCESS MVP>


Ken,

The row source query is:

SELECT [Products Query].[UPC] FROM [Products Query];

Thank you!

Sara

:

Change the code to this:

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
End Sub


The error that you're getting is because the code is not finding a
match
in
the recordset for the value you selected in Combo26. I'm guessing
this
is
because the combo box is based on a lookup field in the table, and
that
what
you think you're selecting is not what the value of the combo box
is.

What is the Row Source query for the Combo26 control?

--

Ken Snell
<MS ACCESS MVP>


message
I have created a form and subform with the wizard. After
creating, I
created
a combobox in my subform with the wizard. This combo box lists
all
products
available. My goal is to use the main form "Quote" to create
quotes
for
variuos customers. The subform is where all products and the
statistics
for
those products will be chosen. I have the subform in datasheet
view.

The issue is with the combobox. When I click on it, all products
are
listed, however when I choose one I get runtime error 3021. When
I
click
Debug, the following line is in yellow:

Me.Bookmark = rs.Bookmark

1. I have no idea what this line means.
2. I think I may need to edit my record source for some part of
the
form
or
subform, but I do not know where or how.
3. This is my first subform with a combo box.
4. I have never had any training in coding, I have gotten this
far
with
books and this forum.

Any suggestions/detailed information would be appreciated.
Please
give
me
step-by-step. Still pretty new with the lingo.

I love reading all of the posts with solutions, they have been
most
helpful
to me. I hope someone out there is able to help a newbie like
me!

Thanks in advance!

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
Me.Bookmark = rs.Bookmark
End Sub

 

[Ken Snell [MVP]]

So this combo box is in a subform? And the subform's RecordSource is
"Products Query"?

OK -- now I need you to post the SQL statement of the Products Query (it's a
stored query, so open the actual query in design view, then go to left end
of toolbar and click the "view" icon and select SQL view. Copy the entire
statement that you'll see there and paste it into your reply message.


--

Ken Snell
<MS ACCESS MVP>

Ken,

Record Source for the subform/Subreport is Quote Subform
When I click of the upper left hand corner of the subform and click
properties, I get a propert window called Form and that Record Source is

: Products Query.

Sara

Perhaps.... post the RecordSource of the form that contains the combo
box.
Let's see what the form's data are.
--

Ken Snell
<MS ACCESS MVP>

Ken,

No, it is a text field.

Could this be a Relationship problem?

Thank you,

Sara

:

So this suggests that your form's recordset (recordsource query) does
not
contain a record that has a UPC value equal to the one you are
selecting
in
the combo box.

Is UPC a text field or a numeric field? If it's a text field, then
your
code
is correct. If it's a numeric field, then change your code to this:

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = " & Me![Combo26]
If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
End Sub

--

Ken Snell
<MS ACCESS MVP>

Ken,

I no longer get the error. However, now when I select a product
from
my
combo box, nothing happens. The rest of my text boxes do not
populate
with
any information.

Sara



:

OK, then I was wrong about this being based on a lookup field.

Did you try the new code I'd posted? What happens with it? Do you
still
get
the error?

--

Ken Snell
<MS ACCESS MVP>


message
Ken,

The row source query is:

SELECT [Products Query].[UPC] FROM [Products Query];

Thank you!

Sara

:

Change the code to this:

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
End Sub


The error that you're getting is because the code is not finding
a
match
in
the recordset for the value you selected in Combo26. I'm
guessing
this
is
because the combo box is based on a lookup field in the table,
and
that
what
you think you're selecting is not what the value of the combo
box
is.

What is the Row Source query for the Combo26 control?

--

Ken Snell
<MS ACCESS MVP>


message
I have created a form and subform with the wizard. After
creating, I
created
a combobox in my subform with the wizard. This combo box
lists
all
products
available. My goal is to use the main form "Quote" to create
quotes
for
variuos customers. The subform is where all products and the
statistics
for
those products will be chosen. I have the subform in
datasheet
view.

The issue is with the combobox. When I click on it, all
products
are
listed, however when I choose one I get runtime error 3021.
When
I
click
Debug, the following line is in yellow:

Me.Bookmark = rs.Bookmark

1. I have no idea what this line means.
2. I think I may need to edit my record source for some part
of
the
form
or
subform, but I do not know where or how.
3. This is my first subform with a combo box.
4. I have never had any training in coding, I have gotten
this
far
with
books and this forum.

Any suggestions/detailed information would be appreciated.
Please
give
me
step-by-step. Still pretty new with the lingo.

I love reading all of the posts with solutions, they have been
most
helpful
to me. I hope someone out there is able to help a newbie like
me!

Thanks in advance!

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
Me.Bookmark = rs.Bookmark
End Sub

 

[Guest]

Ken,

SELECT Products.Items, Products.Unit, Products.[FOB FCD], Products.[FOB QD],
Products.DeliveredFCD, Products.DeliveredQD, Products.[40'QTY(pcs)],
Products.GrossWtLb, Products.[Cube ft], Products.MiniOrderQty,
Products.UpdateDate, Products.[Quote Number], Products.UPC, Products.UPC
FROM Products;

Sara

So this combo box is in a subform? And the subform's RecordSource is
"Products Query"?

OK -- now I need you to post the SQL statement of the Products Query (it's a
stored query, so open the actual query in design view, then go to left end
of toolbar and click the "view" icon and select SQL view. Copy the entire
statement that you'll see there and paste it into your reply message.


--

Ken Snell
<MS ACCESS MVP>

Ken,

Record Source for the subform/Subreport is Quote Subform
When I click of the upper left hand corner of the subform and click
properties, I get a propert window called Form and that Record Source is

: Products Query.

Sara

Perhaps.... post the RecordSource of the form that contains the combo
box.
Let's see what the form's data are.
--

Ken Snell
<MS ACCESS MVP>



Ken,

No, it is a text field.

Could this be a Relationship problem?

Thank you,

Sara

:

So this suggests that your form's recordset (recordsource query) does
not
contain a record that has a UPC value equal to the one you are
selecting
in
the combo box.

Is UPC a text field or a numeric field? If it's a text field, then
your
code
is correct. If it's a numeric field, then change your code to this:

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = " & Me![Combo26]
If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
End Sub

--

Ken Snell
<MS ACCESS MVP>

Ken,

I no longer get the error. However, now when I select a product
from
my
combo box, nothing happens. The rest of my text boxes do not
populate
with
any information.

Sara



:

OK, then I was wrong about this being based on a lookup field.

Did you try the new code I'd posted? What happens with it? Do you
still
get
the error?

--

Ken Snell
<MS ACCESS MVP>


message
Ken,

The row source query is:

SELECT [Products Query].[UPC] FROM [Products Query];

Thank you!

Sara

:

Change the code to this:

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
End Sub


The error that you're getting is because the code is not finding
a
match
in
the recordset for the value you selected in Combo26. I'm
guessing
this
is
because the combo box is based on a lookup field in the table,
and
that
what
you think you're selecting is not what the value of the combo
box
is.

What is the Row Source query for the Combo26 control?

--

Ken Snell
<MS ACCESS MVP>


message
I have created a form and subform with the wizard. After
creating, I
created
a combobox in my subform with the wizard. This combo box
lists
all
products
available. My goal is to use the main form "Quote" to create
quotes
for
variuos customers. The subform is where all products and the
statistics
for
those products will be chosen. I have the subform in
datasheet
view.

The issue is with the combobox. When I click on it, all
products
are
listed, however when I choose one I get runtime error 3021.
When
I
click
Debug, the following line is in yellow:

Me.Bookmark = rs.Bookmark

1. I have no idea what this line means.
2. I think I may need to edit my record source for some part
of
the
form
or
subform, but I do not know where or how.
3. This is my first subform with a combo box.
4. I have never had any training in coding, I have gotten
this
far
with
books and this forum.

Any suggestions/detailed information would be appreciated.
Please
give
me
step-by-step. Still pretty new with the lingo.

I love reading all of the posts with solutions, they have been
most
helpful
to me. I hope someone out there is able to help a newbie like
me!

Thanks in advance!

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
Me.Bookmark = rs.Bookmark
End Sub

 

[Ken Snell [MVP]]

You have two Products.UPC fields in the query. One of them needs to be
deleted from this query.

--

Ken Snell
<MS ACCESS MVP>

Ken,

SELECT Products.Items, Products.Unit, Products.[FOB FCD], Products.[FOB
QD],
Products.DeliveredFCD, Products.DeliveredQD, Products.[40'QTY(pcs)],
Products.GrossWtLb, Products.[Cube ft], Products.MiniOrderQty,
Products.UpdateDate, Products.[Quote Number], Products.UPC, Products.UPC
FROM Products;

Sara

So this combo box is in a subform? And the subform's RecordSource is
"Products Query"?

OK -- now I need you to post the SQL statement of the Products Query
(it's a
stored query, so open the actual query in design view, then go to left
end
of toolbar and click the "view" icon and select SQL view. Copy the entire
statement that you'll see there and paste it into your reply message.


--

Ken Snell
<MS ACCESS MVP>

Ken,

Record Source for the subform/Subreport is Quote Subform
When I click of the upper left hand corner of the subform and click
properties, I get a propert window called Form and that Record Source
is

: Products Query.

Sara

Perhaps.... post the RecordSource of the form that contains the combo
box.
Let's see what the form's data are.
--

Ken Snell
<MS ACCESS MVP>



Ken,

No, it is a text field.

Could this be a Relationship problem?

Thank you,

Sara

:

So this suggests that your form's recordset (recordsource query)
does
not
contain a record that has a UPC value equal to the one you are
selecting
in
the combo box.

Is UPC a text field or a numeric field? If it's a text field, then
your
code
is correct. If it's a numeric field, then change your code to this:

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = " & Me![Combo26]
If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
End Sub

--

Ken Snell
<MS ACCESS MVP>

message
Ken,

I no longer get the error. However, now when I select a product
from
my
combo box, nothing happens. The rest of my text boxes do not
populate
with
any information.

Sara



:

OK, then I was wrong about this being based on a lookup field.

Did you try the new code I'd posted? What happens with it? Do
you
still
get
the error?

--

Ken Snell
<MS ACCESS MVP>


message
Ken,

The row source query is:

SELECT [Products Query].[UPC] FROM [Products Query];

Thank you!

Sara

:

Change the code to this:

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
End Sub


The error that you're getting is because the code is not
finding
a
match
in
the recordset for the value you selected in Combo26. I'm
guessing
this
is
because the combo box is based on a lookup field in the
table,
and
that
what
you think you're selecting is not what the value of the combo
box
is.

What is the Row Source query for the Combo26 control?

--

Ken Snell
<MS ACCESS MVP>


message
I have created a form and subform with the wizard. After
creating, I
created
a combobox in my subform with the wizard. This combo box
lists
all
products
available. My goal is to use the main form "Quote" to
create
quotes
for
variuos customers. The subform is where all products and
the
statistics
for
those products will be chosen. I have the subform in
datasheet
view.

The issue is with the combobox. When I click on it, all
products
are
listed, however when I choose one I get runtime error 3021.
When
I
click
Debug, the following line is in yellow:

Me.Bookmark = rs.Bookmark

1. I have no idea what this line means.
2. I think I may need to edit my record source for some
part
of
the
form
or
subform, but I do not know where or how.
3. This is my first subform with a combo box.
4. I have never had any training in coding, I have gotten
this
far
with
books and this forum.

Any suggestions/detailed information would be appreciated.
Please
give
me
step-by-step. Still pretty new with the lingo.

I love reading all of the posts with solutions, they have
been
most
helpful
to me. I hope someone out there is able to help a newbie
like
me!

Thanks in advance!

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
Me.Bookmark = rs.Bookmark
End Sub

 

[Guest]

Ken,

I have deleted the Products.UPC fields to no avail. Anything else I can try?

Thanks,

Sara

You have two Products.UPC fields in the query. One of them needs to be
deleted from this query.

--

Ken Snell
<MS ACCESS MVP>

Ken,

SELECT Products.Items, Products.Unit, Products.[FOB FCD], Products.[FOB
QD],
Products.DeliveredFCD, Products.DeliveredQD, Products.[40'QTY(pcs)],
Products.GrossWtLb, Products.[Cube ft], Products.MiniOrderQty,
Products.UpdateDate, Products.[Quote Number], Products.UPC, Products.UPC
FROM Products;

Sara

So this combo box is in a subform? And the subform's RecordSource is
"Products Query"?

OK -- now I need you to post the SQL statement of the Products Query
(it's a
stored query, so open the actual query in design view, then go to left
end
of toolbar and click the "view" icon and select SQL view. Copy the entire
statement that you'll see there and paste it into your reply message.


--

Ken Snell
<MS ACCESS MVP>

Ken,

Record Source for the subform/Subreport is Quote Subform
When I click of the upper left hand corner of the subform and click
properties, I get a propert window called Form and that Record Source
is

: Products Query.

Sara

Perhaps.... post the RecordSource of the form that contains the combo
box.
Let's see what the form's data are.
--

Ken Snell
<MS ACCESS MVP>



Ken,

No, it is a text field.

Could this be a Relationship problem?

Thank you,

Sara

:

So this suggests that your form's recordset (recordsource query)
does
not
contain a record that has a UPC value equal to the one you are
selecting
in
the combo box.

Is UPC a text field or a numeric field? If it's a text field, then
your
code
is correct. If it's a numeric field, then change your code to this:

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = " & Me![Combo26]
If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
End Sub

--

Ken Snell
<MS ACCESS MVP>

message
Ken,

I no longer get the error. However, now when I select a product
from
my
combo box, nothing happens. The rest of my text boxes do not
populate
with
any information.

Sara



:

OK, then I was wrong about this being based on a lookup field.

Did you try the new code I'd posted? What happens with it? Do
you
still
get
the error?

--

Ken Snell
<MS ACCESS MVP>


message
Ken,

The row source query is:

SELECT [Products Query].[UPC] FROM [Products Query];

Thank you!

Sara

:

Change the code to this:

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
End Sub


The error that you're getting is because the code is not
finding
a
match
in
the recordset for the value you selected in Combo26. I'm
guessing
this
is
because the combo box is based on a lookup field in the
table,
and
that
what
you think you're selecting is not what the value of the combo
box
is.

What is the Row Source query for the Combo26 control?

--

Ken Snell
<MS ACCESS MVP>


message
I have created a form and subform with the wizard. After
creating, I
created
a combobox in my subform with the wizard. This combo box
lists
all
products
available. My goal is to use the main form "Quote" to
create
quotes
for
variuos customers. The subform is where all products and
the
statistics
for
those products will be chosen. I have the subform in
datasheet
view.

The issue is with the combobox. When I click on it, all
products
are
listed, however when I choose one I get runtime error 3021.
When
I
click
Debug, the following line is in yellow:

Me.Bookmark = rs.Bookmark

1. I have no idea what this line means.
2. I think I may need to edit my record source for some
part
of
the
form
or
subform, but I do not know where or how.
3. This is my first subform with a combo box.
4. I have never had any training in coding, I have gotten
this
far
with
books and this forum.

Any suggestions/detailed information would be appreciated.
Please
give
me
step-by-step. Still pretty new with the lingo.

I love reading all of the posts with solutions, they have
been
most
helpful
to me. I hope someone out there is able to help a newbie
like
me!

Thanks in advance!

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
Me.Bookmark = rs.Bookmark
End Sub

 

[Ken Snell [MVP]]

OK - let me recap the info that we have so far:

(1) Combo26 is a combo box in a subform.


(2) The Row Source of Combo26 is
SELECT [Products Query].[UPC] FROM [Products Query];


(3) The subform's RecordSource is
Products Query


(4) The SQL statement for Products Query is

SELECT Products.Items, Products.Unit, Products.[FOB FCD], Products.[FOB QD],
Products.DeliveredFCD, Products.DeliveredQD, Products.[40'QTY(pcs)],
Products.GrossWtLb, Products.[Cube ft], Products.MiniOrderQty,
Products.UpdateDate, Products.[Quote Number], Products.UPC, Products.UPC
FROM Products;


(5) UPC field is a text field.


(6) The code that runs on the AfterUpdate event of Combo26 is

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
End Sub


(7) When a UPC value is selected in the combo box, nothing happens. The rest
of the text boxes on the subform do not populate with any information.


Now, let's change the code in the AfterUpdate event to see if any record is
found at all. For testing, change the code to this:

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
If rs.NoMatch = False Then
Me.Bookmark = rs.Bookmark
MsgBox "Record found for UPC """ & Me![Combo26] & """."
Else
MsgBox "No record found for UPC """ & Me![Combo26] & """."
End If
End Sub


Try selecting a UPC from the combo box. Which message box do you see? Is the
UPC value in the message box the one that you thought you selected?

Also, post the following values for the Combo26 properties:
Bound Column
Control Source
Column Count
Column Widths

 

[Guest]

Ken,

I verified all information and it is correct. I chanfed the event
procedure. I chose product ACM-982185. The error message I received was "No
record found for ACM-982185"

Bound Column 1
Control Source None
Column Count 1
Column Widths 1"

Thanks again,

Sara

OK - let me recap the info that we have so far:

(1) Combo26 is a combo box in a subform.


(2) The Row Source of Combo26 is
SELECT [Products Query].[UPC] FROM [Products Query];


(3) The subform's RecordSource is
Products Query


(4) The SQL statement for Products Query is

SELECT Products.Items, Products.Unit, Products.[FOB FCD], Products.[FOB QD],
Products.DeliveredFCD, Products.DeliveredQD, Products.[40'QTY(pcs)],
Products.GrossWtLb, Products.[Cube ft], Products.MiniOrderQty,
Products.UpdateDate, Products.[Quote Number], Products.UPC, Products.UPC
FROM Products;


(5) UPC field is a text field.


(6) The code that runs on the AfterUpdate event of Combo26 is

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
End Sub


(7) When a UPC value is selected in the combo box, nothing happens. The rest
of the text boxes on the subform do not populate with any information.


Now, let's change the code in the AfterUpdate event to see if any record is
found at all. For testing, change the code to this:

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
If rs.NoMatch = False Then
Me.Bookmark = rs.Bookmark
MsgBox "Record found for UPC """ & Me![Combo26] & """."
Else
MsgBox "No record found for UPC """ & Me![Combo26] & """."
End If
End Sub


Try selecting a UPC from the combo box. Which message box do you see? Is the
UPC value in the message box the one that you thought you selected?

Also, post the following values for the Combo26 properties:
Bound Column
Control Source
Column Count
Column Widths

 

[Ken Snell [MVP]]

Then what ACCESS is telling you is that there is no record in Products table
with that UPC...which seems completely stupid because you're using Products
table as the source of the selection. So what I'm "guessing" right now is
that there is something else that we've not yet discussed.

What I'm now thinking is that the subform is "linked" to the main form and
that it's showing a filtered set of records based on the record in the main
form. What are the LinkMasterFields and LinkChildFields values for the
subform control (the control that holds the subform -- this control is on
the main form)? If there are values there, then the subform's recordset is
not all the records in Products but is a filtered set. But the combo box is
showing all records from Products, and it's possible that this particular
UPC value is not in any of the records that are in the subform's recordset.

Post back with your findings and then we'll go from here.
--

Ken Snell
<MS ACCESS MVP>

Ken,

I verified all information and it is correct. I chanfed the event
procedure. I chose product ACM-982185. The error message I received was
"No
record found for ACM-982185"

Bound Column 1
Control Source None
Column Count 1
Column Widths 1"

Thanks again,

Sara

OK - let me recap the info that we have so far:

(1) Combo26 is a combo box in a subform.


(2) The Row Source of Combo26 is
SELECT [Products Query].[UPC] FROM [Products Query];


(3) The subform's RecordSource is
Products Query


(4) The SQL statement for Products Query is

SELECT Products.Items, Products.Unit, Products.[FOB FCD], Products.[FOB
QD],
Products.DeliveredFCD, Products.DeliveredQD, Products.[40'QTY(pcs)],
Products.GrossWtLb, Products.[Cube ft], Products.MiniOrderQty,
Products.UpdateDate, Products.[Quote Number], Products.UPC, Products.UPC
FROM Products;


(5) UPC field is a text field.


(6) The code that runs on the AfterUpdate event of Combo26 is

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
End Sub


(7) When a UPC value is selected in the combo box, nothing happens. The
rest
of the text boxes on the subform do not populate with any information.


Now, let's change the code in the AfterUpdate event to see if any record
is
found at all. For testing, change the code to this:

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
If rs.NoMatch = False Then
Me.Bookmark = rs.Bookmark
MsgBox "Record found for UPC """ & Me![Combo26] & """."
Else
MsgBox "No record found for UPC """ & Me![Combo26] & """."
End If
End Sub


Try selecting a UPC from the combo box. Which message box do you see? Is
the
UPC value in the message box the one that you thought you selected?

Also, post the following values for the Combo26 properties:
Bound Column
Control Source
Column Count
Column Widths

 

[Guest]

Ken

Child and Parent Fields are Quote Number.

FYI-I removed the parent/child fields and now my subform shows all records.

Are we getting somewhere?

Then what ACCESS is telling you is that there is no record in Products table
with that UPC...which seems completely stupid because you're using Products
table as the source of the selection. So what I'm "guessing" right now is
that there is something else that we've not yet discussed.

What I'm now thinking is that the subform is "linked" to the main form and
that it's showing a filtered set of records based on the record in the main
form. What are the LinkMasterFields and LinkChildFields values for the
subform control (the control that holds the subform -- this control is on
the main form)? If there are values there, then the subform's recordset is
not all the records in Products but is a filtered set. But the combo box is
showing all records from Products, and it's possible that this particular
UPC value is not in any of the records that are in the subform's recordset.

Post back with your findings and then we'll go from here.
--

Ken Snell
<MS ACCESS MVP>

Ken,

I verified all information and it is correct. I chanfed the event
procedure. I chose product ACM-982185. The error message I received was
"No
record found for ACM-982185"

Bound Column 1
Control Source None
Column Count 1
Column Widths 1"

Thanks again,

Sara

OK - let me recap the info that we have so far:

(1) Combo26 is a combo box in a subform.


(2) The Row Source of Combo26 is
SELECT [Products Query].[UPC] FROM [Products Query];


(3) The subform's RecordSource is
Products Query


(4) The SQL statement for Products Query is

SELECT Products.Items, Products.Unit, Products.[FOB FCD], Products.[FOB
QD],
Products.DeliveredFCD, Products.DeliveredQD, Products.[40'QTY(pcs)],
Products.GrossWtLb, Products.[Cube ft], Products.MiniOrderQty,
Products.UpdateDate, Products.[Quote Number], Products.UPC, Products.UPC
FROM Products;


(5) UPC field is a text field.


(6) The code that runs on the AfterUpdate event of Combo26 is

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
End Sub


(7) When a UPC value is selected in the combo box, nothing happens. The
rest
of the text boxes on the subform do not populate with any information.


Now, let's change the code in the AfterUpdate event to see if any record
is
found at all. For testing, change the code to this:

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
If rs.NoMatch = False Then
Me.Bookmark = rs.Bookmark
MsgBox "Record found for UPC """ & Me![Combo26] & """."
Else
MsgBox "No record found for UPC """ & Me![Combo26] & """."
End If
End Sub


Try selecting a UPC from the combo box. Which message box do you see? Is
the
UPC value in the message box the one that you thought you selected?

Also, post the following values for the Combo26 properties:
Bound Column
Control Source
Column Count
Column Widths

 

[Ken Snell [MVP]]

Most likely. Does the combo box work as desired now?

What is the record source of the main form?
--

Ken Snell
<MS ACCESS MVP>

Ken

Child and Parent Fields are Quote Number.

FYI-I removed the parent/child fields and now my subform shows all
records.

Are we getting somewhere?

Then what ACCESS is telling you is that there is no record in Products
table
with that UPC...which seems completely stupid because you're using
Products
table as the source of the selection. So what I'm "guessing" right now is
that there is something else that we've not yet discussed.

What I'm now thinking is that the subform is "linked" to the main form
and
that it's showing a filtered set of records based on the record in the
main
form. What are the LinkMasterFields and LinkChildFields values for the
subform control (the control that holds the subform -- this control is on
the main form)? If there are values there, then the subform's recordset
is
not all the records in Products but is a filtered set. But the combo box
is
showing all records from Products, and it's possible that this particular
UPC value is not in any of the records that are in the subform's
recordset.

Post back with your findings and then we'll go from here.
--

Ken Snell
<MS ACCESS MVP>

Ken,

I verified all information and it is correct. I chanfed the event
procedure. I chose product ACM-982185. The error message I received
was
"No
record found for ACM-982185"

Bound Column 1
Control Source None
Column Count 1
Column Widths 1"

Thanks again,

Sara

:

OK - let me recap the info that we have so far:

(1) Combo26 is a combo box in a subform.


(2) The Row Source of Combo26 is
SELECT [Products Query].[UPC] FROM [Products Query];


(3) The subform's RecordSource is
Products Query


(4) The SQL statement for Products Query is

SELECT Products.Items, Products.Unit, Products.[FOB FCD],
Products.[FOB
QD],
Products.DeliveredFCD, Products.DeliveredQD, Products.[40'QTY(pcs)],
Products.GrossWtLb, Products.[Cube ft], Products.MiniOrderQty,
Products.UpdateDate, Products.[Quote Number], Products.UPC,
Products.UPC
FROM Products;


(5) UPC field is a text field.


(6) The code that runs on the AfterUpdate event of Combo26 is

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
End Sub


(7) When a UPC value is selected in the combo box, nothing happens.
The
rest
of the text boxes on the subform do not populate with any information.


Now, let's change the code in the AfterUpdate event to see if any
record
is
found at all. For testing, change the code to this:

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
If rs.NoMatch = False Then
Me.Bookmark = rs.Bookmark
MsgBox "Record found for UPC """ & Me![Combo26] & """."
Else
MsgBox "No record found for UPC """ & Me![Combo26] & """."
End If
End Sub


Try selecting a UPC from the combo box. Which message box do you see?
Is
the
UPC value in the message box the one that you thought you selected?

Also, post the following values for the Combo26 properties:
Bound Column
Control Source
Column Count
Column Widths

 

[Guest]

Ken,

The record Source for the main form is Quote.

When I open the form, the subform shows all records from my products query.
However, the UPC is blank. When I choose the UPC from the combo box, it
places that UPC in every record.

Sara

Most likely. Does the combo box work as desired now?

What is the record source of the main form?
--

Ken Snell
<MS ACCESS MVP>

Ken

Child and Parent Fields are Quote Number.

FYI-I removed the parent/child fields and now my subform shows all
records.

Are we getting somewhere?

Then what ACCESS is telling you is that there is no record in Products
table
with that UPC...which seems completely stupid because you're using
Products
table as the source of the selection. So what I'm "guessing" right now is
that there is something else that we've not yet discussed.

What I'm now thinking is that the subform is "linked" to the main form
and
that it's showing a filtered set of records based on the record in the
main
form. What are the LinkMasterFields and LinkChildFields values for the
subform control (the control that holds the subform -- this control is on
the main form)? If there are values there, then the subform's recordset
is
not all the records in Products but is a filtered set. But the combo box
is
showing all records from Products, and it's possible that this particular
UPC value is not in any of the records that are in the subform's
recordset.

Post back with your findings and then we'll go from here.
--

Ken Snell
<MS ACCESS MVP>



Ken,

I verified all information and it is correct. I chanfed the event
procedure. I chose product ACM-982185. The error message I received
was
"No
record found for ACM-982185"

Bound Column 1
Control Source None
Column Count 1
Column Widths 1"

Thanks again,

Sara

:

OK - let me recap the info that we have so far:

(1) Combo26 is a combo box in a subform.


(2) The Row Source of Combo26 is
SELECT [Products Query].[UPC] FROM [Products Query];


(3) The subform's RecordSource is
Products Query


(4) The SQL statement for Products Query is

SELECT Products.Items, Products.Unit, Products.[FOB FCD],
Products.[FOB
QD],
Products.DeliveredFCD, Products.DeliveredQD, Products.[40'QTY(pcs)],
Products.GrossWtLb, Products.[Cube ft], Products.MiniOrderQty,
Products.UpdateDate, Products.[Quote Number], Products.UPC,
Products.UPC
FROM Products;


(5) UPC field is a text field.


(6) The code that runs on the AfterUpdate event of Combo26 is

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
End Sub


(7) When a UPC value is selected in the combo box, nothing happens.
The
rest
of the text boxes on the subform do not populate with any information.


Now, let's change the code in the AfterUpdate event to see if any
record
is
found at all. For testing, change the code to this:

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
If rs.NoMatch = False Then
Me.Bookmark = rs.Bookmark
MsgBox "Record found for UPC """ & Me![Combo26] & """."
Else
MsgBox "No record found for UPC """ & Me![Combo26] & """."
End If
End Sub


Try selecting a UPC from the combo box. Which message box do you see?
Is
the
UPC value in the message box the one that you thought you selected?

Also, post the following values for the Combo26 properties:
Bound Column
Control Source
Column Count
Column Widths

 

[Ken Snell [MVP]]

The combo box should be in the "form header" section of the subform, not in
the Detail section.

--

Ken Snell
<MS ACCESS MVP>

Ken,

The record Source for the main form is Quote.

When I open the form, the subform shows all records from my products
query.
However, the UPC is blank. When I choose the UPC from the combo box, it
places that UPC in every record.

Sara

Most likely. Does the combo box work as desired now?

What is the record source of the main form?
--

Ken Snell
<MS ACCESS MVP>

Ken

Child and Parent Fields are Quote Number.

FYI-I removed the parent/child fields and now my subform shows all
records.

Are we getting somewhere?

:

Then what ACCESS is telling you is that there is no record in Products
table
with that UPC...which seems completely stupid because you're using
Products
table as the source of the selection. So what I'm "guessing" right now
is
that there is something else that we've not yet discussed.

What I'm now thinking is that the subform is "linked" to the main form
and
that it's showing a filtered set of records based on the record in the
main
form. What are the LinkMasterFields and LinkChildFields values for the
subform control (the control that holds the subform -- this control is
on
the main form)? If there are values there, then the subform's
recordset
is
not all the records in Products but is a filtered set. But the combo
box
is
showing all records from Products, and it's possible that this
particular
UPC value is not in any of the records that are in the subform's
recordset.

Post back with your findings and then we'll go from here.
--

Ken Snell
<MS ACCESS MVP>



Ken,

I verified all information and it is correct. I chanfed the event
procedure. I chose product ACM-982185. The error message I
received
was
"No
record found for ACM-982185"

Bound Column 1
Control Source None
Column Count 1
Column Widths 1"

Thanks again,

Sara

:

OK - let me recap the info that we have so far:

(1) Combo26 is a combo box in a subform.


(2) The Row Source of Combo26 is
SELECT [Products Query].[UPC] FROM [Products Query];


(3) The subform's RecordSource is
Products Query


(4) The SQL statement for Products Query is

SELECT Products.Items, Products.Unit, Products.[FOB FCD],
Products.[FOB
QD],
Products.DeliveredFCD, Products.DeliveredQD,
Products.[40'QTY(pcs)],
Products.GrossWtLb, Products.[Cube ft], Products.MiniOrderQty,
Products.UpdateDate, Products.[Quote Number], Products.UPC,
Products.UPC
FROM Products;


(5) UPC field is a text field.


(6) The code that runs on the AfterUpdate event of Combo26 is

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark
End Sub


(7) When a UPC value is selected in the combo box, nothing happens.
The
rest
of the text boxes on the subform do not populate with any
information.


Now, let's change the code in the AfterUpdate event to see if any
record
is
found at all. For testing, change the code to this:

Private Sub Combo26_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[UPC] = '" & Me![Combo26] & "'"
If rs.NoMatch = False Then
Me.Bookmark = rs.Bookmark
MsgBox "Record found for UPC """ & Me![Combo26] & """."
Else
MsgBox "No record found for UPC """ & Me![Combo26] & """."
End If
End Sub


Try selecting a UPC from the combo box. Which message box do you
see?
Is
the
UPC value in the message box the one that you thought you selected?

Also, post the following values for the Combo26 properties:
Bound Column
Control Source
Column Count
Column Widths