Most robust way to add a new record...

[Chris77]

Hello.

I am using continuous forms, and have set AllowAdditions = False.

This removes the "extra record" at the bottom of the page, which is great. However, it is possible for me to click my "add a record" button (this sets AllowAdditions = True and goes to the new record--click, and start typing) ... and then click on another record, thus preserving this new blank record.



This new blank record is confusing to users and gets in the way.

Ideally, OnCurrent should look at the previous selected record; if this is null or empty, it should set AllowAdditions = False and GoTo first record.

Is there any way to do this, or a more general, "most robust" way to go about this task that is navigation-agnostic (e.g. someone clicking on another record after trying to add a new one will be met with the new blank record vanishing)?

Thanks!

[Chris77]

It would appear, from the response here, that everyone just hopes for the best when using Access.

There really is no "best" way to do this, such that the form is stateless?

That is shocking.

[ssanfu]

Don't know your structure (table names, field names, relationships) or button code, but you could bypass the Access UI and insert a new record using SQL. Something like:

Currentdb.Execute "INSERT INTO TableName (field, field2) VALUES ("SomeText", A_Number)

Then, in the form, move to the new record and continue editing. Don't have to change the Allow Additions property.

Or you could use the Add Record button code to set AllowAdditions = True , save the record, then setAllowAdditions = False. Continue editing the record.

I haven't tried either method.... just thinking about it. Probably a couple of other methods also.


Shockingly, I must have missed your original post. Gook luck with your project. If you have more questions, post back with more details.

[Chris77]

Don't know your structure (table names, field names, relationships) or button code, but you could bypass the Access UI and insert a new record using SQL. Something like:

Currentdb.Execute "INSERT INTO TableName (field, field2) VALUES ("SomeText", A_Number)

Then, in the form, move to the new record and continue editing. Don't have to change the Allow Additions property.

Or you could use the Add Record button code to set AllowAdditions = True , save the record, then setAllowAdditions = False. Continue editing the record.

I haven't tried either method.... just thinking about it. Probably a couple of other methods also.


Shockingly, I must have missed your original post. Gook luck with your project. If you have more questions, post back with more details.

First, thank you for the civil reply.

Second, I am trying to do things The Access Way with as little SQL code on the back-end as possible. Frankly, I'd rather do this project over the web using all SQL code, but the tool I've been handed is Access 2010, and I'd like to work within it and reduce direct SQL calls in code where possible. As a side-note, I point all my NotInList drop-down calls at a common function that takes care of inserts and error-trapping. This is a simple thing to point to ("wherever there's a drop-down [combo] box, it'll point to this area of code"), but I'm also designing this with the next person in mind. I don't want to leave them digging through code if I can avoid it.

Both suggestions you give have merit, though I'm generally more inclined to use the second in this particular case (code that I won't be maintaining).

It seems that there ought to be a "best" way to do this in Access, and I was hoping I'd just not seen it. I am using your second suggestion, but need to look at my forms to determine a better way to handle state changes (e.g. if someone is only halfway through adding a new record, and they click on another record, catch this and ask them if they really want to drop their changes). My goal is to make these forms as robust as possible (as the project has changed in scope and will now be deployed throughout the enterprise) and protect users and the data wherever possible.

Finally, a more general question to you, Steve: do you typically set the tab keys to cycle through the selected record only, or just leave it at the default (all records)? I see value in both, though I'm trying to cut down on interaction with the mouse when I can (e.g. I have a slight preference to cycle through all fields of all displayed records).

Thank you for your insights,

C.

[ssanfu]

do you typically set the tab keys to cycle through the selected record only, or just leave it at the default (all records)?

I can't think of a time that I changed from the default of All Records.

Both suggestions you give have merit, though I'm generally more inclined to use the second in this particular case (code that I won't be maintaining).

It seems that there ought to be a "best" way to do this in Access, and I was hoping I'd just not seen it. I am using your second suggestion, but need to look at my forms to determine a better way to handle state changes (e.g. if someone is only halfway through adding a new record, and they click on another record, catch this and ask them if they really want to drop their changes).

The "best" way is what is most effective for that instance. (duh!)

For example, I have a work hours entry form. When the form opens, it executes code that adds a record for each person. The hours field has a default of -1111 hours. Entries are made for the people that worked. When the form closes, code executes that deletes any record with work hours of -1111. This is much better than searching thru a list of people one at a time to add hours.


The good thing about my suggestions is that the new record is created and saved without any other action required. The bad thing is that the new record is created and saved without any other action required. You still need to execute code/query to delete the "new" record if someone wants to drop the "new" record.

An alternative (suggestion 3) is to use an unbound form & controls. It is easier to drop the record, but still requires an append query or a SQL statement/VBA code to add the record. AND requires you to handle everything - adding, validation, deleting...... And you have to use a single form, not continous form.
I have had to use unbound forms a few times and helped others when the only option was to use an unbound form. A lot of work, but effective.

So really, it comes down to your style of programming.........