VBA Code to Autofill Subform

[dgutsche]

Hi there!

I asked a first version of this question a few days back. I'm now at the point of trying to follow the original suggestion to use the AfterUpdateEvent to run some code to do this. Now I've been trying to find an example of someone using VBA to do it (I do it all the time in excel so I'm okay learning from other people's code). What I want to have happen is every time a new 'Age' is added on the subform at the bottom, the 'Cylinder Name' will auto-populate with two strings appended. The first string is the field 'No.' from above (red arrow) which will be a 4-5 digit number (not a combo box - we enter forms by writing in paperwork values, they are unique as we have pre-printed numbers on all the lab work forms). The second string is just to be based on position in the current subform, just in a letter format. What I am on the hunt for is code to:

- auto-fill a subform row by row


- possibly to append two fields together (if I end up having to create an auto-fill field of letters)
- code for storing a value as to the current row of a not yet submitted subform (I can develop code to make the letters if I have that)

[June7]

Generating custom unique identifier is common topic. Review https://www.accessforums.net/program...ple-46469.html

That thread has example code as well as link to the db using it. This is the db I alluded to in another of your threads.

Capturing the Ticket number from main form is simple but assigning the cylinder sequence is the tricky part, and letter is trickier than number. Incrementing a letter means first converting to ASCII code, adding 1, then converting back to letter.

Me.CylinderName = Parent.No & Chr(Asc(Nz(Right(DMax("[Cylinder Name]", "tablename", "Left([Cylinder Name],4)=" & Parent.No),1),"A"))+1)

[dgutsche]

Wow this is very useful. I'll be needing to look up some of those function calls. I can see similarity to what I would have just done in excel/vba anyway. I really appreciate all the insight June7. This will probably take some time to digest.

Cheers!

[June7]

Ooops, little goof in my expression logic.

Dim strSeq As Variant
strSeq = Right(DMax("[Cylinder Name]", "tablename", "Left([Cylinder Name],4)=" & Parent.No),1)
Me.CylinderName = Parent.No & Chr(Asc(UCase(IIf(strSeq LIKE "[a-Z]", strSeq, "@"))) + 1)

The No part really needs to always be the same length, even if placeholder zeros are needed to assure this: 01006, 11023. Otherwise, 11023B will sort before 9201D.

[dgutsche]

Oh wow, would have never caught on to that for a while. Ha ha. Thanks again!

[June7]

Another ooops. My code assumes the No part is 4 digits. Try this instead:

strSeq = Right(DMax("[Cylinder Name]", "tablename", "Val(Nz([Cylinder Name],0))=" & Val(Parent.No)),1)

[dgutsche]

Well unfortunately I was quick to mark this thread as solved when it actually wasn't. It worked when I had dummy data but I only had one records in the large table, and lots of related records in the secondary table. Now that I have real data going in I'm finding the code is no longer working. I will attach some details here.

[dgutsche]

Alright so this code worked brilliantly and I haven't adjusted it since, though I will expand it to allow for 5 numerical digits just to make the database last for a greater period of time.

Since then the new data is creating an error type 3464 (mismatched datatypes).

Attachment 18053

The code given above by June7 is intact, but there seems to be something giving it grief on the first line:

Attachment 18054

I'm wondering if it has something to do with the sorting of the cylinder names in the Cylinders table... I haven't reformatted the values for the tickets. I had considered formatting to get the digits the same first (so ticket 96 would be 00096 and 10555 would still be 10555) though I don't believe that is the causation of the error here...

[June7]

Parameters for text fields require apostrophe delimiters. That's why my revised suggested code converted with Val() function.

You might take another look at the last suggestion.

[dgutsche]

Oh wow, never mind. I forgot I was working off a back up.... ZZZ

June7's code is flawless!

[dgutsche]

Ok I lied. Not quite flawless. The counter isn't updating now. I reverted to an old backup so it doesn't have my changes to the code in here.



Current version of the code:


Private Sub Age_AfterUpdate()

Dim strSeq As Variant
strSeq = Right(DMax("[CylinderName]", "Cylinders", "Val(Nz([Cylinder Name],0))=" & Val(Parent.Number)), 1)
Me.[CylinderName] = Parent.Number & Chr(Asc(UCase(IIf(strSeq Like "[a-Z]", strSeq, "@"))) + 1)

End Sub

[dgutsche]

Online I'm seeing A-Z or a-z used more commonly. Is this about the order of characters in ASCII? In that order A and Z come before a and z... is that the issue perhaps?

[June7]

Have you step debugged? Does strSeq get set correctly?

The expressions work in my testing. Doesn't matter if strSeq is "a" or "A", I still get "B" because of the UCase.

[dgutsche]

Hmmmm well I know how to do breaks (similar to C++ but I don't know how to interrupt before the local variables are cleared. Each time the code is used to do a cylinder name it runs the two lines and disappears, always beginning with the string as a null. I don't know how to dice it up more than that. What is the name of 'step debug' for microsoft? I'm not sure what to google ha ha.

[June7]

You have [CylinderName] and [Cylinder Name] - one with space and one without - which is correct?

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.