Can I use a form to run an update query?

[nevinx]

I have a form set up to run an update query when I click the okay button.
Everything works like it should except I still get a box popping up asking my
for the update value that should have already been given by the form. Any
ideas on what I am doing wrong?

 

[Graham Mandeno]

Can you please post the SQL of your update query, and also say how you are
invoking it from your button's Click event code.

 

[nevinx]

Here is the SQL for the update query:
PARAMETERS [Forms]![Process change form]![Line] Text ( 255 ),
[Forms]![Process change form]![Parameter] Text ( 255 ), [Form]![Process
change form]![To] Text ( 255 );
UPDATE [Set-up sheets] SET [Set-up sheets].Setting = Forms![Process change
form]!To
WHERE ((([Set-up sheets].Line)=[Forms]![Process change form]![Line]) AND
(([Set-up sheets].Parameter)=[Forms]![Process change form]![Parameter]));

And this is how I am invoking with the buttons on click event code:
Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenQuery "setup sheet update query", acViewNormal, acEdit
DoCmd.Close acForm, "Process change form"

Can you please post the SQL of your update query, and also say how you are
invoking it from your button's Click event code.
--


Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a form set up to run an update query when I click the okay button.
Everything works like it should except I still get a box popping up asking
my
for the update value that should have already been given by the form. Any
ideas on what I am doing wrong?

 

[Graham Mandeno]

As a matter of interest, does it work if you set the values in the form and
then run the query from the database window? SQL sometimes fails to resolve
parameters from forms, particularly in cases where the SQL statement is
passed to the Jet DB engine before the parameter values have been resolved.
I have never used OpenQuery to run an action query, so I don't know if this
is the problem in this case.

However, I suggest using a different approach: One way, if you want to use
your existing query, is to set a QueryDef object in your code, then set the
parameter values in your code before issuing the Execute method on the
QueryDef.

In your situation I would assemble a SQL statement "on the fly" and Execute
it against the CurrentDb object:

Dim strSql as String
strSql = "UPDATE [Set-up sheets] SET " _
& "[Setting] = '" & Me![To] & "'" _
& "WHERE ([Line]='" & Me![Line] & "') " _
& "AND ([Parameter]='" & Me![Parameter] & "');"
CurrentDb.Execute strSql, dbFailOnError

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Here is the SQL for the update query:
PARAMETERS [Forms]![Process change form]![Line] Text ( 255 ),
[Forms]![Process change form]![Parameter] Text ( 255 ), [Form]![Process
change form]![To] Text ( 255 );
UPDATE [Set-up sheets] SET [Set-up sheets].Setting = Forms![Process change
form]!To
WHERE ((([Set-up sheets].Line)=[Forms]![Process change form]![Line]) AND
(([Set-up sheets].Parameter)=[Forms]![Process change form]![Parameter]));

And this is how I am invoking with the buttons on click event code:
Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenQuery "setup sheet update query", acViewNormal, acEdit
DoCmd.Close acForm, "Process change form"

Can you please post the SQL of your update query, and also say how you
are
invoking it from your button's Click event code.
--


Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a form set up to run an update query when I click the okay
button.
Everything works like it should except I still get a box popping up
asking
my
for the update value that should have already been given by the form.
Any
ideas on what I am doing wrong?