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?!

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

"Agent Dagnamit" <Agent (E-Mail Removed)> wrote in message
news:046C1FF9-8E9E-4E3F-B36F-(E-Mail Removed)...
>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?!
>

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?

"Alex Dybenko" wrote:

> 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
>
> "Agent Dagnamit" <Agent (E-Mail Removed)> wrote in message
> news:046C1FF9-8E9E-4E3F-B36F-(E-Mail Removed)...
> >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?!
> >
>
>

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


"Agent Dagnamit" <(E-Mail Removed)> wrote in message
news:EF0DE726-BEDD-4D6B-8F24-(E-Mail Removed)...
> 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?
>
> "Alex Dybenko" wrote:
>
>> 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
>>
>> "Agent Dagnamit" <Agent (E-Mail Removed)> wrote in
>> message
>> news:046C1FF9-8E9E-4E3F-B36F-(E-Mail Removed)...
>> >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?!
>> >
>>
>>

Thanks Alex, that's been a great help

Stuart


"Alex Dybenko" wrote:

> 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
>
>
> "Agent Dagnamit" <(E-Mail Removed)> wrote in message
> news:EF0DE726-BEDD-4D6B-8F24-(E-Mail Removed)...
> > 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?
> >
> > "Alex Dybenko" wrote:
> >
> >> 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
> >>
> >> "Agent Dagnamit" <Agent (E-Mail Removed)> wrote in
> >> message
> >> news:046C1FF9-8E9E-4E3F-B36F-(E-Mail Removed)...
> >> >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?!
> >> >
> >>
> >>
>
>