Our database has a unique field that stores a text data. When the users click on a button, info is grabbed from a bunch of other fields to popuate the text one. Now since it is unique I do a Dcount in the code and append the approprate number onto the end
Now this has all been working fine...until now. Somone deleted one of the records. So here's what happened (if you havn't already guessed)...
Lets say the last record in the text field is BOB18
- Info in gathered, result is BOB
- DCOUNT is preformed, there are 17 records now
- count is incrimented by one
- result is BOB18
Which means that the record cannot be saved b/c it conflicts with the other BOB18.
So looks like I will have to find a way to incriment though each individual record and check the number on the end to find a free didgit. Seems easier said than done though, I've been pulling my hair out on it for the last week. Is there a simple solution I'm just overlooking?
Actually, I would forgo the alpha-numeric key altogether and take better advantage of the rdbms capabilities at your fingertips...
My fingertips are fine, it's the users who are screwing around with it
It's a database listing of PCs around an area. And the code creates a hostname based on the physical building/location. The combination is easy enough to find the exact spot of the PC but complicated enough so the users foramt it wrong. Thats why we have a button to generate it for them.
So how would I go about this,
- Run a query to return all the records based on the prefix, sort it
- Loop through looking to see if theres a number out of place
The guy who designed the naming convention put three didgits at the end incase there was ever 100 PCs in an area. The actual problem im running into is (maybe just been looking at it too long) that I keep getting an error compairing 001 to 010. There must be a trim function to hack off the zero(s) at the start
write a function in vba with an string return variable
pass it the prefix
open a recordset limited to the prefix sorted in ascending order
establish a testvar say testvar=1
while recordset.eof =false
find the first missing number (if testvar<recordset.var then exit loop
It's a database listing of PCs around an area. And the code creates a hostname based on the physical building/location.
You would be far better off creating a seperate table for buildings/locations and referencing it with a foriegn key. Then you can make it idiot proof by providing a combo box with all the locations. That's not to mention the additional flexibility you'll gain in reporting, say for instance you want to produce an inventory by location... with your current setup you'll have to parse out the alpha part of the key and group based on that field with varying degrees of accuracy. With another table it's a matter of a simple join statement.