How to instantly fill up a field in a form

[aingram]

undefined
Hi im new here

Ive got a db that im having problems with (Access 97)

i got a date field (date1) - which is automatically todays date
ive got a combo box (option) as below - it displays the name - :-

name days
one 1
two 2
three 3

and another date field (date2) - which i want (date1) + (option) to add in

Ive setup an update query that does this fine
So i setup a command button in a form that runs a macro that runs the query

But the (date2) field does not update when i press the button when viewing a record it ignores it and does the rest of the records fine

So i have to flick away run the query while viewing another record and then go back.

Does anyone know how to do this with out the flicking

thanks

Andrew

 

[Mile-O]

i got a date field (date1) - which is automatically todays date
ive got a combo box (option) as below - it displays the name - :-

name days
one 1
two 2
three 3

and another date field (date2) - which i want (date1) + (option) to add in

As the second date is dependent upon the two other fields in the same table you can remove the second date field from your table - it is not needed, a waste of space, and goes against the Third Normal Form (3NF) of database normalization.

Since it's a calculated value, you would perform the calculation in a query, form, or report at runtime so that no errors can ever be made.

If, in a query, you wanted the second date you would use the following syntax:

Date2: DateAdd("d", [DaysField], [Date1])

In SQL, this is written this

SELECT DateAdd("d", [DaysField], [Date1]) AS Date2
FROM MyTable;

On the form, you can put in the Date2 control's ControlSource:

=DateAdd("d",[DaysControl],[Date1])

 

[aingram]

Thanks But??

Ive tried your suggestions with no luck so i have attached a stripped down version of what im trying to do,

So in the form when i select one day the days box i want the snd_date to populate with tommorows date

Thanks again

Andrew

 

[Mile-O]

I've played about with your database.

 

[aingram]

Thanks

That works great, on a form

This is to go into a orders database so that on each order i can select
one day
two days
one week
and so on

and then print of the order with the issued date and a target completion date, as far as i can see the way it works just now is only for show in the form, is there a way to make the date store in a field??????????????????

 

[Mile-O]

As I said before, it goes against the rules of database normalization to store calculated values.

I showed you how to do the calculation in a query. I'm now attaching a version of the database with the calculation performed in the query.

Also, leave your tables only for data storage. Don't try to use them in a form or a report - when you need the dataa, make a query of the table and use the query in the form or report. This is because tables don't do anything but with a query you can summarise the data, add calculations, and sort the data.