Parameterized Update query not working correctly in Access 97
Quote:
>The problem with the update query is that now it only updates the first
>record, i.e the one with ID = 1 even if you select the 2nd , 3rd, 4th etc..
Now that your parameters are not getting mixed up,
what happens when you do this:
Quote:
>I put a break point on the query execute line so that I could check out
>values of the parameters before running the query. They were as
>prmNewMethod = "Post2"
>prmMethodID = 1
I would guess that the query is OK now, and the problem is with
the combo box. (Or actually, I guess that by now you have probably
fixed it)
Quote:
>Hi,
>Thanks for responding.
>However this did not work either.
>A couple of extra things to mention are :
> The forms record source is the table - tblShipMethod.
> The cboShippingMethod's control source is ShippingMethod.
> The cboShippingMethod's rowsource is SELECT * from tblShipMethod.
>The rowsource is as you would expect to populate the combo box.
>The reason the combo box's control source has a value (ShippingMethod)
>because if it doesn't Access (stupidly) won't left you select a value from
>the list, i.e You click an item in the list but it doesn't close the list
>and but the value in the text area of the combo box.
>The reason the form is bound to tblShipMethod is down to a similar reason
as
>for the combo box, i.e the combo box displays #name? if the form isn't
>bound.
>The problem with the update query is that now it only updates the first
>record, i.e the one with ID = 1 even if you select the 2nd , 3rd, 4th etc..
>Any more ideas ?
>Oh as well I tried using embedded SQL by building a string up - "UPDATE
>tblShipMethod SET ShippingMethod = " & txtShippingMethod.Value etc.... and
>then executing that on the CurrentDB, this didn't work either it had the
>same problem.
>Thanks again
>Peter Row
>> try this:
>> PARAMETERS prmNewMethod Text, prmMethodID Long;
>> > UPDATE tblShipMethod SET ShippingMethod = prmNewMethod
>> > WHERE ShippingMethodID = prmMethodID;
>> >Hi,
>> >I have a form which allows the user to edit a list of shipping methods
>(e.g
>> >FedEx, ParcelForce etc..) which are stored in a table.
>> >I have set up the following parameterised update query in the DB :
>> > UPDATE tblShipMethod SET ShippingMethod = prmNewMethod
>> > WHERE ShippingMethodID = prmMethodID;
>> >I set up the parameters in code before I execute the query as follows :
>> > Set qdfQuery = CurrentDb.QueryDefs("qry_UpdateShippingMethod")
>> > qdfQuery.Parameters("prmNewMethod") = txtShippingMethod.Value
>> > qdfQuery.Parameters("prmMethodID") = cboShippingMethod.Column(0)
>> > qdfQuery.Execute dbConsistent
>> >In the shipping methods table the first record has an ID of 1 and a text
>> >shipping method of "Post", so for the purposes of testing I simply
>changed
>> >the method to "post2"
>> >I put a break point on the query execute line so that I could check out
>> the
>> >values of the parameters before running the query. They were as follows
:
>> >prmNewMethod = "Post2"
>> >prmMethodID = 1
>> >However after executing the query and refreshing the form, instead of
>> >updating "Post" to "Post2" as it should of done it has instead changed
>> >"Post" to "1". Obviously this is not correct. Any ideas ?????
>> >As you can see in the code above that sets up the parameters and the SQL
>of
>> >the query I have set the parameters in the order in which they appear in
>> the
>> >query. I know this makes a difference in some versions of SQL where you
>> >specify the parameter by index value rather than my name as in this
>> >situation, but with Access you never know so I did it any way.
>> >Regards
>> >Peter Row
