Re: Add Next Number To A Field from another Form

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi Douglas,
I am using Access 2002 and the Microsoft DAO 3.6
Library is already a reference, and I'm still getting the error message as:
"syntax error in INSERT INTO statement"
..
with debug showing me:



CurrentDb.Execute strSQL, dbFailOnError

maybe I should change the (,) commas to (;).

Ange


"Douglas J. Steele" <NOSPAM_djsteele@xxxxxxxxxxxxxxxxx> wrote in message
news:e9Mse4waIHA.1168@xxxxxxxxxxxxxxxxxxxxxxx
What version of Access are you using? If it's Access 2000 or 2002, you may
have to go into the References (Tools | References from the menu bar while
in the VB Editor) and add a reference to Microsoft DAO 3.6 Object Library.

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


"Ange Kappas" <angekap@xxxxxx> wrote in message
news:fojtn0$nik$2@xxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi,
I asked for help from a previous problem I had which Fred was so
kind to help, but I forgot to mention that the Table concerned was
triggered from a Button from another Form called ACCOUNTS SEARCH.

What I wanted to do is to add code to a Button "On Click" in the Form
ACCOUNTS SEARCH which would open the Table ACCOUNT NUMBER look at the
last record, add a new record which is the next number. The ACCOUNT
NUMBER Table has ONLY one field called ACCOUNT NUMBER.
Hence if the last number was let's say "4" I want to add a new record
automatically being "5".

The code Fred gave me was:

Dim strSQL As String

strSQL = "Insert into ACCOUNT NUMBER([Account Number]) values (" &
DMax("[Account Number]", "ACCOUNT NUMBER") + 1 & ");"

CurrentDb.Execute strSQL, dbFailOnError

The Copy Paste of the code is as follows:



Private Sub ACCOUNTS_CHARGE_Click()

Dim strSQL As String

strSQL = "Insert into ACCOUNT NUMBER([Account Number]) values (" &
DMax("[Account Number]", "ACCOUNT NUMBER") + 1 & ");"

CurrentDb.Execute strSQL, dbFailOnError

DoCmd.OpenQuery "ACCOUNTS TRIGGER"


End Sub

Which gives me an error on the:
CurrentDb.Execute strSQL, dbFailOnError
Line

Hope someone can tell me where it is wrong !

Thanks
Ange






.

Relevant Pages

  • Re: Why SQL command doesnt work
    ... > run the SQL. ... > DoCmd.RunSQL Strsql ... Note, though, that it requires a reference to be set to DAO. ... > for Microsoft DAO 3.6 Object Library, ...
    (microsoft.public.access.gettingstarted)
  • Re: Message Alert
    ... So the two strSQL lines needs to be changed also: ... A reference set to Microsoft DAO 3.6 Object Library ... check a few things in your VBA editor. ... assembled strSQL by pressing Ctrl + G, which opens the Immediate window, ...
    (microsoft.public.access.modulesdaovba)
  • Re: Netkkkop alert.
    ... Reference the attached, beginning at ... People don't post from email accounts you dipshit. ... You are either Mark tyring to distract the focus of the thread or you ... Mark was arrogant then as he is now. ...
    (rec.models.railroad)
  • Re: Ask not what your country can do for you
    ... reference with "this" could only be pointing forward. ... Lincoln Portrait," which has the pre-refrain "This is what Abe Lincoln ... radio glitch. ... appropriately healthy skepticism when it comes to accounts of scientific ...
    (sci.lang)
  • Re: Add Next Number To A Field from another Form
    ... have to go into the References (Tools | References from the menu bar while ... ACCOUNTS SEARCH which would open the Table ACCOUNT NUMBER look at the last ... Dim strSQL As String ... CurrentDb.Execute strSQL, dbFailOnError ...
    (microsoft.public.access.forms)