Adding to table through Lookup Field

[shawnvw]

Here's what I have so far:

The form contains a subform, which draws records from the a form called "Sessions Subform". As you probably know, this form is a datasheet form (a form that looks like a table, which I think is cool), that draws its records from a table called "Sessions".

The Sessions table has a column called "LookupClient". As you probably know, this is a lookup field with a combo box that lets me select a client's "LastName" from the Clients table. (Sessions is the "many", and Clients is the "one").

When I start filling in the subform, everything's fine until I have to select a Client, only to find that the client is not in the database. Up until now, I've been interrupting my work flow in order to open the Client table and add the new client.

Is there any way that I can add a new client to Clients table, directly from the subform?

(I know that some folks frown on lookup fields. If you want me to do away with them, please explain how to keep the same functionality on my subform.)

 

[boblarson]

Lookups are okay at form level, just don't use them at table level. If you have them at table level get rid of them. You can use a lookup table with combo box or list box on a form to select an item from another table to insert via the bound column of the control.

As for adding on the fly, if you set the combo's Limit to List property to True and then in the On Not In List event you put code you can add that value to the combo.

See here for more about that:
http://www.access-programmers.co.uk/forums/showpost.php?p=627928&postcount=4

 

[shawnvw]

>>>I know that some folks frown on lookup fields.<<<
Not sure what you mean?

This is what I mean:
http://www.mvps.org/access/lookupfields.htm

He seems to make some good points.

 

[boblarson]

This is what I mean:
http://www.mvps.org/access/lookupfields.htm

He seems to make some good points.

Yes, you are correct - You do NOT want to use lookups at TABLE level (which is the point of the article). This does NOT mean that you can't, or shouldn't, use them at FORM level (using combo boxes, list boxes, etc.). The use at form level is perfectly acceptable and is highly encouraged.

 

[shawnvw]

Lookups are okay at form level, just don't use them at table level. If you have them at table level get rid of them. You can use a lookup table with combo box or list box on a form to select an item from another table to insert via the bound column of the control.

Okay. Umm...could you walk me through the steps, using my example?

Again, I have the Sessions table, the Clients table, and I need to match every Client to one or more Sessions.

Thanks.