How to Enable New Record Entry in Continuous Form Without Scrolling to the Last Row?

[pjo3us]

Is it possible to configure a continuous form so that when the 'New Record' button is pressed, a new entry can be made in the same location where I was before entering the new record? I want to avoid being sent to the last row of the form.

 

[CJ_London]

You mean in a continuous form you want to create a blank row in the middle of a dataset for subsequent completion by the user in a similar way that you would insert a row in excel?

The problem you would have is order - databases order data, excel does not. So to achieve what you want you you would need to create an order value with gaps to enable the insert to add a record in the right place -then redisplay the data

Not saying it can’t be done, but would probably be a bit messy

A common technique is to use a continuous form with the ‘new record’ entered in the header or footer

 

[DickyP]

Two points here:

Of course you go to the end of the form as you are adding a record to the end of the table.

The new record should be at the right place in the table(, once saved) if you have a sort order defined for the form.

Personally I always define my continuous forms with Allow Additions, Allow Deletions and Allow Edits set to False and Have Add, Edit and Delete buttons and a record Details form. This way I am in control of the data (and yes it is a little bit of extra work but worth it).

 

[Edgar_]

Maybe...
Add a column to store the position of each record and order your form by that. When you want to add a new record that you want to show between two records, you will need to update the position of the records after the one you inserted. For example, suppose you had this dataset:

id    title    position
1    a        1
2    b        2
3    c        3

And if you wanted to add a record between 2 and 3, then, before inserting that new record, update your dataset like this:

id    title    position
1    a        1
2    b        2
3    c        4

Finally, add the record and make it take that position on insert, it would look like this in the end:

id    title    position
1    a        1
2    b        2
3    c        4
4    d        3

That would require updating where position > 2 to their position + 1. That why record 3 became 4, because the new record took position 3.

Hope it makes sense. You could also have your position column be multiples of 10, so that there are gaps between the dataset and you don't need to reorder anything. So, for example, when you insert a new record between 2 and 3, whose positions would be 20 and 30, the new record would probably be 25.

Just ideas...

 

[pjo3us]

You mean in a continuous form you want to create a blank row in the middle of a dataset for subsequent completion by the user in a similar way that you would insert a row in excel?

No, I would like it to be possible to enter new data in the place where the user is currently located, and the record will be in the last place of the record after the form is refreshed. This would help me a lot because a lot of data is entered where about 90% of the same values are repeated, so it would give the user the possibility of using a separate button for the possibility of copying the same record that is located above and copying it to a new field, so that the entire text does not have to be typed again. I hope you understand what I want

 

[Gasman]

You can do that without having to overwrite current records contents.
Your method sounds a horrible idea to me. Are those records not linked elsewhere?

 

[CJ_London]

Have you looked at setting the form data entry property to true?

Have you looked at setting default values - this can be done in the form before update event

 

[pjo3us]

You can do that without having to overwrite current records contents.
Your method sounds a horrible idea to me. Are those records not linked elsewhere?

I probably confused you. Most of the new input would use the normal way at the end of the record. But users send me feedback if it is possible that at the moment when the new record is almost identical to the records that were entered before, and to speed up the new entry in such a way that the entered fields are copied. That's why I'm asking if it's possible to come in a continuous form at the location of that record and start a new entry so that I can copy most of the same records that are above.

 

[arnelgp]

here is a demo of "cloning" a record.

 

[CJ_London]

problem with that is the focus remains on the original record

 

[arnelgp]

. That's why I'm asking if it's possible to come in a continuous form at the location of that record and start a new entry so that I can copy most of the same records that are above.

just add a Msgbox to inform the user that the Current record has been copied As New record.

problem with that is the focus remains on the original record

i think that was his intention? so he can copy more record "above", rather than going to the "new" copied record (post#1)

 

[CJ_London]

perhaps - attached is what I was suggesting

 

[ebs17]

A simple copy of a record (example from @arnelgp) will fail if there are fields that are uniquely indexed.

My tips for copying any record from the endless form would be...
1) Deal with the table definition
2) Transfer the selected record to a recordset
3) Use an additional instance of the form as an input form - as a pop-up or as a (second) subform in a main form
4) Write the recordset content into the input form, either omitting the uniquely indexed fields or marking them with a red background, for example
5) Now the user can make their changes
6) Perform validation when saving (Form_BeforeUpdate)

For user convenience, the developer has to put in more effort.

By the way: I am always amazed that the person asking the question is bombarded with sample databases that show individual measures better or worse. But the person asking the question could also upload a demo database with the form and the table linked to it, possibly supplemented by tables with relationships to be observed, without being asked, and show real relationships straight away - which can be more complex than the individual considerations mentioned above.

 

[arnelgp]

A simple copy of a record (example from @arnelgp) will fail if there are fields that are uniquely indexed.

you backread upto post #5 to understand what the OP has.

 

[Gasman]

Why not just give the users a button, or checkbox?
I entered over 22k records into one DB using this form.

I would copy over only the Date and Ship.

 

[arnelgp]

the good about my approach is that it will also Copy the Attachments and OLE field to the new record.

 

[ebs17]

because a lot of data is entered where about 90% of the same values are repeated

Something like this could be examined critically. Actually, only foreign key fields should be considered for repetition.

 

[Gasman]

Something like this could be examined critically. Actually, only foreign key fields should be considered for repetition.

As mine were.

 

[Pat Hartman]

Is it possible to configure a continuous form so that when the 'New Record' button is pressed, a new entry can be made in the same location where I was before entering the new record? I want to avoid being sent to the last row of the form.

This is not possible to change by using any form settings. The "new" record is ALWAYS at the end of the displayed records.

There are two ways to handle this.
1. Set allow additions to off. Add a "new" button with code to set allow additions on and move to the "new" record
2. Create an unbound form and position it on top of the existing subform. Add an Insert button that will validate the data and run an append query and sort the subform so the new record shows where it belongs. ALSO - because you now have two forms that allow changes to the same data, you need to export all your validation code to a separate function in a standard module. Pass in a reference to the calling form so that you don't have to duplicate the code because that is the road to disaster. In the validation procedure, you reference the form fields using frm. rather than Me. Keep in mind that the called validation function needs to return true or false depending on whether the save should be canceled or not. I know that sounds backwards so make sure you code it carefully and write notes to yourself so you won't change it during a senior moment.

Call ValidateCust(Me)




Public Function ValidateCust(frm as Form) as boolean
'''''
End Function

 

[Pat Hartman]

Actually, instead of calling the procedure. A better solution is:

Cancel = ValidateCust(Me)

That allows the function to determine if the record gets saved or not.