AutoComplete Text Box

[WebGodiva]

I have a unique situation where I have a form with a client name on it. I would like to have another text box on the form which would be completed automatically when someone enters the clients name in the first text box.

The new text box would make a unique identifier from the original entry.

Sample:

Form1

ClientName: (user enters here)
Address1:
Address2:
CCity:
CState:
CZip:
CLTID: (unique id/primary key)

I would like to have CLTID completed automatically with an acronym for the client (i.e., if the client were Keating, have the acronym Keat)...is that possible?

thanks bunches.



"One of the secrets of life is to make stepping stones out of stumbling blocks." Jack Penn

 

[MajP]

Why not just use an autonumber? Sounds like a bad idea to me.

Smith John
Simth Mike
Smithers
Smithson

What is your rule for all the above cases to ensure uniqeness of the PK?

 

[Remou]

Something like this?

Private Sub txtSurname_AfterUpdate()
Dim rs As DAO.Recordset
Dim i As Integer
Dim strID As String

If Me.NewRecord Then
Set rs = CurrentDb.OpenRecordset("tblTable", adOpenDynamic)
i = 1
Do While True
    strID = Left(Me.txtSurname, 4) & Format(i, "00")
    rs.FindFirst "ID='" & strID & "'"
    If rs.NoMatch Then
        Exit Do
    Else
        i = i + 1
    End If
Loop
Me.txtID = strID
End If

End Sub

You will need to do more work if this is a multi-user database. You will also need to decide how large this is likely to get. You may need three numbers tacked on the name.

 

[WebGodiva]

I'll give your solution a try. I'm not very concerned about the names as they are company names and in our industry, there really is no duplication of names. Your solution looks as though it would work so i'm off to give it a try...


thanks bunches for the quick response.




"One of the secrets of life is to make stepping stones out of stumbling blocks." Jack Penn