Update field

[Xcarnate]

I need to update a field in a table with a value that has been calculated in a form.
It works a bit like this:

The calculated value is called DDate, and is calulated from

![orders]![StartDate] + 7

I now need this new value to over-write the StartDate value.

I have tried all sorts of junk to make this work, the one that made the most sence (but still failed) was:
Value = [orders!StartDate]
Change Value = Me!DDate

Am i falling at the last hurdle or totally off track?

 

[bjackson]

is your form that sets the value bound to a table or unbound

 

[Xcarnate]

It is bound to several Tables, the Customer table (so the customers name can be displayed) the Orders table (contains the StartDate value) and the Publication table (so they know What item they are talking about).

Ive noticed that when i have any form bound to several tables it doesnt let me mannually change any of the refranced values on the form. Could that be a problem?

Thanks for taking an interest in my problem, btw.

 

[bjackson]

you cant change values on forms based on query's

i dont use macros so im not sure what you can do with them

however what i would do is

1.put a text box on your form ,set its visible property to false.and have its control source set to the id of the record in the orders table.now you can identify which record you want to update

docmd.runsql("UPDATE value SET orders.[value] = [forms]![yourformname]![textboxnamethatholdsyournewvalue]
WHERE (((orders.ID)=[forms]![formname]![textboxnamethatis hidden]));")
me.form.requery

 

[Xcarnate]

That worked perfectly after a bit of tweeking, thanks for helping. On a related note... now that i have created this macro how do i make it run on all records?

i have set it to run 'On open' and that works fine, but it only runs on the 1st record. Is there a 'Select All' command i can put at the start of the macro?

 

[bjackson]

get rid of the where in the sql statement

here's a trick that i use,as im not a programmer so
i have to work out my own ways,which arent always
the best way, but who cares as long as they work

create the query you want to run as a stored query.
put all your criteria in it and when you have finished
it give it a small name,i use a1.go into design view and select sql
view.copy and paste the sql between the " " into the docmd.runsql("")

then see if it works,sometimes as you did with some tweaking of
field names etc you get your result

regards bjackson