Update field in table based on query 1

[199840]

I have an InfoPath form which submits and recieves data from an access table.
I want to do the calculations in Access, i am using a datediff function to
calculate the number of days between two dates. I want to have the query run
whenever data is written to the table?

Any suggestions greatly recieved
Stu

 

[rubbernilly]

You shouldn't normally have to keep calculated data in the database... it gets you out of normalization and can lead to administrative headaches.

Can you not just have a calculated control on the form using the DateDiff() function?

 

[199840]

The DateDiff function doesnt work in InfoPath, i am not worried about normalisation. Can it still be done??

 

[rubbernilly]

A table does not have events to trigger an update query... but you can build a function that would take parameters of your primary key (to make sure that you got only the one record you wanted to update), construct an UPDATE SQL statement, and then do a DoCmd.RunSQL, setting the DateDiff field to the proper value. Then you would just need to make sure that whenever you commit a record to the table you would be launching the Function right after. Normally when I run into this sort of thing, I end up going with an unbound form (so that I control when the information is written to the table).

HTH

 

[199840]

I am a little unsure where i need to put the SQL statement?
How do i use an unbound form, and then be able to control what information is submitted?

The SQL for my query is as folows
UPDATE Table2 SET Table2.[Date] = DateDiff("d",[Date1],[Date2]);
I am not sure if i cant use this as the default value for the field, but when i enter this in the default value for the field i recieve an error.

A little bit more information would be really helpful
Thanks again
Stu

 

[rubbernilly]

I don't know InfoPath at all, so forgive if this will not work but this is the solution I was suggesting:

You build a form with the controls on it you need to update the table, but none of those controls are bound to the table fields. This means that you have to provide all of the record navigation, validation, and commitment functionality the user will need. You will need a button to move to the next record - validating that the current record should be saved first, etc.

So, once you have the form built, your "Save Record" code (running when the user clicks a "Save" button and/or when they navigate to the next record) would first validate the information, then commit the changes to the table (by writing the values of the controls to the table. It would then use your primary key information to construct an UPDATe statement.

The idea there is that your primary key information for this record will return a unique record and make it so that the Update Query updates only that one record.

The way it is set up now, it sounds like you have a query (which is not yet working properly) that you want to run every time a new record is written to the table. The problem with that is that there is no limit to the number of records returned by the UPDATE statement. It will always re-write the Date field for all of the records in the table. That's why I'm saying do it for one record at a time as the record is written. The way you ensure that you do it when the record is written is by unbinding your form.

Finally, you shouldn't use "Date" as a field name. That is a reserved word and is probably causing you some difficulty.

Honestly, I wonder if this will help, though. If you can get to the point where you can run code and DoCmd.RunSQL or execute an UPDATE query... you should just be able to use the DateDiff() function to get the value to write to the table in that field without having to go the extra step of an SQL statement at all. Hmm... Like I said, I don't know InfoPath, so I guess I'll just give you the information above and let you see if it works for you.

HTH

 

[199840]

Thanks, i am pretty sure that this would work within Access as what you are suggesting is a stepping through each record. But i dont think InfoPath will accept that kind of thing, or at least not at a basic coding level.
Thanks anyway