ON CLICK event working on DOUBLE CLICK

[Anjani]

Hello,

My FORM has 3 main components along with other components in it.

1. Project Number (Text Box)
2. Created Date (Text Box)
3. Specimen ID (Text Box)

Specimen ID has prespecified format to follow i.e. "YY-Project Number-001" where "YY" is last two digits of the Year pulled from CREATED DATE. "001" is the increamental number which should be populated ON CLICK in the SPECIMEN ID text box. When i click on the next column of the Specimen ID it should populate as "YY-Project Number-002".

I have written the following code at ON CLICK event of the SPECIMEN ID text box: -

Private Sub Specimen_ID_Click()
'Get the next Spec ID
Dim NextSpecID As String
Dim CurrSpecID As String
 
On Error Resume Next
 
 
'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(Me.Created_Date, "yy") & "-" & Me.Project_Number & "-" & NextSpecID
 
'Now put it in the form
Me.[specimen Id] = NextSpecID
 
 
 
End Sub
 
 

The problem is that when i click once on the Specimen ID column, it displays "--001" and when i click again at the same place, it gives the desired output.

This should happen in 1 click.

Kindly help.

Thanks

[Rabbit]

Most likely this is because the record has not been created yet and there are no values in those other fields you're trying to reference. Instead of referencing those other fields, take what the default values would be and code that in place of the reference.

Also, you probably shouldn't run the code on click. What happens if they accidentally click in there? Instead, you should populate the default value.

[Anjani]

other two values are always produced before clicking on Specimen ID.
I want the format to generate on click and hence the code has been written on the CLICK event.
Please help.

[zmbd]

Please do the following:

Read the first section of: Before Posting (VBA or SQL) Code take care of any compiler errors. Your formatting is fine; however, I just want to make sure the other settings are correct so that we're on the same page. Reading the remainder of the link would also be helpful.

Second, let's see what you are actually getting in your code:
using the posted code block as reference:
insert at line 11 debug.print "--" format(now(),"yyyy-mm-dd hh:nn")
Following the above inserted at line 11 debug.print "CurrSpecID: " & CurrSpecID
insert at line 14 debug.print "NextSpecID: " & NextSpecID
insert at line 19 debug.print "NextSpecID: " & NextSpecID
insert at line 22 debug.print "NextSpecID: " & NextSpecID
insert at following the above insert at line 22 debug.print "---"

Run your code three times via the click event.
From the main Access window press <ctrl><g>
In the debug window, copy and paste the results back here.

I want the format to generate on click and hence the code has been written on the CLICK event.

"I Want..." comes across a tad harsh. Thus, I would caution you on the potentially perceived tone in the way this is worded. It would have been maybe better to have provided a reasoning behind the need/desire to have the onclick event handle this code for you. Rabbit has a great deal of experience with database and software design and as such, the suggestions made by Rabbit should be taken with some consideration. Indeed, you have access to a consultant that could be costing you several hundreds of US-Dollars per hour!

[NeoPa]

Indeed Anjani. You ignore the advice of Rabbit at your peril. There is very little chance that it makes sense to update this value when the control is clicked. The DefaultValue property is always a better bet. When to set it should technically be after the operator has already committed the data to the table, but many people prefer that the operator can see it, so prepare it in advance. This allows for the possibility, in a multi-user environment, that the save can fail due to the re-use of the same ID. So, if you want the operator to see the value then you should set the DefaultValue in the Form_Current() event procedure, but if you really want to do it properly you set it (The value in this case.) in the Form_BeforeUpdate() event procedure.

If you follow Z's advice about debugging your procedure, you'll have some very helpful information about which parts of your code is behaving in a way you're not anticipating, which will allow you to focus on that particular area.