Error 3101

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am attempting to create a Combo Box in a form that links to another table
and I get an Error 3101 message (The Microsoft Jet database engine cannot
fine a record in the table <name> with key matching field(s) <name>.) Since
the Combo Box is built on the table on the "one" side of the relationship and
is being placed in a form that is built on the table on the "many" side of
the relationship, I'm confused as to why I should be getting this message. I
thought this would just look up the data on the "one" side and then store it
in the table on "many" side of the relationship.

Can anyone help?
 
Laura said:
I am attempting to create a Combo Box in a form that links to another table
and I get an Error 3101 message (The Microsoft Jet database engine cannot
fine a record in the table <name> with key matching field(s)
Click to expand...
the Combo Box is built on the table on the "one" side of the relationship and
is being placed in a form that is built on the table on the "many" side of
the relationship, I'm confused as to why I should be getting this message. I
thought this would just look up the data on the "one" side and then store it
in the table on "many" side of the relationship.

Can anyone help?
Click to expand...

Laura,

It's hard to say for sure without knowing the structure of your
tables, the SQL of the query, the Form's RecordSource, and the
Control's ControlSource.


Sincerely,

Chris O.
 
Chris,

I am creating a Sales Form with a subform. The Sales Form should read from
a Customer Table, an Inventory Table, and an Extras Table. It writes to a
Sales Table and a Sales Inventory Table.

The pertinent table structures are as follows:

Sales Inventory Table: (the first two fields comprise a composite primary key)
InvoiceNo
InventoryNo
QuantityOrdered
InventoryPrice
ExtrasID
ExtrasDescription


Extras Table: (the first field is the primary key)
ExtrasID
ExtrasDescription
ExtrasCost
ExtrasPrice

I have a Form with a subform built using the wizard. The form includes
fields from the Sales Table and the Customer Table and has no problem. The
subform includes fields from the Sales Inventory Table, the Inventory Table
and the Extras Table. (I can enter an InventoryID and I get data from the
Inventory table with no problem, so I have not included the structure for
that table above.) I am trying to build a Combo Box in the SubForm to bring
in the ExtrasDescription and ExtrasPrice fields and this is where I am
encountering the Error message. The SQL for the Combo Box reads as follows:

SELECT [Extras Table].ExtrasID, [Extras Table].ExtrasDescription, [Extras
Table].ExtrasPrice FROM [Extras Table] ORDER BY [ExtrasDescription];

The subform's RecordSource reads as follows:
SELECT [Sales Inventory Table].InventoryID, [Sales Inventory
Table].QuantityOrdered, [Sales Inventory Table].ExtrasID, [Inventory
Table].InventoryDescription, [Inventory Table].InventoryPrice, [Extras
Table].ExtrasDescription, [Extras Table].ExtrasPrice, [Sales Inventory
Table].InvoiceNo FROM [Inventory Table] INNER JOIN ([Extras Table] INNER JOIN
[Sales Inventory Table] ON [Extras Table].ExtrasID=[Sales Inventory
Table].ExtrasID) ON [Inventory Table].InventoryID=[Sales Inventory
Table].InventoryID;


And the ControlSource for the ComboBox in the subform is ExtrasDescription.

Laura
 
Laura said:
Chris,

I am creating a Sales Form with a subform. The Sales Form should read from
a Customer Table, an Inventory Table, and an Extras Table. It writes to a
Sales Table and a Sales Inventory Table.

The pertinent table structures are as follows:

Sales Inventory Table: (the first two fields comprise a composite primary key)
InvoiceNo
InventoryNo
QuantityOrdered
InventoryPrice
ExtrasID
ExtrasDescription


Extras Table: (the first field is the primary key)
ExtrasID
ExtrasDescription
ExtrasCost
ExtrasPrice

I have a Form with a subform built using the wizard. The form includes
fields from the Sales Table and the Customer Table and has no problem. The
subform includes fields from the Sales Inventory Table, the Inventory Table
and the Extras Table. (I can enter an InventoryID and I get data from the
Inventory table with no problem, so I have not included the structure for
that table above.) I am trying to build a Combo Box in the SubForm to bring
in the ExtrasDescription and ExtrasPrice fields and this is where I am
encountering the Error message. The SQL for the Combo Box reads as follows:
Click to expand...
And the ControlSource for the ComboBox in the subform is ExtrasDescription.

Laura
Click to expand...

Laura,

The structure of [Inventory Table] seems to be missing.

I reproduce the queries here:

SELECT [Extras Table].ExtrasID
,[Extras Table].ExtrasDescription
,[Extras Table].ExtrasPrice
FROM [Extras Table]
ORDER BY [ExtrasDescription];

The subform's RecordSource reads as follows:
SELECT [Sales Inventory Table].InventoryID
,[Sales Inventory Table].QuantityOrdered
,[Sales Inventory Table].ExtrasID
,[Inventory Table].InventoryDescription
,[Inventory Table].InventoryPrice
,[Extras Table].ExtrasDescription
,[Extras Table].ExtrasPrice
,[Sales Inventory Table].InvoiceNo
FROM [Inventory Table]
INNER JOIN
([Extras Table]
INNER JOIN
[Sales Inventory Table]
ON [Extras Table].ExtrasID =
[Sales Inventory Table].ExtrasID)
ON [Inventory Table].InventoryID =
[Sales Inventory Table].InventoryID;


Hmm. I can't see anything obvious.

What is the RowSource and BoundColumn of the combo box?


Sincerely,

Chris O.
 
Chris,

As I said, I left out the structure for the Inventory Table because I don't
believe that's the problem but I'll insert it here.

Inventory Table: (first field is primary key)
InventoryID
InventoryDescription
InventoryCost
InventoryPrice

The Combo Box RowSource is:
SELECT [Extras Table].ExtrasID, [Extras Table].ExtrasDescription, [Extras
Table].ExtrasPrice FROM [Extras Table] ORDER BY [ExtrasDescription];

The Bound Column is 1

Laura


Chris2 said:
Laura said:
Chris,

I am creating a Sales Form with a subform. The Sales Form should read from
a Customer Table, an Inventory Table, and an Extras Table. It writes to a
Sales Table and a Sales Inventory Table.

The pertinent table structures are as follows:

Sales Inventory Table: (the first two fields comprise a composite primary key)
InvoiceNo
InventoryNo
QuantityOrdered
InventoryPrice
ExtrasID
ExtrasDescription


Extras Table: (the first field is the primary key)
ExtrasID
ExtrasDescription
ExtrasCost
ExtrasPrice

I have a Form with a subform built using the wizard. The form includes
fields from the Sales Table and the Customer Table and has no problem. The
subform includes fields from the Sales Inventory Table, the Inventory Table
and the Extras Table. (I can enter an InventoryID and I get data from the
Inventory table with no problem, so I have not included the structure for
that table above.) I am trying to build a Combo Box in the SubForm to bring
in the ExtrasDescription and ExtrasPrice fields and this is where I am
encountering the Error message. The SQL for the Combo Box reads as follows:
Click to expand...
And the ControlSource for the ComboBox in the subform is ExtrasDescription.

Laura
Click to expand...

Laura,

The structure of [Inventory Table] seems to be missing.

I reproduce the queries here:

SELECT [Extras Table].ExtrasID
,[Extras Table].ExtrasDescription
,[Extras Table].ExtrasPrice
FROM [Extras Table]
ORDER BY [ExtrasDescription];

The subform's RecordSource reads as follows:
SELECT [Sales Inventory Table].InventoryID
,[Sales Inventory Table].QuantityOrdered
,[Sales Inventory Table].ExtrasID
,[Inventory Table].InventoryDescription
,[Inventory Table].InventoryPrice
,[Extras Table].ExtrasDescription
,[Extras Table].ExtrasPrice
,[Sales Inventory Table].InvoiceNo
FROM [Inventory Table]
INNER JOIN
([Extras Table]
INNER JOIN
[Sales Inventory Table]
ON [Extras Table].ExtrasID =
[Sales Inventory Table].ExtrasID)
ON [Inventory Table].InventoryID =
[Sales Inventory Table].InventoryID;


Hmm. I can't see anything obvious.

What is the RowSource and BoundColumn of the combo box?


Sincerely,

Chris O.
Click to expand...
 
Laura said:
Chris,

As I said, I left out the structure for the Inventory Table because I don't
believe that's the problem but I'll insert it here.

Inventory Table: (first field is primary key)
InventoryID
InventoryDescription
InventoryCost
InventoryPrice

The Combo Box RowSource is:
SELECT [Extras Table].ExtrasID, [Extras Table].ExtrasDescription, [Extras
Table].ExtrasPrice FROM [Extras Table] ORDER BY [ExtrasDescription];
Click to expand...

My apologies, as I already asked for that.
The Bound Column is 1
Click to expand...

Oh, I realize now why I asked for the RowSource again. From your last
post: "And the ControlSource for the ComboBox in the subform is
ExtrasDescription."

How can the combo box both have a ControlSource and a RowSource? That
would make it both Bound and Unbound (I don't think that can happen,
though I'm not sure).


Sincerely,

Chris O.
 
Chris,

The light dawned! I fixed the problem. I had the Control Source for the
Combo Box set to Extras Description rather than Extras ID. It works now.
Thanks for your help.

Laura

Chris2 said:
Laura said:
Chris,

As I said, I left out the structure for the Inventory Table because I don't
believe that's the problem but I'll insert it here.

Inventory Table: (first field is primary key)
InventoryID
InventoryDescription
InventoryCost
InventoryPrice

The Combo Box RowSource is:
SELECT [Extras Table].ExtrasID, [Extras Table].ExtrasDescription, [Extras
Table].ExtrasPrice FROM [Extras Table] ORDER BY [ExtrasDescription];
Click to expand...

My apologies, as I already asked for that.
The Bound Column is 1
Click to expand...

Oh, I realize now why I asked for the RowSource again. From your last
post: "And the ControlSource for the ComboBox in the subform is
ExtrasDescription."

How can the combo box both have a ControlSource and a RowSource? That
would make it both Bound and Unbound (I don't think that can happen,
though I'm not sure).


Sincerely,

Chris O.
Click to expand...
 
How can the combo box both have a ControlSource and a RowSource? That
would make it both Bound and Unbound (I don't think that can happen,
though I'm not sure).
Click to expand...

the RowSource has nothing to do with whether a combo box control is bound or
unbound. if the control's ControlSource is a field in the form's underlying
table/query/SQL statement, then the control is bound [to that field].
otherwise, the control is unbound [not bound to a field in the form's
underlying table/query/SQL statement]. the RowSource simply dictates where
Access gets the values for the combo box's "droplist".

hth
 
answers inline.

Chris2 said:
tina said:
How can the combo box both have a ControlSource and a RowSource? That
would make it both Bound and Unbound (I don't think that can happen,
though I'm not sure).
Click to expand...

the RowSource has nothing to do with whether a combo box control is bound or
unbound. if the control's ControlSource is a field in the form's underlying
table/query/SQL statement, then the control is bound [to that
Click to expand...
field].

The RecordSource of the Form?
Click to expand...

correct. the ControlSource of a bound control on a form is set to the name
of a field in the form's RecordSource, which is a table, or a query, or a
SQL statement.
otherwise, the control is unbound [not bound to a field in the form's
underlying table/query/SQL statement].

the RowSource simply dictates where
Access gets the values for the combo box's "droplist".
Click to expand...

But that is also what the ControlSource does, it takes the list
directly from the named column in the Form's RecordSource.
Click to expand...

no, a combobox control's ControlSource property does not populate the
control's droplist. the ControlSource supplies the "reference" that tells
Access what field's value will be displayed in that control, for the current
record in the RecordSource of the form.

a combobox control's RowSource tells Access where to get the values that
will be displayed in the control's droplist, and when the RowSourceType is
Table/Query it may also tell Access how the filter and/or sort the records.
basically, that's all it does. if the combobox's LimitToList property is set
to No, then the value entered in the field does not even have to match any
values on the list.
Can you please explain the behavior of the combo box when using
both?
Click to expand...

i can't think of a way to explain it other than above. if it's unclear, read
up on the ControlSource topic and the RowSource topic in Access Help, that
may help. or maybe somebody else will pitch in to help clarify.

hth
 
tina said:
How can the combo box both have a ControlSource and a RowSource? That
would make it both Bound and Unbound (I don't think that can happen,
though I'm not sure).
Click to expand...

the RowSource has nothing to do with whether a combo box control is bound or
unbound. if the control's ControlSource is a field in the form's underlying
table/query/SQL statement, then the control is bound [to that
Click to expand...
field].

The RecordSource of the Form?
otherwise, the control is unbound [not bound to a field in the form's
underlying table/query/SQL statement].

the RowSource simply dictates where
Access gets the values for the combo box's "droplist".
Click to expand...

But that is also what the ControlSource does, it takes the list
directly from the named column in the Form's RecordSource.

Can you please explain the behavior of the combo box when using
both?


Sincerely,

Chris O.
 
Back
Top