Custom Reference number how to update from vba

[marlind]

Client wants to populate leading request number based on 20130001 ... 20130010..20130999...20131000 and so forth
I have the code
Public Function Leadingzeros(StrTable As String)
Dim db As DAO.Database
Dim rec As DAO.Recordset
Dim rec1 As DAO.Recordset


Dim mycount As Integer
Dim RequestNumber As Field
Dim yearcheck As Integer


Set db = CurrentDb()
Set rec = db.OpenRecordset(StrTable)
Set rec1 = db.OpenRecordset("tblgeneratereqnumber")




yearcheck = DatePart("yyyy", Date)
'MsgBox (yearcheck)

Do While Not rec.EOF

rec.Edit
If Left(rec("RequestNumber"), 4) = yearcheck Then mycount = mycount + 1
rec.MoveNext
Loop
'rec.Close
rec.AddNew

Select Case mycount
Case Is < 9
rec("RequestNumber") = yearcheck & "000" & mycount + 1
Case Is < 99
rec("RequestNumber") = yearcheck & "00" & mycount + 1
Case Is < 999
rec("RequestNumber") = yearcheck & "0" & mycount + 1
End Select
MsgBox (rec("RequestNumber"))
rec1.Edit
rec1("newrequestnumber") = rec("RequestNumber")

rec.Close






Leadingzeros = True



End Function
I want to get rec("RequestNumber") on my new record when I go to the next record. Only thing I know is to put it in a table and do an update query.

Thanks for any help.

 

[spikepl]

This is waaaaaaaay too complex. Make your number like so

Dim Request Number As Long

RequestNumber=Nz(Dmax("RequestNumber", "tblRequests", "RequestNumber>=" & Year(Date())*10000#) ,0)+1

 

[marlind]

I want to click on the new request button have it populate the request number with the formulae above.

 

[marlind]

So they have a new request and the click on the new record button to add data to all the fields. Requestnumber is on the form to be pubulated. Not the key field.

 

[marlind]

Just to clarify I use
DoCmd.GoToRecord acForm, "frmpublicrecordslog", acNewRec
to get to a new record.

 

[marlind]

I got it I was way to long thanks.