Full Version: Unable to add new record
UtterAccess Forums > Microsoft® Access > Access Forms
Mike15739
I am currently working on a project for this company i am workign for and after making some updates to the form, I am not allowed to add a new record to the form. Any idea on why this could be happening, the navigation button for adding a new record is grey and unable to be clicked. Not to mention the button i made to add new order isn't working either. Any idea?
fkegley
I believe the form is based on a recordset that cannot be updated. Access is VERY FUSSY about this. You need to review the rules for updatable recordset in the Help files. I believe it is under Updatable Recordset.
Mike15739
Thanks for your help, i will look into that. I just find it weird that it was working fine and as soon as i make some changes to the form layout, it won't let me add a new record but i never changed any relations
freakazeud
Make sure allowadditions is set to YES in the form's property window.
HTH
Good luck
HiTechCoach
Frank was not refering to changing relations.
id you make any changes the the record source of the form? If based on a query, did you add a table to the query? This may cause the record source to not be updatable.
I did you change the form's Allow Additions property?
Mike15739
Thanks a lot, I appreciate the help. I did some search in the help that the last guy told me to do and it mentioned something about changing the record set type in the form properties from Dynaset to Dynaset (Inconsistent Updates). I tried that, and it works but it never saves new record into the form but it saves it into the table but doesn't have the right CompanyID so that must be what is breaking it. Oh yeah, and i just checked AddAdditions, it is set to yes. Any help would be appreciated man, thanks
freakazeud
Hi,
Odon't think you want to change the recordset type.
Leave it as it was...is the form based on a query? Try to open the query and see if you can actually edit data in there. If not then you indeed have an non updatable query as already earlier mentioned.
This can have many reasons, some might be:
. Improper join
2. Aggregate functions
3. Multiple non-hierarchial 1-many relationships
4. Security
5. No primary key on a linked table.
All these things will result in a query which is non updatable. Most likely it is a relationship issue.
HTH
Good luck
Mike15739
Yeah, that must be it. I will look into that. Thanks for your help again man
freakazeud
Sure thing!
Good luck.
Mike15739
Now the fun part is finding out where the query is bad but i never really changed anything with the query or the relationships but i will look into it
Mike15739
I am just debating on recreating the form from scratch since i already made all the changes to the query that i needed. but there has got to be an easier way because all i did was just add new fields to the query that have no relations to other tables, that query is only using three tables with two relationships (Company.CompanyID to Order.CompanyID) and (Product.ProductLine to Order.ProductLine)
freakazeud
Did you actually check if you can edit data in the query?? Because if you can then the query is not the problem.
If you can not then you need to find the error in the possible above mentioned things.
HTH
Good luck
Mike15739
Good Call, it is a problem with the query because i can't edit it. You don't happen to have AIM or MSN Messager, do you?
freakazeud
No...you will get all the help you need here on UA.
Utter Access "works" not only because members are willing and able to respond to posted questions but also because members are willing to publicly post questions that are of concern to them at the moment. With the number of Utter Access members worldwide, it is almost a certainty that, if you have a question, someone else, somewhere, has a similar or identical question.
ublicly posting your question will afford YOU the possibility of a much larger range of answers, OTHERS will find your question and the resulting responses helpful in their endeavours.
Mike15739
Ok man, thanks for all your help and i will keep you updated if i get this thing figured out. I appreciate all the help
freakazeud
You're welcome!
fkegley
It is best if you forget about Dynaset (Inconsistent Updates)--- this is pretty much guaranteed to result in "orphan" records on the many side of the relationship.
Mike15739
Hey man, I have been looking at it for the past two hours and still nothing. I can't find out where the query is bogging down. It is only using three tables with two relationships. I even recreated a new query and the same stuff is happening
Edited by: Mike15739 on Fri Jan 13 12:05:07 EST 2006.
Mike15739
Hey man, i think i fixed it, just used an Inner Join that the query wasn't reading. But, i just came across another problem where in my Order form, i have a drop down box that reads from the Company Table and all the companies that are there. But once you fill out the order form, it adds a new Company with the same name but a different CompanyID. Any suggestions, on why this is happening?
freakazeud
Hi,
I'm glad you fixed your problem and got it sorted out.
I'm not understanding your next request. Where is it adding this company to? What is your combobox based on and what are you trying to achive?
HTH
Good luck
Mike15739
Oh sorry about that. Well the basics are that the Order table contains the CompanyID. In the Order form, you can select from a combo box all the companies that are on record (in the Company table). If the company places multiple orders, i want the orders to represent with that company ID. But with my form, once i add a new order with an existing company, it saves the order but it adds another company name (with that same name) with a new companyID. Does that make any sense? I am just trying to allow a company to place multiple orders and be able to keep track of them in reports.
Edited by: Mike15739 on Fri Jan 13 14:07:10 EST 2006.
freakazeud
No not really frown.gif
Maybe you can attach your application so I can have a look at what your set up is like.
Try to keep it under 500kb and zipped. Keep confidential info out.
HTH
Good luck
Mike15739
Thanks a lot man, i really appreciate it. I am just working for this company and i have a deadline for later today and i need to work on the reports for them. I just tried to zip it but it is a little over, do you have an email address i can send it to.
Mike15739
Nevermind, i compacted it. Lets try it. Don't worry abuot the info, it is all bogus for testing purposes
freakazeud
Try to compact it before you zip it (tools--database utilities--...). This should decrease the size and work then.
HTH
Good luck
Mike15739
Yeah i did that, did you see what i sent as an attachment. I really appreciate all your help man
freakazeud
Yeah I just saw it...must have typed your reply when I was typing mine frown.gif
I'll look at it.
freakazeud
What form and control am I looking at and what should I be doing to recreate the problem. I can already say that your data is not proparly normalized and you will need to fix that before you go of building forms/queries/reports/code...!
HTH
Good luck
Mike15739
Feel free to add sample orders as a test, like i said, this is just bgous data
Mike15739
the Order form and the Company Name control. Just pretty much put in bogus data if you were putting in a new order but select a previous business that is in the Company Name drop down box. Then if you look at table after you created the order, there will be a new companyID with the same name, i don't want that. How else would i normalize the data. The only important things are CompanyID which is in the Order table as a foreign key and ProductLine from the Product table (primary key) is in the Order table as a foreign key.
freakazeud
Hi,
yes...I looked at it and my suspiciouse is confirmed. Your table structures are not correct. Why are you linking the company CompanyID from the Company Table to the orders table? The company ID there is an autonumber and therefor the value created will be an autonumber (where you get the "bogus" number from).
HAs far as I can see this is a way you could set your tables up (this is just an example, so you might need more, but it will get you started down the right path):
blCustomer
CustomerID PrimaryKey/Autonumber
LastName
FirstName
tblCompanies
CompanyID PrimaryKey/Autonumber
CompName
CodeID ForeignKey/Number
tblCodes
CodeID PrimaryKey/Autonumber
Code
tblProducts
ProductID PrimaryKey/Autonumber
ProductName
ProductDescp
tblOrders
Orderyes...I looked at it and my suspiciouse is confirmed. Your table structures are not correct. Why are you linking the company CompanyID from the Company Table to the orders table? The company ID there is an autonumber and therefor the value created will be an autonumber (where you get the "bogus" number from).
HAs far as I can see this is a way you could set your tables up (this is just an example, so you might need more, but it will get you started down the right path):
blCustomer
CustomerID PrimaryKey/Autonumber
LastName
FirstName
tblCompanies
CompanyID PrimaryKey/Autonumber
CompName
CodeID ForeignKey/Number
tblCodes
CodeID PrimaryKey/Autonumber
Code
tblProducts
ProductID PrimaryKey/Autonumber
ProductName
ProductDescp
tblOrders
ORDERID PrimaryKey/Autonumber
CompanyID ForeignKey/Number
ProductID ForeignKey/Number
DateOrdered
Comments
...
I'm leaving now for the day/weekend, so if you can't figure it out then hopefully someone else can jump in and help you out. Maybe you need to create a new thread and link to this so people know where you are at.
HTH
Good luck
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.