best way to update field in recordset?

[kcn]

Hi,

I need to regularly update a field (field 1) in a table based on the the data in two other fields in the same table. The data changes both when a new record is imported *and* when changes are made to fields 2 and 3. Note these are all text fields. The table itself has around 500 records and will grow very slowly. I'm using Access 2000.

I guess my question is what is the is the best (classic)method for automtically updating this field? Is there a standard way to do this?

curiously,
kerry

ps: By the way, I know one should not generally not base the value of one field on the values in other fields, but in this case this is our best option.

 

[KMKelly]

If the two fields are in the same table, you might think about just doing the calculation on the fly in queries and reports as needed. But, if you want to have a field with that info, you could create a standard update query and run it however often you think is necessary.

Create a new query and select update from the query menu. Add the field you want to update and enter the calculation in the "update to:" line. Just be careful to put field names in brackets, it won't do it automatically.

When you run it, it would run every record based on that calcuation, but you can filter and run only records since a certain date, etc.

It may really be worth looking at just doing the calculations on the fly though.

Kris

 

[kcn]

thanks for your response. but i do need to automate this. i'm assuming i need to run an update procedure that either runs 4 update queries for the different codes i need set in the field (basesd on those two fields), or that references a case statement.

i guess a more specific question would be "what is the standard way to automate a field update?"

thanks again,
kerry

 

[psemianonymous]

The standard way to do something automated is:
1) when you enter the data into the database, change the calculated field's value
2) when you modify the data, ever, change the calculated field's value

Unfortunately, Access doesn't have any sort of triggers, so we have to emulate the functionality of #1 and #2. We do this by:
1. Disabling users' ability to see the tables directly
2. When they are editing the data in a form, we use the form's BeforeUpdate() event to change the calculated field value before any changes are saved.
3. If you are importing data, make sure you run a "after import" process that sets the calculated field. New data entered via the form will be caught by the BeforeUpdate() event.


Hopefully I haven't been too 'meta' in my description.

 

[kcn]

hi,

i may have an unusual situation here where the users like to work both behind the scenes and with forms so that i need some way to bypass the beforeupdate event (although thanks, that was helpful). the imports are irregular and are not currently automated.

i also find i'm beating around the bush because what i really need is for someone to say "here's the code you would use to update the data in your table whenever someone presses a button". i've been playing around with code for updating recordsets but can not get anything to work due to my vast lack of experience!

thanks for your help. more suggestions will be much apprecited!

k.

 

[IcesMaster]

Just make a button and open the click event and try this code:

Private Sub buttonX_click()
Dim mSql As string
sSql = &quot;UPDATE tablename SET columnnametoupdate = columnname1 + columnname2 WHERE columnnametoupdate <> columnname1 + columnname2&quot;
DoCmd.RunSql mSql
End Sub

Is that what you mean?

Why not create query with caculated field ?

 

[kcn]

hi,

yes. that's closer. because field 1 needs to be set to four different categories depending on data in fields 2 and 3, i guess i would need to create four separate sql statements, or four separate queries and run each of them. i'll give this a try. it's far more simple than what i was attempting by creating a public function.

thanks again.