Re: Exclusive Form

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, ...)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"tboggs" <tboggs@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F9E5FC2D-3D31-455C-B894-5D450711B41D@xxxxxxxxxxxxxxxx
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













"Stuart McCall" wrote:

Answers inline:

"tboggs" <tboggs@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:370E2CD4-034A-4E6A-97CC-8229C4C0DF70@xxxxxxxxxxxxxxxx
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)

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.

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

Do you have any ideas?????

Try doing it the way I described above.

<SNIP>





.

Relevant Pages

  • Re: hide a message
    ... At depends on what syntax you use to run the query. ... CurrentDb.Execute "QueryName", dbFailOnError ... "Avi Algazi-Atik Computers Ltd" wrote in message ...
    (microsoft.public.access.formscoding)
  • RE: EXISTS reserved word in FROM clause
    ... we make a good pair because I know Access SQL ... from what you described the query syntax that I ... >The subquery should return the records: ...
    (microsoft.public.access.queries)
  • Re: Bizarre subquery syntax problem
    ... With Access, the official syntax for a subquery always requires an alias, so ... SELECT p.AS UID, p.Firstname AS Firstname, p.Lastname AS Lastname, ... I am seeing bizarre problems with a subquery. ... core query runs fine- ...
    (microsoft.public.access.queries)
  • Re: PSP
    ... I'd be interested in your opinions on syntax of PSP... ... Furthermore, if a query fails, it does not ... > look like it allows the programmer to directly mix the Prolog and HTML. ... Another downside is that predicates cannot be ...
    (comp.lang.prolog)
  • RE: sending a query to access in asp.net
    ... Microsoft Online Support ... | Regarding on the ACCESS sql query statement issue you mentioned, ... | Dim param As New OleDb.OleDbParameter ... While the ACCESS database use '*' as the widchar for like syntax, ...
    (microsoft.public.dotnet.framework.aspnet)