Generate next letter when certain event is true in MS Access

[th3spankst3r]

I am writing a database, which I am very new to, and have come across a problem. Maybe someone can find a way around the problem or solve the problem directly. The table that I'm working on will manage equipment repair orders (EROs). EROs are assigned a 3 letter prefix and a 2 number suffix. e.g. HDD03. When HDD99 is the last ERO used, the next ERO would be HDE00 and so on until HDZ, after which, it would use HDA00 or the first unopened equipment repair order number (they would close when complete). I made seperate fields for the prefix and suffix. However, I need some sort of code or work-around to automatically increase the prefix when the last suffix used equals 99. Also, I haven't looked into it yet because I have been stuck on this issue, however I also would need the code to increase the suffix, without it being an autonumber field, because I already have one of those. Thanks a lot.

 

[Golom]

Ahhh ... reality. What a concept!

You need to assess what is realistic FOR YOU. You can (probably) get this code version running reasonably quickly and, if changes to the "rules" are rare, you'll be an ex-marine before modifications are needed.

Meaning no disrespect but your coding/database skills appear to be at the beginner level (as everyone's were at some time.) Given that, you should probably go with what seems the clearest path to you. Now is probably not the time to start up a significant learning curve just to handle this one issue.

 

[th3spankst3r]

I agree, and no disrespect taken. It's been years since I have written ANY code at all, and have never worked with VB in any sense. I'm doing this mainly as a learning tool, to remind myself what programming is all about. However, I would have failed myself if I spent the next time learning and had nothing to show for it. Therefore, I'd like to have a functional database before I leave.

 

[th3spankst3r]

The problem that I'm having with this code is that it cannot be ran independently because it depends on values entered by the user on a form. The code looks good to me, however, when I start inputting data into the form and then run the function, it gives me errors. This makes it difficult for me to debug the code. I cannot exactly describe the problem on a forum anymore either, simply because I would have to describe my entire database plan for anybody to understand what I am trying to do. Perhaps this is why nobody has written a database for our maintenance shop yet.