Generating an Alpha ID

[KateM]

I would like to have my OrganizationID be the first 8 letters of the
organization. Can this be done automatically?

 

[KARL DEWEY]

Set the default to that string. Add characters as necessary when generating
new records.

 

[Sprinks]

Hi, Kate.

Left([Organization], 8) will return the first 8 characters. But do you want
to include spaces or other non-alphabetical characters? You will also need
to trap for errors should the unique key already exist. You could write code
to scan the organization name, building a real 8-character name:

Dim i As Integer
Dim strOrganization As String
Dim MyEightCharKey As String
Dim intChar As Integer

' Assign the organization name to a variable
strOrganization = Me!Organization

' Initialize the eight character key
MyEightCharKey = ""

' Build 8-character string
For i = 1 To Len(strOrganization)
' Take ASCII code value of the ith character. Letters are 65-90 and
97-122
intChar = Asc(Mid(strOrganization, i, 1))
If ((intChar >= 65 And intChar <= 90) _
Or (intChar >= 97 And intChar <= 122)) Then

' It's a letter; add to string
MyEightCharKey = MyEightCharKey & Chr(intChar)

' If it's 8 characters long, we're done; exit loop
If Len(MyEightCharKey) = 8 Then
Exit For
End If

End If

Next i

' If it's not 8 characters long, there weren't 8 letters. Display
message.
If Len(MyEightCharKey) <> 8 Then
MsgBox MyEightCharKey & " is only " & Len(MyEightCharKey) & "
letters."
Else
Me!YourTextKey = MyEightCharKey
End If

Optionally, you can use UCase to convert to all caps. However, while I used
to use a similar strategy, I think AutoNumber keys are easier to implement.

Hope that helps.
Sprinks

 

[KateM]

I have done little with the expression builder, I see where the default value
is but am unsure what the string would look like. Could you help me with that?

Thanks so much

 

[KateM]

Thank you, yes this is what I would like to do. For Example if the
Organization name is City of Rochester, would it ignore spaces and give me
CITYOFRO? If the organization is Kodak, will it give KODAK?

Thanks again,
Kate
--
Center for Governmental Research
Research/Technology

Hi, Kate.

Left([Organization], 8) will return the first 8 characters. But do you want
to include spaces or other non-alphabetical characters? You will also need
to trap for errors should the unique key already exist. You could write code
to scan the organization name, building a real 8-character name:

Dim i As Integer
Dim strOrganization As String
Dim MyEightCharKey As String
Dim intChar As Integer

' Assign the organization name to a variable
strOrganization = Me!Organization

' Initialize the eight character key
MyEightCharKey = ""

' Build 8-character string
For i = 1 To Len(strOrganization)
' Take ASCII code value of the ith character. Letters are 65-90 and
97-122
intChar = Asc(Mid(strOrganization, i, 1))
If ((intChar >= 65 And intChar <= 90) _
Or (intChar >= 97 And intChar <= 122)) Then

' It's a letter; add to string
MyEightCharKey = MyEightCharKey & Chr(intChar)

' If it's 8 characters long, we're done; exit loop
If Len(MyEightCharKey) = 8 Then
Exit For
End If

End If

Next i

' If it's not 8 characters long, there weren't 8 letters. Display
message.
If Len(MyEightCharKey) <> 8 Then
MsgBox MyEightCharKey & " is only " & Len(MyEightCharKey) & "
letters."
Else
Me!YourTextKey = MyEightCharKey
End If

Optionally, you can use UCase to convert to all caps. However, while I used
to use a similar strategy, I think AutoNumber keys are easier to implement.

Hope that helps.
Sprinks

I would like to have my OrganizationID be the first 8 letters of the
organization. Can this be done automatically?

 

[Sprinks]

Hi, Kate.

The way it's currently written, it will return CityofRo. To make it all
caps, use the UCase function.

MyEightCharKey = UCase (MyEightCharKey)

Sprinks

Thank you, yes this is what I would like to do. For Example if the
Organization name is City of Rochester, would it ignore spaces and give me
CITYOFRO? If the organization is Kodak, will it give KODAK?

Thanks again,
Kate
--
Center for Governmental Research
Research/Technology

Hi, Kate.

Left([Organization], 8) will return the first 8 characters. But do you want
to include spaces or other non-alphabetical characters? You will also need
to trap for errors should the unique key already exist. You could write code
to scan the organization name, building a real 8-character name:

Dim i As Integer
Dim strOrganization As String
Dim MyEightCharKey As String
Dim intChar As Integer

' Assign the organization name to a variable
strOrganization = Me!Organization

' Initialize the eight character key
MyEightCharKey = ""

' Build 8-character string
For i = 1 To Len(strOrganization)
' Take ASCII code value of the ith character. Letters are 65-90 and
97-122
intChar = Asc(Mid(strOrganization, i, 1))
If ((intChar >= 65 And intChar <= 90) _
Or (intChar >= 97 And intChar <= 122)) Then

' It's a letter; add to string
MyEightCharKey = MyEightCharKey & Chr(intChar)

' If it's 8 characters long, we're done; exit loop
If Len(MyEightCharKey) = 8 Then
Exit For
End If

End If

Next i

' If it's not 8 characters long, there weren't 8 letters. Display
message.
If Len(MyEightCharKey) <> 8 Then
MsgBox MyEightCharKey & " is only " & Len(MyEightCharKey) & "
letters."
Else
Me!YourTextKey = MyEightCharKey
End If

Optionally, you can use UCase to convert to all caps. However, while I used
to use a similar strategy, I think AutoNumber keys are easier to implement.

Hope that helps.
Sprinks

I would like to have my OrganizationID be the first 8 letters of the
organization. Can this be done automatically?

 

[KateM]

I am having trouble on where this goes. I have started a module but how does
it affect the table tblorganization? I haven't done anything with code before
and not sure where this goes.

Thanks again for all your help,

Kate
--
Center for Governmental Research
Research/Technology

Hi, Kate.

The way it's currently written, it will return CityofRo. To make it all
caps, use the UCase function.

MyEightCharKey = UCase (MyEightCharKey)

Sprinks

Thank you, yes this is what I would like to do. For Example if the
Organization name is City of Rochester, would it ignore spaces and give me
CITYOFRO? If the organization is Kodak, will it give KODAK?

Thanks again,
Kate
--
Center for Governmental Research
Research/Technology

Hi, Kate.

Left([Organization], 8) will return the first 8 characters. But do you want
to include spaces or other non-alphabetical characters? You will also need
to trap for errors should the unique key already exist. You could write code
to scan the organization name, building a real 8-character name:

Dim i As Integer
Dim strOrganization As String
Dim MyEightCharKey As String
Dim intChar As Integer

' Assign the organization name to a variable
strOrganization = Me!Organization

' Initialize the eight character key
MyEightCharKey = ""

' Build 8-character string
For i = 1 To Len(strOrganization)
' Take ASCII code value of the ith character. Letters are 65-90 and
97-122
intChar = Asc(Mid(strOrganization, i, 1))
If ((intChar >= 65 And intChar <= 90) _
Or (intChar >= 97 And intChar <= 122)) Then

' It's a letter; add to string
MyEightCharKey = MyEightCharKey & Chr(intChar)

' If it's 8 characters long, we're done; exit loop
If Len(MyEightCharKey) = 8 Then
Exit For
End If

End If

Next i

' If it's not 8 characters long, there weren't 8 letters. Display
message.
If Len(MyEightCharKey) <> 8 Then
MsgBox MyEightCharKey & " is only " & Len(MyEightCharKey) & "
letters."
Else
Me!YourTextKey = MyEightCharKey
End If

Optionally, you can use UCase to convert to all caps. However, while I used
to use a similar strategy, I think AutoNumber keys are easier to implement.

Hope that helps.
Sprinks


:

I would like to have my OrganizationID be the first 8 letters of the
organization. Can this be done automatically?

 

[Sprinks]

VBA Code is stored in Modules. In addition to general modules defined on the
Modules tab, each form has its own module (if code has been written).

The code as written was designed to go in the Organization Name's
AfterUpdate event procedure, which will run automatically each time the field
is changed.

Open the form in design view, click on the organization name field, show its
properties with View, Properties or by right-click, Properties. Then click
on the Event tab, click on the AfterUpdate field, and click the ellipsis to
the right, and select Code Builder, if necessary. Access will create the
shell of the procedure for you. Cut and paste the code there, changing
control names as necessary.

Hope that helps.
Sprinks

I am having trouble on where this goes. I have started a module but how does
it affect the table tblorganization? I haven't done anything with code before
and not sure where this goes.

Thanks again for all your help,

Kate
--
Center for Governmental Research
Research/Technology

Hi, Kate.

The way it's currently written, it will return CityofRo. To make it all
caps, use the UCase function.

MyEightCharKey = UCase (MyEightCharKey)

Sprinks

Thank you, yes this is what I would like to do. For Example if the
Organization name is City of Rochester, would it ignore spaces and give me
CITYOFRO? If the organization is Kodak, will it give KODAK?

Thanks again,
Kate
--
Center for Governmental Research
Research/Technology


:

Hi, Kate.

Left([Organization], 8) will return the first 8 characters. But do you want
to include spaces or other non-alphabetical characters? You will also need
to trap for errors should the unique key already exist. You could write code
to scan the organization name, building a real 8-character name:

Dim i As Integer
Dim strOrganization As String
Dim MyEightCharKey As String
Dim intChar As Integer

' Assign the organization name to a variable
strOrganization = Me!Organization

' Initialize the eight character key
MyEightCharKey = ""

' Build 8-character string
For i = 1 To Len(strOrganization)
' Take ASCII code value of the ith character. Letters are 65-90 and
97-122
intChar = Asc(Mid(strOrganization, i, 1))
If ((intChar >= 65 And intChar <= 90) _
Or (intChar >= 97 And intChar <= 122)) Then

' It's a letter; add to string
MyEightCharKey = MyEightCharKey & Chr(intChar)

' If it's 8 characters long, we're done; exit loop
If Len(MyEightCharKey) = 8 Then
Exit For
End If

End If

Next i

' If it's not 8 characters long, there weren't 8 letters. Display
message.
If Len(MyEightCharKey) <> 8 Then
MsgBox MyEightCharKey & " is only " & Len(MyEightCharKey) & "
letters."
Else
Me!YourTextKey = MyEightCharKey
End If

Optionally, you can use UCase to convert to all caps. However, while I used
to use a similar strategy, I think AutoNumber keys are easier to implement.

Hope that helps.
Sprinks


:

I would like to have my OrganizationID be the first 8 letters of the
organization. Can this be done automatically?

 

[KARL DEWEY]

What I posted is set the field default of the table.
Open the table in design view and click on the field.
On the menu click on VIEW - Properties.
Type in your 8 letters as you wish them to be - enclosed in quotes -
"Abcdefgh"
Save and close the table.