parameter??

[Guest]

I have added a command button to a form with the following:-

Dim str As String
str = "UPDATE Testtable SET Testtable.CentreId = 1, Testtable.TypeId = 4
WHERE ((Left([Testtable]![CTEXT],3)="PAY"));"
DoCmd.RunSQL (str)

When I click the button, I am getting a prompt for a paramenter. If I run
the query I do not get the prompt. Is there a simple way to avoid getting
the prompt from the button

 

[strive4peace]

Hi Jer,

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a module window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

had you compiled, you would have seen that you cannot have "PAY" within
a string delimited by double quote marks.

If you want a quote mark within a delimited string:
1. use a single quote '
....Left([Testtable]![CTEXT],3)='PAY'));"
OR
2. two double quotes ""
....Left([Testtable]![CTEXT],3)=""PAY""));"

also, instead of using
DoCmd.RunSQL (str)

do this:

currentdb.execute str

then, you don't have to worry about Echo and Warnings...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Thank you Crystal
--
thanks as always for the help

Hi Jer,

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a module window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

had you compiled, you would have seen that you cannot have "PAY" within
a string delimited by double quote marks.

If you want a quote mark within a delimited string:
1. use a single quote '
....Left([Testtable]![CTEXT],3)='PAY'));"
OR
2. two double quotes ""
....Left([Testtable]![CTEXT],3)=""PAY""));"

also, instead of using
DoCmd.RunSQL (str)

do this:

currentdb.execute str

then, you don't have to worry about Echo and Warnings...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I have added a command button to a form with the following:-

Dim str As String
str = "UPDATE Testtable SET Testtable.CentreId = 1, Testtable.TypeId = 4
WHERE ((Left([Testtable]![CTEXT],3)="PAY"));"
DoCmd.RunSQL (str)

When I click the button, I am getting a prompt for a paramenter. If I run
the query I do not get the prompt. Is there a simple way to avoid getting
the prompt from the button

 

[strive4peace]

you're welcome, Jer happy to help

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*