Problem with assignimg default value to text box when entering new record

[ezybusy]

Hi everyone
I have an entering form that allow me to insert record into a table.
On that form I have a field named "addedby" which picks the name of the current logged in userid as default value when entering new record.
I have the following code that allow the above to work:

Private Sub Form_Current ()
If Me.New record = True Then
Me!Addedby = Forms!frm_MainMenu.Txt UserId
End If
End Sub

However, when a new record entry is cancelled (clik on add new button and cancel entry), new row is created in the table as if the entry process was not cancelled.
Meaning if we have to cancel 1000 record entry, we are going to end up having 1000 rows created with all fields being blank, except the ID.

Note that my table has the field "Id" as Auto number which is the primary.

Can anyone help me get rid of this problem.

[ranman256]

sorry but thats the way default value works.
Instead of entering directly on the table, you can enter data on an unbound form.
Clicking the Save button will run an append query. This way there are no broken records.

[ItsMe]

If you do not want to use an unbound form, you can use the Form's unload event for data validation. In the unload event, you can add code to make sure the required fields are completed, as well as including the User in the record.

One thing to keep in mind is that Users may choose to use the Close button of a form or use the Navigation Buttons to navigate from one record to another. A user may fill out all of the required fields, then decide they want to cancel. They may believe closing the form or using another method is the same as cancelling. With bound forms, you need to take extra steps to determine which datum gets committed to the table.

[ezybusy]

Hi ITSME
Please can you help me with the steps in using Form's unload event.
I have attached a sample of the application.MySample.zipMySample.zip

[ItsMe]

I took a look at your DB. The Unload event can be overridden by embedded macros. Since you have embedded macros to do things like save a record and navigate to the next record, you will have to code these processes, too.

The topic of data validation is huge. There are so many things to check for. In addition, you have to apply error trapping. So, in case there is a Run Time exception, you need to write code to handle that. When you lock a User into completing something before they can do anything else, you need to offer them a way out. Managing runtime exceptions and handing control back to the user is not simple.

Having said that, here are some examples that can get you started.

One way to manage required fields is at the table level. In design view of a table you can set the properties of a field to Required = True. Then, in your form you can create an event to log your user. Applying the constraints for required fields at the table level, you could add the following to the Form's Unload event.
Me!AddedBy = Forms!frm_MainMenu.TxtUserId

The problem with the above is when a user only wants to view records. So you will not want that code executing if the form is opened in DataEntry Mode. So you could probably get by with a check like ...
If Me.DataEntry = True Then
Me!AddedBy = Forms!frm_MainMenu.TxtUserId
end if

So you would have to recreate your controls for saving records and such. In other words, remove the embedded macros. Then something like the above should work. The other option is to manage constraints in the form. So, do data validation in the form and not depend on the table. This can get complex real quick. Let me know if you are interested in managing the constraints with code.

[ezybusy]

ItsMe
I really don't get the way in which constraint could help me here since all I am looking to achieve is to allow the user to cancel data entry at any time without getting new rows created in my table after every cancellation.
Therefore I think enforcing some sort of constraint at either form or table level will in some way force the user to complete data entry even when they wish to cancel the entry process.
Pardon me if I am wrong, but thats how it sounds to me.

However, if applying constraint programmatically can be a way of helping me achieve what I want then please guide me.
Thx for been so patient with me.

[ItsMe]

The following code will assign the value to the field.
Me!AddedBy = Forms!frm_MainMenu.TxtUserId

You already know about that. The question is, "When do you want to do this?" You could place the code in the Unload Event of the form
Me!AddedBy = Forms!frm_MainMenu.TxtUserId

You can place the code in the On Current
Me!AddedBy = Forms!frm_MainMenu.TxtUserId

However, there are other factors that need to be considered. You need to apply constraints to the data. Every data base has them. Well, any real database should have constraints. For you, it may be as simple as using the Unload Event and making sure the form was opened in DataEntry Mode.

If Me.DataEntry = True Then
 Me!AddedBy = Forms!frm_MainMenu.TxtUserId
 end if

You already recognize that the OnCurent is not satisfactory. So you need to consider the business rules, create restraints, and make sure the constraints are enforced. This is how you maintain the integrity of the data.

I am trying to think of a way that you can do this and still use the embedded macros. Right now, I am drawing a blank.

[ezybusy]

Ok ItsMe.
I am also trying to think of some logic that could work.

[ezybusy]

Actually what I need is to keep track/history of the following:
-When a new record is created, want to know who created that record, the ID of the created record and the time/date at which it was created.

- when a record is deleted or modified, keep track of the author of these modifications, with date/time and ID of the record modified / deleted.

So in case assign default value to my table is complicated , can you help me in any other way to achieve what I mentioned above??

[ItsMe]

-When a new record is created, want to know who created that record, the ID of the created record and the time/date at which it was created.

All of this information can be stored in the record itself. This is common. Sometimes, logging is something that is stored separate from the table(s) and even separate from the database. For instance, error logging is normally stored outside of the database.

One thing though, I am not aware of any built in macros that do this sort of thing. Also, the macros you employed on your form there will override VBA code in many instances. So, if you want to log who created a record and the On Current event is not working I don't know what else to suggest. The only way I know how to manage the Rule you defined is to use VBA.

For me, it's easiest to manage all of the business rules, referential integrity, and other constraints using code. I use some built in features of Access, but most of them I avoid. There are always exceptions and sometimes Macros or the Wizard are helpful.

- when a record is deleted or modified, keep track of the author of these modifications, with date/time and ID of the record modified / deleted.

If you are going to keep track of modifications, you will need another table, at least. As for deleting, just, do not allow it. Instead, use a Yes/No field to indicate the status of a record. Some column names I use for Yes/No fields are Archive, Obsolete, and Active. I usually reserve Active to indicate where, within the Business Processes, a record is. For instance, A sales order has a certain lifecycle. After a sale is closed, I will cause the Active field to equal False. Similarly, I might Archive an order that is older than three years or five years. That same record may become Obsolete if a User deletes it.

[ssanfu]

Another idea.....

In addition to what ItsMe has advised you, in Post#1 you said

Meaning if we have to cancel 1000 record entry, we are going to end up having 1000 rows created with all fields being blank, except the ID

I have a dB to enter hours. The default value is -1111. When the form closes (the form unloads), a delete query is executed that deletes any records where hours = -1111.
-1111 means the record is incomplete because hours were not entered. There should never be a record where the hours = -1111.

Do you have a field that MUST have data? If the field is NULL or you can set a default value to check for? You could use the method above.
Or check if every field except the PK field (autonumber) in empty/null? If all fields are empty/NULL, delete the record.

I totally agree with ItsMe about not allowing record deletes, but if you can determine WHAT constitutes a canceled entry, there are ways to remove that (empty) record.

[ezybusy]

Thx for the suggestion the idea Ssanfu.
Considering the sample db I attached earlier on, you will notice that I have a field "importername". Let's consider that field as the base for cancellation of incomplete records. That is "if importername is null then the record should be delete".
Can you guide me on how to go about it?

Thinking about the logic I am sure we will end up with a lot of waste (huge gaps between records since the Id is an auto increment).

However I still want to try it out and see wether it can help me sort out my issue.
Kindly guide me.

[ssanfu]

Try:
Open the code module for the form "ImporterDetails"
Paste in the following:

Private Sub Form_Unload(Cancel As Integer)
    CurrentDb.Execute "DELETE * FROM Tbl_Importer WHERE importername IS NULL"
End Sub

BTW; "profile" is misspelled in 3 places.....

[ezybusy]

Thx for quick reply Ssanfu.
Just tried the above, it works. But as I thought there are gaps between records.

I was thinking of the following logic:

If Me.New record = True then
(If importername is not null then)
Me!addedby = forms!frm_Main_Menu.txtUserId
(Else cancel entry)
End if

It's is possible to write such a code? ??

[ssanfu]

But as I thought there are gaps between records.

Don't understand.
What do you mean "gaps between records"??
In what table? What field?