Add Next Number To A Field from another Form

A

Ange Kappas

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
 
D

Douglas J. Steele

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.
 
A

Ange Kappas

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 said:
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

(no private e-mails, please)


Ange Kappas said:
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
Click to expand...
Click to expand...
 
M

MikeB

Douglas J. Steele said:
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.
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 !
Click to expand...
Click to expand...

You are likely leaving out the continuation character. Fred's post may
(likely) have just "wrapped" after the ampersand.

Try:

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

fredg

Douglas J. Steele said:
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.
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 !
Click to expand...
Click to expand...

You are likely leaving out the continuation character. Fred's post may
(likely) have just "wrapped" after the ampersand.

Try:

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

Not only did it wrap but the table name was different.
not
"Insert into ACCOUNT NUMBERAccount Number.

Account Number has a space in the table name, Place it within
brackets.
 
F

fredg

Douglas J. Steele said:
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.
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 !
Click to expand...

You are likely leaving out the continuation character. Fred's post may
(likely) have just "wrapped" after the ampersand.

Try:

strSQL = "Insert into ACCOUNT NUMBER([Account Number]) values (" & _
DMax("[Account Number]", "ACCOUNT NUMBER") + 1 & ");"
Thanks
Ange
Click to expand...
Click to expand...

Not only did it wrap but the table name was different.
not
"Insert into ACCOUNT NUMBERAccount Number.

Account Number has a space in the table name, Place it within
brackets.
Click to expand...

Whoops! Sent prematurely.

Not only did it wrap, but the name of the table in the original post
was tblAccountno (no space in the table name), not Account Number
(with the space)..

The name of the table, if it includes a space, must be wrapped within
brackets.... [Account Number].
Also the OP left out the space in front of the first (.
NUMBER([Account Number]) values

It should read:
NUMBER ([Account Number]) values

So now is the name of the table "Account Number" and the name of the
field in the "Account Number" table also "Account Number"?
That would confuse anybody.

If so try (all on one line):
strSQL = "Insert into [ACCOUNT NUMBER] ([Account Number]) values (" &
DMax("[Account Number]", "ACCOUNT NUMBER") + 1 & ");"
 
A

Ange Kappas

Thanks Fred,
IT WORKED !!!!!
FANTASTIC !!!!

Ange



fredg said:
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.
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 !

You are likely leaving out the continuation character. Fred's post
may
(likely) have just "wrapped" after the ampersand.

Try:

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


Thanks
Ange
Click to expand...

Not only did it wrap but the table name was different.
not
"Insert into ACCOUNT NUMBERAccount Number.

Account Number has a space in the table name, Place it within
brackets.
Click to expand...

Whoops! Sent prematurely.

Not only did it wrap, but the name of the table in the original post
was tblAccountno (no space in the table name), not Account Number
(with the space)..

The name of the table, if it includes a space, must be wrapped within
brackets.... [Account Number].
Also the OP left out the space in front of the first (.
NUMBER([Account Number]) values

It should read:
NUMBER ([Account Number]) values

So now is the name of the table "Account Number" and the name of the
field in the "Account Number" table also "Account Number"?
That would confuse anybody.

If so try (all on one line):
strSQL = "Insert into [ACCOUNT NUMBER] ([Account Number]) values (" &
DMax("[Account Number]", "ACCOUNT NUMBER") + 1 & ");"
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Check to see if a record exists 1
Add Next Number To A Field 2
same insert into problem 9
Need help in NotInList event 5
Too few Paramets, expected 1 error 1
Mail Merge from Access - Word throws "Cannot Open Data Source" Error 2
Excel Export Filtered Form Data To Excel 0
cant get cbo to stop outputting key# 0

Top