Global current in sql 'insert'

[wilow]

Hi,

I have problem with order in sql insert.
- I have one global variable defined in access module.

public us as string

this variable I set on value just like this:

us = environ("USERNAME")

An now when I clicked button in form I want to insert this value into my form. I tried:

DoCmd.RunSQL ("INSERT INTO HISTORIA_OPERACJI(kto, co, ile, operacja, login) VALUES(" & Me.kto_wst & ", " & Me.nr_ref_wst & ", " & Me.ile_wst & ", " & 1 & "," & us & ");")

And when I run this line access display msgbox "Insert parameter value" with text: "us.value"
But I don't want display variable in msgbox, I want insert variable.value without msgbox.

 

[gemma-the-husky]

a string (like username) has to be wrapped in "" characters.

instead of docmd try this framework.
it will give you a clearer idea of what is wrong with your sql statement

you could also display the sqlstring in a msgbox so you can check it looks correct.

'code fragment
 
'set sqlstrg to contain your update statement
 
msgbox sqlstrg
on error goto fail
currentdb.execute sqlstrg, dbfailonerror
...
exit sub
 
 
fail:
msgbox "error processing statement: " & sqlstrg & vbcrlf & "Error: " & err & " Desc: " & err.description

 

[wilow]

Thanx for your reply
When I executed your code I see '3061' error. I created sample base. In this base we have one table, one module and one form. In module I declared public variable with user_name and I want insert this user name to table (users) using button in the form.

 

[Galaxiom]

See quote marks in red.

strSQL = "INSERT INTO HISTORIA_OPERACJI(kto, co, ile, operacja, login) VALUES(" & Me.kto_wst & ", " & Me.nr_ref_wst & ", " & Me.ile_wst & ", " & 1 & ", [B][COLOR=red]'[/COLOR][/B]" & us & "[B][COLOR=red]'[/COLOR][/B]);")

Will also be required for the other values if they are strings.

BTW It will break if the username can include an apostrophe. In that situation you wuld need to double up any apostophes using the Replace() function.