Cannot add record; join key table “xxx” not in recordset (1 Viewer)

N

Nano

Registered User.
Local time
Today, 13:41
Joined
May 14, 2012
Messages
91
So I am back for my seemingly weekly programing problem, I have the order confirmation form with a query subform showing the items in the order finally worki9ng however I have a new problem.

On my original data entry from with a subform that appears as a datasheet to input the items for the order/quote whenever I add a new product to the order it only displays the first item. Every item I add to the order after that doesn’t display, however it does show up on the query on the order confirmation form showing that the database does save the additional entries.

I have made a backup copy of the database and have tried to replace the subform with a new Query based subform using the same query I used in the order conformation form. The data does display then, however not I can’t edit the subform. I get a message “Cannot add record; join key table “T_QuoteDetails” not in recordset”

Any ideas? I can either use the original sub table or this new query to fix the problem.
 
boblarson

boblarson

Smeghead
Local time
Today, 10:41
Joined
Jan 12, 2001
Messages
32,059
Can you post a copy of your database (with fake records) so we can take a look to see what you're doing?
 
S

sergeos

New member
Local time
Today, 10:41
Joined
Jun 26, 2012
Messages
1
I have the same problem.
When i want add record from frmJournal.

My task:
1. Must enter name which only presented in table Pupils. (without useing list, only textbox).

So, when i write pupil name into field, they automatical set field PupilID, for normally save record.
 

Attachments

  • Database5.mdb
    324 KB · Views: 367
N

Nano

Registered User.
Local time
Today, 13:41
Joined
May 14, 2012
Messages
91
boblarson said:
Can you post a copy of your database (with fake records) so we can take a look to see what you're doing?
Click to expand...


Ok, the problem is with the form called F_Quote and the subform. This ver is the query based subform.
 

Attachments

  • NanoQuote Sample.accdb
    1.4 MB · Views: 379
boblarson

boblarson

Smeghead
Local time
Today, 10:41
Joined
Jan 12, 2001
Messages
32,059
Nano said:
Ok, the problem is with the form called F_Quote and the subform. This ver is the query based subform.
Click to expand...

Unfortunately, I only have Access 2003 here at work (we're working on getting 2007) so I can't look until I get home.

But I do have a question for you. Are you sure that you do not have the table that the subform is based on in the main form's record source and you do not have the table the main form is based on in the subform's recordsource. You can't use the same table in both.

Also, for the form's record source, you do not need to include any lookup tables in the form's record source if you are using combo boxes or list boxes to display the values since their row sources have the tables they need to display the field.
 
N

Nano

Registered User.
Local time
Today, 13:41
Joined
May 14, 2012
Messages
91
boblarson said:
But I do have a question for you. Are you sure that you do not have the table that the subform is based on in the main form's record source and you do not have the table the main form is based on in the subform's recordsource. You can't use the same table in both.

Also, for the form's record source, you do not need to include any lookup tables in the form's record source if you are using combo boxes or list boxes to display the values since their row sources have the tables they need to display the field.
Click to expand...

The information on the sub form should be independent from the main form. The main form is just address, billing, and customer information. The sub form is product information.

As for the lookups I don't believe I put any in the subform, although last time I really worked on this subform about a month ago it was working fine. I have been working so long on this database it is becoming hard to keep track of everything. When you get a chance please double check and make sure that is not the problem.
 
N

Nano

Registered User.
Local time
Today, 13:41
Joined
May 14, 2012
Messages
91
boblarson said:
Unfortunately, I only have Access 2003 here at work (we're working on getting 2007) so I can't look until I get home.
Click to expand...

boblarson did you ever get a chance to look at my database?
 
boblarson

boblarson

Smeghead
Local time
Today, 10:41
Joined
Jan 12, 2001
Messages
32,059
Nano said:
boblarson did you ever get a chance to look at my database?
Click to expand...

I'm sorry it totally slipped my mind. I have now put a reminder on my phone which should remind me when I am at home.
 
boblarson

boblarson

Smeghead
Local time
Today, 10:41
Joined
Jan 12, 2001
Messages
32,059
Okay, I've taken a look and here are the results (it isn't pretty):

1. The subform is based on a query which is based on another query. That second query, which the first is based on, has some major flaws. First, it has more than the Quote Details table. You don't include the other tables in there. There is no need. Second, you have the ORDER ID based on the main form's QuoteID control. That is not necessary. When you have a subform, you use the Master/Child links properties of the subform control (control on the parent form which displays/houses the actual subform).



2. If the fields SURFACE, CORE, And GEN are in the Products table, there is no need to have them in the Order Details table also. You just need to select a product and you can DISPLAY the other values using something like this =[ProductID].[Column](3), but they don't need to be stored in the Order Details table. See the attached revised version of your database.

3. You are using lookups at table level and that is not good. You should get rid of them as they can have unintended consequences. See here for more about that:

http://www.mvps.org/access/lookupfields.htm

4. Customer Name should not be in the Orders Table. You store the Customer ID.

5. Shipping Companies need to be in a table because that can change. So it is better to change a value (add/delete/edit) than changing the design.

6. You are storing redundant data. So, for example, you have CustomerID in both the Quotes and the Shipping table. Why? If the quote is what is shipped, then the quoteID should be in there but not the customer ID. What can clue you in is that if your Relationships appear to make a circle then it is likely wrong somewhere.

Here are the modified relationships:



7. ProductCategoryID in T_Products needs to be NUMBER (LONG INTEGER) not text.

8. Payments should be in a separate table, not as fields in the Quote table.

9. Don't use special characters (other than underscores) in field or object names. (like you used Payment #).

10. Don't use spaces in field or object names. It will save you some headaches.
 

Attachments

  • nano_subform01.png
    nano_subform01.png
    19.2 KB · Views: 10,657
  • nano_relationships.png
    nano_relationships.png
    61.3 KB · Views: 10,510
  • NanoQuote Sample_revBL_2012-06-28.accdb
    1.5 MB · Views: 279
N

Nano

Registered User.
Local time
Today, 13:41
Joined
May 14, 2012
Messages
91
boblarson said:
Okay, I've taken a look and here are the results (it isn't pretty):

1. The subform is based on a query which is based on another query. That second query, which the first is based on, has some major flaws. First, it has more than the Quote Details table. You don't include the other tables in there. There is no need. Second, you have the ORDER ID based on the main form's QuoteID control. That is not necessary. When you have a subform, you use the Master/Child links properties of the subform control (control on the parent form which displays/houses the actual subform).



2. If the fields SURFACE, CORE, And GEN are in the Products table, there is no need to have them in the Order Details table also. You just need to select a product and you can DISPLAY the other values using something like this =[ProductID].[Column](3), but they don't need to be stored in the Order Details table. See the attached revised version of your database.

3. You are using lookups at table level and that is not good. You should get rid of them as they can have unintended consequences. See here for more about that:

http://www.mvps.org/access/lookupfields.htm

4. Customer Name should not be in the Orders Table. You store the Customer ID.

5. Shipping Companies need to be in a table because that can change. So it is better to change a value (add/delete/edit) than changing the design.

6. You are storing redundant data. So, for example, you have CustomerID in both the Quotes and the Shipping table. Why? If the quote is what is shipped, then the quoteID should be in there but not the customer ID. What can clue you in is that if your Relationships appear to make a circle then it is likely wrong somewhere.

Here are the modified relationships:



7. ProductCategoryID in T_Products needs to be NUMBER (LONG INTEGER) not text.

8. Payments should be in a separate table, not as fields in the Quote table.

9. Don't use special characters (other than underscores) in field or object names. (like you used Payment #).

10. Don't use spaces in field or object names. It will save you some headaches.
Click to expand...

Thank you Boblarson, you have no idea how much this has helped me. I am sorry it was such a mess, like I have said in other threads I this is the first time I have tried to make a database more complex then one table and a form. Thank you so much!:D
 
N

Nano

Registered User.
Local time
Today, 13:41
Joined
May 14, 2012
Messages
91
So Boblarson I have had a chance to look over most of your changes, Things are looking good so far, although there are still a few things I need to change. I see you fixed the shipping company combo in the T/F_Shipping Info however that combo is a old combo I forgot about that I had meant to remove however I really need that functionally to be in the F_Confirm_Ship_Info form. Although I am not really sure how to move that over.

One more quick question on the F_Quote form I have the shipping address for the order, if the person is selected it should only display shipping address for the customerID, however if the person it not loaded first, (AKA the first record) the form has to be refreshed before it will show the correct address. Also when changed it no longer saves the address for the record. Any ideas?
 
K

Kubalism

Registered User.
Local time
Today, 10:41
Joined
Jul 4, 2012
Messages
36
Sir Nano and boblarson I am having the same error. Can anyone of you take a look at my post? please...
 
You must log in or register to reply here.

Similar threads

G
Solved Error #size! on calculated control based on subform value
Replies
2
Views
177
GaP42
G
T
Solved Add new record from list box
Replies
5
Views
393
arnelgp
arnelgp
H
Solved Problem with Rowsource in Combobox of Continuous Form.
Replies
15
Views
330
HillTJ
H
J
Solved subform remains blank after new record saved
Replies
3
Views
170
Jaks
J
E
How to add/edit records in a subform?
Replies
10
Views
972
ekje
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom