Changing Data Type - Text to ReplicationID

[Blake D.]

I have two tables (tblClients and tblEmployees). Field [Counselor] in
tblClient uses the PK of tblEmployee as a lookup. Currently, the PK is

a text field containing the person's name.
I want to add a ReplicationID field, and make that the PK. I then need

to change the Counselor field in tblClient to store the ReplicationID
but show the Name, and not lose any data in the process.
Can anyone provide some help on how to do this, please? Boss wants
this project done by week's end, and this is my biggest stumbling
block.

[Bri]

Blake D. wrote:

I have two tables (tblClients and tblEmployees). Field [Counselor] in
tblClient uses the PK of tblEmployee as a lookup. Currently, the PK is

a text field containing the person's name.
I want to add a ReplicationID field, and make that the PK. I then need

to change the Counselor field in tblClient to store the ReplicationID
but show the Name, and not lose any data in the process.
Can anyone provide some help on how to do this, please? Boss wants
this project done by week's end, and this is my biggest stumbling
block.

Is there a reason you aren't using an Autonumber field this new PK? You
shouldn't be using any of the special fields/datatypes that Replication
uses for your own use.

If you are using the Lookup feature of the Datasheet view of a Table,
they you would just need to modify the Combo settings. ColmnWidths is
the one that will hide the PK field:

Rowsource = SELECT myAutoID, EmployeeName FROM tblEmployees
BoundColumn = 1
ColumnCOunt = 2
ColumnWidths = 0;2
ListWidth = 2

You would be better off building a form rather than using a direct table
view in which case you would have a Combobox on the form that would do
the lookup and would be setup the same way. In fact, if you use the
wizard to create the combo, it will volunteer to hide the PK if you
select it and at least one other column.

--
Bri

[Blake D.]

I am using an Autonumber with type RepID. I should have clarified
that, sorry.

The tblClient is designed with the Counselor field being a ComboBox as
follows:
RowSource= SELECT tblEmployees.EM PLOYEES FROM tblEmployees WHERE
(((tblEmployees .COUNSELOR)=-1));
Bound Col = 1
ColumnCount = 1

I tried what you suggested, but the problem is the 2K records already
in place have text in the Counselor field (the counslor's name) instead
of the GUID being stored and just displaying the GUID (0") and Name
(2").

What I want to achieve is the Counselor Field in tblClients to be a
ComboBox as follows:
RowSource = SELECT tblEmployees.EM PLOYEE_GUID, tblEmployees.EM PLOYEES
FROM tblEmployees WHERE (((tblEmployees .COUNSELOR)=-1));
Bound Col = 1
ColumnCount = 2
Heads = 0
ColumnWidths = 0;2
ListWidth = Auto

[David W. Fenton]

"Blake D." <Bl****@Choctaw Recovery.com> wrote in
news:11******** **************@ g43g2000cwa.goo glegroups.com:

I want to add a ReplicationID field, and make that the PK.

Have you considered all the problems Access has with handling GUIDs?

See:

http://trigeminal.com/usenet/usenet011.asp?1033

I can't think of a proper use for them in any Access application of
any kind.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Bri]

Blake D. wrote:

I am using an Autonumber with type RepID. I should have clarified
that, sorry.
Use either Long Integer with either Increment or Random. The RepID type
is for use by Replication. It is overkill to use it in a non-replicated
app as it is 16 bytes as compared to 4 bytes for a Long. Also, others in
this forem have indicated that there are other problems associated with
doing this.
The tblClient is designed with the Counselor field being a ComboBox as
follows:
RowSource= SELECT tblEmployees.EM PLOYEES FROM tblEmployees WHERE
(((tblEmployees .COUNSELOR)=-1));
Bound Col = 1
ColumnCount = 1

I tried what you suggested, but the problem is the 2K records already
in place have text in the Counselor field (the counslor's name) instead
of the GUID being stored and just displaying the GUID (0") and Name
(2").

What I want to achieve is the Counselor Field in tblClients to be a
ComboBox as follows:
RowSource = SELECT tblEmployees.EM PLOYEE_GUID, tblEmployees.EM PLOYEES
FROM tblEmployees WHERE (((tblEmployees .COUNSELOR)=-1));
Bound Col = 1
ColumnCount = 2
Heads = 0
ColumnWidths = 0;2
ListWidth = Auto

Here is a very good reason NOT to use the Replication ID type. You
cannot use it for any other field. First change your tblEmployees PK to
a Long Integer (as per my previous example I will assume it is named
myAutoID). Then create a NEW field in tblClients:
Name: CounselorID
Type: Long Integer
Default: 'remove the zero default
Indexed: Yes (Duplicates OK)

Then you need a query to populate this new field:
UPDATE tblClients INNER JOIN tblEmployees
ON tblClients.Coun selor = tblEmployees.Em ployees
SET tblClients.Coun celorID = [tblEmployees].[myAutoID]

Then if all went well you can delete the original Councelor field.

Then setup your Lookup (preferably in a Form not in the Table, I don't
know why MS even allows you to do it on a Table, it will cause you lots
of problems down the road).

--
Bri