what is wrong with this code?

[Guest]

i have been trying to do an update to a table through code so i can clear a
record that causes a form to load on a specific record. everything else works
great but when i put in this code my button breaks.

heres the code:

Private Sub btnExit_Click()
On Error GoTo Err_btnExit_Click

DoCmd.Close acForm, "zzMAINFORM"

If Not IsNull(DLookup("Value", "tblSys", "[Variable]=ProductIDLast")) Then
DoCmd.RunSQL "UPDATE tblSys SET tblSys.[Value] = ""1"" WHERE
(((tblSys.Variable)=""ProductIDLast""));"
End If

DoCmd.Quit

Exit_btnExit_Click:
Exit Sub
Err_btnExit_Click:
MsgBox Err.DESCRIPTION
Resume Exit_btnExit_Click
End Sub

i keep getting "you canceled the previous operation". i hope it has to do
with the quotes on the sql statement, but i am unsure of what is really
wrong. there is an unload event on zzMAINFORM and so i told this button to
close the form first, then update the record in the table, then close the
application.

i dont know where the problem is in the sql, but that is what my biggest
suspect is at the moment.

any ideas?

 

[Jeff Boyce]

Are you saying that you want Access to:

1. close the form (zzMAINFORM) that holds your data
2. update your table based on the data in the zzMAINFORM
3. quit the application

How will Access be able to do the update if you've already closed the form?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[OldPro]

i have been trying to do an update to a table through code so i can clear a
record that causes a form to load on a specific record. everything else works
great but when i put in this code my button breaks.

heres the code:

Private Sub btnExit_Click()
On Error GoTo Err_btnExit_Click

DoCmd.Close acForm, "zzMAINFORM"

If Not IsNull(DLookup("Value", "tblSys", "[Variable]=ProductIDLast")) Then
DoCmd.RunSQL "UPDATE tblSys SET tblSys.[Value] = ""1"" WHERE
(((tblSys.Variable)=""ProductIDLast""));"
End If

DoCmd.Quit

Exit_btnExit_Click:
Exit Sub
Err_btnExit_Click:
MsgBox Err.DESCRIPTION
Resume Exit_btnExit_Click
End Sub

i keep getting "you canceled the previous operation". i hope it has to do
with the quotes on the sql statement, but i am unsure of what is really
wrong. there is an unload event on zzMAINFORM and so i told this button to
close the form first, then update the record in the table, then close the
application.

i dont know where the problem is in the sql, but that is what my biggest
suspect is at the moment.

any ideas?

What is ProductIDLast? is it a variable? If so, then you are using
the wrong syntax. Is it a number? If so then the query should look
more like :
If Not IsNull(DLookup("Value", "tblSys", "[Variable]=" &
ProductIDLast)) Then
DoCmd.RunSQL "UPDATE tblSys SET tblSys.[Value] = '1' WHERE
(((tblSys.Variable)=" & ProductIDLast & "));"
End If
If it is a character string, then it should look like this:
If Not IsNull(DLookup("Value", "tblSys", "[Variable]=' " &
ProductIDLast & " ' ")) Then
DoCmd.RunSQL "UPDATE tblSys SET tblSys.[Value] = '1' WHERE
(((tblSys.Variable)=' " & ProductIDLast & " ' ));"
End If

Although double quotes work okay, it is easier to use double quotes on
the SQL string itself, and single quotes inside the SQL string. It
just makes it clearer.

A good debugging tool is the F9 and F8 function keys. Position the
cursor over a line of code and hit F9. The execution of the code will
stop at that point. Use F8 to continue to execute the code one line
at a time until you find the error.
A good trick for debugging SQL is to copy the SQL string to the View
SQL of a query, run it and see where it errors out. The easy way to
do this is to use F9 to stop the code, and then Ctrl-G to bring up the
debug screen. For this to work, the SQL string must be inside a
variable. Then type ? VarName (or whatever the variable's name is) to
get the full SQL string and then copy it to a new query.

 

[Guest]

Jeff boyce wrote:
Are you saying that you want Access to:

1. close the form (zzMAINFORM) that holds your data
2. update your table based on the data in the zzMAINFORM
3. quit the application

How will Access be able to do the update if you've already closed the form?

DawnTreader's Response: the value i am trying to change is not on the form,
nor does it come from the form. there is code for when the zzMAINFORM unloads
to insert a value into a table that stores what the last record looked at
was. i want to clear that value when people exit the database so the next
time it comes up at the default record.

the unload event is designed to store it so that i can close the form, open
another form, then when i close the second form, it goes back to the record i
was looking at last. this part works perfect. but when i close the db and
reopen it shows the record i was at last the last time i opened the db.

make sense?

so i want to clear that value which i stored in a table with 2 other peices
of information on the same record line. that value of what record was open
last is what i am trying to change. i want the exiting of the database by
this button to put a 1 in place of the value of the last record i was looking
at.

oldpro wrote:

What is ProductIDLast? is it a variable? If so, then you are using
the wrong syntax. Is it a number? If so then the query should look
more like :
If Not IsNull(DLookup("Value", "tblSys", "[Variable]=" &
ProductIDLast)) Then
DoCmd.RunSQL "UPDATE tblSys SET tblSys.[Value] = '1' WHERE
(((tblSys.Variable)=" & ProductIDLast & "));"
End If
If it is a character string, then it should look like this:
If Not IsNull(DLookup("Value", "tblSys", "[Variable]=' " &
ProductIDLast & " ' ")) Then
DoCmd.RunSQL "UPDATE tblSys SET tblSys.[Value] = '1' WHERE
(((tblSys.Variable)=' " & ProductIDLast & " ' ));"
End If

Although double quotes work okay, it is easier to use double quotes on
the SQL string itself, and single quotes inside the SQL string. It
just makes it clearer.


DawnTreader Responds:

tried the character one because i thought that might work. but
"ProductIDLast" is not a variable, a number or a character string, it is the
actual text in the field variable in the table tblSys. it is the "name" of
the record line in the table. for instance in my tblSys i have 3 fields,
variable, value, description. variable is the name of the value i am trying
to store, value is the actual value of the variable and description is pretty
much worthless to anyone but me.

so the value of the record "ProductIDLast" is what i am trying to change.

basically i got the code from that great man, Allen Browne at this page:

http://www.allenbrowne.com/ser-18.html

i put it in and it works wonders. but i have the problem that when i open
the db the next time that it goes to the last record i looked at. i want to
keep that from happening.

 

[OldPro]

Jeff was right, it looks like you are trying to run code from a form
after you have closed it. Your SQL statement looks okay, assuming
that one of the records really does have a ProductID of 1. Put the
code in the unload event of your main form.

 

[Guest]

ok. the problem is that if i put it in the unload then the code i have there
will be useless. what i need is a way to run code before the db closes.

is there a db close event that i can put my code into?

 

[OldPro]

ok. the problem is that if i put it in the unload then the code i have there
will be useless. what i need is a way to run code before the db closes.

is there a db close event that i can put my code into?





- Show quoted text -

It sounds like your main form doesn't stay loaded all the time. In
that case you need a way to tell when the application is being closed
or opened and a place to put the code outside of the form. One way
would be to call your main form from another form that remains open
throughout. That way you could put the code in its unload event.
Another way is to open your database using an autoexec macro, which
would call a public function which in turn would call your main form.
Then you could put the code in the public function.

 

[Guest]

thats it. there is where i need to clear that variable, in the autoexec. if i
put it there then i can clean the record before the form opens.

THANKS!