Jul 8 2010, 06:00 PM
Click to view attachment
Thank you for stopping by! Please see the attached screenshot. Basically, I have a form attached to a query in data sheet view. When I try to enter a new record, I get the following message.
Your assistance is deeply appreciated!
Jul 8 2010, 06:09 PM
There was an instance of this some while back where there was some ambiguity in the query because of it's structure.
Generally, it is best to avoid using multiple tables in a query that is the record source of a form, as it is all to easy to end up with a query that is not editable. You may be able to use 'DISTINCTROW' in the query which may remove the ambiguity but prefereable to restructure your forms to use a single table, using subforms where necessary.
Jul 8 2010, 06:37 PM
Thanks for replying. It is nice to have you helping me once again.
THE query is currently attached to only one table.
However, I will check to see if it is using distinct row.
Talk to you later.
Jul 9 2010, 03:00 AM
Good to touch base again with you, hope you're well.
Re the query, if you only have a single table in your query then you would not expect it to stop you from adding records, even without DistinctRow.
Could you post a copy of your db with the Form, Query and Table showing the problem and I'd be happy to take a look. As usual delete or disguise any sensitive data etc...
Jul 9 2010, 11:28 AM
Click to view attachment
Ok, I attached the DB. Thanks again for looking at my DB. It's probably something simple I'm over looking. Or maybe something new to learn...?
Let me know what you find.
Jul 9 2010, 04:51 PM
Ok, I think I pined-point the problem. However, I don't know "why" it's causing the problem. If you open form Transactions Subform in Form View (or datasheet view), you'll notice a control (or column) called Mileage Cost. This column really doesn't exist in the table. This column only exists in the query to calculate the mileage dollar amount. If I remove this control/column from the form, then I am able to enter a new record.
Does this make sense?
Jul 13 2010, 01:44 PM
How's it going? How was your weekend? Any ideas why this field is keeping the whole form from entering records?
Jul 16 2010, 03:51 PM
Hmmm...I don't know. I still can't figure out why this field is doing this.
Jul 16 2010, 08:11 PM
remove the zero in defaut value =0 of Mileage Cost control in transaction subform, you can set to zero using NZ function the query
hope this helps
Jul 17 2010, 12:38 AM
Thank you for the suggestion. I will try it out on Monday at work. I'll let you know what happens.
Jul 17 2010, 08:53 AM
Jul 20 2010, 05:09 PM
You're awesome! That did the trick.
Thanks again for your tme and suggestion.
Jul 20 2010, 05:28 PM
You are welcome Rey
Glad we could help
thank you very much
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here