Solved

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

Posted on 2008-10-06
4
591 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

710 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