What is the best way to set up an editable data entry form?? (single form with sub-form)

[yellowgelb]

HI,
I am having a bit more general question.
My aim is to set up a data entry form for field data where the entered data is editable until the final submit. This could be around 20 to around 300 new rows (Id) at one time. The main table has around 25 columns (by now 10000 records). The plan is, to use a single form to enter the new records and when one catch (row/new record) is finished, it will pop up in the continuous form of the included sub-form. People often make little mistakes when they enter the data that's why they should be able to correct the entered data in the sub-form. Later they should submit the data and everything will be attached to the original table.

My question is: What would be the best way to implement that. My ideas so far are:

1. Set up a copy of the original basic data table (tblBasicDataCopy) which will be filled with the new information. So the forms could be bound to this table. After attaching the data through a query to the original table (tblBasicData) the copy table (tblBasicDataCopy) would be emptied. (Pro: easy to set up; Con: people are writing this will bloat the database. But how much will it bloat? We've got around 2000 new ID in the main table per year plus a more a few more in additional tables.)

2. Set up unbound forms and a temporary database with the needed table, which stores all information until submitting. After submitting the temp database would be deleted.


(Con probably takes longer to set up. Not sure if it is more error-prone? Pro no bloat)

What would you recommend? Or does anyone have better suggestions? All ideas appreciated.

Thanks heaps for your help!!

[ItsMe]

It is doubtful you are trying to address an issue that is unique to you. However, I do not know that I fully understand.

Often times I will use a subform in DS view as a summary for data entry. After the user is finished entering data into the fields within the Main Form, they hit a "Submit" button. This fires code for validation and Requeries the subform.

The subform does not allow edits. However, if the user needs to make an edit, they can double click any field, within the subform, to open a popup modal form to edit the relative record.

The main tricks are avoiding warning messages that another user has edited the same record and data validation. I will be sure to use code to save the record, naming all open forms, and I may opt for an unbound form as the Main Form.

[yellowgelb]

Hi thanks for your answer.

Your approach would be an option for me. I am wondering to which source your subform is bound. I would like to make sure that the user can only edit data which she/he is entering. I don't won't them to edit data which was entered by someone else a few days ago or years. I want to make sure that they have (through this form) only access to the field data they have collected on their last trip. That's way I thought I have to use e.g. temporary table as a source for the subform.

How would you deal with that? Thanks again for your help!

I attached a screenshot of the form. I hope this will help to explain what I want to do.

[ItsMe]

The subform can use the same or a very similar query. If there are many fields that need input, I will break it up into stages/phases. The user would input data into a dozen or so fields and save their record to move onto the next stage. The summary/subform may, only, offer a portion of all the available fields (within any given session). The ability to edit is managed by an entirely different form and sometimes requires its own query. The user would reference the subform to determine which record they want to edit.

In order to distinguish one user's entries from another user and in order to distinguish any given user's input from one session/day from another session/point in time, I will create session ID's. The session ID is generated at the start of the session and included in every record created (for that session), at the Parent table level.

I generate session ID's using GUID and the user's UserID Primary Key. I combine the two because there is a very slight chance of duplicating a GUID. If it is super critical, a timestamp can be combined with the GUID. The timestamp would remain the same value throughout the session, regardless of the current time. The timestamp is merely a unique value combined with another unique value, the GUID.

Here is an extract from one of my DB's, used to generate a session ID. It generates the GUID and then combines it with another unique number that is already present in a form. The code illustrates generating a DUID and reformatting it so it is acceptable for SQL and VBA. You may need to create a reference in order for the Objects to be created, I cannot recall.

'Generate a unique ID for the session
Dim strSesID As String
Dim objGUID As Object
Dim strGUID As String
Set objGUID = CreateObject("Scriptlet.TypeLib")
strGUID = objGUID.Guid
'Make it suitable for SQL
strGUID = Mid(strGUID, 2, Len(strGUID) - 4)
strGUID = Replace(strGUID, "-", "_")
strSesID = Forms!frmMenu.PrimeNum.Value & "_" & strGUID

[CJ_London]

you can also look at using transactions - here are some links

https://support.microsoft.com/en-us/kb/248011
http://stackoverflow.com/questions/1...-a-transaction
https://www.safaribooksonline.com/li...0/ch04s05.html