Exclusive Form

[tboggs]

Is there a way to set one form to be exclusive? The form that I want to be
exclusive automatically posts a number that is required and if two people
enter before hitting the save button, then the second person has to retype
all of the information.

I have a fairly good knowledge of programming, but I'm not sure how to
accomplish this in Access.

Any help would be greatly appreciated!

- Travis

 

[Stuart McCall]

Is there a way to set one form to be exclusive? The form that I want to
be
exclusive automatically posts a number that is required and if two people
enter before hitting the save button, then the second person has to retype
all of the information.

I have a fairly good knowledge of programming, but I'm not sure how to
accomplish this in Access.

Any help would be greatly appreciated!

- Travis

Set the form's 'Record Locks' property to 'Edited Record'. Should do exactly
what you want.

 

[tboggs]

That didn't seem to work. The second person can still open the form. I
suspect this is because the first person hasn't saved the record yet and
Access isn't picking up that the record needs to be locked?

What I would really like, is for a popup to tell the 2nd person to open the
form that someone is already in the form and that they must wait for them to
finish before opening. I would also like the form to automatically close on
the 2nd person so no information can be inputted.

Thanks again for any help that can be provided!!

- Travis

 

[Stuart McCall]

That didn't seem to work. The second person can still open the form. I
suspect this is because the first person hasn't saved the record yet and
Access isn't picking up that the record needs to be locked?

What I would really like, is for a popup to tell the 2nd person to open
the
form that someone is already in the form and that they must wait for them
to
finish before opening. I would also like the form to automatically close
on
the 2nd person so no information can be inputted.

Thanks again for any help that can be provided!!

- Travis

Well Access doesn't work that way 'out of the box', so you need to roll your
own system to do this.

I suggest a new table with 1 field:

FormName Text 50

In the form's OnOpen event, put:

If Dcount("FormName", FormTable", Me.Name) > 0 Then
'Tell user no go till the form is free again
Exit Sub
End If
CurrentDb.Execute "Insert Into FormTable Set FormName = '" & Me.Name & "'",
dbFailOnError

(that should be all one line)

In the Form's OnClose event, put:

CurrentDb.Execute "Delete From FormTable Where FormName = '" & Me.Name &
"'", dbFailOnError


Note that this technique 'locks' the form, not the record. That seems to be
what you're asking for.

 

[tboggs]

Your code has definitely got me going in the right direction, but I’m getting
some errors. I believe this is becoming more difficult because the first guy
that made the database put spaces in the names of objects as you will see
below.

------------------------------------------------------------------

First, I created a new table called FormLock with one field named FormLock
Text 50 (I’m confused in this area)

Then, I created a field in the normal table (Case Files) that records are
saved to in the form I want to lock (Add New Case File). The field is also
called FormLock Text 50

Finally, I added that field to the form (Add New Case File) and made the
FormLock fields correspond to each other between the two tables (1 record to
1 record). I also made this text box invisible on the form.

---------------------------------------------------------------------
Here is the code that I’m currently using on the OnOpen Event:

If DCount("'Add New Case File'", "Case Files", Me.FormLock) > 0 Then
MsgBox ("You may not open this form until another user exits.")
DoCmd.Close
Exit Sub
End If

CurrentDb.Execute "Insert Into Case Files Set Add New Case File = '" &
Me.FormLock & "'", dbFailOnError

I don’t get any errors, but the form seems to think that someone is already
in it and will not let the first user in.
------------------------------------------------------------------

Here is the code that I’m currently using on the OnClose Event:

CurrentDb.Execute "Delete From Case Files Where Add New Case File = '" &
Me.FormLock & "'", dbFailOnError

On this event, I get run-time error 3075: syntax error (missing operator)
in query expression ‘Add New Case File =’â€.

I’ve tried putting underscores between Case Files and Add New Case File, but
this also gives me the error:

Run-time error 3078

The Microsoft office access database engine cannot find the input table or
query ‘Case_Files’. Make sure it exists and that its name is spelled
correctly.

--------------------------------------------------------------------

Do you have any ideas?????

Thanks for the help!!!

- Travis

 

[Stuart McCall]

Answers inline:

Your code has definitely got me going in the right direction, but I’m
getting
some errors. I believe this is becoming more difficult because the first
guy
that made the database put spaces in the names of objects as you will see
below.

My fault for not being clear enough. I meant a field of type text with a
length of 50. There ought to be 1 field called FormLock. It's type should be
set to Text. If your table is open in design view and you select the field's
name in the top list, the bottom part of the screen shows a property called
Field Size. Set this to 50. Also I think you may have trouble with your
table having the same name as your field. Rename the table to (say)
FormLocks.

The purpose of this table is to contain the name of any open form which you
need to lock, so when the form opens it adds a record with FormLock
containing it's own name. This indicates that the form is 'busy'. When the
form is closed, the same record is deleted, thereby 'freeing up' the form
for use by others.

Then, I created a field in the normal table (Case Files) that records are
saved to in the form I want to lock (Add New Case File). The field is
also
called FormLock Text 50

I don't see a need for this and definitely didn't suggest it.

Finally, I added that field to the form (Add New Case File) and made the
FormLock fields correspond to each other between the two tables (1 record
to
1 record). I also made this text box invisible on the form.

Again, no need to do this (that I can see).

---------------------------------------------------------------------
Here is the code that I’m currently using on the OnOpen Event:

If DCount("'Add New Case File'", "Case Files", Me.FormLock) > 0 Then
MsgBox ("You may not open this form until another user exits.")
DoCmd.Close
Exit Sub
End If

CurrentDb.Execute "Insert Into Case Files Set Add New Case File = '" &
Me.FormLock & "'", dbFailOnError

Totally wrong I'm afraid. It needs to be something like:

If Dcount("FormLock", "FormLocks", "FormLock = '" & Me.Name & "'") Then
MsgBox ("You may not open this form until another user exits.")
DoCmd.Close
Exit Sub
End If
CurrentDb.Execute "Insert Into FormLocks Set FormLock = '" & Me.Name & "'",
dbFailOnError

I don’t get any errors, but the form seems to think that someone is
already
in it and will not let the first user in.
------------------------------------------------------------------

Here is the code that I’m currently using on the OnClose Event:

CurrentDb.Execute "Delete From Case Files Where Add New Case File = '" &
Me.FormLock & "'", dbFailOnError

Wrong again:

CurrentDb.Execute "Delete From FormLocks Where FormLock = '" & Me.Name &
"'", dbFailOnError

On this event, I get run-time error 3075: syntax error (missing operator)
in query expression ‘Add New Case File =’”.

I’ve tried putting underscores between Case Files and Add New Case File,
but
this also gives me the error:

Run-time error 3078

The Microsoft office access database engine cannot find the input table or
query ‘Case_Files’. Make sure it exists and that its name is spelled
correctly.

Try doing it the way I described above.

<SNIP>

 

[tboggs]

Please advise as to why there is a syntax error when I copy and past your
code. "Syntax error in INSERT INTO statement." (Yes, I have moved the
insert into command all to one line)

I have the following

OnOpen:

If DCount("FormLock", "FormLocks", "FormLock = '" & Me.Name & "'") Then
MsgBox ("You may not open this form until another user exits.")
DoCmd.Close
Exit Sub
End If

CurrentDb.Execute "Insert Into FormLocks Set FormLock = '" & Me.Name &
"'", dbFailOnError


OnClose:

CurrentDb.Execute "Delete From FormLocks Where FormLock = '" & Me.Name &
"'", dbFailOnError

 

[Douglas J. Steele]

You're using the syntax for an UPDATE query, not an INSERT INTO query.

The syntax for INSERT INTO is either

INSERT INTO Table(Field1, Field2, ...)
SELECT Field1, Field2, ...
FROM OtherTable

or

INSERT INTO Table(Field1, Field2, ...)
VALUES (Value1, Value2, ...)

 

[Stuart McCall]

Please advise as to why there is a syntax error when I copy and past your
code. "Syntax error in INSERT INTO statement." (Yes, I have moved the
insert into command all to one line)

I have the following

OnOpen:

If DCount("FormLock", "FormLocks", "FormLock = '" & Me.Name & "'") Then
MsgBox ("You may not open this form until another user exits.")
DoCmd.Close
Exit Sub
End If

CurrentDb.Execute "Insert Into FormLocks Set FormLock = '" & Me.Name &
"'", dbFailOnError


OnClose:

CurrentDb.Execute "Delete From FormLocks Where FormLock = '" & Me.Name &
"'", dbFailOnError

<SNIP>

Sorry, my bad. The line should be:

CurrentDb.Execute "Insert Into FormLocks (FormLock) Values (" & Me.Name &
")", dbFailOnError

 

[tboggs]

Thanks a ton! It works and it makes sense; however, now I'm getting a syntax
error on the delete from. I looked up the help on the delete from statement,
but I'm not understanding the error.

Here is the code I'm using on the close event:

CurrentDb.Execute "Delete From FormLocks Where (FormLock) Values (" &
Me.Name & ")", dbFailOnError


Here is the error I'm getting:

Syntax error (missing operator) in query expression '(FormLock) Values



Again, thank you both for helping me out with this. The syntax is much
different than what I'm used to doing.


- Travis

 

[Stuart McCall]

Thanks a ton! It works and it makes sense; however, now I'm getting a
syntax
error on the delete from. I looked up the help on the delete from
statement,
but I'm not understanding the error.

Here is the code I'm using on the close event:

CurrentDb.Execute "Delete From FormLocks Where (FormLock) Values (" &
Me.Name & ")", dbFailOnError


Here is the error I'm getting:

Syntax error (missing operator) in query expression '(FormLock) Values



Again, thank you both for helping me out with this. The syntax is much
different than what I'm used to doing.


- Travis

It was only the 1st SQL statement that was screwed up. The Delete syntax I
posted 2 messages back is what you want:

CurrentDb.Execute "Delete From FormLocks Where FormLock = '" & Me.Name &
"'", dbFailOnError

If you want to learn more about SQL and it's syntax, you could do a lot
worse that to start here:

http://www.w3schools.com/sql/default.asp

 

[tboggs]

Thanks for all the help and def the link! I will be reading it over the
weekend!

- Travis