Appending Records - Populate Another Field with Autonumber Concat w/Text

[tcb]

In the table that I am appending new records to I have an autonumber
field.

The append to table has another field that is the autonumber
concatenated with text.

Is it possible to append records and concatenate the other field in the
append action?

 

[Jeff Boyce]

I can't be sure, from your description, but it sounds like you are trying to
combine more than one value in a single field (your "autonumber concatenated
with text"). This is not a good idea. Moreover, if the field you are
trying to append to is truly an Access Autonumber field, you DON'T want to
be appending anything to it.

An Access Autonumber field is generated by Access, to serve as a unique row
identifier.

You've described HOW you are trying to do something, but not WHY or WHAT.
If you'll describe a bit more about those, the newsgroup readers may be able
to offer more specific suggestions...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[John Vinson]

Is it possible to append records and concatenate the other field in the
append action?

Yes. It's also a VERY BAD IDEA to store multiple pieces of information
in one field, and it's almost as bad to store information redundantly.

What are you trying to *accomplish*? What's the purpose of this field?
I'm all but sure there's a better way to accomplish your goal!

John W. Vinson[MVP]

 

[tcb]

I agree it's not a good idea to keep multiple info in one field. In
this case the primary key is EMPLID in tbl_Person. It is an 11
character text field, though it is almost always an 8 digit number.
This is assigned by the organization. For other people kept in this
table who are not employees and do not have an organization assigned
EMPLID, I need to assign a unique code that cannot match any existing
EMPLID. To do that I want to concatenate text with the autonumber into
a new field, let's say: NoEMPLID_001. I can't use just the autonumber
because there is a small chance that it could match an exsiting EMPLID.

 

[David F Cox]

The append to table has another field that is the autonumber

concatenated with text.

confusing

is it [add_to_table].[key] = [Input table].[autonumber key] & "My bit of
text" ?
if so you can do it.
You made it sound like the add to table was generating the autonumber an I
do not believe it is possible to concate text to such a field at the moment
of creation.


That number may have been an autonumber once, but now it is just a number

 

[John Vinson]

I agree it's not a good idea to keep multiple info in one field. In
this case the primary key is EMPLID in tbl_Person. It is an 11
character text field, though it is almost always an 8 digit number.
This is assigned by the organization. For other people kept in this
table who are not employees and do not have an organization assigned
EMPLID, I need to assign a unique code that cannot match any existing
EMPLID. To do that I want to concatenate text with the autonumber into
a new field, let's say: NoEMPLID_001. I can't use just the autonumber
because there is a small chance that it could match an exsiting EMPLID.

Don't use an Autonumber for this purpose, then. It's not controllable.

What you could do is maintain your own programmatically assigned ID.
If (as I gather) you can safely use NoEMPLID as a prefix, and be sure
that it will not conflict with any present or future company-assigned
ID's, you could use code like

Dim strID As String
Dim iID As Integer
' get the largest existing non-employee ID
strID = DMax("[EMPLID]", "[tbl_Person]", "[EMPLID] LIKE 'NoEMPLID_*'")
' extract the integer portion
iID = Val(Right(strID, 3))
If iID = 999 Then
MsgBox "Too many non employees, send this one home", vbOKOnly
Me.EMPLID = NULL
Else
Me.EMPLID = "NoEMPLID_" & Format(iID + 1, "000")
End Ef


to assign a new sequential ID. You might want to omit the underscore
and use four digits, just in case.

John W. Vinson[MVP]

 

[tcb]

Sorry for the confusion in my original post. I could have written that
better.

Thanks for your solution.

Following up on the autonumber not being controllable, I am curious.
What do you mean by that?

2) I often use autonumbers as the primary key for lookup tables. Is
that an acceptable practice? I do that so people don't have to create
unique codes for new items, but a solution something like what you
wrote would work.

I agree it's not a good idea to keep multiple info in one field. In
this case the primary key is EMPLID in tbl_Person. It is an 11
character text field, though it is almost always an 8 digit number.
This is assigned by the organization. For other people kept in this
table who are not employees and do not have an organization assigned
EMPLID, I need to assign a unique code that cannot match any existing
EMPLID. To do that I want to concatenate text with the autonumber into
a new field, let's say: NoEMPLID_001. I can't use just the autonumber
because there is a small chance that it could match an exsiting EMPLID.

Don't use an Autonumber for this purpose, then. It's not controllable.

What you could do is maintain your own programmatically assigned ID.
If (as I gather) you can safely use NoEMPLID as a prefix, and be sure
that it will not conflict with any present or future company-assigned
ID's, you could use code like

Dim strID As String
Dim iID As Integer
' get the largest existing non-employee ID
strID = DMax("[EMPLID]", "[tbl_Person]", "[EMPLID] LIKE 'NoEMPLID_*'")
' extract the integer portion
iID = Val(Right(strID, 3))
If iID = 999 Then
MsgBox "Too many non employees, send this one home", vbOKOnly
Me.EMPLID = NULL
Else
Me.EMPLID = "NoEMPLID_" & Format(iID + 1, "000")
End Ef


to assign a new sequential ID. You might want to omit the underscore
and use four digits, just in case.

John W. Vinson[MVP]

 

[John Vinson]

Sorry for the confusion in my original post. I could have written that
better.

Thanks for your solution.

Following up on the autonumber not being controllable, I am curious.
What do you mean by that?

2) I often use autonumbers as the primary key for lookup tables. Is
that an acceptable practice? I do that so people don't have to create
unique codes for new items, but a solution something like what you
wrote would work.

Autonumbers are find IF you use them for their intended purpose:
meaningless unique keys. If people will be looking at them or
assigning meaning to their values, it's better to use some other
solution; autonumbers will always have gaps, and can become random.
They should NOT be used in any situation (such as yours) where you
want control over the value - once the autonumber is assigned, it's
not editable and it's not going to be reused.

John W. Vinson[MVP]