CHANGING KEY FIELDS IN RELATED TABLES

[Guest]

Hi Guys,

I have two tables in a one-to-many relationship: Billing is related to DAR
through field [Test#] so that each DAR record can have many Billing records.
The problem is that both Billing and DAR do not have their primary fields set
to autonumber. The [Test#] field is a text field.

I want to introduce an autonumber field and then relate the two tables
through this new field. Creating the new autonumber field RequestID was easy,
and after sorting the records the way I wanted, I copied them to the new
template table of DAR which I called DAR1. For template table Billing1, I
created its new autonumber field BillingID, and a RequestID field set to long
Integer, which I intend to use to relate the two tables later. All records in
Billing also copied to Billing1 successfully.

I got stuck when I tried to retrieve the data in RequestID field of DAR1
into Billing1. I used an Update query to set RequestID in Billing1 to:

DLookUp("[RequestID]","DAR1","[Test#]=" & [Test#])

When I run the query, I get this error message: "...didn't update 6145
fields due to a type conversion failure, 0 record(s) due to key violations; 0
record(s) due to lock violations, and 0 record(s) due to validation rule
violation." What should I do?

Is there an easier way to accomplish my task? The DAR table too has over
5000 records.

 

[Guest]

As the Test# columns are of text data type the value needs to be wrapped in
quotes characters in the criterion of the DLookup function:

DLookUp("[RequestID]","DAR1","[Test#]=""" & [Test#] & """")

More efficiently, however you can join the tables:

UPDATE Billing1 INNER JOIN Dar1
ON Billing1.[Test#] = Dar1.[Test#]
SET Billing1.RequestID = Dar1.RequestID;

Ken Sheridan
Stafford, England