Edit/Add data on multiple related tables

[Steph83]

Hello,

I am new to MS access and having trouble to find a way of managing the following:

I have a "main table" (tblProjects) and 3 "detail tables" (tblSchedule, tblCosts, tblResources). Those 3 detail tables have the "projectID" as foreign key.

I have main form (frmProjects) with a subform showing a project overview (datasheet view)on the bottom and a tab control on the top with the following tabs:
- frmProjects_sub:
- frmSchedule_sub
- frmCosts_sub
- frmResource_sub

I basically want the user to select a project from the subfrm on the mainfrm and then gets the different details to the selected project on the differnet tabs. This part is working and realised through an unbound text control on the main form which provides the primary key to the tabs.

Now I want to add command buttons to add a new project. when clicking that button a new form with the same 4 tabs should popup, having all controls empty to fill in the data. But I only want a record set to be created when the user clicks on a "save" button. Until then the user should be able to go through the tabs and change all information.
The same with an edit button, just that the selected record should be displayed, waiting to be changed.
That means there should also be a "cancel" button, where all changes can be undone if the save button isn't clicked.

I was reading a lot about recordsets, insert queries... but I don't know how to do it and how to start?

I hope it is understandable?! If anything else is missing please ask.

Thanks a lot for helping me out!

[zmbd]

An image here would help.
Most of the newer window-OSs include the "Snip Tool"
Use it to capture and save a jpg of your open form.
Using the advanced editor you can upload the image file directly into a reply-post by using the [Manage Attachments} button. The upper section of the dialog that opens will allow you to browse to and upload files from your computer.

As for answering the question directly:

> Usually we require one question per thread; however, for me I'll give you "variations on a theme" for solutions for both questions"

when clicking that button a new form with the same 4 tabs should popup, having all controls empty to fill in the data.

+ This really seems to be an unnecessary complication. Is there any reason you cannot allow a new record to be created directly in the current form?
-- A related option is a new instance of your current bound form in "Data Entry" mode, this is my preferred method. I will usually open the new instance of the form in data entry mode, Dialog view state, and as Modal so the calling code will pause until the form is closed; once the data entry form is closed then the calling code can continue and invoke a Requery. With the Tempvars collection you can set a value with the new entry's Primary key if a new entry was created using one of the form events (on_Close or after_update depending on the situation) and the main form can move to that record, or if some value such as -1, to simply leave things alone.

The advantage here is that you only need one form and one set of code/queries to maintain any business rules.

+ The other option is to use an unbound form (or a hybrid of bound and unbound controls and subforms) that has the controls you need for the new record. This requires a great deal of VBA to get this method to work reliably. How conversant are you with VBA? The first time I did this it took me months to get everything working properly - and I had a fairly sound grounding in programing.

The same with an edit button, just that the selected record should be displayed, waiting to be changed.

Again, this seems like an unnecessary complication. The record is already displayed in the form - why not allow the edit to occur in this form? I have several forms where when first opened edits are locked out. I have a command button that will toggle the state (to be honest, most often I use a toggle button and the before_update event of the form checks to see if this control state is "True" and either allows the record to save or cancels the update).

If you really want a new form, then again, you should consider opening a new instance of the current form and have the filter set such that only the record with the project primary key in question is shown.

There is again, the option of an unbound/hybrid form as I mentioned previously for the "new record." You can open an instance of this form and pass the values to the control either individually by setting the values from the calling VBA or by passing the values in the form.openarg property and parsing these values from the string in the on_load event

> For any of the aforementioned options you can open them as modal which will pause the calling code or you can set-up custom events (NOT FOR THE NEW VBA PROGRAMMER) that you can use to trigger form updates etc...

Hope that helps you to choose a direction for each of these questions.

[NeoPa]

Hi Steph.

What you want is all-but impossible. It could be done by ignoring the way Access normally works and building a very complicated set of arrays to hold all the data in unbound controls that fundamentally reflect your table structure, but it would be many levels of nasty.

If multiple records for each linked table weren't required (I'm talking about something like an overall invoice with multiple detail lines.) then a Form could be built with multiple Pages (Tabs) and bound to a complicated QueryDef that JOINed all the required tables together in a way that's supported, but the limitation that all of the tables are limited to a single record would seem to me to make that thoroughly impractical for most scenarios.

You possible already know, but the standard Access way is that you could have a Form which contains multiple Sub-Forms, where each Sub-Form handles a different table, and where a record is always saved to disk whenever you leave a Sub-Form. Not quite what you're after I know, but the alternatives are extreme in my view. Enough so that it would be almost pointless to design this in Access, as you'd lose the most valuable benefits that Access has to offer. Those that set Access apart from the rest of the field as far as RAD (Rapid Application Development) is concerned.

Good luck with whatever you decide anyway :-)

[Steph83]

Hello again,

thank you both very much for your help. To start with, I don't have any programming Background, which is why I will follow the advice and use the forms I have.

So my way Forward now is to lock and disable the controls and unlock and enable them through the Buttons "add" and "edit".

Doing so, I get an error when creating a new record which I don't understand:

I have a mainform with a tab Control with 4 pages and subforms und another subform in datasheet view (see attached screenshot1).

in the tab Control pages I Show the different Information related to the ID selected in the frmProject_list. Therefore I have an invisible TextBox in the Header of the mainform.

I managed to go to a new record in subform frmProject_list when clicking on the button "add" which then changes its caption to "save". But when I start entering Information I get the error shown in screenshot1 (you can't assign a value to this object.).


Could anyone help me to solve this?

(the frmProject_list has no Master/child link, the subforms in the tab controls have the link to the TextBox in the mainform Header, data entry done through frmProject_details which Record Source is an updateable query - see screenshot 2)

Thank you very much!

[PhilOfWalton]

Hi Steph

With great respect for zmbd & Neopa who both know soooo much more than I do, I think there may be a much simpler solution.

My approach would be a main form for the project, showing all the details relevant to the project, and a Combo box in the form's footer to select the relevant project.
I see no purpose in the datasheet.

Then place the 3 subforms on the main form if there is sufficient space, to get everything on. I would avoid the Tab Control so that the user can see all the information in one go without having to flip from page to page.

The main form and the subforms would all be bound to their appropriate RecordSources, and the Controls would also be bound in the usual way.

So, what happens when we edit an existing record? The data gets saved when the focus is moved from one form or subform to another. If you have managed to avoid using the Tab Control, everything is still visible and can be re-edited ad nauseum.

So far so good. Now if you added a Recourse, and subsequently didn’t want it, just delete the record from the Recourses subform.

If you have added a project by mistake as well as the costs, recourses and schedule, there are 2 options.
1) Delete the records in the subforms, then delete the Project record
2) In the relationship window, set Cascade Delete Related Records to true, and by deleting the project, the corresponding costs, recourses and schedules will also be deleted.

Other minor points to consider.
Normally IDs are of no interest to the user, they are basically for Access’s internal use, so I tend to show them at the end of a form in small, grey font.
I would have thought that PlatformID would show some word rather than a number.

Hope this may give you a different and much simpler and more conventual approach to your problem

Phil

[PhilOfWalton]

Hi Steph

Have just had a second thought.

Instead of using the ComboBox to select a project, use a ListBox which would be similar to red area in your second image, and that would basically be the project summary.
Again is the Platform Alpha or numeric?
Remember when you update any of the 4 forms/subforms, to ReQuery the ListBox.

Phil

[Steph83]

Hello Phil,

thanks for your Input. With no Background in Access and programming this Kind of Input is always appreciated.

The idea of the datasheet was to show a summary of the project with relevant information from the various tables (from details, cost, schedule). And then showing the details on top. It is actually still the idea.

And for the failure mentioned, I am suspecting that it is showing up, because I am entering data into a subform that is linked (master/child) to the subform that is giving the input. So the detail form tries to show something is being created in the specific moment. Is that understandable?!

I noticed that I can submit the failure and create the new record anyway. Is there a way of creating a new ID/record when clicking “add”?

I simply use this Code to create a new record:

If Me.cmdAdd.Caption = BUTTON_CAPTION_SAVE Then
            Me![frmProject_list].SetFocus
            DoCmd.GoToRecord , , acNewRec
        End If

[PhilOfWalton]

Hi Steph

Basically you can't add anything into the subforms until the project exists (the ProjectID would be Null at that point, so the linked (master/child) won't work).

The code for an Add Button would be something like (assuming the Command Button is on the main form)

Private Sub CmdAdd_Click
    DoCmd.GoToRecord , , acNewRec
End Sub

As for your datasheet summary, as I said in my last post, if you use a list box instead, this serves the dual purpose of showing the summary, and allow you to select a project for editing.

If you want me to have a look at you Db, I am more than willing because due to heath problems I am restricted in what I can do and am bored to death.

Send me a private message if you want to do that.

Phil

[NeoPa]

There seems to be some confusion here. I can't speak for ZMBD of course, but my answer was directed specifically at that part of the question where it was made clear that none of the data across the whole project should be saved until all of it has been entered and has essentially passed muster. Without complicated use of unbound Forms or even some very complex use of the concept of not-yet-current projects within your table(s), this is not possible to do with Access. Certainly there are clever ways to get around this, but as I mentioned earlier this is not beginner-level stuff.

All that said, if Steff is prepared to waive that specific of her question then Access is a really great tool with which to create a project to manage such stuff. My own advice would be to use a Tab Control if there's too much data to fit neatly onto a form. It sounds to me like there is, but Phil appears to believe otherwise. Be under no doubt, Phil is nobody's fool. He may have overlooked a detail, but he'll certainly have reason for suggesting what he does. As with all of us, if you follow his advice there may be mis-steps along the way, but you can be sure he'll get you to where you need to be in the end.

[Rabbit]

I'm also confused as to why all info needs to be present before anything can be saved. It seems like an unnecessary and complicated requirement.

However, one way to meet that requirement would be to duplicate all the tables so the duplicated tables can be used as working tables where everything is moved into/out of.

[NeoPa]

Rabbit:
It seems like an unnecessary and complicated requirement.

Certainly complicated. I expect only Steff or her boss can decide how necessary it is.

As for alternative tables, that's certainly an idea. It goes in parallel with the idea of using a simple flag in the table or tables to hold the same data but separate out the data that hasn't been finalised yet. The techniques are many and varied, but I doubt we would want to discuss them here as I suspect this thread is going to be covered by more straightforward/beginner-level discussions. I only introduced some of the possibilities in order to illustrate how appropriate they aren't for here.

[PhilOfWalton]

@ Neopa

No, I'm certainly not against Tab Controls where things don't fit on a form, but so often you see a DB with half a dozen Pages on the Tab Control with a miserable little Subform when there is plenty of room to have 3 or 4 subforms on one page and then the user doesn't gave to keep "turning" pages.

Equally you often see single forms that are too long for the screen and use vertical scrollbars, when if the form was a bit wider and not so long, it is much easier to use.

Phil

[NeoPa]

That's very much along the lines I was thinking Phil. As far as I can see that just supports my earlier contention that you're a safe pair of hands to help Steff through this situation.

[Steph83]

Thank everyone for the support. I will certainly follow the advices, many requirements aren't requirements anymore... As said, I'm beginning, with no background. So it's good to see get advice of how to use and which kind of forms to use different purposes.