Prevent Opening Form if Record in Use

[Taff]

Hi all,

I have a database shared across a network and the problem I am having is when one of my forms is closed, if data is missing it will fill them in.

If someone already has the record open is it possible to prevent opening of the form with that corresponding record?

Thanks for any help.

Anthony

 

[SQL_Hell]

here you go:


Situation: You know there will be multiple users, and your application has some data entry forms. You don't want users to overwrite a record that someone else is editing.

Resolution: You want to set the Application to pessimistic Locking. This means that once a user looks at a record, it's locked up and only that user can make changes.

How To:
1. Stick this in your module.
Public Sub SetRecLocks()
'This command sets "Pessismistic" locking.
'If someone is poking around in a given record, no one else can edit it.
Application.SetOption "Default Record Locking", 2
End Sub

2. Presuming you've got a form that always opens at startup, stick this event driven piece in it
Private Sub Form_Load()
SetRecLocks
End Sub

 

[Taff]

Thanks for the reply SQL_Hell,

Unfortunately when a user clicks a command button to close the form, if certain fields are null, it automatically inserts the required information.

So even with pessimistic locking it does not seem to be stopping the user from editing the data.

Ant.

 

[ColinEssex]

If users are allowed to get away without filling in certain fields yet they get filled in automatically, why not have the fields fill in automatically by default anyway and not have the user do it?

Or have it so the user can't close the form until the required fields have been filled in by them

Col

 

[Taff]

Hi Col,

I originally had the fields filled in by default values but apparently this is confusing for the user when adding a new record?

Unfortunately certain fields are required for some records but not others.

Ant.

 

[sammylou]

Another way round the field data problem, is to ensure that records cannot be saved unless certain mandatory fields are fill in . You can do this easily by using vba with a save button on the form. I have a record ID which is generated by the database when a new record is entered, if mandatory fields are not filled in the the record will not be allocated a record ID. If the user then exits the form and the record ID = zero then they will be prompted that the record will not be saved unless they complete the required fields. Addtionally, message boxes will pop up when updating existing records to ensure mandatory fields are still populated according to your requirements. This stops people leaving rubbish records in the database

 

[ColinEssex]

or just make the fields required in table properties

 

[Taff]

Thanks again for your replies,

will have a look into it.

Ant.

 

[sammylou]

or just make the fields required in table properties

Bear in mind that this will only work if the criteria for making the field required is static. It may be that fields are required dependent on the data entered in other fields

 

[Taff]

That's correct Sammylou.

Ant.