Read and update a field in another table.

[Artist.Anon]

A simple request and surely a simple answer. My code looks messy etc. it must be easier to do than what I am trying. I would appreciate if someone would take the time to help a new VBAer with this and let me know what the code would be to accomplish this:

I want to read the value from a table 'systemDefaults', field 'sequenceNumber' in the first record (ID=1) and then update it by 1.

Thank you.

[Evilferret]

Mostly the code is SQL not VBA, but here you go

DoCmd.RunSQL "SELECT sequenceNumber FROM systemDefaults WHERE ID=1"
DoCmd.RunSQL "UPDATE systemDefaults SET sequenceNumber=sequenceNumber+1 WHERE ID=1"

You said you want to read the value and then update it. The SELECT statement above will read the value, but it's probably not what you're looking for as you can't set a variable equal to a select statement (Since it returns a recordset, not a value)... You didn't really define why you wanted to read it though.

If you're trying to set a variable in your program equal to the value prior to the update, this would be your code (Replace VariableName with the variable you want to store the value in):

VariableName = DLookup("sequenceNumber", "systemDefaults", "ID=1") 
DoCmd.RunSQL "UPDATE systemDefaults SET sequenceNumber=sequenceNumber+1 WHERE ID=1"

If you only want to do the update and don't care about reading and storing the value, you can just run this:

DoCmd.RunSQL "UPDATE systemDefaults SET sequenceNumber=sequenceNumber+1 WHERE ID=1"

[Artist.Anon]

Thank you. Just what I needed. I had found the DLookUp but couldn't find the DUpdate, hehe