Sequence number 1

[Nene75]

Hi Everyone:

I have a Resume Database. I need to have computer generate the autonumber/Sequence number based on when the Resume was received at the HR as (Year/Month/Sequence = 200401-001).

Can anyone help how should I do this?

Thanks in advance!

 

[vb5prgrmr]

This is just one suggestion of many that come to mind but here goes ...

Add a table to your database and lets call it (at least for now, you can call it whatever you like) TempCnt. Now this table will have three number fields (Y, M, Cnt).

What you would do is something like ...

'open recordset pessimistic locking
'this table will only contain one record at all times
'check year to see if it is current against system time
'check month to see if current against system time
'if neither year or month is correct based upon system time then update record with correct year, month, and reset cnt to 1
'else if everything is current then retrieve value and update record with new value...
[tt]
SomeVariable = Rs.Fields("Cnt&quot.Value + 1
'if dao/rdo then rs.edit else no need
Rs.Fields("Cnt&quot = Rs.Fields("Cnt&quot + 1
Rs.Update
Rs.Close
[/tt]

so then you would be able to do what ever you needed to do with the retrieved information.

As you can see the Cnt field in the above psuedo code is the last used number and not the next available number for that date.

Good Luck

 

[Nene75]

vb5prgrmr:

Thanks for the prompt reply.

I am getting error message

"Object Resuired"
The following code is highlighted:
SomeVariable = Rs.Fields("Cnt&quot.Value + 1

How do I 'open recordset pessimistic locking?

Actually, the ResumeSubmissionDate is manually entered by HR User - This is not the current System Date. As Resumes are received in 100s per day and user only enter few of them in a day so how can I use the above code based on ResumeSubmissionDate + Sequence number? Sorry, I should have explain this in the first place.

 

[vb5prgrmr]

>How do I 'open recordset pessimistic locking?

How do you execute SQL statements like Select * from sometable?

How are you accessing this database? ADO, DAO, RDO, RDODC, DAODC, ADODC?

Ok, well it seems that you are going to need then to change the design of my suggestion so as to keep track of year and month (no need for day right!?). So instead of the table keeping only one record in it, it will contian records in it for each year and month combination that users have entered data for. ...
[tt]
Y M Cnt
2004 01 003
2004 02 001 'when next month comes around
[/tt]

So that means instead of select * it will need to be
[tt]
Select * From TempCnt Where Y = " & useryearinput & " AND M = " & usermonthinput
Set Rs = ...
[/tt]

then you can pretty much do as I have outlined.

Good Luck

 

[Nene75]

vb5prgrmg:

I should have mentioned that I am buidling the DB in Access 2000. The code you have provided above set to be as a default value of the ResumeNumber field? ResumeSubmissionDate is Day/Month/Year and I just need YearMonth-sequence number. ResumeSubmissionDate is manually entered by the user.

 

[vb5prgrmr]

Ok, so then you are doing your coding in the Access2k VBA environment? Meaning you are creating forms and other objects directly in the database, right? If so then Forum707 may have been a better bet for you question but it still can be answered here by anyone since VB and VBA are so closely related.

Which also means that you are using mostly bound controls... so, lets say you have a textbox or whatever method you are using to collect the date from the user as to where they are to continue from. Using my revised method above lets say you have some history in this database and you have the following records in the TempCnt table
[tt]
Y M Cnt
2003 11 256
2003 12 027
2004 01 003
[/tt]

Now the user wants to continue to enter data that was recieved in the last month of the last year (12/03) and lets say that date is December 15, 2003 (12/15/03) that they want to start from. So the user enters the date (12/15/03 (or whatever format just so long as you know)). The user fills out the rest of the information and then the user clicks a button and you do something like the following...

Get the month from user entry(M = 12)
Get the year from user entry(Y = 2003)
You would then select the correct record from the the table based upon the criteria entered by the user and if the record did not exist you would create it (in this scenario it does), else you edit the correct record as show above.

Now you may want to look through the help files for Access2k on how to open a recordset to get examples of the coding you will need to open the recordset. You may also want to take a look at the update methods of the recordset or the SQL syntax of the same name (update).

Good Luck

 

[Nene75]

vb5prgrmr:

Thanks a lot for taking time and interest on solving this problem. I'll try your suggestions and will post on the VBA forum for further help.

Thanks!

 

[Nene75]

vb5prgrmr:

The problem is solved with the following:

The default value for the ResumeNumber field is set to =resNum().
The resNum Function is as follows:

Function resNum()

If IsNull(Forms!frmApplicantData!fsubResumeSubRevInfo!ResumeSubmissionDate.Value) Then
Forms!frmApplicantData!fsubResumeSubRevInfo!ResumeNumber.Value = " "
Else
Forms!frmApplicantData!fsubResumeSubRevInfo!ResumeNumber.Value = _
Format(Forms!frmApplicantData!fsubResumeSubRevInfo!ResumeSubmissionDate.Value, "yyyymm&quot & "-" & _
Format(Forms!frmApplicantData!fsubResumeSubRevInfo!ResumeID.Value, "000&quot
End If

End Function

vb5prgrmr: Thank you for taking the time and interest on helping.

 

[vb5prgrmr]

Your welcome and I'm glad you got it working.