Sequence Number/Auto Number 1

[Nene75]

Hi Everyone:

I am building a Resume Database in MS Access 2000. 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). So the number should be generated from ResumeSubmissionDate and 3 digit sequence number. ResumeSubmissionDate field is filled by the HR admin.

Can anyone help how should I do this?

Thanks in advance!

 

[Nene75]

I am using the following code but I am getting run-time error 2450 - MS Access can't find the fsubResumeSubRevInfo form refered to in VB ... :

Function resNum()

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

Please help!
Thanks!

 

[RoyVidar]

Hi!

Only adressing the 2450.

Perhaps some reference issue - by the name, it sounds like you're refering to a subform.

If it's from the main/parent form, then perhaps:

[tt]Me!fsubResumeSubRevInfo.Form!ResumeNumber.Value[/tt]

If it's in a module then perhaps:

[tt]Forms!MainFormName!fsubResumeSubRevInfo.Form!ResumeNumber.Value[/tt]

- another issue to check - the subform reference refers to the name of the subform control, which doesn't have to be the same as the subform name viewed in the database window. Check out the properties for the subform (where the link child / master fields are) and check if you use the same name.

Roy-Vidar

 

[Nene75]

Roy-Vidar:

Thanks for the quick reply.

Yes its a subform where the ResumeNumber should be auto generated. I had once changed the subform name that's why it was not finding it. And yes I am using the code in the Module.

Here's the corrected code and it works great:

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

Thanks for helping me find the errors!

 

[Nene75]

Roy-Vidar,

The ResumeNumber generated doesn't get stored/saved into the table. The number is only displayed in the forms. Any idea?

Pleae help!

Thanks in advance!

 

[RoyVidar]

You don't say anything about errormsg, is it just that you haven't bound the control to the corresponding field in the forms recordsource?

Roy-Vidar

 

[Nene75]

Roy-Vidar:

Got it! The ControlSource was unbound.
Thanks a lot - !!!!