Auto generation from main form to sub form

[Anjani]

Private Sub Specimen_ID_GotFocus()
'Get the next Spec ID
Dim NextSpecID As String
Dim CurrSpecID As String
 
'Get the last 3 characters from the last specimen ID belonging to this project number
CurrSpecID = Nz(Right(DMax("[Specimen ID]", "Spec", "[Project Number] = '" & Me.Project_Number & "'"), 3), 0)
 
'Increment the ID by 1
NextSpecID = Nz(CurrSpecID, 0) + 1
 
'Add in zeros until it's 3 characters long
Do Until Len(NextSpecID) = 3
    NextSpecID = "0" & NextSpecID
Loop
 
'Build the next ID.
NextSpecID = Format(Date, "yy") & "-" & Me.Project_Number & "-" & NextSpecID
 
'Now put it in the form
Me.[Specimen ID] = NextSpecID
 
End Sub
 

Hello, I am using MS access 2003. I have a "Project Number" mentioned on the Main form, which is to be filled manually. I have linked a text box named "Specimen ID" from the subform to the "Project Number".Relation between Project Number and Specimen ID is ONE to MANY. Specimen Id has a format like - "12-201213-001"
In this, 12 stands for the current year, 201213 stands for the project number, and 001 stands for the number of specimen id under that particular project number.
as i click on the next row under specimen id, it automatically generates next specimen id as 12-201213-002. The code used for the same is as stated above.
The problem is that i am not able to modify "Specimen ID" as 12-201213-001A and 12-201213-001B and so on. If i try to do it, it shows a RUN TIME ERROR "13".
I feel that i will have to use the If-Then-Else loop, but i am not able to frame it properly as i am a novice in coding. Please help me with it.

[NeoPa]

I'm not inclined to delete this because you've clearly gone to some effort in trying to provide relevant information.

Unfortunately, it isn't really a question and your code has been posted as a picture.

Please post the code correctly (As text in [ CODE ] tags) and tell us what your question actually is. To be clear, a question is not a question if it is merely in the form "Here is my code - Please fix it for me". We need an actual question.

NB. If your response doesn't provide the question adequately then I expect my response will be to delete the thread, so this is likely to be your only chance to get it right in this thread.

[Anjani]

I hope its clear now...

[zmbd]

Runtime error 13: Type mismatch.
Also: When posting your errors please post the EXACT number and text. There are are a few error messages where this is critical information as the wording or the numbers are slightly different from each other.


I betcha that if you will place the STOP at line 5 in the code block, then [F8] step thru the code that you'll find your error.

At some point in your code you've tried to assign a string or date etc... to a declared numerical or some other misdeclared data-type to assignment. This is either as a variable in your code or as a field in one of your tables.

[NeoPa]

Anjani:
I hope its clear now...

The inclusion of the code is a definite improvement, but you still need to include the actual error message, the line the error occurred on, and the actual question you want answered. Please read my earlier post for why your original post is not considered a question.

PS. Please understand that, as I have already made it clear what is required for this to be considered as a question and that it may well be deleted if such information isn't forthcoming, this will still happen regardless of any other posts from members attempting to be helpful. This is a pre-requisite. This is not a 'nice to have' if other conversations fail you.