Append Query in VBA - to append VBA variable values to Access tabl

[Guest]

I want to run an append query to append data to an existing Access table
calculated from procedures run against a recordset. This doesnt work:


DoCmd.RunSQL "INSERT INTO Results ( [AXA Ref] ) SELECT [Axaref] AS [AXA Ref]"


...even though "AXA Ref" is definately a valid field in table "Results", and
"Axaref" is an existing VBA variable (with value 1). When I run this,, the
Access "Enter Parameter Value" dialogue box appears.

However, I can assign the value "Axaref" to a field on a form, and then use
that form control's value in an append query, like this:

Forms![AdvertForm].[axa] = AxaRef

DoCmd.RunSQL "INSERT INTO Results ( [AXA Ref] ) SELECT
Forms![AdvertForm].[axa] AS [AXA Ref]

I'm sure it isnt really necessary to transfer the value of a VBA variable to
a Form control before it is useable in an Access Database....is it?!

 

[Alex Dybenko]

Hi,
try:
DoCmd.RunSQL "INSERT INTO Results ( [AXA Ref] ) Values(" & AxaRef & ")"

if AxaRef is a string variable - then make sure to include it in quotes

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

Thanks Alex, that works. What is the reasoning behind the "&" character,
presumably refering to my VBA variable?

Also, sorry to bug you again, what would the code be if AxaRef was a date
field that I wanted to append?

Hi,
try:
DoCmd.RunSQL "INSERT INTO Results ( [AXA Ref] ) Values(" & AxaRef & ")"

if AxaRef is a string variable - then make sure to include it in quotes

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

I want to run an append query to append data to an existing Access table
calculated from procedures run against a recordset. This doesnt work:


DoCmd.RunSQL "INSERT INTO Results ( [AXA Ref] ) SELECT [Axaref] AS [AXA
Ref]"


..even though "AXA Ref" is definately a valid field in table "Results",
and
"Axaref" is an existing VBA variable (with value 1). When I run this,,
the
Access "Enter Parameter Value" dialogue box appears.

However, I can assign the value "Axaref" to a field on a form, and then
use
that form control's value in an append query, like this:

Forms![AdvertForm].[axa] = AxaRef

DoCmd.RunSQL "INSERT INTO Results ( [AXA Ref] ) SELECT
Forms![AdvertForm].[axa] AS [AXA Ref]

I'm sure it isnt really necessary to transfer the value of a VBA variable
to
a Form control before it is useable in an Access Database....is it?!

 

[Alex Dybenko]

Hi,

& AxaRef - will add a value of AxaRef, converted into string, to SQL string.
as for dates - you have to put them in #mm/dd/yyyy# format, so your code
will be the following:

DoCmd.RunSQL "INSERT INTO Results ( [AXA Ref] ) Values(#" &
format(AxaRef,"mm\/dd\/yyyy") & "#)"

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

Thanks Alex, that works. What is the reasoning behind the "&" character,
presumably refering to my VBA variable?

Also, sorry to bug you again, what would the code be if AxaRef was a date
field that I wanted to append?

Hi,
try:
DoCmd.RunSQL "INSERT INTO Results ( [AXA Ref] ) Values(" & AxaRef & ")"

if AxaRef is a string variable - then make sure to include it in quotes

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

message

I want to run an append query to append data to an existing Access table
calculated from procedures run against a recordset. This doesnt work:


DoCmd.RunSQL "INSERT INTO Results ( [AXA Ref] ) SELECT [Axaref] AS [AXA
Ref]"


..even though "AXA Ref" is definately a valid field in table "Results",
and
"Axaref" is an existing VBA variable (with value 1). When I run this,,
the
Access "Enter Parameter Value" dialogue box appears.

However, I can assign the value "Axaref" to a field on a form, and then
use
that form control's value in an append query, like this:

Forms![AdvertForm].[axa] = AxaRef

DoCmd.RunSQL "INSERT INTO Results ( [AXA Ref] ) SELECT
Forms![AdvertForm].[axa] AS [AXA Ref]

I'm sure it isnt really necessary to transfer the value of a VBA
variable
to
a Form control before it is useable in an Access Database....is it?!

 

[Guest]

Thanks Alex, that's been a great help

Stuart

Hi,

& AxaRef - will add a value of AxaRef, converted into string, to SQL string.
as for dates - you have to put them in #mm/dd/yyyy# format, so your code
will be the following:

DoCmd.RunSQL "INSERT INTO Results ( [AXA Ref] ) Values(#" &
format(AxaRef,"mm\/dd\/yyyy") & "#)"

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

Thanks Alex, that works. What is the reasoning behind the "&" character,
presumably refering to my VBA variable?

Also, sorry to bug you again, what would the code be if AxaRef was a date
field that I wanted to append?

Hi,
try:
DoCmd.RunSQL "INSERT INTO Results ( [AXA Ref] ) Values(" & AxaRef & ")"

if AxaRef is a string variable - then make sure to include it in quotes

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

message
I want to run an append query to append data to an existing Access table
calculated from procedures run against a recordset. This doesnt work:


DoCmd.RunSQL "INSERT INTO Results ( [AXA Ref] ) SELECT [Axaref] AS [AXA
Ref]"


..even though "AXA Ref" is definately a valid field in table "Results",
and
"Axaref" is an existing VBA variable (with value 1). When I run this,,
the
Access "Enter Parameter Value" dialogue box appears.

However, I can assign the value "Axaref" to a field on a form, and then
use
that form control's value in an append query, like this:

Forms![AdvertForm].[axa] = AxaRef

DoCmd.RunSQL "INSERT INTO Results ( [AXA Ref] ) SELECT
Forms![AdvertForm].[axa] AS [AXA Ref]

I'm sure it isnt really necessary to transfer the value of a VBA
variable
to
a Form control before it is useable in an Access Database....is it?!