Increment a number then add back to table

[simonmarkjones]

Hi am very new to programing VBA in Access.

I'm trying to increment a number stored in a table.
So far i've got.

Function IncrementLastUsedNumber()
Dim myNumber As Integer

myNumber = DMax("[LastUsedNumber]", "tableLastUsedNumber")+1
End Function

I now need to be able to write this new value back to the table over
the previous number.

I want to be able to do this with VBA rather than storing the value in
a textbox on a form first.

Cheers!

[Beacher]

Could you not just use SQL to write the variable back to the table?
Dim strSQL AS String
strSQL = "SQL CODING HERE"
DoCmd.RunSQL(strSQL)

Something like that?
si************@gmail.com wrote:

Hi am very new to programing VBA in Access.

I'm trying to increment a number stored in a table.
So far i've got.

Function IncrementLastUsedNumber()
Dim myNumber As Integer

myNumber = DMax("[LastUsedNumber]", "tableLastUsedNumber")+1
End Function

I now need to be able to write this new value back to the table over
the previous number.

I want to be able to do this with VBA rather than storing the value in a textbox on a form first.

Cheers!

[simonmarkjones]

Probably but don't know any SQL either.

Never had to do this befor but work needs it done by friday.

Want to use it for an autogenerated number of a certain format.

I've coded all the other parts to this just not sure how to add back to
database.

Sorry

[Beacher]

Dim strSQL AS String
strSQL = "UPDATE tblName SET fieldName = " & myNumber & " WHERE
fieldName2 = 'criteria' "
DoCmd.RunSQL(strSQL)

I don't know if you want to replace this number in every row of your
table.. if so then take off the word WHERE and on

[simonmarkjones]

Wow that helped thankyou.

Guess i'll have to tech myself some SQL now.

My first newsgroup message ever as well. Thanks for the quick response!

Have a good day.

[Beacher]

My first too :)... No problem, glad I could help!