Insert Into using variables

[Guest_imported]

dim mynote as string
mynote = me!note.value
DoCmd.RunSQL "insert into tblbogus (bogus)" & " Values(" & mynote & ""

mynote is dim'ed as a string.

When the runsql is executed it doesn't insert the value of the mynote variable into the table, instead it treats it as a parameter and puts up a dialog box asjking you for it's value.

How can I pass a variable into an insert into operation?

 

[Robertd]

Hi Markl

this is a little long winded but the approach has advantages

dim mySQL as String

mySQL = "INSERT INTO TableName ( Field1, Field2, field3)"
mySQL = mySQL & " SELECT "
mySQL = mySQL & Me.FormControl
mySQL = mySQL & " AS Field1,"
mySQL = mySQL & aVariable
mySQL = mySQL & " AS Field2,"
mySQL = mySQL & AnotherVar
mySQL = mySQL & " AS Field3;"

DoCmd.SetWarnings (False) ' hides run query prompts
DoCmd.RunSQL mySQL 'Insert record to TableName
DoCmd.SetWarnings (True) ' all ways turn back
' or you won't get any warnings

in your case this should be a bit closer to it

DoCmd.RunSQL "insert into tblbogus (bogus) Select "
& mynote & " as FieldName;"

<OR>

Dim mySQL as String

mySQL = &quot;insert into tblbogus (bogus) Select &quot;
mySQL = mySQL & mynote
mySQL = mySQL & &quot; as FieldName;&quot;

DoCmd.SetWarnings (False) ' hides run query prompts
DoCmd.RunSQL mySQL 'Insert record to TableName
DoCmd.SetWarnings (True) ' all ways turn back

in either case the string needs to evaluate to a legal SQL statement. ;-)

HTH


Robert Dwyer
rdwyer@orion-online.com.au

 

[Durkin]

I think that you are probably trying to put mynote into a text field. If this is the case you need to put single quotes around the string or Jet will think it's a parameter name. Try this:

DoCmd.RunSQL &quot;insert into tblbogus (bogus) Values('&quot; & mynote & &quot;');&quot;

If this isn't the case, ignore this.

Durkin
alandurkin@bigpond.com