Solved

Access: Limit data entry and edits to only one record on a sub form

Posted on 2008-10-06
4
595 Views
Last Modified: 2013-11-28
Is there a way to use datasheet view on a subform within an unbound mainform but only allow one record to be either added or edited?

For example, you can have zero records or a max of one record for the given parent/child relationship.
0
Comment
Question by:BradleyOnTheRoad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 10

Expert Comment

by:calpurnia
ID: 22655277
I think you need to tell us a bit more about this. Firstly, if the main form is unbound, how can it have a parent/child relationship with the subform? And secondly, if there's an exact 1-to-1 correspondence between the 'parent' and the 'child', this suggests that you don't need to split your data across 2 tables. I may well have got the wrong end of the stick here, but some extra details about the problem would be useful.
0
 

Author Comment

by:BradleyOnTheRoad
ID: 22655650
"Firstly, if the main form is unbound, how can it have a parent/child relationship with the subform?"
Through text box controls that change as the underlying recordset changes.
"And secondly, if there's an exact 1-to-1 correspondence between the 'parent' and the 'child', this suggests that you don't need to split your data across 2 tables."
Yes, I struggled with that. However, it's a case where if you edit one type of record it will be a one to one relationship. On the other hand, you can edit the related table directly.
Here's what happens, a package leaves a facility enroute to a satelite office. That package becomes the responsibility fo the satellite office. Now that package can still move from customer to customer even though the package remains the property of the satellite office.
Upon initial shipment from the main office, the date of shipment and the first assignment to a satellite office and a customer are all the same date. Of course, once the package begins moving from customer to customer the dates will change unrelated to the initial ship date.
I'm trying to get rid ofsome of the grunt work on keeping the dates the same but I may have to wind up coding it.
0
 
LVL 10

Accepted Solution

by:
calpurnia earned 500 total points
ID: 22672858
So you've got one table (lets call it 'Package') that contains things like the PackageID, SatelliteOffice (assuming a given package will always remain the responsibility of the same office) and InitialShipmentDate, and another table ('PackageMovement') that records the movement of the package from customer to customer, and has fields like PackageID, CustomerID and ShipmentDate.

Rather than using a subform, could you just have a drop-down box on the main form to select the CustomerID for the initial customer, then use an INSERT statement to add a new row to the PackageMovement table? Yes, it's coding, but it shouldn't take very much.
0
 

Author Closing Comment

by:BradleyOnTheRoad
ID: 31503640
Yes, that's much better. Thanks
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question