Increment a number then add back to table

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!

re: Increment a number then add back to table

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?
simonmarkjones@gmail.com wrote:[color=blue]
> 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[/color]
in[color=blue]
> a textbox on a form first.
>
> Cheers![/color]

re: Increment a number then add back to table

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

re: Increment a number then add back to table

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

re: Increment a number then add back to table

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.

re: Increment a number then add back to table

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