Incremental alphanumeric default values and more!

[JRodko]

I am still having (Auto) show up when I enter a new record. Would my error be somewhere else? Here is my code.

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.SAMP_Num = "(Auto)" Then
Dim PrevValue As Variant


PrevValue = ELookUp("SAMP_NUM", "[Geochemistry - Samples]", , "SAMP_NUM Desc")

If Not IsNull(PrevValue) Then
'Increment the value from last time
Me.SAMP_Num = Succ(PrevValue)
Else
'No previous records were found so start from the bottom
Me.SAMP_Num = "SRC000000"
End If
End If


End Sub

[kd2017]

Reattach your db please

[JRodko]

Here we go. I very much appreciate you taking the time to help me with this. I have been messing about for over an hour with no progress.

[kd2017]

The database you posted works for me as is!


So it display's "auto" until a new record is actually created, then it will generate a samp_num. "auto" is just a place holder, it's supposed to say that, it indicates to the user that if left alone a number will be generated automatically. A place holder much in the same way the AutoNumber primary key field would say "(New)"...

[JRodko]

EDIT: OK. I figured it out! It's working for me now, but it won't fill until I start typing in the comment box.

Is there a way to make it full automated, so that I can just keep tabbing through? The same thing happens with my Length field.

Thank you so much!!!

EDIT2: On second thought. I think this actually works better. Thank you again!

I don't know know to mark this as solved but it's solved.

Solved
Solved.
Solved!

[kd2017]

I don't follow, it is fully automated. It'll fill as soon as you type in any of the fields or start a new record. Do you mean you need it to edit existing records?

BTW it *might* be better for your situation locate the previous samp_num by latest modified date rather than highest samp_num. Like so:

PrevValue = ELookUp("SAMP_NUM", "[Geochemistry - Samples]", , "datModified Desc")

[June7]

The sample number will not generate until you enter some value in another control. The only control that does not have a default value is Comment. As soon as something is typed in that box or any of the others to overwrite default value, the sample number is created. Unfortunately, because this is first record the number is SRC000000. My original code deals with this circumstance.