Table Design

[zburns]

I have a table tbl_Clients with the standard fields of FirstName, LastName, etc. I also have a field ReferredBy in which I would like to draw from names of existing clients who made the referral, seems simple enough ... not so. Any help on accomplishing this (I would think) common task would be greatly appreciated.

[orange]

How many people/clients can be part of a referral? What exactly is a referral?

[zburns]

Sorry,

A new client may have been referred by only one existing client.
I was hoping to have a combo pulling from existing client names (concanted fname and lname) to populate the bound ReferredBy field in a form.
Thanks for your help

[orange]

If you are going to keep the referredBy value, then you need a spot in the table. Since you only have 1 referrer, you can just add a field to the Client record.

You could use used the ClientId to populate the referredBY
You can populate the comboReferrers on the form using (untested)

Select clientid,ClientFirstName, clientLastname from clientTable where
clientid <> Me.clientID

This should give you all clients except the one you are working with (Client can't refer himself/herself)

Then in the afterupdate of the comboReferrers

Update ClientTable
Set ReferredBy = me.comboReferrers


That should be the basics of it.
Good luck.

[zburns]

Thanks - getting there..

cboReferredBy
Control Source: ReferredBy
Row Source:
SELECT ClientID, FirstName, LastName FROM tblClients WHERE ClientID <> Me.ClientID;

Getting: Enter Parameter Value Me.ClientID

Where have I messed up?

Again, thanks for your help & patience

[orange]

Try
SELECT ClientID, FirstName, LastName FROM tbl_Clients WHERE ClientID <> Me.ClientID;

I;m nit sure what fields are in your table and I had the namewrong.

[zburns]

Almost there...

cboReferredBy After Update event Compile Error
Sub or Function not defined

Update word in Update tblClients is highlighted. tblClients is correct name

[orange]

You have a form with a combo right?
You go to design, combo, right click for properties,events , select code , choose after update.

Busy at the moment
see if this helps
http://www.fmsinc.com/microsoftacces...xes/index.html

[zburns]

Yes, I did all that. Still getting a compile error - sub or Function not defined

[orange]

What is the sub/function name in the error message?
Show us all the code in the cmbo after Update event

[zburns]

I sure messed up. Combo spelled wrong. Working fine now. My apologies. Sorry to take up so much of your time.
Now, how do I close this as being solved??

Thanks again

[orange]

Glad you have it working.

At the top of this page, click on Thread Tools.