saving values from dlookup to new table

[Guest]

Using DLookup I am able to retrieve value from an CONTACTS table into my
form. I want to save that value in the ATTENDEES table which is controlling
the form. Instead it seems to lookup the value everytime. My other reports
and forms work from the ATTENDEES table. Is there a way to save the value to
the new table?

 

[Steve Schapel]

Rick,

I am not sure how you are using DLookup in this context, but it may not
be the most applicable approach. The standard way of doing this is to
use a Combobox for the entry of data via your form. The combobox is
bound to the Contact or ContactID (or whatever) field from your
Attendees table, and the Contacts table is the Row Source of the
combobox. Process is then automatic.

If you need more help with this, please post back with further details,
with examples, of waht you want to achieve.

 

[Guest]

Wow! Thanks for the quick help! I like the combo box idea and that is
probably where I will concentrate.

I started with a Contacts Management Template, customized and then added
most of an Event Management Template. Both have reports and forms working
off their respective tables (that I don't yet have the knowledge to
re-create). When I enter the FirstName and LastName on the Attendees form,
I want to retrieve the address and phone, fax, home phone etc from the
corresponding record in the CONTACTS table AND then save that in the
ATTENDEES table.

You must have tremendous patience to deal with rookies like me. Thanks.

 

[Steve Schapel]

Rick,

What you are proposing would normally be regarded as an invalid database
design. All of the Contact-related data should be stored in the
Contacts table, one field of which is a Primary Key field, where the
data is unique for each record. In the absence of any real-world data
to uniquely identify each Contact, an Autonumber field is often used for
this purpose.

And then, in the Attendees table, *only one field* should be included to
identify the Contact, and this will be the corresponding data to the
Contacts table's primary key. In the Attendees table, as long as you
know the unique identifier, you can easily retrieve the name, address,
phone, etc, via a query based on both tables. You don't want the same
data replicated in more than one table.

I assure you that it will be absolutly worth every ounce of effort you
put into understanding this concept.

As to your form management of this data, this article may be of help...
http://accesstips.datamanagementsolutions.biz/lookup.htm

 

[Guest]

Thank you - you are talking about Normalization? I will put in the effort
you suggest - and I think I knew better than to try it this way. This may be
why my learning curve has been so slow - I am afraid to jump in and create my
own and have instead tried for months to just "customize" the templates.

One last question - when using the combo-box scenario you earlier described
- is that a combo box for each field?

 

[Guest]

Final thanks! I am looking at the examples at the link you gave me and
rethinking my design - makes a lot of sense.

 

[Steve Schapel]

One last question - when using the combo-box scenario you earlier described
- is that a combo box for each field?

I'm not sure what you mean by "each field". If you mean each field
represented on the form from the Attendees table, then no, you would
only use a combobox for those fields, such as the ContactID, where you
want the value to be looked up from a pre-defined set of values (in this
case in the other table). Does that answer it?