Tables :: Cannot Add Records - Join Key Not In Recordset
Nov 21, 2012
I inhereted a database, and i know nothing about access. I'm trying to add a new payment record and I get this error: cannot add record(s); join key of table 'tblPayments' not in record set. I have never created or updated access before.
Basically, I want to be able to click on the New (blank) record button, and then start adding in my Attraction, Date, etc fields in the main form, however I keep coming up with the error:
Cannot add record(s); join key of table 'OrdersList' is not in recordset.
I've gone back and checked all my relationships, and the query the form is based on, and all appears to be working there fine. I can manually enter information on to the tables & queries just fine, (but obviously I don't want other users to be able to see these).
I'm wondering if it's something to do with the Auto Number, which is also my PK in table 'OrdersList'. As the first design of this database had this field set to a Text field and I would manually enter the next sequence and I didn't have any issues adding new Orders to the form.
The form is 'Orders', which is based on a query called 'Orders List'.
I have a form that gets its info from a query, I would like to be able to add a new item, Customer, etc, etc. But, when I try to add one (I have a button using VBA code, ill post that at the end) it gives me the error
Code: Cannot add record(s); join key or table tblitems not in recordset.
Code: Private Sub cmdEventNewI_Click() Me.Visible = False DoCmd.OpenForm "frmItemsEdit1", acNormal, , , acFormAdd, acDialog Me.Visible = True Me.lstItems.Requery End Sub ' cmdEventNewI_Click
I have a table orders details with a sub form displaying the order, I have a combo box to display the product from products table to place in the sub form products box . I keep getting the error message join key of table details not in record set I have checked my relationships and for keys and there all there .
And then called this join as a symbol or variable, and then have it use to select the items from these joined tables, can this be done in Access? Here is an example of a code that I created, but it has an error message saying the FROM syntax is incorrect.
Code: SELECT firstJOIN.trainID, firstJOIN.trainName, firstJOIN.stationID, firstJOIN.stationName, firstJOIN.distance_miles, firstJOIN.time_mins FROM (trains INNER JOIN ((station INNER JOIN lineStation ON station.stationID = lineStation.stationID) INNER JOIN bookingLeg ON bookingLeg.startID = station.stationID or bookingLeg.endID = station.stationID ) ON trains.trainID = bookingLeg.tid) as firstJOIN
Can Access do something similar to this, in the FROM statement I joined 4 tables, because each unique fields are in each table and I have to joined them to get those fields. I called this join firstJOIN and in the SELECT statement, I list those columns in the table by calling it firstJOIN.trainID. Can Access do something like this, but syntax it differently?
I am fairly new to access, and have the following problem with the attached new DB. Thought I have designed the start of a good database with a "Junction Table". Have 1 problem, cannot enter a new name on the from, gives the error below:
"Join key of personnel table not in recordset" ...
Okay - the other database is in the works and is going sloooow (the one that is being created with normalization).
Meanwhile, my other database that has no normalization I am having a problem running a query (now I know why its so important to have good structure).
To give you an idea on how bad this is...:o .....
3 tables - 94 fields to each table - each table has identicle fields, just different data. :eek:
Anyways, I have students in each table (each table is an Annual Report). I want to be able to run a simple query and combine all the students into one.
How would I achieve this?
Thank you!
And for all who are thinking about designing a database - READ READ READ and do some more reading on normalization!!!
HELP!!! :confused: I do have a 5 tables 4 tables do have a foreign key of the main table. I join the 4 tables with the main table but when I am editing the information I cannot edit it. and no error appears. so I am just wondering what happen with my joined tables? please help me!!!!Thank you in advance
How do I join two tables. I have a table and a lookup table. My table has products on there that are listed as custom or basic. I have a look up table that has an ID for basic and Custom. In my table, i want it to read what the id is for each product instead of it reading "basic" or custom. Someone said that I need to join the two tables and do an update query, but I don't understand how to.
That is the question: Whether 'tis nobler in the mind to suffer with VBA Programming and the outrageous errors, Or to take up arms against a sea of Access troubles.
Sorry I was in the mood for Shakespeare.
A quick summary first: In the attached file I have my Relationships. One main table, Workorders with various one-to-many relationships back to their respective tables. If you look at the attachment and see a field with 'wrk' that's my foreign key.
What I'm trying to do is this: Where-ever there is a 'wrk' field I want to add in all the fields (minus the ID Primary Key) into the main Workorders Form.
Then on the form itself I should just be able to enter in the data that is required for the fields.
So here's what I'm thinking for the coding is to Join the various tables to thlet me know if I'm on track or not. I'll start with a small one, because if I can get that right, the rest should be simple.
SELECT Model.ComputerID, Make.ComputerID FROM Computer INNER JOIN wrkComputerID ON Model.ComputerID = Make.ComputerID;
there must be a way to do this, but i haven't been able to figure it out yet...
table one has three fields i need to be concerned with, member_ID, range_start, and range_end. no part of any range spanning range_start through range_end will be duplicated.
table two has several fields i need, but the field of primary concern is a person_ID that will fall somewhere within a range designated in the first table.
how in the world can i join these tables, since none of the IDs in the second table are actually listed in the first, but fall in between values?
i need to get member_ID from table one, person_ID and several other fields from table two.
i have 2 tables, A and B A table StockDate Price 02/04/2001 1.12 04/05/2001 1.15 14/08/2002 1.14 18/09/2003 1.26 and so on 1.48 up to 2005
B Table StockDate Price 02/04/2003 1.12 04/05/2003 1.15 14/08/2003 1.14 18/09/2004 1.26 and so on 1.48 up to 2005
i like to know how to write codes to join 2 tables into 1 table like C Table StockDate Price 02/04/2001 1.12 04/05/2001 1.15 14/08/2002 1.14 18/09/2003 1.26 02/04/2003 1.12 --> B Table 04/05/2003 1.15 14/08/2003 1.14 18/09/2004 1.26 and so on 1.48
basically from what i can understand, there are 3 options.
1. Where join field from both tables are equal 2. All records from table 1 and only those from table 2 where they match 3. All records from table 2 and only those from table 1 where they match
how do i go about having all records from both tables showing?
I have a form that uses the following join as its control source:SELECT * FROM [Purchase Order] LEFT JOIN [Work Orders] ON [Purchase Order].WorkOrderID = [Work Orders].WorkOrderID; I also enclosed a small picture of the relationship between the two tables. This is the problem I'm getting when using my purchase order form.
What I Want: When I type in the "Work Order #" it will lookup the most recent work order with the same number and put its ID into the purchase order record.
What I'm Getting: When I type in the "Work Order #" a brand new work order is created and the "Work Order ID" field gets the next number in the sequence.
Make note that I'm not using code for any of this. This is happening all on its own. I think the join is messing things up here. Any ideas?
I work for the school dist. and we have 2 tables for 2 different groups. I need to pull data from them both and create a new Query. Example Table 1: Name, address, phone
Table 2: Name, address, phone
(No similarities in ether database, and we'd like to NOT export data)
I'm hoping it's an easy fix that I've overlooked. Michelle
I have made a form I am trying to bound 3 tables to.
The 3 tables are
tblPersonalInfo tblBookingInfo tblSkills
How I am trying to set it up is that I enter someones personal details in once. But using the same personal details someone can have more then one booking. With the skills....each person can have lots of different skills.
Basically I work for a recruitment agency and I have been asked to build a database that can add new people.add skills to that person.....then add different jobs to the same person.
I have joined all tables togeather (I think wrong but trying) the tblbookinginfo is joined to tblbooking using a 1 - Many relationship using the primary key of tblPersonalInfo to the field PersonalID in the table tblBookingInfo I have joined tblSkills to the table tblbookingID through SkillsID
I created a query using all 3 tables and have used this to bound the form....
The form contains 3 pages and the 1st page works ok (it only contains info from the tblPersonalInfo) the 2 other pages don't seem to work as I want them to.
I would like to be able to click to add a new person and also new booking and new skills
Currently I am able to add new personal details but nothing else.
Have some problems with a Purchase Order (PO) System I'm trying to do.
Figure 1 is the PO entry form. The upper part of the form shows the Header, while the bottom part shows the Line, containing all the ordered item details.
I separated the information keyed in into 2 tables, namely Header table (Figure 2) and Line table (Figure 3). The header table shows only the header details. With the order ID, the header is linked to the Line table where all the ordered items detail can be found.
I want to join the 2 tables and put the data into a new table, which will later be exported to text/spreadsheet for other purposes. I know it can be done using the make-table query. But my concern is about the layout/format of the table.
I would want to put my data where there's one header row followed by the corresponding line rows. Then another new header row with its line rows. I'll give an example:
H XXX XXXXXXXX XX X L XXXXX XXX XX L XXXXX XXX XX L XXXXX XXX XX H XXX XXXXXXXX XX X L XXXXX XXX XX L XXXXX XXX XX L XXXXX XXX XX L XXXXX XXX XX
Note that not all the fields for Header row and Line rows are the same. So can I put different data into the same column?
Would appreciate it if anyone could solve my problems. Thanks. ;)
Hi, I have an unbound form that is based on a query. On the Load-event I have populated textboxes with the underlying records (it works fine).
The underlying query is based on 2 tables that are joined on (PrimaryKey) Table1.ID = Table2.ID (ForeignKey).
The Form has one Add-CmdButton. When the user will click the Add-button the content of the textbox will get added or saved in the underlying database.
I want to know the SQL to add, so that I can write the code behind on the Click-event of the button.
I know on how to add records with a single table (writing code behind), however, I want to know on how to do when it comes with joined tables (INSERT SQL statement ?).
I would like to join two tables with one query. Here is the twist though. The first table would have a set of value like so:
0 50 100 300 500 750 1200 1500
the second table would have values as such
0 1000 1300 1500 1750
by just setting up the relationship to show all values from the sirt table and only those from the second table that match I get a list of values that will not work for me. What I would like to reflect in the query is all the values from the first table and if there is for instance no value of 500 in the second table I would like to return 1000. For instance there is a zero in each table so that would be output twice. There is however no value of 50 in the second table so the query would read 50 and null or blank when I would like it to read 50 and 1000 (The next value higher).