Controlling when new record gets written

[Steve@trop]

I have several forms that are set to open on a new record. I'm finding that while I'm working on the design of these forms I'm creating alot of blank records. This makes me think that when users start opening these forms, they may end up creating a lot of blank records as well.

I think this is happening because there are controls on these forms that have default values set in them.

Is there a way that I can control how and when an actual new record is created from a form? I was considering using a particular field as a trigger, once it has a value, the record is created. If it is null, then the record is discarded.

If I make a certain field a required field in the table, will that prevent a record from being created without an error message?

Thanks,

Steve

 

[PaulO]

I have several forms that are set to open on a new record. I'm finding that while I'm working on the design of these forms I'm creating alot of blank records. This makes me think that when users start opening these forms, they may end up creating a lot of blank records as well.

I think this is happening because there are controls on these forms that have default values set in them.

Is there a way that I can control how and when an actual new record is created from a form? I was considering using a particular field as a trigger, once it has a value, the record is created. If it is null, then the record is discarded.

If I make a certain field a required field in the table, will that prevent a record from being created without an error message?

Thanks,

Steve

Yes, this is a good way to prevent blank records. Equally, you could put 'Required' flag on the Field in the actual data Table and/or back this up with a cleanup query that deletes blank records each time you Load the Form.

 

[Steve@trop]

Thanks Paul,

I made one of the fields required in the data table. Then I added a button on the form that uses the "Undo Record" command so that users won't get stuck on the form if they change their mind about entering data.

I didn't find a way to make any of the text or combo box controls required fields in the form. Am I missing something?

 

[PaulO]

I didn't find a way to make any of the text or combo box controls required fields in the form. Am I missing something?

Not quite sure what you mean, Steve, but if you go into Design mode in the Form there's a Toolbox that will enable you to add various Form Objects such as Text Boxes and Combos.

 

[Mihail]

Yes, I think that you have missing something.
The REQUIRED property can be set at TABLE level for a certain field.
At the form level you have controls bounded to this fields.
The most usually controls are text boxes but also you can have combo boxes and check boxes (maybe other controls that I can't remember now or I have no knowledge about.
This controls are windows. (as the house windows) that enable you to see the value from a table for a certain record and a certain field. This "windows" have no requirements. It is equal for a window if you write or not data into the field.
On the other hand, for the table is NOT equal. If you try to save a record (by moving to other record as example) the TABLE will react and will say "You must write something in this field because this field is required".

In short, you can NOT set the REQUIRED property for a control.

 

[Steve@trop]

Thanks guys,

I know that fields can be set to be required at the table level. What I didn't know was that you can't set them to be required at the form level. It's OK, I think doing it this way is going to work fine.

Thanks again,

Steve

 

[Steve@trop]

I went in and set required fields in each one of my tables and this had the desired effect UNTIL I split and upsized the database. Everything seems to work OK but for some reason it's back to creating a record even though required fields are blank.

I went in and looked at the properties of the field in SQL Management Studio and it shows Required = True. I even tried making "Allow Zero Length" False. I can still create blank records. What am I doing wrong?

Thanks,

Steve

 

[Mihail]

Steve, all the setup seems to be good. Can't understand. Upload your database (better as .mdb) and I'll take a look, maybe I'll find an answer.

 

[Steve@trop]

Hi Mihail,

It works fine after I split it but after I upsized it (to SQL Express) that is when the problem started. I suspect it has something to with the translation to SQL tables. I don't know if there is a way to post this version. Perhaps Access ignores validation rules for linked tables?

Thanks,

Steve

 

[Mihail]

Sorry, but I can't help you more. Hope others will can.
Good luck !

 

[Steve@trop]

When I look at the properties of the table in Management Studio, it says the field is required. However, when I go into Design mode of the table it says it allows nulls for that field. If I change it, it won't let me save the change. It says the table needs to be dropped and re-created. I tried re-doing the upsizing but that didn't help. The new database had the same problem.

Steve

 

[Steve@trop]

I did some research and found one site that says that the upsizing wizard "doesn't support" required fields in tables. It sets all table fields to allow null values. I guess there is a SQL Server Migration Assistant that I can download and try. Anybody have any experience with this?

Steve