Record set not updatable

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

Guest

Please help me with this problem, after linking 2 tables I am no longer able
to update the recordset in a form:

Table 1: I've set up an option group that stores the value in a field named
'Reason for Return'.
Table 2: I have 2 columns - 'Reason for Return' and 'Description'.

In my form, when I use the field 'Reason for Return' from Table 1, I get the
correct information and am able to add new records, however when I try to
link the 2 tables so that I can add the 'Description' field to the form, the
recordset becomes un-updatable.

What am I doing wrong? Thanks for any suggestions.
 
DianeD said:
Please help me with this problem, after linking 2 tables I am no
longer able to update the recordset in a form:

Table 1: I've set up an option group that stores the value in a
field named 'Reason for Return'.
Table 2: I have 2 columns - 'Reason for Return' and 'Description'.

In my form, when I use the field 'Reason for Return' from Table 1, I
get the correct information and am able to add new records, however
when I try to link the 2 tables so that I can add the 'Description'
field to the form, the recordset becomes un-updatable.

What am I doing wrong? Thanks for any suggestions.
Click to expand...

Please post the SQL of the form's recordsource query.
 
SELECT [2005 RETURNS_MAIN TABLE].*, [CUST_ADDRMSTR TABLE].CUST_NAME, [2005
RETURNS_PRODUCT].*, [REASON FOR RETURN_RA DATA].Description
FROM [REASON FOR RETURN_RA DATA] RIGHT JOIN (([2005 RETURNS_MAIN TABLE] LEFT
JOIN [CUST_ADDRMSTR TABLE] ON [2005 RETURNS_MAIN TABLE].CUST_ID =
[CUST_ADDRMSTR TABLE].CUST_ID) LEFT JOIN [2005 RETURNS_PRODUCT] ON [2005
RETURNS_MAIN TABLE].[LOG NO (RA#)] = [2005 RETURNS_PRODUCT].[Main Report
Link]) ON [REASON FOR RETURN_RA DATA].[Reason for Return] = [2005
RETURNS_MAIN TABLE].[Reason for return];
 
Please help me with this problem, after linking 2 tables I am no longer able
to update the recordset in a form:

Table 1: I've set up an option group that stores the value in a field named
'Reason for Return'.
Table 2: I have 2 columns - 'Reason for Return' and 'Description'.

In my form, when I use the field 'Reason for Return' from Table 1, I get the
correct information and am able to add new records, however when I try to
link the 2 tables so that I can add the 'Description' field to the form, the
recordset becomes un-updatable.

What am I doing wrong? Thanks for any suggestions.
Click to expand...

Is the "Reason for Return" the Primary Key of Table2? If not, that
would cause this problem. Also, if you have <yuck, PTOOIE!> Lookup
fields in your table, you'll have similar problems.

You may also want to consider basing the Form simply on Table1 and
using a Combo Box bound to Reason for Return, based on Table2, and
displaying the Description.

John W. Vinson[MVP]
 
DianeD said:
SELECT [2005 RETURNS_MAIN TABLE].*, [CUST_ADDRMSTR TABLE].CUST_NAME,
[2005 RETURNS_PRODUCT].*, [REASON FOR RETURN_RA DATA].Description
FROM [REASON FOR RETURN_RA DATA] RIGHT JOIN (([2005 RETURNS_MAIN
TABLE] LEFT JOIN [CUST_ADDRMSTR TABLE] ON [2005 RETURNS_MAIN
TABLE].CUST_ID = [CUST_ADDRMSTR TABLE].CUST_ID) LEFT JOIN [2005
RETURNS_PRODUCT] ON [2005 RETURNS_MAIN TABLE].[LOG NO (RA#)] = [2005
RETURNS_PRODUCT].[Main Report Link]) ON [REASON FOR RETURN_RA
DATA].[Reason for Return] = [2005 RETURNS_MAIN TABLE].[Reason for
return];
Click to expand...

Thats a fairly complex join for an updatable query. I'm not sure of the
relationships involved, but you may want to try the following
adjustments:

1. Include the fields

[REASON FOR RETURN_RA DATA].[Reason for Return]

and

[CUST_ADDRMSTR TABLE].CUST_ID

in the fields selected by the query. You don't need to have any
controls bound to them on the form.

2. Make sure that [REASON FOR RETURN_RA DATA].[Reason for Return] is
defined as the primary key of table [REASON FOR RETURN_RA DATA].

3. If none of that helps, try setting the form's RecordsetType property
to "Dynaset (Inconsistent Updates)".

As a suggestion for the future, your life as a database developer will
be a lot easier if you don't include spaces in your table and field
names. :-)
 
As soon as I set the primary key on Reason for Return field in Table 2
everything worked perfectly. Thank you so much!
 
Back
Top