Storing multiple fields in a table from a single form combo lookup

Hi,
I'm wondering if it's possible to store multiple fields of data from a
single lookup combo box on a data input form.

I'm familiar with how to populate form fields with 'reference/display' data
from a lookup but my issue is that I need the multiple look up data stored
rather than just 'displayed' for potential individual record adjustments.

I am creating an event registration database and need to track fees payable
and catering for each delegate for a multi day event involving several types
of registration (full, day only etc), involving up to 9 catering occassions
(tick boxes for a.m. refreshments, lunch and p.m. refrehsments for each day).
I managed to create a lookup that displayed the default values for fees and
catering attendance according to the parameters of the particular
registration type however couldn't then make adjustments to individual
records eg delegate 'A' advises they can't attend lunch on day 2 so I need to
make an adjustment to their registration record accordingly in order to
ensure my catering numbers report remains accurate.

Any help/suggestions gratefully accepted as I've been searching
forums/advice sites for hours and all seem to lead back to the same solution
- that there should be no need to 'store' data that is already contained in
other tables but in this case I can't think of a way around it unless I
create an 'Adjustments' table that stores any changes to the 'default' data
and my 'Amount Payable' and 'Catering Numbers' reports calculate the
difference between the default and adjustments but that seems to be the long
way around....

On Sun, 31 May 2009 18:42:01 -0700, EdHead
wrote:

Hi,
I'm wondering if it's possible to store multiple fields of data from a
single lookup combo box on a data input form.

Yes, using the AfterUpdate event of the combo box. But it would ordinarily be
A Very Bad Idea to do so. See below.

I'm familiar with how to populate form fields with 'reference/display' data
from a lookup but my issue is that I need the multiple look up data stored
rather than just 'displayed' for potential individual record adjustments.

I am creating an event registration database and need to track fees payable
and catering for each delegate for a multi day event involving several types
of registration (full, day only etc), involving up to 9 catering occassions
(tick boxes for a.m. refreshments, lunch and p.m. refrehsments for each day).

And if you add another occasion you'll, what, redesign your table, rewrite all
your queries, revise all your forms, redo all your reports!? OUCH! If you have
a one (event) to many (catering occasions) relationship you would do much
better to model it as a one to many relationship, with each catering occasion
being a new *RECORD* in a table related one to many to the Events table.

I managed to create a lookup that displayed the default values for fees and
catering attendance according to the parameters of the particular
registration type however couldn't then make adjustments to individual
records eg delegate 'A' advises they can't attend lunch on day 2 so I need to
make an adjustment to their registration record accordingly in order to
ensure my catering numbers report remains accurate.

Any help/suggestions gratefully accepted as I've been searching
forums/advice sites for hours and all seem to lead back to the same solution
- that there should be no need to 'store' data that is already contained in
other tables but in this case I can't think of a way around it unless I
create an 'Adjustments' table that stores any changes to the 'default' data
and my 'Amount Payable' and 'Catering Numbers' reports calculate the
difference between the default and adjustments but that seems to be the long
way around....

If you use a table with foreign keys to the event and to the attendee tables,
and add a new record for each catering event, then you can easily specify
vegetarian, omit a record, add an extra fee, or whatever. If you have each
event as a field in the table, it's a lot less flexible and adaptable.
--

John W. Vinson [MVP]

Thanks, John.
I certainly take your point about a catering table with multiple catering
records for each delegate (i guess I kind of lost my way off the 'relational'
track because I was a little pre-occupied trying to devise a way to automate
the data input for the catering events). If I link up a catering table as
suggested, is there any way you could suggest for me to achieve some degree
of auto-population of catering records applicable to the type of
registration? Registration code is currently determined by concatenation of
3 registration categories.

Thanks for your advice.



Currently m

"John W. Vinson" wrote:

On Sun, 31 May 2009 18:42:01 -0700, EdHead
wrote:

Hi,
I'm wondering if it's possible to store multiple fields of data from a
single lookup combo box on a data input form.

Yes, using the AfterUpdate event of the combo box. But it would ordinarily be
A Very Bad Idea to do so. See below.

I'm familiar with how to populate form fields with 'reference/display' data
from a lookup but my issue is that I need the multiple look up data stored
rather than just 'displayed' for potential individual record adjustments.

I am creating an event registration database and need to track fees payable
and catering for each delegate for a multi day event involving several types
of registration (full, day only etc), involving up to 9 catering occassions
(tick boxes for a.m. refreshments, lunch and p.m. refrehsments for each day).

And if you add another occasion you'll, what, redesign your table, rewrite all
your queries, revise all your forms, redo all your reports!? OUCH! If you have
a one (event) to many (catering occasions) relationship you would do much
better to model it as a one to many relationship, with each catering occasion
being a new *RECORD* in a table related one to many to the Events table.

I managed to create a lookup that displayed the default values for fees and
catering attendance according to the parameters of the particular
registration type however couldn't then make adjustments to individual
records eg delegate 'A' advises they can't attend lunch on day 2 so I need to
make an adjustment to their registration record accordingly in order to
ensure my catering numbers report remains accurate.

Any help/suggestions gratefully accepted as I've been searching
forums/advice sites for hours and all seem to lead back to the same solution
- that there should be no need to 'store' data that is already contained in
other tables but in this case I can't think of a way around it unless I
create an 'Adjustments' table that stores any changes to the 'default' data
and my 'Amount Payable' and 'Catering Numbers' reports calculate the
difference between the default and adjustments but that seems to be the long
way around....

If you use a table with foreign keys to the event and to the attendee tables,
and add a new record for each catering event, then you can easily specify
vegetarian, omit a record, add an extra fee, or whatever. If you have each
event as a field in the table, it's a lot less flexible and adaptable.
--

John W. Vinson [MVP]

On Sun, 31 May 2009 20:25:01 -0700, EdHead
wrote:

Thanks, John.
I certainly take your point about a catering table with multiple catering
records for each delegate (i guess I kind of lost my way off the 'relational'
track because I was a little pre-occupied trying to devise a way to automate
the data input for the catering events). If I link up a catering table as
suggested, is there any way you could suggest for me to achieve some degree
of auto-population of catering records applicable to the type of
registration? Registration code is currently determined by concatenation of
3 registration categories.

Sure; you could have a "template" set of catering records with those fields,
and run an Append query using the categories as criteria.
--

John W. Vinson [MVP]

Ah yes....I did attempt an apend query before I posted here but wasn't sure
if this is what I needed - now you've confirmed it I will have another go at
tweaking the criteria which just wasn't doing it for me.
Thanks for all your help.

Helen

"John W. Vinson" wrote:

On Sun, 31 May 2009 20:25:01 -0700, EdHead
wrote:

Thanks, John.
I certainly take your point about a catering table with multiple catering
records for each delegate (i guess I kind of lost my way off the 'relational'
track because I was a little pre-occupied trying to devise a way to automate
the data input for the catering events). If I link up a catering table as
suggested, is there any way you could suggest for me to achieve some degree
of auto-population of catering records applicable to the type of
registration? Registration code is currently determined by concatenation of
3 registration categories.

Sure; you could have a "template" set of catering records with those fields,
and run an Append query using the categories as criteria.
--

John W. Vinson [MVP]