insert error

P

Patrick

hi all,

i am having problems inserting some data. can anyone see a problem with
this code?

strCurrency = frmSettings.txtSettingsCurrenciesAddCurrency.Value
dblConversion =
frmSettings.txtSettingsCurrenciesAddConversion.Value
strSymbol = frmSettings.txtSettingsCurrenciesAddSymbol.Value
strDefault = frmSettings.chkSettingsCurrenciesAddDefault.Value

'write SQL
strSQL = "INSERT INTO [Currencies] (currency, conversion,
symbol, use) VALUES ('" & strCurrency & "', " & dblConversion & ", '" &
strSymbol & "', " & strDefault & ")"

'run query
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

I just get error 3134 but it doesnt tell me what the problem is, and i
cant find it. I have the table [Currencies], with the following table
structure:

[id] {autoNumber}
[currency] {text}
[symbol] {number}
[use] {number}

any help would be great. thanks

Patrick
 
P

Patrick

hi there,

this is the sql statement if a check box is checked:
INSERT INTO [Currencies] (id, currency, conversion, symbol, use) VALUES
('', 'GBP', 1, '£', -1)

and if it isnt:
INSERT INTO [Currencies] (id, currency, conversion, symbol, use) VALUES
('', 'GBP', 1, '£', 0)

thanks

Patrick
 
B

Brian Bastl

Patrick,

You need to stick to your original thread if you are replying to someone.
Too much trouble to search the newsgroup for your previous thread(s).

Did you have a particular question, or are you posting your solution?

Brian


hi there,

this is the sql statement if a check box is checked:
INSERT INTO [Currencies] (id, currency, conversion, symbol, use) VALUES
('', 'GBP', 1, '£', -1)

and if it isnt:
INSERT INTO [Currencies] (id, currency, conversion, symbol, use) VALUES
('', 'GBP', 1, '£', 0)

thanks

Patrick
 
P

Patrick

sorry, i thought i was sticking to the original thread. TC asked me to
display the content of strSQL, so that is what I did.

I wasn't posting a solution, I still have no idea what is wrong with
the sql statement. I get error 3134 but no reason as to what is causing
the problem. just something with my syntax.

thanks

Patrick
 
B

Brian Bastl

this is the sql statement if a check box is checked:
INSERT INTO [Currencies] (id, currency, conversion, symbol, use) VALUES
('', 'GBP', 1, '£', -1)
and if it isnt:
INSERT INTO [Currencies] (id, currency, conversion, symbol, use) VALUES
('', 'GBP', 1, '£', 0)

Are you using a stored query to do the append, or are you doing this with
VBA?

First off, why are you inserting a null string for the id? I'm assuming id
is the <PK - autonum> in your Currencies table.
Just append the non-null values. The autonumber will take care of itself.

Secondly, if you're doing the append within a procedure, then the checkbox
value needs to be referenced like the following:

'this will insert either -1 or 0 depending on checkbox value

INSERT INTO [Currencies] (currency, conversion, symbol, use) VALUES
('GBP', 1, '£', " & Me.YourCheckBox & ")

HTH,
Brian
 
P

Patrick

hey thanks for that, good point about the autonumber, I didnt think of
that...

i have amended the code to reflect exactly what you have above, but I
still get an error. :eek:(
 
B

Brian Bastl

Hi Patrick,
I've got to head off for the day. But here's my suggestion:

You might try just doing a single value append initially. Start with the
first value. If it works, then increase it to the first two values, and so
on. You might also try changing the various apostrophes to quotes or
apostrophe plus quotes. You'll need to figure it out by trial and error.
As far as the '£' symbol, you may need to reference that a different way
(Chr(?)), so leave it out of the Insert statement completely until
everything else works. Then start a new thread asking how to include special
characters, specifically the £. And don't forget to include your amended
code, and any subsequent code pertaining to it. The MVPs generally ignore
posts with little or no info to work with.

When TLOML retires for the evening, I'll play around with this and report
any progress.

HTH,
Brian
 
B

Brian Bastl

Patrick,

I've been pulling my hair out for the last 30 minutes. Kept getting the same
error over and over no matter what I tried. I knew my syntax was right, but
it wasn't until Marshall Barton responded to my own post that I knew what to
fix.

You've got to change your table's field names and the controls on your forms
so that they don't conflict with Reserved words in Access.
Some Reserved words:
Name, Currency, Text, Date, etc...
As soon as I changed Currency to CurType in tblCurrencies, and Currency to
txtCurrency on my form, it worked.

Here's a small sample of what you were trying to do:
----------------------------------------------------
Private Sub Command2_Click()

Dim db As DAO.Database
Dim st As String
Set db = CurrentDb

' Chr(128) = ? = EUR
' Chr(163) = £ = GBP
' Chr(165) = ¥ = YEN

st = st & "INSERT INTO tblCurrencies (CurType, Symbol) "
st = st & "VALUES " & "('" & UCase(Me.txtCurrency) & "','" & Chr(163) & "')"
db.Execute st, dbFailOnError

End Sub
 

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

Loop Help 4
error 3061 3
Type mismatch 1
Code will not run 1
Failure to Run 4
Code won't run 1
After Update and After Insert Events 2
No current record. 2

Top