SQL trouble.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have added the following code to the onClick event on a button on an Add
Account form. I want it to add a record to the Charges table using the
values that are in several controls on that form. I can't for the life of me
figure out what I am doing wrong. Any help would be greatly appreciated.

Dim SQLText As String

SQLText = "INSERT INTO Charges (Service Name, Charge Amount, Account Number)"
SQLText = SQLText & " SELECT " & Me!Service Name & ", " & Me!Charge Amount &
", " & Me!Account Number & ""
DoCmd.RunSQL SQLText

Cory
 
Try the alternate syntax.

AND all your field names and control names have S P A C E S which means you will
have to use [] around them. You are much better off codewise if you eliminate
those spaces.

In addition, if your field is a text (string) field, you will need to have quote
marks around the values you are going to insert. I assummed that Service Name
and Account Number are text fields and not number field and Charge Amount is a
number field.

SQLTExt = "INSERT INTO Charges ([Service Name], [Charge Amount], [Account Number])"
SQLText = SQLText & " Values( """ & Me![Service Name] & """, " & Me![Charge
Amount] &
", """ & Me![Account Number] & """)"
 
Unfortunately it will take forever to go back and change all of the fields
and control name and references and whatnot to names without space. Either
way I redid the sql statements to what you posted above and it seemed to be
working, but for whatever reason, it is not now... It keeps giving me
"didn't add 1 record to the table due to key violations". Now I am adding
this to an empty table. The primary key is an autonumber field. I don't see
how there can be duplicate key entries like that. Any thoughts?

SQLText = "INSERT INTO Charges ([Service Name], [Charge Amount], [Account
Number])"
SQLText = SQLText & " Values( """ & Me![Service Name] & """, """ &
Me![Charge Amount] & """, """ & Me![Account Number] & """)"
DoCmd.RunSQL SQLText

SQLText2 = "INSERT INTO Payments ([Payment Amount], [Account Number],
[Notes])"
SQLText2 = SQLText2 & " Values(""" & Me![Payment Amount] & """, """ &
Me![Account Number] & """, """ & Me![Notes] & """)"
DoCmd.RunSQL SQLText2

Cory


John Spencer said:
Try the alternate syntax.

AND all your field names and control names have S P A C E S which means you will
have to use [] around them. You are much better off codewise if you eliminate
those spaces.

In addition, if your field is a text (string) field, you will need to have quote
marks around the values you are going to insert. I assummed that Service Name
and Account Number are text fields and not number field and Charge Amount is a
number field.

SQLTExt = "INSERT INTO Charges ([Service Name], [Charge Amount], [Account Number])"
SQLText = SQLText & " Values( """ & Me![Service Name] & """, " & Me![Charge
Amount] &
", """ & Me![Account Number] & """)"
I have added the following code to the onClick event on a button on an Add
Account form. I want it to add a record to the Charges table using the
values that are in several controls on that form. I can't for the life of me
figure out what I am doing wrong. Any help would be greatly appreciated.

Dim SQLText As String

SQLText = "INSERT INTO Charges (Service Name, Charge Amount, Account Number)"
SQLText = SQLText & " SELECT " & Me!Service Name & ", " & Me!Charge Amount &
", " & Me!Account Number & ""
DoCmd.RunSQL SQLText

Cory
Click to expand...
Click to expand...
 
Without knowing you table structure, it is difficult to speculate.

What is the primary key in charges? Are there foreign keys - such as Service
Name or Account Number that point to other tables? If so, you may be getting an
error message because the Service Name or Account Number does not exist in the
other table(s).

SQLText = "INSERT INTO Charges ([Service Name], [Charge Amount], [Account
Number])"
SQLText = SQLText & " Values( """ & Me![Service Name] & """, """ &
Me![Charge Amount] & """, """ & Me![Account Number] & """)"
DoCmd.RunSQL SQLText

If Payment Amount is a number field, then you need to change the following query
to eliminate the quote marks around Payment Amount.

SQLText2 = "INSERT INTO Payments ([Payment Amount], [Account Number],
[Notes])"
SQLText2 = SQLText2 & " Values(" & Me![Payment Amount] & ", """ &
Me![Account Number] & """, """ & Me![Notes] & """)"
DoCmd.RunSQL SQLText2
Unfortunately it will take forever to go back and change all of the fields
and control name and references and whatnot to names without space. Either
way I redid the sql statements to what you posted above and it seemed to be
working, but for whatever reason, it is not now... It keeps giving me
"didn't add 1 record to the table due to key violations". Now I am adding
this to an empty table. The primary key is an autonumber field. I don't see
how there can be duplicate key entries like that. Any thoughts?

SQLText = "INSERT INTO Charges ([Service Name], [Charge Amount], [Account
Number])"
SQLText = SQLText & " Values( """ & Me![Service Name] & """, """ &
Me![Charge Amount] & """, """ & Me![Account Number] & """)"
DoCmd.RunSQL SQLText

SQLText2 = "INSERT INTO Payments ([Payment Amount], [Account Number],
[Notes])"
SQLText2 = SQLText2 & " Values(""" & Me![Payment Amount] & """, """ &
Me![Account Number] & """, """ & Me![Notes] & """)"
DoCmd.RunSQL SQLText2

Cory

John Spencer said:
Try the alternate syntax.

AND all your field names and control names have S P A C E S which means you will
have to use [] around them. You are much better off codewise if you eliminate
those spaces.

In addition, if your field is a text (string) field, you will need to have quote
marks around the values you are going to insert. I assummed that Service Name
and Account Number are text fields and not number field and Charge Amount is a
number field.

SQLTExt = "INSERT INTO Charges ([Service Name], [Charge Amount], [Account Number])"
SQLText = SQLText & " Values( """ & Me![Service Name] & """, " & Me![Charge
Amount] &
", """ & Me![Account Number] & """)"
I have added the following code to the onClick event on a button on an Add
Account form. I want it to add a record to the Charges table using the
values that are in several controls on that form. I can't for the life of me
figure out what I am doing wrong. Any help would be greatly appreciated.

Dim SQLText As String

SQLText = "INSERT INTO Charges (Service Name, Charge Amount, Account Number)"
SQLText = SQLText & " SELECT " & Me!Service Name & ", " & Me!Charge Amount &
", " & Me!Account Number & ""
DoCmd.RunSQL SQLText

Cory
Click to expand...
Click to expand...
Click to expand...
 
Well both the Charges and Payments Tables refer to the primary key in
Accounts table, Account number, also an autonumber field. This particular
form is the account creation corm where user inputs information about the
account. The code below is used to insert a charge and payment with $0.00
amount into the respective tables so that a calculation on another form
doesn't get screwy. Regardless, It is my impression that as soon as you
start entering information on the form it writes it to the underlying table.
So when the SQL statement is executed, there should not be any conflict
between Charges and Accounts or Payments and Accounts.


I just tested my theory... By entering the information in the form and
checking the table without losing or hitting the associated button, it did
not enter the information... By hitting the Design View button and Form View
button, it wrote the data to the table and after executing the statement it
worked. So. I guess the question now is, how do I make it write the account
to the table...

Cory


John Spencer said:
Without knowing you table structure, it is difficult to speculate.

What is the primary key in charges? Are there foreign keys - such as Service
Name or Account Number that point to other tables? If so, you may be getting an
error message because the Service Name or Account Number does not exist in the
other table(s).

SQLText = "INSERT INTO Charges ([Service Name], [Charge Amount], [Account
Number])"
SQLText = SQLText & " Values( """ & Me![Service Name] & """, """ &
Me![Charge Amount] & """, """ & Me![Account Number] & """)"
DoCmd.RunSQL SQLText

If Payment Amount is a number field, then you need to change the following query
to eliminate the quote marks around Payment Amount.

SQLText2 = "INSERT INTO Payments ([Payment Amount], [Account Number],
[Notes])"
SQLText2 = SQLText2 & " Values(" & Me![Payment Amount] & ", """ &
Me![Account Number] & """, """ & Me![Notes] & """)"
DoCmd.RunSQL SQLText2
Unfortunately it will take forever to go back and change all of the fields
and control name and references and whatnot to names without space. Either
way I redid the sql statements to what you posted above and it seemed to be
working, but for whatever reason, it is not now... It keeps giving me
"didn't add 1 record to the table due to key violations". Now I am adding
this to an empty table. The primary key is an autonumber field. I don't see
how there can be duplicate key entries like that. Any thoughts?

SQLText = "INSERT INTO Charges ([Service Name], [Charge Amount], [Account
Number])"
SQLText = SQLText & " Values( """ & Me![Service Name] & """, """ &
Me![Charge Amount] & """, """ & Me![Account Number] & """)"
DoCmd.RunSQL SQLText

SQLText2 = "INSERT INTO Payments ([Payment Amount], [Account Number],
[Notes])"
SQLText2 = SQLText2 & " Values(""" & Me![Payment Amount] & """, """ &
Me![Account Number] & """, """ & Me![Notes] & """)"
DoCmd.RunSQL SQLText2

Cory

John Spencer said:
Try the alternate syntax.

AND all your field names and control names have S P A C E S which means you will
have to use [] around them. You are much better off codewise if you eliminate
those spaces.

In addition, if your field is a text (string) field, you will need to have quote
marks around the values you are going to insert. I assummed that Service Name
and Account Number are text fields and not number field and Charge Amount is a
number field.

SQLTExt = "INSERT INTO Charges ([Service Name], [Charge Amount], [Account Number])"
SQLText = SQLText & " Values( """ & Me![Service Name] & """, " & Me![Charge
Amount] &
", """ & Me![Account Number] & """)"

Cory wrote:

I have added the following code to the onClick event on a button on an Add
Account form. I want it to add a record to the Charges table using the
values that are in several controls on that form. I can't for the life of me
figure out what I am doing wrong. Any help would be greatly appreciated.

Dim SQLText As String

SQLText = "INSERT INTO Charges (Service Name, Charge Amount, Account Number)"
SQLText = SQLText & " SELECT " & Me!Service Name & ", " & Me!Charge Amount &
", " & Me!Account Number & ""
DoCmd.RunSQL SQLText

Cory
Click to expand...
Click to expand...
Click to expand...
 
As you have found out, a new record is not written to the table from a form
until you (a) Close the form, (b) move to another record on the form, or (c)
cause the record to be saved.

One way you can force the record to be saved by using the statement

If Me.Dirty = True Then Me.Dirty = False

In your VBA code before you execute the SQL statement(s).



Cory said:
Well both the Charges and Payments Tables refer to the primary key in
Accounts table, Account number, also an autonumber field. This particular
form is the account creation corm where user inputs information about the
account. The code below is used to insert a charge and payment with $0.00
amount into the respective tables so that a calculation on another form
doesn't get screwy. Regardless, It is my impression that as soon as you
start entering information on the form it writes it to the underlying
table.
So when the SQL statement is executed, there should not be any conflict
between Charges and Accounts or Payments and Accounts.


I just tested my theory... By entering the information in the form and
checking the table without losing or hitting the associated button, it did
not enter the information... By hitting the Design View button and Form
View
button, it wrote the data to the table and after executing the statement
it
worked. So. I guess the question now is, how do I make it write the
account
to the table...

Cory


John Spencer said:
Without knowing you table structure, it is difficult to speculate.

What is the primary key in charges? Are there foreign keys - such as
Service
Name or Account Number that point to other tables? If so, you may be
getting an
error message because the Service Name or Account Number does not exist
in the
other table(s).

SQLText = "INSERT INTO Charges ([Service Name], [Charge Amount], [Account
Number])"
SQLText = SQLText & " Values( """ & Me![Service Name] & """, """ &
Me![Charge Amount] & """, """ & Me![Account Number] & """)"
DoCmd.RunSQL SQLText

If Payment Amount is a number field, then you need to change the
following query
to eliminate the quote marks around Payment Amount.

SQLText2 = "INSERT INTO Payments ([Payment Amount], [Account Number],
[Notes])"
SQLText2 = SQLText2 & " Values(" & Me![Payment Amount] & ", """ &
Me![Account Number] & """, """ & Me![Notes] & """)"
DoCmd.RunSQL SQLText2
Unfortunately it will take forever to go back and change all of the
fields
and control name and references and whatnot to names without space.
Either
way I redid the sql statements to what you posted above and it seemed
to be
working, but for whatever reason, it is not now... It keeps giving me
"didn't add 1 record to the table due to key violations". Now I am
adding
this to an empty table. The primary key is an autonumber field. I
don't see
how there can be duplicate key entries like that. Any thoughts?

SQLText = "INSERT INTO Charges ([Service Name], [Charge Amount],
[Account
Number])"
SQLText = SQLText & " Values( """ & Me![Service Name] & """, """ &
Me![Charge Amount] & """, """ & Me![Account Number] & """)"
DoCmd.RunSQL SQLText

SQLText2 = "INSERT INTO Payments ([Payment Amount], [Account Number],
[Notes])"
SQLText2 = SQLText2 & " Values(""" & Me![Payment Amount] & """, """ &
Me![Account Number] & """, """ & Me![Notes] & """)"
DoCmd.RunSQL SQLText2

Cory

:

Try the alternate syntax.

AND all your field names and control names have S P A C E S which
means you will
have to use [] around them. You are much better off codewise if you
eliminate
those spaces.

In addition, if your field is a text (string) field, you will need to
have quote
marks around the values you are going to insert. I assummed that
Service Name
and Account Number are text fields and not number field and Charge
Amount is a
number field.

SQLTExt = "INSERT INTO Charges ([Service Name], [Charge Amount],
[Account Number])"
SQLText = SQLText & " Values( """ & Me![Service Name] & """, " &
Me![Charge
Amount] &
", """ & Me![Account Number] & """)"

Cory wrote:

I have added the following code to the onClick event on a button on
an Add
Account form. I want it to add a record to the Charges table using
the
values that are in several controls on that form. I can't for the
life of me
figure out what I am doing wrong. Any help would be greatly
appreciated.

Dim SQLText As String

SQLText = "INSERT INTO Charges (Service Name, Charge Amount,
Account Number)"
SQLText = SQLText & " SELECT " & Me!Service Name & ", " & Me!Charge
Amount &
", " & Me!Account Number & ""
DoCmd.RunSQL SQLText

Cory
Click to expand...
Click to expand...
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

openrecordset--too few parameters 1
Inserting Record into SQL database 3
Add a record to an SQL Table 5
Read MS SQL 2000 table with "dynamic" pass through query 4
Why will this not put a value in the box? Please 1
Which one is better between these two approaches? 5
Code to open 1 form using different tables 5
Code Snipit for saving to CSV 1

Back
Top