Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 610
  • Last Modified:

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

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
BradleyOnTheRoad
Asked:
BradleyOnTheRoad
  • 2
  • 2
1 Solution
 
calpurniaCommented:
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
 
BradleyOnTheRoadAuthor Commented:
"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
 
calpurniaCommented:
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
 
BradleyOnTheRoadAuthor Commented:
Yes, that's much better. Thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now