Add and save record in continuous form with buttons (1 Viewer)

[dev_omi]

I'm trying to build a form that looks like this:

This mimics some forms that I've seen in commercial database software. It is a continuous form embedded in a single form, the latter of which has no data source. I want the buttons on the side to be the only way to add or delete records from the subform. The "Delete" button added by the wizard actually seems to work fine. The "New Record" and "Save" buttons are the ones that I'm not sure how to implement. Here is what I want:

1. There should not be an option to add a record at the bottom of the subform, BUT clicking the button should jump you to the bottom of the subform and allow you to enter and save a new record.
2. That said, I want to require the user to click a save button to save the record: I don't want it to save if they click a different record/control, or close the form.

The only idea I've had is to have the "New Record" button open a second data entry version of the form directly on top of the first. That still doesn't solve the problem of requiring the user to click a save button, though.

Any ideas?

 

[dev_omi]

Another idea I had is to turn off "Allow Additions" on the subform, have the "New Record" button add a record to the Category table with a dummy code value, set the focus to the subform, navigate to the new record with that code value, and finally delete the dummy code value, leaving the user to edit and save the new record. (However, I worry about what happens if a user for some reason leaves an entry with the dummy code value. And again, it doesn't give me the option to prevent saving unless the save button is pressed.)

 

[plog]

You gave us a very detailed description of what you want and ended with an open ended, ambiguous question. So I don't know exactly what you want from us, so how's this:

Start taking bites

What you want to achieve is possible. It's going to be a learning curve, but nothing impossible. You know exactly what you want, now its time to slowly achieve each of those things until you have the finished product. Turn your post into a task list, organize them, pick a plan for tackling them, then give it a go. Other than that, my only advice is you will need to learn VBA. You are not going to be able to just place controls on a form and get what you want.

If you have specific issues with a specific task, ask that.

 

[dev_omi]

You gave us a very detailed description of what you want and ended with an open ended, ambiguous question. So I don't know exactly what you want from us, so how's this:

Start taking bites

What you want to achieve is possible. It's going to be a learning curve, but nothing impossible. You know exactly what you want, now its time to slowly achieve each of those things until you have the finished product. Turn your post into a task list, organize them, pick a plan for tackling them, then give it a go. Other than that, my only advice is you will need to learn VBA. You are not going to be able to just place controls on a form and get what you want.

If you have specific issues with a specific task, ask that.

Umm, sure the question was open ended, but it wasn't ambiguous. I'm looking for a good (if not the best) way to accomplish what I've laid out in my "detailed description". I've seen forms like the one in my screenshot enough that I would expect there to be a good chance that someone on an Access forum would have encountered these design challenges before.

I also know (or expected) that it will require VBA. That's not a problem. I added the buttons through the wizard so that I would have an informative screenshot.

I know how to make a button that adds a new record to a subform (SetFocus + DoCmd.GoToRecord , , acNewRec). The problem is that this requires me to "Allow Additions" on the subform, which as I said, I'd like to avoid. So how do I make such a button that adds a new record without having to set "Allow Additions" on? (Is that task "specific"?)

Edit: And then my second question/task: How do I make it so that the only way to save a record in a subform is by clicking a "Save" button in the parent form? This includes preventing it from saving when you click out of it for any other reason. (Is that specific?)

 

[plog]

So how do I make such a button that adds a new record without having to set "Allow Additions" on?

I would add a footer to your subform and add 2 inputs to it that would make it look like a new blank row of data. You can make this 2 inputs invisible upon load and when your button is clicked you can make them visible and take the focus to it.

How do I make it so that the only way to save a record in a subform is by clicking a "Save" button in the parent form?

Because these inputs are unbound nothing will be saved. When your Save button is clicked you can build an SQL INSERT statement and take the values from those inputs and add a record to the subforms underlying datasource and then refresh the subform so that the new record shows.

This includes preventing it from saving when you click out of it for any other reason.

This sounds dicey--I enter my data in the unbound inputs then misclick and don't hit the button, or I accidentally hit tab or I scroll up the form to see if the one I am adding is already in there--then what? Delete what I typed? Show a message when I eventually go to the Save button that says no can do because you didn't directly hit Save after typing in data? I would rethink this. But it is possible, you just need to add something to the Lost Focus event to let the form know that the user just left the unbound inputs.

 

[dev_omi]

Okay interesting! Not exactly what I had in mind, but it would work for sure. I played around and it didn't seem like it would be possible to get the footer to look exactly like a new entry--it doesn't play nice with the scroll bar, banded rows, etc. Maybe there is a way to do it. But honestly, this solution gives it a kind of privileged data entry character that would perhaps make it clearer (also allows you to scroll up the list and compare, as you suggest, while keeping the potential new entry in view).

Found some code elsewhere to set character limits on the unbound text boxes (forum wouldn't let me post the link--I can post the code if anyone wants it).

As for your last point, I worded it based on the idea that the user would be entering data into bound controls. The real idea was to make it so that clicking the save button was the only way to commit the data to the table. Your solution has that by default.

Thanks so much!

 

[dev_omi]

I would add a footer to your subform and add 2 inputs to it that would make it look like a new blank row of data. You can make this 2 inputs invisible upon load and when your button is clicked you can make them visible and take the focus to it.

Because these inputs are unbound nothing will be saved. When your Save button is clicked you can build an SQL INSERT statement and take the values from those inputs and add a record to the subforms underlying datasource and then refresh the subform so that the new record shows.

This sounds dicey--I enter my data in the unbound inputs then misclick and don't hit the button, or I accidentally hit tab or I scroll up the form to see if the one I am adding is already in there--then what? Delete what I typed? Show a message when I eventually go to the Save button that says no can do because you didn't directly hit Save after typing in data? I would rethink this. But it is possible, you just need to add something to the Lost Focus event to let the form know that the user just left the unbound inputs.

Okay, I'm sorry: I'm having a hell of a time getting the "New Record" button to make the subform footer visible. I'm trying stuff like this:

Me!subform!FormFooter.Visible = True
Me!subform!FormFooter.Visible = True
Me!subform.Form!FormFooter.Visible = True
Me!subform.Section(acFooter).Visible = True
Forms!form!subform...

Nothing works. It can't seem to find the footer of the subform. I've triple checked all of the names, and I've tried setting the focus to the subform before changing the footer properties. Thoughts?

Edit: I realize now that you said specifically that I could toggle the visibility of the input boxes, not the footer itself. Got it. Thanks.

 

[plog]

Thinking about it, it may be easier to make those inputs part of the main form. Visually it's going to be the same, but in VBA you will no longer have to go through the subform to reference them. In fact Me! will work if you make those unbound boxes part of the main form. And tab-wise you can make the save button come right after the last input.

 

[arnelgp]

you can use Transaction in your form?
is there a way to save and Don't save bounded fields? | Access World Forums (access-programmers.co.uk)

 

[CJ_London]

Just to ask the question- presumably it is intended that the records in the subform cannot be edited?

 

[dev_omi]

Thinking about it, it may be easier to make those inputs part of the main form. Visually it's going to be the same, but in VBA you will no longer have to go through the subform to reference them. In fact Me! will work if you make those unbound boxes part of the main form. And tab-wise you can make the save button come right after the last input.

Getting there! I like the way it's shaping up. Thanks for your help. (The drop down at the top toggles the subform.)

Just to ask the question- presumably it is intended that the records in the subform cannot be edited?

It's a good question. The original idea was that the user could edit them, but to commit the edits, they would have to click the "Save" button (so yes/no, in response to your question). I did some more research, and it seems like a lot of people think that's a bad idea. My guess for how best to do it would be to run code on update (?) that checks whether the "Save" button was pressed, and if it wasn't, have a message box that asks the user whether they want to save or discard changes. Does that sound right? (I just have to cobble together the code bit by bit, so it's not the fastest process, and like I said, I've kind of cooled off of the idea based on the responses I was seeing.)

Inspired by your question, I locked the code field but allowed the user to unlock it by double clicking (relocking on loss of focus). I want this form to be the main way to edit records in the underlying tables, and it needs to be possible to edit the codes (it would make sense if y'all knew what the project was). I figured that might be a nice happy medium.

 

[dev_omi]

Incidental question: As these subprocedures start to stack up for a given form, should I be making named modules within the form to organize them? Or is that bad?

 

[dev_omi]

Another question (sorry to stack them up, I'm just getting some good help here): The reason I have the little gap in the subform there is so that when the list gets big enough to require a scrollbar, it doesn't crowd out the subform controls. Is there a better solution to that problem? Ideally, the subform would grow to accommodate the scrollbar.

 

[CJ_London]

think that will be disconcerting to users, just make the subform control wide enough. You can hide the subform border if that helps. Or hide the subform control borders as well or instead

 

[CJ_London]

should I be making named modules within the form to organize them?

Forms have a module with subs and functions, you cannot nest modules inside another one.

just give you controls meaningful names

 

[CJ_London]

My guess for how best to do it would be to run code on update (?) that checks whether the "Save" button was pressed, and if it wasn't, have a message box that asks the user whether they want to save or discard changes

You might want to investigate using an ado disconnected recordset. That way user can change a number of records then save them all with one click of the save button

alternatively look at using transactions- Google begintrans and committrans

 

[KitaYama]

My guess for how best to do it would be to run code on update (?) that checks whether the "Save" button was pressed, and if it wasn't, have a message box that asks the user whether they want to save or discard changes. Does that sound right?

If you want user confirmation for saving a record, you don't need a save button at all.
Put the message box in Beforeupdate event of the sub form with yes/no buttons.
If user selects yes, do nothing and exit sub. Record will be saved.
If user selects No, then Cancel=true and me.undo and exit sub. The record won't be saved.

 

[CJ_London]

Don’t disagree with that principle but for a simple one field (to be edited) table per the op’s example would be a real PITA for users

 

[KitaYama]

Don’t disagree with that principle but for a simple one field (to be edited) table per the op’s example would be a real PITA for users

For sure. I will never do it myself. But it's OP's requirement.

 

[LarryE]

View attachment 102121

Another question (sorry to stack them up, I'm just getting some good help here): The reason I have the little gap in the subform there is so that when the list gets big enough to require a scrollbar, it doesn't crowd out the subform controls. Is there a better solution to that problem? Ideally, the subform would grow to accommodate the scrollbar.

If the delete button is on the unbound main form and it is clicked, will the record selected on the subform be deleted?