I initially had the parent form bound but because I need the ability only to write to the tables when the user used the save button I had to use an unbound form. Unfortunately I'm now not sure how to handle my subform which has a one to many relationship with the parent table and form.
Any suggestions would greatly be appreciated.
Subforms link to the control, not the data. They will link to unbound controls as easily as bound controls, though you may have to set the Child and Master links yourself.
There are probably better ways around restricting user's ability to save data than creating an unbound form. Have you thought of adding code to the form's insert and update events to interupt or redirect processing?
What I was attempting to create was a way that the user could open a form enter a new record or edit an existing record but I didn't want the changes or new form to write to the tables unless specified by the user. Basically I wanted to begintrans when the form was opened and only committrans if the user chose to save. Unfortunatley with bound tables I was finding this impossible.....again I don't know that much about Access. So I decided to try this work around.
Access saves changes every time you move to a new record, so yes it is going to be difficult to do what you want to do. It is only in the subform where you want to do this, or are you requiring users to explicitly save changes in the main form as well? Are you allowing users to create or edit several records before committing their work, or do they have to save one record before they can move on to another?
Like I said, this isn't a simple thing to do, so make sure the functionality is worth the effort.
A simpler solution would be to have a message box pop up to confirm changes or additions to each record. This would just require a few lines of VB code in the subform's BEFORE UPDATE event.
Unfortunately the functionality is critical to the application. The parent form only has one record but the subform(containing accounting items) needs to have several records per parent form. This is why I can't add a save / undo method to the BEFORE update. It gets very annoying working with multiple records in a form.
i don't know that this directly addresses your question, but here it comes anyway:
i hate, loathe, and despise A's standard form setup: it's too easy for a stupid user to create/delete records and otherwise screw up the data. navigation is killed in ALL my forms. allow add/del is killed in almost all.
for your new-with-confirmation issue, i make a button New which opens a pop-up/modal unbound form with textboxes for all the appropriate fields from whatever tables are involved in a new record + Create & Cancel button. the user can mess with this form for hours without hurting any tables.
when the user hits the Create button, i have all the time in the world to go through each textbox and make sure the wannabe-data fits, send warning messages etc etc. then if everything really is OK it's a simple matter to make the recordset(s) and .addnew
apart from the "filter" word (i don't use A filters) YES.
if your subform is based on a query (if it isn't, change it anyway!) you can grab the ID from any edit open form (including the parent form) in query design grid. click in the criteria box of ID (doesn't need to be retrieved, but it needs to be on the grid) and then the magic-wand builder button on the main menu. navigate forms; all forms; your parent form and then double-click the ID field.
I'm sorry you will have to excuse me, I'm not that experienced with Access.
I have a main form ( frmSubCO) which is unbound and the fields are populated when the user selects a SubCOID from a dropdown box. Upon saving the field changes/additions are written to the tables using an OpenRecordsrc.
I did not understand how I would add a subform and have it link to the SubCOID on the main form. I also still need to be able to open add new records in the detail/subform.
the Subform is based on a qry with the SubCOID being the parameter.
you have a combo Forms!frmSubCo.SomeName which allows users to select from existing SubCOID. the combo works (???)
you need to get the literal value
(with the real name, of course) into the criteria box of a query in the column that contains the field SubCOID (the query also needs to contain anything else that you want retrieved). the magic-wand can add the criteria, or you can manually type it.
please do this with a new query, and please don't mess with parameters
select something in the combo, keep frmSubCo open and
now run the query: with luck the query will display what you want your subform to display.
if YES: base your subform on this query
if NO: open the query in SQL view and paste the SQL into this site.