Runtime Error 3021

[Ken Snell [MVP]]

If your subform is a continuous forms view, what do you want to use the
combo box for? The way you're coding it, it's to be used as a "navigation"
tool, moving the subform to the desired record. With a continuous forms
view, it will still work that way, but by scrolling the subform to the
desired record.

--

Ken Snell
<MS ACCESS 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

 

[Guest]

Ken,

My goal is to create quotes for customers. Each quote would have a unique
quote number. I have over 11,000 products to choose from.

I would like to enter all the quote information (Customer, Date, Rep,
Number, etc.) and from there choose the products I would like to quote. I am
going to base the customer's price on the price I received from my
manufacturing facility.

After choosing all products for this quote, I will export to Excel and send
to my customer.

I have moved the combo box to the form header. But now in datasheet view I
can't see it.

Sara

If your subform is a continuous forms view, what do you want to use the
combo box for? The way you're coding it, it's to be used as a "navigation"
tool, moving the subform to the desired record. With a continuous forms
view, it will still work that way, but by scrolling the subform to the
desired record.

--

Ken Snell
<MS ACCESS 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

 

[Ken Snell [MVP]]

OK - let's back up. You want to use the combo box to select a specific part
to be put into a quote? Then we're taking the wrong approach entirely here.

So what you want is to be able to add a part to a new record that is a
detail of a quote? And the combo box should show you all the available parts
that you can select from? Are you storing the part's UPC in the quote table?
The subform is not based on any query that appears to be related to a
specific quote?

I'm sorry, I am willing to help you get to a solution, but it appears that I
have had the wrong understanding of what your form is designed to do.

So, can we back up all the way to the beginning? Tell me about the main form
(and its RecordSource). Tell me what the purpose of the subform is. And how
you want to use the form and subform.
--

Ken Snell
<MS ACCESS MVP>

Ken,

My goal is to create quotes for customers. Each quote would have a unique
quote number. I have over 11,000 products to choose from.

I would like to enter all the quote information (Customer, Date, Rep,
Number, etc.) and from there choose the products I would like to quote. I
am
going to base the customer's price on the price I received from my
manufacturing facility.

After choosing all products for this quote, I will export to Excel and
send
to my customer.

I have moved the combo box to the form header. But now in datasheet view
I
can't see it.

Sara

If your subform is a continuous forms view, what do you want to use the
combo box for? The way you're coding it, it's to be used as a
"navigation"
tool, moving the subform to the desired record. With a continuous forms
view, it will still work that way, but by scrolling the subform to the
desired record.

--

Ken Snell
<MS ACCESS 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>



message
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