Solved

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

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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.
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…

910 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now