Flummuxed by the Switch Function

[Tal]

I am utterly confused. I only speak rudimentary VBA, so please be gentle, but
here it goes.
For the purposes of this discussion, I have 3 tables:

tblDonations: All the information about a donation (lots o' lookups)
tblClients: Donor and Recipients information (one to many rel with
tblDonorAddresses)
tblNameLookup: keyNameLookup with values 1 to 7

I am creating an intake form for donations, so the recordsource is
tblDonations
In this table I have a field "keyDonor" that looks up the "keyClient" field
in the tblClients.
I also have a "keyReceiptTo" field that looks up values in the tblNameLookup.
I need to write the code that will return something like the following in a
cboReceiptTo combo box on my form where the Control Source is the lookup
field in tblDonations to tblNameLookup where

If keyNameLookup = 1 then return [tblClients].[FirstName] & " " &
[tblClients].[LastName]
If keyNameLookup = 2 then return [tblClients].[SpouseFirstName] & " " &
[tblClients].[LastName]
etc...
Where [tblClients].[keyClient] = Me.keyDonor (which derives it's value from
a combo box that lists all Clients.

I was told to use the Switch Function but I have absolutely no idea how to
do so.

Help desperately needed and much appreciated.
Many thanks,

Talia

 

[Allen Browne]

To use the Switch function, you would have a monster expression in the
Control Source of a text box on your form.

It would be something like this (all on one line):
=Switch([keyNamLookup] = 1,
[tblClients].[FirstName] & " " & [tblClients].[LastName],
[keyNamLookup] = 2,
[tblClients].[SpouseFirstName] & " " & [tblClients].[LastName],
[key...

Unless this is some kind of training exercise, I'm not sure I would do it
this way. IME, you cannot categorize the way people should be addressed into
a neat list of key name lookups like that. In some cases, people want to use
their titles (or not, or combine titles such as Rev Dr), to use first name,
all names, initials, suffixes, combined names, with same surnames or
different surnames, or ... And then you have cultures where the family name
should be listed first. Others are known by a middle name, and ...

There are just too many combinations to validly categorise like this. IMHO,
you would be better to store an Addressee field in your client table that
holds the name as it should be used when addressing that client. You can
have a go at automatically assigning it when the names are entered, but the
user can overwrite it with whatever the valid name should be for that
particluar person.

 

[Tal]

Hi Allen,

Thanks so much for you help.
Actually our donors have a tendency to want their receipts to be addressed
in all sorts of wonderful but limited ways. I actually solved my issue by
defining the rowsource of the combo box as a value list and building an
expression into the LostFocus event of the original client combo. Works like
a charm because it limits the list to only those possibilities that are valid
for the particular donor.
I mention this because I think it might have been your code on someone
else's posting that did the trick.

So thank you!!!

Cheers,
Talia

To use the Switch function, you would have a monster expression in the
Control Source of a text box on your form.

It would be something like this (all on one line):
=Switch([keyNamLookup] = 1,
[tblClients].[FirstName] & " " & [tblClients].[LastName],
[keyNamLookup] = 2,
[tblClients].[SpouseFirstName] & " " & [tblClients].[LastName],
[key...

Unless this is some kind of training exercise, I'm not sure I would do it
this way. IME, you cannot categorize the way people should be addressed into
a neat list of key name lookups like that. In some cases, people want to use
their titles (or not, or combine titles such as Rev Dr), to use first name,
all names, initials, suffixes, combined names, with same surnames or
different surnames, or ... And then you have cultures where the family name
should be listed first. Others are known by a middle name, and ...

There are just too many combinations to validly categorise like this. IMHO,
you would be better to store an Addressee field in your client table that
holds the name as it should be used when addressing that client. You can
have a go at automatically assigning it when the names are entered, but the
user can overwrite it with whatever the valid name should be for that
particluar person.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am utterly confused. I only speak rudimentary VBA, so please be gentle,
but
here it goes.
For the purposes of this discussion, I have 3 tables:

tblDonations: All the information about a donation (lots o' lookups)
tblClients: Donor and Recipients information (one to many rel with
tblDonorAddresses)
tblNameLookup: keyNameLookup with values 1 to 7

I am creating an intake form for donations, so the recordsource is
tblDonations
In this table I have a field "keyDonor" that looks up the "keyClient"
field
in the tblClients.
I also have a "keyReceiptTo" field that looks up values in the
tblNameLookup.
I need to write the code that will return something like the following in
a
cboReceiptTo combo box on my form where the Control Source is the lookup
field in tblDonations to tblNameLookup where

If keyNameLookup = 1 then return [tblClients].[FirstName] & " " &
[tblClients].[LastName]
If keyNameLookup = 2 then return [tblClients].[SpouseFirstName] & " " &
[tblClients].[LastName]
etc...
Where [tblClients].[keyClient] = Me.keyDonor (which derives it's value
from
a combo box that lists all Clients.

I was told to use the Switch Function but I have absolutely no idea how to
do so.

Help desperately needed and much appreciated.
Many thanks,

Talia