How to Relink a record?

[GaryS]

Here's the situation:

Two tables, tA and tB each with autonumber PK's A_Id and
B_Id.

A record in tB is related to exactly one record in tA.

I've placed a foreign key in tB called B_AId, with values
taken from A_Id.

This is working fine, for example in displaying related tA
info in a form for tB data (frmBdata).

Problem: This is a new database obtained by converting an
old one. There are some cases where one of the tB records
needs to be linked to a different tA record.

To frmBdata I added an unbound combo box Combo1 to display
A_Id's. The idea is that I can select the new A_Id in the
combo box and then store it as the new foreign key value.

The After Update event for the Combo box invokes a macro
which does this:

RunSQL UPDATE [tB] SET [tB].[B_AId] = Me!Combo1 WHERE
((([tB].[B_Id])=Me!B_Id));

When this runs, I get the message:
Enter parameter: Me!Combo1

That tells me that my attempted reference to the value in
the combo box isn't correct; what should it be? Isn't Me
the current active form and doesn't Combo1 contain the
selected value After Update?

Thanks.

 

[tina]

try this, if B_AId and B_Id are number data types:

DoCmd.RunSQL "UPDATE [tB] SET [tB].[B_AId] = " & Me!Combo1
& " WHERE >((([tB].[B_Id])= " & Me!B_Id & "))"

try this, if B_AId and B_Id are text data types:

DoCmd.RunSQL "UPDATE [tB] SET [tB].[B_AId] = '" & Me!
Combo1 & "' WHERE >((([tB].[B_Id])= '" & Me!B_Id & "'))"

other than that, i'm not sure why you have a "greater
than" operator right after WHERE in your statement. i
assume you just made a mistake when you typed the code in
your post...

hth