Multiple Fields Completed from One Lookup Field

[RSS705]

Hello again!

Much simpler question this time, despite how the title is worded

What We Have:
We have a form, which has a field linked to a "Recipients" table through a lookup. The field is bound to the recipient's name, but the lookup displays additional columns to help the user identify the correct recipient in the case of duplicate names.

The Problem:
The "Payment" table's Recipient field only displays the recipient's name. So where duplicates occur, it's impossible to know which recipient is the correct one associated to a payment.

Desired Solution:
I would like to add an additional field to the "Payment" table called "RecipientIDNo". I need a way to auto-populate this field, based on the already existing Recipient field. Is this possible?

If you are wondering why I just don't modify the recipient field on our payment form to be bound to the user ID number, there are two reasons. (1) The field is linked to too many other reports and queries at this point. I believe it could cause an enormous amount of work to modify. And (2) the form likes to display whatever it is bound to. There may be a workaround for this, but I'm not aware of it and don't want the form displaying a number instead of a recipient name.

Preferred solution would use VBA, but I'm open to anything.

Thanks all,
Rob

 

[CJ_London]

Sorry to say this, but you should normalise your data. You say this is too much work, but by not doing it you are creating more work trying to compensate for it.

But to answer your question, does your lookup field include (or can include)the recipientID? If so, then in the after update event for this control you can put the following code:

me.txtRecipientID=me.cbolookup.column(x)

where txtRecipientID is the name of the control on your form for your new RecipientIDNo field, cbolookup is the name of your lookup control for the recipient and x is the column number where the recipientID can be found in the rowsource (first column is column 0)

 

[RSS705]

Worked like a charm, thank you.

I will look into how to normalise the database. It was an inherited system and I'm somewhat new to data management. Appreciate the help.

 

[RSS705]

Follow up question actually!

While the code worked when used applied after the form itself was updated, I also need to give this command when another form closes. How can I specify for a field in another form to take another field's lookup column value within the same form?

I tried something along these lines but am not having much luck. Thanks again.

Forms![PaymentForm]![RecipientIDNumber] = Forms![PaymentForm]![Recipient].Column(1)

 

[CJ_London]

depends on where you code is - as you've written it the code is in one form and the controls are in another - is this the case?

If the other form is closing then it's too late

 

[RSS705]

Hey, thanks for the reply.

Yes, one form will need to activate the code for the other form.

The other form (the form which the code affects) remains open after the form which activates the code closes.

Hope that's clear.

 

[CJ_London]

So where is your code at the moment? - it needs to be in the close event of the form which is closing.

Assuming your naming and column number is correct the code looks OK and there is an assumption that a value has been selected in the Recipient combo. To check this type the following in the immediate window and press return (or use debug.print)

?Forms![PaymentForm]![Recipient].Column(1)

 

[RSS705]

Resolved You kind of nudged me in the right direction.

The issue was that I was expecting the code to apply all records within a given form. This, I now realize does not work, and the code only affects the selected record.

I found a workaround after that became clear that is very database specific, probably wouldn't help sharing it. Thanks for the helps!