insert error

[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

 

[TC]

Display the content of strSQL, and paste the exact output here.

TC [MVP Access]

 

[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

 

[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

 

[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

 

[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

 

[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. (

 

[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

 

[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

 

[Patrick]

excellent, thanks very much for your help. it is now working a treat.

 

[Brian Bastl]

glad to help

Brian