Saving data back to table

[wvrider]

I have a database that saves a field(MainID) in this format: 2008-0001-ABC.
The 0001 is saved in a different database table(ValueReset). Each time a new
record is created, that number in incremented one and saved back to a field
in another table. My question is how do i pull that value from the table,
increase it by one, and then save that value back to the table using vba?

I am pulling the value using this:
DLookup("[IncNumber]", "ValueReset", "[ID] = 1") and I get the value I need,
I can incriment the value, just cannot save it back to the database so that
next time, it will be one higher.

Any help or suggestions? thanks. I would prefer in code if possible. thanks

 

[Klatuu]

Dim varNewMain As Variant
Dim lnglNextNum As Variant
Dim strSQL As String

'Get the current value and add 1 to it.
lngNextNum = Nz(DLookup("[IncNumber]", "ValueReset", "[ID] = 1"), 0) + 1
'Update the ValueReset table
strSQL = "UPDATE ValueRest SET IncNumber = " & lngNextNum;"
Currentdb.Execute strSQL, dbFailOnError

'Now here is a good way to create the new MainID:
varNewMain = Split(Me.txtMainID, "-")
varNewMain(1) = Format(lngNextNum, "0000")
MetxtMainID = Join(varNewMain, "-")

'txtMainID being a form control that is bound to the MainID field.

 

[wvrider]

Thanks for the quick reply. I am getting an error though in this part of the
code:

strSQL = "UPDATE ValueRest SET IncNumber = " & lngNextNum;"

after typing it, the ; is highlighted and this error is shown: Compile
Error: Expected end of Statement.

Then when I compile it and save it gives me Compile Error Syntax Error.

I am using access 2007 if that makes a difference. Thanks again for the help.

 

[Klatuu]

My bad, I wrote it wrong. Too bad there is not VBA syntax checker in the
newsgroup <g> Should be:

strSQL = "UPDATE ValueRest SET IncNumber = " & lngNextNum & ";"

 

[wvrider]

Thank you very much! I tried putting quotes here and there, but didn't come
up with that. Thanks again. that solved about 5 of my other problems I had
as well.