Combination of text and incrementing number? 1

[Ravensleach]

Hi

I have a client who insists on the ID being a unique combination of letters and an incrementing number (I know this is a bad idea …)

The string is: The person’s first initial, followed by the first two letters of their surname, followed by an autonumber which restarts for each combination of letters.

So John Brown is JBR1
Jack Brittain is JBR2
Mary Smith is MSM1
Mark Smeaton is MSM2
Mike Smythe is MSM3
Etc.

I’ve some code which works in Access 97; it’s included in the form. It won’t import into Access 2000. I can post it here if that’s useful.

Can anyone help? Thanks.

 

[GingerR]

what's the code from Access 97? Did it work properly in Access 97 the way the client wants it? Why won't it import into Access 2000? Any error messages? Do you mean it won't WORK in Access 2000?

Thanks--

 

[Ravensleach]

It doesn't work. The first error message is Compile error: User-defined type not defined and it highlights
MyDB As DAO.Database

I think the operative bit of code is:


Private Function CalcNextID(strIDRoot As String)
Rem Amended 24.9.1997. Now fills in gaps in IDs
Dim strSQLString As String
Dim strCurID As String
Dim strPrevID As String
Dim iCurID As Integer
Dim iPrevID As Integer
Dim iCounter As Integer
Dim MyDB As DAO.Database
Dim MatchingIDs As DAO.Recordset
Dim Searching As Integer

On Error GoTo Err_CalcNextID
glrEnterProcedure "CalcNextID"

Set MyDB = DBEngine.Workspaces(0).Databases(0)
strSQLString = "SELECT DISTINCTROW tblMembers.MemberID FROM tblMembers WHERE ((tblMembers.MemberID Like '" & strIDRoot & "*')) ORDER BY tblMembers.MemberID;"
Set MatchingIDs = MyDB.OpenRecordset(strSQLString, DB_OPEN_SNAPSHOT, DB_FORWARDONLY)

iCurID = 1
iPrevID = 0
Searching = True

While (Not MatchingIDs.EOF) And Searching
strCurID = MatchingIDs.Fields("MemberID")
iCurID = Val(Right$(strCurID, Len(strCurID) - Len(strIDRoot)))
If (iCurID - iPrevID) <= 0 Then 'ie, iCurID = 0
Searching = False
ElseIf (iCurID - iPrevID) > 1 Then
Searching = False
Else 'iCurID - iPrevID = 1
strPrevID = strCurID
iPrevID = Val(Right$(strPrevID, Len(strPrevID) - Len(strIDRoot)))
MatchingIDs.MoveNext
End If
Wend

MatchingIDs.Close

CalcNextID = strIDRoot & Trim$(Str(iPrevID + 1))

Exit_CalcNextID:
glrExitProcedure "CalcNextID"
Exit Function

Err_CalcNextID:
Select Case Err
Case Else
glrErrorOutput Err, Error$
End Select
Resume Exit_CalcNextID

End Function 'CalcNextID

Private Sub txtMemberID_AfterUpdate()
On Error GoTo Err_txtMemberID_AfterUpdate
glrEnterProcedure "txtMemberID_AfterUpdate"

Dim varIDFound As Variant

varIDFound = DLookup("[MemberID]", "tblMembers", "[MemberID] = txtMemberID")
If Not IsNull(varIDFound) Then
MsgBox "Member ID " & txtMemberID & " is already in use."

'Cancel record entry.
DoCmd.DoMenuItem A_FORMBAR, A_EDITMENU, 1, , A_MENU_VER20

Else
'
End If

Exit_txtMemberID_AfterUpdate:
glrExitProcedure "txtMemberID_AfterUpdate"
Exit Sub

Err_txtMemberID_AfterUpdate:
Select Case Err
Case Else
glrErrorOutput Err, Error$
End Select
Resume Exit_txtMemberID_AfterUpdate
End Sub 'txtMemberID_AfterUpdate

thanks for looking at this

 

[RoyVidar]

Set a reference to the Microsoft DAO 3.# Object Library (in VBE - Tools | References)

Roy-Vidar

 

[Ravensleach]

Thank you very much

 

[Ravensleach]

Hi

Me again. I tried that and at first it seemed to work but then it didn't (same error message and code highlighted).

I've looked at the references for the old Access97 form and they are: Visual Basic for Applications; Microsoft Access 8.0 Object Library; utility; Microsoft DAO 3.51 Object Library.

The selected references in the new Access2000 form are:
Visual Basic for Applications; Microsoft Access 9.0 Object Library; OLE Automation.

As I'm sure you know it won't let me unselect the 9.0 Object Library and when I try to select the 8.0 I get an error message "Name conflicts with existing module, project, or object library". Is there anything I can do to resolve the conflict?
Thanks in advance
Ravensleach

 

[RoyVidar]

Access 2000 is version 9.0, 97 is 8.0, you can't change that. Again, you need (also) a reference to the Microsoft DAO Object library for that code to work. In A97, it is usually version 3.51, in higher versions, it is version 3.6.

Roy-Vidar

 

[GingerR]

Yes make sure you have a reference to DAO!