Search results

I found the problem is Beetle's code. I apologize for calling it Lugbolt's code in the earlier email. The issue was that strLetter = Me![LastName] had to be = Left(![LastName], 1) Everything seems to be working correctly now. I can't thank you all enough for helping with this issue. It has...

Thanks, but I can't get the code to work. It runs, but it doesn't update the MemID field on the form. Noted the typo in set mydb=currendb and changed it.

Lugbolt's code worked, but instead of taking the first letter of the last name for the new ID it is taking the Last name and adding 1. So in my database I have L345 and Lee1. That is why when I typed Lawrence for the last name it gave me an error that I was trying to add to much data to a field...

I made sure I did not use the MemID as PK. It is indexed as unique, but I have a separate hidden autonumber field for the PK. When I get the new database put together and tested with fake data, I plan to append the data from the old database into the new tables.

Hm, when I run Beetle's code I get the following error. (I should have stated up front the ID field is set for 6 characters, but nothing in the code leads me to think the data is going to be too large for the field.) I only have two existing record in the tblMembers with MemID of L345 and C23...

Thank you. I will try the code this afternoon. At least I can see that I was in the right ballpark with what I was attempting to do. Given that I am stuck with the 3000+ IDs that are already assigned I am not going to be able to go with Lagbolt's simple but elegant solution.

Thanks. I love the idea of storing the numbers separate from the letters, but my problem is what do I do with the 3000+ IDs that are already in the database? Even if I start with a fresh number plan, I still need to start each one from the point the old set of ID numbers end. Be assured I am not...

That is correct. Each letter/number combination increments separately. What I had tried was: Private Sub LastName_BeforeUpdate() Dim strLeftLetter As String strLeftLetter = Left([LastName], 1) Dim intbiggestsofar As Integer intbiggestsofar = 0 Dim db As DAO.Database Dim rstMembers As...

I am trying to update a database for non-profit organization. Their membership numbers are a combination of the first letter of the surname plus an incremented number (i.e. L345). Currently, the users figure out the new number manually. I need help automating the new number on a new member form...