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.EMPLOYEES 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.EMPLOYEE_GUID, tblEmployees.EMPLOYEES
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.Counselor = tblEmployees.Employees
SET tblClients.CouncelorID = [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