Incremental alphanumeric default values and more!

[JRodko]

Hi Guys,

I have attached a sample database with a couple different problems I'm working on.

I have alphanumeric sample ID's which are usually continuous but not always, so when working with a series of them I need to be able to have any of them user-defined and then default to continuous from there again. You can see I have linked the frmProjects to frmSamples using projectID, which is how may data is typically organized (projectID unique primary key in project table)

I can not change the table structure at all as I'm trying to build this all onto an existing database with loads of data. The sample numbers will be unique to the project but may not be unique to the entire database.. but dealing with duplicates is not my issue here. I just need to figure out how to incrementally increase the number portion as per user definition. I am assuming VBA but I'm really not proficient with it yet.

The second portion is that I have written some code in the before update event field that sets the next start value to be the previous end value, so that my numbers can be continuous. I also have it so that the next end value defaults to an equal increment so that I can keep tabbing and have all my depths automatically fill. It works, but it makes me a little nervous because you'll see the calculated Length field doesn't fill until I start typing elsewhere in the record, which makes me think the process could be a little smoother.

Thirdly, when you continue to tab within the subform, you will see it tabs through the master form as well. How do I make it so that for whatever subform I am working in, pressing tab will only get me to the end of the last record within in that subform and will not change the main form or go to a blank entry like if you tab all the way through the Beta03 project data?



Thank you. This is a re-hash of question I have been trying to solve for a while with some new additions. Alphanumeric.zip

[June7]

Generating custom unique identifiers is a common topic. Start with https://www.accessforums.net/showthread.php?t=23329

That is normal behavior for DefaultValue property.

Try setting combobox TabStop property to No and see if that does at least part of what you want. Form also has a Cycle property which I tried changing but doesn't help.

[JRodko]

Thank you for responding to my thread

I have tried to follow your example but I am really struggling with the syntax and just VBA in general.

I have a field [SAMP_NUM] which is three letters and a a six digit number. "SRC123456" which is stored as a string.

I am trying to use the Before Update Event to set SAMP_NUM.DefaultValue = One numerical increment to that string.

So the first SAMP_NUM value is SRC123456 the default that fills to the next record would be SRC123457.

Is per your instructions I'm trying to set a variable to SAMP_NUM, then separate the number portion, increase by 1, and re-concatenate.

I can't even get my temporary variables to work. I am certain this is actually fairly simple but I have no idea of the appropriate syntax.

Any help is greatly appreciated I will also post in the thread thank you.

I have also tried this following a youtube tutorial but It's not working either. Access does not recognize "getDigits" and I have tried using Val() but that won't work either.

***Private Sub SAMP_Num_BeforeUpdate(Cancel As Integer)
Dim strOldID As String
Dim lngCurrentNumber As Long
Dim longNextNumber As Long
Dim strNextNumber As String
Dim strNewID As String
strOldID = SAMP_Num
lngCurrentNumber = getDigits(strOldID)
lngNextNumber = lngCurrentNumber + 1
strNextNumber = String(6 - Len(CStr(lngNextNumber)), "0") & CStr(lngNextNumber)
strNewID = "SRC" & strNextNumber
SAMP_Num.DefaultValue = strNewID
End Sub***

[June7]

Is this a multi-user split db? In my db, I call the function when user clicks a button for 'Login Sample' and then immediately save record, then open the record for user to complete other data. This avoids possibility of multiple users grabbing the same sample number.

The code you posted is not doing what mine does. It doesn't even use DLookup() to pull existing value to build from. Access won't recognize GetDigits unless you build a custom Function called GetDigits. Maybe GetDigits should do a DLookup, but you did not post code for that function.

If you want to provide db for analysis, follow instructions at bottom of my post.

[kd2017]

Here is my solution to this problem: https://www.accessforums.net/showthr...449#post410449
The function 'succ' will auto increment any arbitrary string. I've also attached an example database to that post and how I use it for "auto" fields.

[JRodko]

kd2017

Thank you very much for this. I think I am close but am getting code errors. I have copied the modules over to my database and named them exactly. Here is the code I am running.

My table name is "Geochemistry - Samples", my field in the form is "SAMP_NUM". Note, I do not need to pull any from any other field.. only looking to increment the previous value entered. It does not need to start anywhere. My first error is on
If Me.SAMP_NUM = "(Auto)' Then.. Can you see what I'm doing wrong?

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.SAMP_Num = "(Auto)" Then
Dim PrevValue As Variant
PrevValue = ELookUp("SAMP_NUM", "Geochemistry - Samples", , "")

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 = "SRC"
End If
End If


End Sub

Thank you.

[kd2017]

If you're getting errors make sure to always post the error number and descriptions for us.

Just to double check, you did copy over the Allen Browne module with ELookUp, correct?

This doesn't look right:

PrevValue = ELookUp("SAMP_NUM", "Geochemistry - Samples", , "")

You'll need to sort the field by *something* to get the latest samp_num. Maybe try this:

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

The last parameter where I put "SAMP_NUM Desc" is the sorting parameter.

[JRodko]

Ok I have tried that. I am still getting Run-time error '2447': There is an invalied use of the . (dot) or ! operator or invalid parenthesis.

The code is below. The highlighted row is the first one, I don't know if that matters. I have copied over the modAllenBrowne and modSucc functions identically.

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 = "SRC"
End If
End If


End Sub

[kd2017]

Try enclosing your table name in square brackets:

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

If this still doesn't work then please attach your database.

[JRodko]

OK. Not working. I will attach it.Sampnumbers.zip Attached inline.

You'll notice I'm also carrying over defaults to the next record that is causing accurate results but some strange behavior. If you notice anything in there I'd be delighted to hear your opinion.

[kd2017]

You just need to set the default value of your SAMP_Num textbox to "(Auto)".

[JRodko]

When do this the field just fills as (Auto) regardless of where I put the quotes it seems. That's when I put it in the default value of the SAMP_Num control.

[kd2017]

This is literally the only change I made to your database and it's working for me:


Does this:

[JRodko]

Oh crap. I think there has been some confusion. There should be an incremental number. The SRC is just the prefix.

SRC123456
SRC123457
SRC123458

Is it because it's a character?

[kd2017]

That's ok, just adjust this part as needed:

Else
'No previous records were found so start from the bottom
Me.SAMP_Num = "SRC000001" 'or whatever the first number is supposed to be.
End If