vb help

S

Steinarr G.

can any one tell my why this works... >>

Dim strSQL As String

strSQL = _
"INSERT INTO facturer (name1, name2) " & _
"VALUES('" & Me!name1 & "','" & Me!name2 & "')"

CurrentDb.Execute strSQL, dbFailOnError


But not this..... >>

Dim strSQL As String

strSQL = _
"INSERT INTO facturer (name1, name2 ,name3, name4, name5, name6, name7,
name8, name9, nake10) " & _
"VALUES('" & Me!name1 & "','" & Me!name2 & "','" & Me!name3 & ",'" &
Me!name4 & ",'" & Me!name5 & ")" & _
"'" & Me!name6 & "','" & Me!name7 & "','" & Me!name8 & "','" & Me!name9
& "','" & Me!name10 & "')"

CurrentDb.Execute strSQL, dbFailOnError


regards.

Steinarr.
 
S

Sandra Daigle

Hi Steinarr,

It looks like you're missing some of the inside quotes - usually when you
are having trouble with an SQL string that you are building in code the
easiest way to debug it yourself is to used debug.print to write the results
to the immediate window, then manually copy the string into the SQL view of
a new query and try to open it. Often you'll spot the errors without even
opening the query but when you do try to open it, Jet will give you more
information on errors.

Anyway here's what I see - first you have nake10 in the field list where I
assume you meant name10.

Next, I think that it's a bit easier to see that the quotes are balanced if
you use line continuations and break each piece into it's own line. Also, I
prefer using doubled up double quotes over the apostrophe since they will
still work even when your data contains an apostrophe. Often I'll use a
constant for this string or you can use Chr(34) to return the double quote.

strSQL = _
"INSERT INTO facturer (name1, name2 , " _
& "name3, name4, name5, " _
& "name6, name7, name8, name9, name10) " & _
"VALUES(" _
& """" & Me!name1 & """, " _
& """" & Me!name2 & """, " _
& """" & Me!name3 & """, " _
& """" & Me!name4 & """, " _
& """" & Me!name5 & """, " _
& """" & Me!name6 & """, " _
& """" & Me!name7 & """, " _
& """" & Me!name8 & """," _
& """" & Me!name9 & """, " _
& """" & Me!name10 & """)"
 
S

Sandra Daigle

Hi Steinarr,

It looks like you're missing some of the inside quotes - usually when you
are having trouble with an SQL string that you are building in code the
easiest way to debug it yourself is to used debug.print to write the results
to the immediate window, then manually copy the string into the SQL view of
a new query and try to open it. Often you'll spot the errors without even
opening the query but when you do try to open it, Jet will give you more
information on errors.

Anyway here's what I see - first you have nake10 in the field list where I
assume you meant name10.

Next, I think that it's a bit easier to see that the quotes are balanced if
you use line continuations and break each piece into it's own line. Also, I
prefer using doubled up double quotes over the apostrophe since they will
still work even when your data contains an apostrophe. Often I'll use a
constant for this string or you can use Chr(34) to return the double quote.

strSQL = _
"INSERT INTO facturer (name1, name2 , " _
& "name3, name4, name5, " _
& "name6, name7, name8, name9, name10) " & _
"VALUES(" _
& """" & Me!name1 & """, " _
& """" & Me!name2 & """, " _
& """" & Me!name3 & """, " _
& """" & Me!name4 & """, " _
& """" & Me!name5 & """, " _
& """" & Me!name6 & """, " _
& """" & Me!name7 & """, " _
& """" & Me!name8 & """," _
& """" & Me!name9 & """, " _
& """" & Me!name10 & """)"
 
J

John Smith

nake10 and the extra closing bracket after Me!name5 seem like likely
candidates.
 
J

John Smith

nake10 and the extra closing bracket after Me!name5 seem like likely
candidates.
 
S

Steinarr G.

Thankes.. Sandra .. and John.
John it was just a typo in my examle there =)

i´ve sett it upp like this now..
if i use .. " " & _ og " " _ > & i get.. " To many line forwards.. error
"
But now i still get this.. Syntax Error..

could you look at this code..
i guess it scramles upp here .. so
it´s here.. to ..
view in notepad. with no word wrap
http://www.steinki.net/code.zip

And it´s also here.

'## BEGIN ##

Dim strSQL As String

strSQL = _
"INSERT INTO StoredReports (clientID, name, address, by, carnr, carname,
job1, job2, job3, job4, job5, job6, job7, job8, job9, job10, job11, job12,
job13, job14, job15, job16, job17, job18, job19, job20, job21, job22,
antal1, antal2, antal3, antal4, antal5, antal6, antal7, antal8, antal9,
antal10, antal11, antal12, antal13, antal14, antal15, antal16, antal17,
antal18, antal19, antal20, antal21, antal22, PrisPAntal1, PrisPAntal2,
PrisPAntal3, PrisPAntal4, PrisPAntal5, PrisPAntal6, PrisPAntal7,
PrisPAntal8, PrisPAntal9, PrisPAntal10, PrisPAntal11, PrisPAntal12,
PrisPAntal13, PrisPAntal14, PrisPAntal15, PrisPAntal16, PrisPAntal17,
PrisPAntal18, PrisPAntal19, PrisPAntal20, PrisPAntal21, PrisPAntal22, sum1,
sum2, sum3, sum4, sum5, sum6, sum7, sum8, sum9, sum10, sum11, sum12, sum13,
sum14, sum15, sum16, sum17, sum18, sum19, sum20, sum21, sum22, beskriv,
fragt, moms, prisUmoms, prisMmoms)" & _
"VALUES(" _
& """" & Me!ClientID & """,""" & Me!name & """,""" & Me!Address & ""","""
& Me!by & """,""" & Me!carnr & """, """ & Me!carname & """, " _
& """" & Me!Job1 & """,""" & Me!Job2 & """,""" & Me!Job3 & """,""" &
Me!Job4 & """,""" & Me!Job5 & """,""" & Me!Job6 & """,""" & Me!Job7 &
""",""" & Me!Job8 & """,""" & Me!Job9 & """,""" & Me!Job10 & """,""" &
Me!Job12 & """,""" & Me!Job13 & """,""" & Me!Job14 & """,""" & Me!job15 &
""",""" & Me!Job16 & """,""" & Me!Job17 & """,""" & Me!Job18 & """,""" &
Me!Job19 & """,""" & Me!Job20 & """,""" & Me!Job21 & """,""" & Me!Job22 &
""", " _
& """" & Me!Antal1 & """,""" & Me!Antal2 & """,""" & Me!Antal3 & ""","""
& Me!Antal4 & """,""" & Me!Antal5 & """,""" & Me!Antal6 & """,""" &
Me!Antal7 & """,""" & Me!Antal8 & """,""" & Me!Antal9 & """,""" & Me!Antal10
& """,""" & Me!Antal11 & """,""" & Me!Antal12 & """,""" & Me!Antal13 &
""",""" & Me!Antal14 & """,""" & Me!Antal15 & """,""" & Me!Antal16 & ""","""
& Me!Antal17 & """,""" & Me!Antal18 & """,""" & Me!Antal19 & """,""" &
Me!Antal20 & """,""" & Me!Antal21 & """,""" & Me!Antal22 & """," _
& """" & Me!PrisPAntal1 & """,""" & Me!PrisPAntal2 & """,""" &
Me!PrisPAntal3 & """,""" & Me!PrisPAntal4 & """,""" & Me!PrisPAntal5 &
""",""" & Me!PrisPAntal6 & """,""" & Me!PrisPAntal7 & """,""" &
Me!PrisPAntal8 & """,""" & Me!PrisPAntal9 & """,""" & Me!PrisPAntal10 &
""",""" & Me!PrisPAntal11 & """,""" & Me!PrisPAntal12 & """,""" &
Me!PrisPAntal13 & """,""" & Me!PrisPAntal14 & """,""" & Me!PrisPAntal15 &
""",""" & Me!PrisPAntal16 & """,""" & Me!PrisPAntal17 & """,""" &
Me!PrisPAntal18 & """,""" & Me!PrisPAntal19 & """,""" & Me!PrisPAntal20 &
""",""" & Me!PrisPAntal21 & """,""" & Me!PrisPAntal22 & """," _
& """" & Me!sum1 & """,""" & Me!sum2 & """,""" & Me!sum3 & """,""" &
Me!sum4 & """,""" & Me!sum5 & """,""" & Me!sum6 & """,""" & Me!sum7 &
""",""" & Me!sum8 & """,""" & Me!sum9 & """,""" & Me!sum10 & """,""" &
Me!sum11 & """,""" & Me!sum12 & """,""" & Me!sum13 & """,""" & Me!sum14 &
""",""" & Me!sum15 & """,""" & Me!sum16 & """,""" & Me!sum17 & """,""" &
Me!sum18 & """,""" & Me!sum19 & """,""" & Me!sum20 & """,""" & Me!sum21 &
""",""" & Me!sum22 & """,""" & Me!Beskriv & """,""" & Me!fragt & """,""" &
Me!moms & """,""" & Me!PrisUmoms & """,""" & Me!PrisMmoms & """)"

CurrentDb.Execute strSQL, dbFailOnError

'### END ###

Then a report opens with info from here and there not that it has anything
to do with this. =)

I hope you see the error here if you show you the whole code i´m trying to
use, not just those examples.
 
S

Steinarr G.

Thankes.. Sandra .. and John.
John it was just a typo in my examle there =)

i´ve sett it upp like this now..
if i use .. " " & _ og " " _ > & i get.. " To many line forwards.. error
"
But now i still get this.. Syntax Error..

could you look at this code..
i guess it scramles upp here .. so
it´s here.. to ..
view in notepad. with no word wrap
http://www.steinki.net/code.zip

And it´s also here.

'## BEGIN ##

Dim strSQL As String

strSQL = _
"INSERT INTO StoredReports (clientID, name, address, by, carnr, carname,
job1, job2, job3, job4, job5, job6, job7, job8, job9, job10, job11, job12,
job13, job14, job15, job16, job17, job18, job19, job20, job21, job22,
antal1, antal2, antal3, antal4, antal5, antal6, antal7, antal8, antal9,
antal10, antal11, antal12, antal13, antal14, antal15, antal16, antal17,
antal18, antal19, antal20, antal21, antal22, PrisPAntal1, PrisPAntal2,
PrisPAntal3, PrisPAntal4, PrisPAntal5, PrisPAntal6, PrisPAntal7,
PrisPAntal8, PrisPAntal9, PrisPAntal10, PrisPAntal11, PrisPAntal12,
PrisPAntal13, PrisPAntal14, PrisPAntal15, PrisPAntal16, PrisPAntal17,
PrisPAntal18, PrisPAntal19, PrisPAntal20, PrisPAntal21, PrisPAntal22, sum1,
sum2, sum3, sum4, sum5, sum6, sum7, sum8, sum9, sum10, sum11, sum12, sum13,
sum14, sum15, sum16, sum17, sum18, sum19, sum20, sum21, sum22, beskriv,
fragt, moms, prisUmoms, prisMmoms)" & _
"VALUES(" _
& """" & Me!ClientID & """,""" & Me!name & """,""" & Me!Address & ""","""
& Me!by & """,""" & Me!carnr & """, """ & Me!carname & """, " _
& """" & Me!Job1 & """,""" & Me!Job2 & """,""" & Me!Job3 & """,""" &
Me!Job4 & """,""" & Me!Job5 & """,""" & Me!Job6 & """,""" & Me!Job7 &
""",""" & Me!Job8 & """,""" & Me!Job9 & """,""" & Me!Job10 & """,""" &
Me!Job12 & """,""" & Me!Job13 & """,""" & Me!Job14 & """,""" & Me!job15 &
""",""" & Me!Job16 & """,""" & Me!Job17 & """,""" & Me!Job18 & """,""" &
Me!Job19 & """,""" & Me!Job20 & """,""" & Me!Job21 & """,""" & Me!Job22 &
""", " _
& """" & Me!Antal1 & """,""" & Me!Antal2 & """,""" & Me!Antal3 & ""","""
& Me!Antal4 & """,""" & Me!Antal5 & """,""" & Me!Antal6 & """,""" &
Me!Antal7 & """,""" & Me!Antal8 & """,""" & Me!Antal9 & """,""" & Me!Antal10
& """,""" & Me!Antal11 & """,""" & Me!Antal12 & """,""" & Me!Antal13 &
""",""" & Me!Antal14 & """,""" & Me!Antal15 & """,""" & Me!Antal16 & ""","""
& Me!Antal17 & """,""" & Me!Antal18 & """,""" & Me!Antal19 & """,""" &
Me!Antal20 & """,""" & Me!Antal21 & """,""" & Me!Antal22 & """," _
& """" & Me!PrisPAntal1 & """,""" & Me!PrisPAntal2 & """,""" &
Me!PrisPAntal3 & """,""" & Me!PrisPAntal4 & """,""" & Me!PrisPAntal5 &
""",""" & Me!PrisPAntal6 & """,""" & Me!PrisPAntal7 & """,""" &
Me!PrisPAntal8 & """,""" & Me!PrisPAntal9 & """,""" & Me!PrisPAntal10 &
""",""" & Me!PrisPAntal11 & """,""" & Me!PrisPAntal12 & """,""" &
Me!PrisPAntal13 & """,""" & Me!PrisPAntal14 & """,""" & Me!PrisPAntal15 &
""",""" & Me!PrisPAntal16 & """,""" & Me!PrisPAntal17 & """,""" &
Me!PrisPAntal18 & """,""" & Me!PrisPAntal19 & """,""" & Me!PrisPAntal20 &
""",""" & Me!PrisPAntal21 & """,""" & Me!PrisPAntal22 & """," _
& """" & Me!sum1 & """,""" & Me!sum2 & """,""" & Me!sum3 & """,""" &
Me!sum4 & """,""" & Me!sum5 & """,""" & Me!sum6 & """,""" & Me!sum7 &
""",""" & Me!sum8 & """,""" & Me!sum9 & """,""" & Me!sum10 & """,""" &
Me!sum11 & """,""" & Me!sum12 & """,""" & Me!sum13 & """,""" & Me!sum14 &
""",""" & Me!sum15 & """,""" & Me!sum16 & """,""" & Me!sum17 & """,""" &
Me!sum18 & """,""" & Me!sum19 & """,""" & Me!sum20 & """,""" & Me!sum21 &
""",""" & Me!sum22 & """,""" & Me!Beskriv & """,""" & Me!fragt & """,""" &
Me!moms & """,""" & Me!PrisUmoms & """,""" & Me!PrisMmoms & """)"

CurrentDb.Execute strSQL, dbFailOnError

'### END ###

Then a report opens with info from here and there not that it has anything
to do with this. =)

I hope you see the error here if you show you the whole code i´m trying to
use, not just those examples.
 
S

Steinarr G.

Hi again ..

Then i tryed this..

'## BEGIN ##

Dim strSQL As String
Debug.Print
strSQL = _
"INSERT INTO StoredReports (clientID, name, " _
& "address, by, " _
& "carnr, carname)" & _
"VALUES(" _
& """" & Me!ClientID & """," _
& """" & Me!name & """," _
& """" & Me!Address & """," _
& """" & Me!by & """," _
& """" & Me!carnr & """," _
& """" & Me!carname & """)"

CurrentDb.Execute strSQL, dbFailOnError

'### END ###


And This..

'## BEGIN ##

Dim strSQL As String

strSQL = _
"INSERT INTO StoredReports (clientID, name, address, by, carnr,
carname)" & _
"VALUES('" & Me!ClientID & "','" & Me!name & "','" & Me!Address & "', "
& _
"'" & Me!by & "','" & Me!carnr & "','" & Me!carname & "')"

CurrentDb.Execute strSQL, dbFailOnError

'### END ###

No luck .. same Syntax error..
huh..

does your sixth sence sence anything .. ?

Steinki.
 
S

Steinarr G.

Hi again ..

Then i tryed this..

'## BEGIN ##

Dim strSQL As String
Debug.Print
strSQL = _
"INSERT INTO StoredReports (clientID, name, " _
& "address, by, " _
& "carnr, carname)" & _
"VALUES(" _
& """" & Me!ClientID & """," _
& """" & Me!name & """," _
& """" & Me!Address & """," _
& """" & Me!by & """," _
& """" & Me!carnr & """," _
& """" & Me!carname & """)"

CurrentDb.Execute strSQL, dbFailOnError

'### END ###


And This..

'## BEGIN ##

Dim strSQL As String

strSQL = _
"INSERT INTO StoredReports (clientID, name, address, by, carnr,
carname)" & _
"VALUES('" & Me!ClientID & "','" & Me!name & "','" & Me!Address & "', "
& _
"'" & Me!by & "','" & Me!carnr & "','" & Me!carname & "')"

CurrentDb.Execute strSQL, dbFailOnError

'### END ###

No luck .. same Syntax error..
huh..

does your sixth sence sence anything .. ?

Steinki.
 
J

John Smith

Ok, a few other thoughts, is ClientID a string? If it is a number it should
not have any quotes round it.

Having a column or control called 'Name' is bad news. Name is a reserved word
(not that Access stops you using it!), and you are liable to get the name
property of the object when you are expecting to get your data. In general,
don't use any word that Access uses to name any of your own objects - strange
errors tend to occur!

Have you tried :

Debug.print strSQL

after you assign it but before you try and execute it? Often just looking at
what you have built makes the problem obvious. If not, past it into the SQL
window of a new query and try and run it. This usually gets you a much more
helpful error message!

John
 
J

John Smith

Ok, a few other thoughts, is ClientID a string? If it is a number it should
not have any quotes round it.

Having a column or control called 'Name' is bad news. Name is a reserved word
(not that Access stops you using it!), and you are liable to get the name
property of the object when you are expecting to get your data. In general,
don't use any word that Access uses to name any of your own objects - strange
errors tend to occur!

Have you tried :

Debug.print strSQL

after you assign it but before you try and execute it? Often just looking at
what you have built makes the problem obvious. If not, past it into the SQL
window of a new query and try and run it. This usually gets you a much more
helpful error message!

John
 
S

Steinarr G.

Holy Crap ..

I found it..

you where talking aboute.. 'name' ... that it´s not whise .. and so on ..

well .... " by " whas the problem.

hehe

www.steinki.net/code.txt

i own you a pizza ;)

hehe .. biggtime thankes.. both of ja.
 
S

Steinarr G.

Holy Crap ..

I found it..

you where talking aboute.. 'name' ... that it´s not whise .. and so on ..

well .... " by " whas the problem.

hehe

www.steinki.net/code.txt

i own you a pizza ;)

hehe .. biggtime thankes.. both of ja.
 

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

Top