I would like some help on trying to reference the current record in a form. I have written an update sql statement which is triggered by a change on one of the fields but at the moment it runs an updates all records in the table instead of just the current one. Can someone help please!

Won't the record save automatically after the field has been changed and the form is closed?

Col

yes it does. my problem is that I have set an event that whenever a selection is changed in one of the form fields it executes a SQL command. This command is set to update two other fields in the table. I would like it to only update the fields for the current record but at the moment the SQL updates the whole table. Do you know how to reference the current record in anyway for a sql command or maybe using VB?

Sounds like you need to do a loop through the recordset until it reaches the matching IdNo that is on the form then do the update when it finds it.

Col

I think you are right but the problem is I do not know how to REFERENCE the CURRENT ID number of the form.

You'll need a control on the form that contains that id number. It can be invisible if you don't want to 'see' it. Then refer to it with something like "[tableIDfield] = " & Me.[FormIDControlField]

However, if you're only updating records in the currently displayed record this shouldn't be necessary. You can use Me.ControlName to refer to those fields and do the calculations immediately in the AfterUpdate event of whatever field has the data that changed. No update query required.

Now, that being said, what exactly are you 'changing', and does it need to be stored in the table? Often you can use query fields to get the same result without redundant storage problems.

[This message has been edited by David R (edited 05-24-2002).]