update query

  • Thread starter Thread starter j.t.w
  • Start date Start date
J

j.t.w

Hi NG,

I'm having a problem. I just moved my tables over to SQL Server 2000.
I'm using A2K as the front-end.

For now, all I'm trying to do is to grab the PONumber in the
PO_NextNumber table and then increment it.

Using the SQL Query Analyzer, I can update the table. I then copied
the syntax from there. What I'm getting is a "Syntax error in UPDATE
statement" error message.

*************Code Start******************************
Private Sub cmdAdd_Click()
Dim intPONumber As Double
intPONumber = DLookup("PONumber", "PO_NextNumber")

strSQL = "UPDATE [MyDB].[dbo].[PO_NextNumber] " & _
"Set [PONumber] = " & intPONumber + 1

CurrentDb.Execute strSQL
Me.PONo = intPONumber
End Sub
*************Code End********************************

Could someone please let me know where my syntax is wrong?

Also, when adding a record in Access, the autonumber is immediately
generated and shows on the form. SQL Server generates the Identity
number when the record is submitted. How would I go about creating and
running a stored procedure to act like Access's Autonumber feature?

Thanks for any and all help. I still have soooo much to learn.
j.t.w
 
Not to answer your question, but: it is not good to get the current # with
DLookup(), then put that number into the update statement as a constant.
There's a big gap there, where someone else could jump in between the
DLookup() call and the SQL update. Surely way better to:

"UPDATE [PO_NextNumber] SET [PONumber] = [PONumber] + 1"

or somesuch.

HTH,
TC
 
TC,

Thanks for your response. Unfortunately, it still does not work.

I've changed my code to how you suggested and even made it as simple
as possible.

Private Sub cmdAdd_Click()
CurrentDb.Execute "UPDATE [PO_NextNumber] SET [PONumber] =
[PONumber] + 1"
End Sub

I get a "Run-time error '3073': Operation must use an updateable
query".

If I change it to...
CurrentDb.Execute "UPDATE [Tropic].[dbo].[PO_NextNumber] SET
[PONumber] = [PONumber] + 1"

I then get a "Run-time-error '3144': Syntax error in UPDATE statement.

What am I doing wrong? Again, Thanks for your help.
j.t.w
 
Ok. Now it works. I deleted my table in SQL Server and started fresh.

Thanks for your help. Your code works as you have stated.
j.t.w
 
Hi, I've been busy for the past few days, hope you are still reading this.

I truly can't see how:

"UPDATE [PO_NextNumber] SET [PONumber] = [PONumber] + 1"

would possibly give you runtime error 3073 ("Operation must use an
updateable query") if PO_NextNumber is a table. Are you sure it is a table,
not a query? If it >is< a query, are you sure it is updatable? (open it in
datasheet view & try to edit an existing PONumber value).

Is PONumber an autonumber field? If so, you will not be able to update it.
Autonumbers are assinged by Access, automatically.

HTH,
TC


j.t.w said:
TC,

Thanks for your response. Unfortunately, it still does not work.

I've changed my code to how you suggested and even made it as simple
as possible.

Private Sub cmdAdd_Click()
CurrentDb.Execute "UPDATE [PO_NextNumber] SET [PONumber] =
[PONumber] + 1"
End Sub

I get a "Run-time error '3073': Operation must use an updateable
query".

If I change it to...
CurrentDb.Execute "UPDATE [Tropic].[dbo].[PO_NextNumber] SET
[PONumber] = [PONumber] + 1"

I then get a "Run-time-error '3144': Syntax error in UPDATE statement.

What am I doing wrong? Again, Thanks for your help.
j.t.w




"test" <[email protected]> wrote in message
Not to answer your question, but: it is not good to get the current # with
DLookup(), then put that number into the update statement as a constant.
There's a big gap there, where someone else could jump in between the
DLookup() call and the SQL update. Surely way better to:

"UPDATE [PO_NextNumber] SET [PONumber] = [PONumber] + 1"

or somesuch.

HTH,
TC
Click to expand...
Click to expand...
 
Oops, just read your second post. Glad it works ok now.

TC


j.t.w said:
TC,

Thanks for your response. Unfortunately, it still does not work.

I've changed my code to how you suggested and even made it as simple
as possible.

Private Sub cmdAdd_Click()
CurrentDb.Execute "UPDATE [PO_NextNumber] SET [PONumber] =
[PONumber] + 1"
End Sub

I get a "Run-time error '3073': Operation must use an updateable
query".

If I change it to...
CurrentDb.Execute "UPDATE [Tropic].[dbo].[PO_NextNumber] SET
[PONumber] = [PONumber] + 1"

I then get a "Run-time-error '3144': Syntax error in UPDATE statement.

What am I doing wrong? Again, Thanks for your help.
j.t.w




"test" <[email protected]> wrote in message
Not to answer your question, but: it is not good to get the current # with
DLookup(), then put that number into the update statement as a constant.
There's a big gap there, where someone else could jump in between the
DLookup() call and the SQL update. Surely way better to:

"UPDATE [PO_NextNumber] SET [PONumber] = [PONumber] + 1"

or somesuch.

HTH,
TC
Click to expand...
Click to expand...
 
Back
Top