Not In RecordSet ???????

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

Guest

I think this is a relationship issue!!

I have one table (t_customers) with years of data.
I needed to create a form linked to that "t_customers".

So, I created a new table, then a query with both tables, then the new form.
On the original form, I coded it with a button to open the new from, linking
the Key field and displaying that field - This part works fine but I cannot
update/edit the rest of the new data on the new form.

I have tried every relationship on the query using the key field from either
table..

What else is there??????

The error at the bottom is "Cannot add record(s) primary key for table
t_CHECK not in recordset"
 
First make sure that both tables are joined in the Relationship window with
referiental integrity enabled.

Then make a form out of the primary table. Next put a subform based on the
second table on the first form. With RI enabled, the wizard should easily
figure out how to properly maintain the link between the two tables.
 
Post the SQL statement of the form's Record Source property. Is there a
reason you created a new table instead of just using the original table?
 
1. When I try to create a relationship the "Enforce Referiental Integrity" is
disabled-shaded out I cannot change it.

2. I don't want to create a subform on the main form - I need a new form to
popup - which it does and the code I created on the button autopopulates the
key field. I just need to update the rest of the data....

Suggestions??
 
The reason I created a new table is because the information is completely
seperate.
Here is the story: This database is many years old, users are all
throughout the State of Florida. I needed to add a form to produce a check
request. There is a lot of information that needs to be on this form. So, I
created the table with all the details for the check request. Then I created
the form. Then I created a button from the original form which will open the
check request form linked by the Key.

This all works fine except when I try to enter data. The key field shows
the correct record but the other fields do not allow input/edit...

SELECT t_DataTracking.ICNSR, t_CheckREQ.ChkREQ, t_CheckREQ.BCBSF,
t_CheckREQ.FCSO, t_CheckREQ.INCP, t_CheckREQ.FCL, t_CheckREQ.COI,
t_CheckREQ.FCIA, t_CheckREQ.NAVIGY, t_CheckREQ.BusinessUnit, t_CheckREQ.Date,
t_CheckREQ.CHGmonetery, t_CheckREQ.CHGacct, t_CheckREQ.CHGcc,
t_CheckREQ.CHGrecipient, t_CheckREQ.CHGaffillate, t_CheckREQ.CHGproduct,
t_CheckREQ.CHGmanacct, t_CheckREQ.CHGmanhour, t_CheckREQ.Total,
t_CheckREQ.Details, t_CheckREQ.SpecialBank, t_CheckREQ.SpecialHandling,
t_CheckREQ.OverNight, t_CheckREQ.PickUP, t_CheckREQ.SendCheck,
t_CheckREQ.AddComments, t_CheckREQ.thecosts, t_CheckREQ.Requestor,
t_CheckREQ.RequestorPhone, t_CheckREQ.Signature, t_CheckREQ.Department,
t_CheckREQ.ApprovedBy, t_CheckREQ.Asignature, t_CheckREQ.Title,
t_CheckREQ.Adepartment, t_CheckREQ.EMP, t_CheckREQ.President, t_CheckREQ.Pdate
FROM t_CheckREQ RIGHT JOIN t_DataTracking ON t_CheckREQ.ICNSR =
t_DataTracking.ICNSR;
 
Which field in the "t_CheckREQ" table is the primary key? Make sure that it
is in the query's field list.

Also, you're using a RIGHT JOIN in the query, which is unusual for this use
of the query (if I'm understanding your setup correctly). RIGHT JOIN means
that the query will return all records from the "t_DataTracking" table and
only matching records from the "t_CheckREQ" table (which I believe is the
new table?). More likely, you want to use INNER JOIN.
 
The Key is ICNSR - yes it's in the query.

The right join is the only way the key field populates on the new form:
This simple code on my button opens the new form and displays the key field
related to the original form.. The other two joins also do not allow me to
enter data except in the key field, which I do not what to enter the data....

What am I missing????

stLinkCriteria = "[ICNSR]=" & "'" & Me![ICNSR] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
 
I think you misunderstood my question. I asked what the primary key field is
in the "t_CheckREQ" table. You gave me the primary key field for the other
table. You must have the primary key field from the "t_CheckREQ" table in
the query -- this would be t_CheckREQ.ICNSR , correct?

Add the t_CheckREQ.ICNSR field to the query's list of fields in the SELECT
clause.

And, quite honestly, I'm not sure why you need to join the two tables for
your form's desired operation. Instead, what I'd suggest is that you use a
form/subform setup -- have the main form's RecordSource query be this:

SELECT t_DataTracking.ICNSR FROM t_DataTracking ORDER BY
t_DataTracking.ICNSR;


Then have the subform use this query as its RecordSource:

SELECT t_DataTracking.ICNSR, t_CheckREQ.ChkREQ, t_CheckREQ.BCBSF,
t_CheckREQ.FCSO, t_CheckREQ.INCP, t_CheckREQ.FCL, t_CheckREQ.COI,
t_CheckREQ.FCIA, t_CheckREQ.NAVIGY, t_CheckREQ.BusinessUnit,
t_CheckREQ.Date,
t_CheckREQ.CHGmonetery, t_CheckREQ.CHGacct, t_CheckREQ.CHGcc,
t_CheckREQ.CHGrecipient, t_CheckREQ.CHGaffillate, t_CheckREQ.CHGproduct,
t_CheckREQ.CHGmanacct, t_CheckREQ.CHGmanhour, t_CheckREQ.Total,
t_CheckREQ.Details, t_CheckREQ.SpecialBank, t_CheckREQ.SpecialHandling,
t_CheckREQ.OverNight, t_CheckREQ.PickUP, t_CheckREQ.SendCheck,
t_CheckREQ.AddComments, t_CheckREQ.thecosts, t_CheckREQ.Requestor,
t_CheckREQ.RequestorPhone, t_CheckREQ.Signature, t_CheckREQ.Department,
t_CheckREQ.ApprovedBy, t_CheckREQ.Asignature, t_CheckREQ.Title,
t_CheckREQ.Adepartment, t_CheckREQ.EMP, t_CheckREQ.President,
t_CheckREQ.Pdate
FROM t_CheckREQ;


Then use [ICNSR] as the field in the LinkChildFields and LinkMasterFields
properties of the subform control (the control on main form that holds the
subform object). The subform then will track with the ICNSR value that you
have showing in the main form, and the ICNSR value in the subform will be
filled in by ACCESS with the value from the main form.
--

Ken Snell
<MS ACCESS MVP>





Dan @BCBS said:
The Key is ICNSR - yes it's in the query.

The right join is the only way the key field populates on the new form:
This simple code on my button opens the new form and displays the key
field
related to the original form.. The other two joins also do not allow me
to
enter data except in the key field, which I do not what to enter the
data....

What am I missing????

stLinkCriteria = "[ICNSR]=" & "'" & Me![ICNSR] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria



Ken Snell (MVP) said:
Which field in the "t_CheckREQ" table is the primary key? Make sure that
it
is in the query's field list.

Also, you're using a RIGHT JOIN in the query, which is unusual for this
use
of the query (if I'm understanding your setup correctly). RIGHT JOIN
means
that the query will return all records from the "t_DataTracking" table
and
only matching records from the "t_CheckREQ" table (which I believe is the
new table?). More likely, you want to use INNER JOIN.
Click to expand...
Click to expand...
 
1. The Key field has the same name in both tables.
2. If I chang ethe RecordSource in the main form that completly changes the
source of the form and all the data for every case is lost.
3. the code you suggested for the sub form is identical to what it has.

** Your suggestion to use a sub form is the hit **
Although I cannot add a sub form directly on the main form, I created a new
from with the same table the main form uses. Then just made a subform on it.
My button on the mainform links the forms by the key field with
stLinkCriteria = "[ICNSR]=" & "'" & Me![ICNSR] & "'"

Thank you for the help..




Ken Snell (MVP) said:
I think you misunderstood my question. I asked what the primary key field is
in the "t_CheckREQ" table. You gave me the primary key field for the other
table. You must have the primary key field from the "t_CheckREQ" table in
the query -- this would be t_CheckREQ.ICNSR , correct?

Add the t_CheckREQ.ICNSR field to the query's list of fields in the SELECT
clause.

And, quite honestly, I'm not sure why you need to join the two tables for
your form's desired operation. Instead, what I'd suggest is that you use a
form/subform setup -- have the main form's RecordSource query be this:

SELECT t_DataTracking.ICNSR FROM t_DataTracking ORDER BY
t_DataTracking.ICNSR;


Then have the subform use this query as its RecordSource:

SELECT t_DataTracking.ICNSR, t_CheckREQ.ChkREQ, t_CheckREQ.BCBSF,
t_CheckREQ.FCSO, t_CheckREQ.INCP, t_CheckREQ.FCL, t_CheckREQ.COI,
t_CheckREQ.FCIA, t_CheckREQ.NAVIGY, t_CheckREQ.BusinessUnit,
t_CheckREQ.Date,
t_CheckREQ.CHGmonetery, t_CheckREQ.CHGacct, t_CheckREQ.CHGcc,
t_CheckREQ.CHGrecipient, t_CheckREQ.CHGaffillate, t_CheckREQ.CHGproduct,
t_CheckREQ.CHGmanacct, t_CheckREQ.CHGmanhour, t_CheckREQ.Total,
t_CheckREQ.Details, t_CheckREQ.SpecialBank, t_CheckREQ.SpecialHandling,
t_CheckREQ.OverNight, t_CheckREQ.PickUP, t_CheckREQ.SendCheck,
t_CheckREQ.AddComments, t_CheckREQ.thecosts, t_CheckREQ.Requestor,
t_CheckREQ.RequestorPhone, t_CheckREQ.Signature, t_CheckREQ.Department,
t_CheckREQ.ApprovedBy, t_CheckREQ.Asignature, t_CheckREQ.Title,
t_CheckREQ.Adepartment, t_CheckREQ.EMP, t_CheckREQ.President,
t_CheckREQ.Pdate
FROM t_CheckREQ;


Then use [ICNSR] as the field in the LinkChildFields and LinkMasterFields
properties of the subform control (the control on main form that holds the
subform object). The subform then will track with the ICNSR value that you
have showing in the main form, and the ICNSR value in the subform will be
filled in by ACCESS with the value from the main form.
--

Ken Snell
<MS ACCESS MVP>





Dan @BCBS said:
The Key is ICNSR - yes it's in the query.

The right join is the only way the key field populates on the new form:
This simple code on my button opens the new form and displays the key
field
related to the original form.. The other two joins also do not allow me
to
enter data except in the key field, which I do not what to enter the
data....

What am I missing????

stLinkCriteria = "[ICNSR]=" & "'" & Me![ICNSR] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria



Ken Snell (MVP) said:
Which field in the "t_CheckREQ" table is the primary key? Make sure that
it
is in the query's field list.

Also, you're using a RIGHT JOIN in the query, which is unusual for this
use
of the query (if I'm understanding your setup correctly). RIGHT JOIN
means
that the query will return all records from the "t_DataTracking" table
and
only matching records from the "t_CheckREQ" table (which I believe is the
new table?). More likely, you want to use INNER JOIN.
--

Ken Snell
<MS ACCESS MVP>




The reason I created a new table is because the information is
completely
seperate.
Here is the story: This database is many years old, users are all
throughout the State of Florida. I needed to add a form to produce a
check
request. There is a lot of information that needs to be on this form.
So, I
created the table with all the details for the check request. Then I
created
the form. Then I created a button from the original form which will
open
the
check request form linked by the Key.

This all works fine except when I try to enter data. The key field
shows
the correct record but the other fields do not allow input/edit...

SELECT t_DataTracking.ICNSR, t_CheckREQ.ChkREQ, t_CheckREQ.BCBSF,
t_CheckREQ.FCSO, t_CheckREQ.INCP, t_CheckREQ.FCL, t_CheckREQ.COI,
t_CheckREQ.FCIA, t_CheckREQ.NAVIGY, t_CheckREQ.BusinessUnit,
t_CheckREQ.Date,
t_CheckREQ.CHGmonetery, t_CheckREQ.CHGacct, t_CheckREQ.CHGcc,
t_CheckREQ.CHGrecipient, t_CheckREQ.CHGaffillate,
t_CheckREQ.CHGproduct,
t_CheckREQ.CHGmanacct, t_CheckREQ.CHGmanhour, t_CheckREQ.Total,
t_CheckREQ.Details, t_CheckREQ.SpecialBank, t_CheckREQ.SpecialHandling,
t_CheckREQ.OverNight, t_CheckREQ.PickUP, t_CheckREQ.SendCheck,
t_CheckREQ.AddComments, t_CheckREQ.thecosts, t_CheckREQ.Requestor,
t_CheckREQ.RequestorPhone, t_CheckREQ.Signature, t_CheckREQ.Department,
t_CheckREQ.ApprovedBy, t_CheckREQ.Asignature, t_CheckREQ.Title,
t_CheckREQ.Adepartment, t_CheckREQ.EMP, t_CheckREQ.President,
t_CheckREQ.Pdate
FROM t_CheckREQ RIGHT JOIN t_DataTracking ON t_CheckREQ.ICNSR =
t_DataTracking.ICNSR;
Click to expand...
Click to expand...
Click to expand...
 
Comments inline...

--

Ken Snell
<MS ACCESS MVP>


Dan @BCBS said:
1. The Key field has the same name in both tables.
Click to expand...

Ok. I now see that as I review the ON clause in the original query.

2. If I chang ethe RecordSource in the main form that completly changes
the
source of the form and all the data for every case is lost.
Click to expand...

You don't need to change the RecordSource for the main form SO LONG AS it
contains the t_DataTracking.ICNSR field. You then can link the subform and
main form via the ICNSR fields, and you can use the subform's query that I
suggested. It may be that the only change you needed to your original form
was to be sure that the LinkChildFields and LinkMasterFields properties were
correct?


3. the code you suggested for the sub form is identical to what it has.
Click to expand...

I was thinking that the big query you'd originally posted was the query in
this subform?

** Your suggestion to use a sub form is the hit **
Although I cannot add a sub form directly on the main form, I created a
new
from with the same table the main form uses. Then just made a subform on
it.
My button on the mainform links the forms by the key field with
stLinkCriteria = "[ICNSR]=" & "'" & Me![ICNSR] & "'"

Thank you for the help..
Click to expand...

You're welcome. Glad you got a solution identified.
 
Back
Top