Creating a New Record for Another Form

[Guest]

Hi, folks. I'm certain that this has been covered many times, but I can't
find it.

I need to add a new record to a linked form, and then use the new record.
Using the Northwinds database as an example, I'm in the Order form. I need
to add a new order, for a new client.

In the Order form, the client is a combo box; you can't type a new entry
there. So I added a command button to create a new record in the Customers
form. So far, so good. But when I add the new Customer and come back to the
Orders form, the new customer isn't visible; apparently, the combo box list
was generated before the new Customer was added, and I have to close (or
requery) the Orders form before I can use the new Customer.

Is there a way to automate that "requery" and drop that new Customer right
into the Orders form? Thanks.

 

[John Vinson]

Hi, folks. I'm certain that this has been covered many times, but I can't
find it.

I need to add a new record to a linked form, and then use the new record.
Using the Northwinds database as an example, I'm in the Order form. I need
to add a new order, for a new client.

In the Order form, the client is a combo box;

Ummm...

No. You don't.

Forms don't contain data, any more than my office window "contains"
the Owyhee Mountain range.

And the client is not a combo box. The client field contains data,
probably a numeric ClientID; the Form *displays* that value using a
Combo Box as a tool.

Don't mix levels!

you can't type a new entry
there. So I added a command button to create a new record in the Customers
form. So far, so good. But when I add the new Customer and come back to the
Orders form, the new customer isn't visible; apparently, the combo box list
was generated before the new Customer was added, and I have to close (or
requery) the Orders form before I can use the new Customer.

Is there a way to automate that "requery" and drop that new Customer right
into the Orders form? Thanks.

The simplest way is to use the NotInList event of the Combo Box; see
http://www.mvps.org/access and search for "notinlist". Failing that,
you can requery the combo box itself (without requerying the entire
Form). E.g.

Forms![Your Form Name]![Your Combo Name].Requery


John W. Vinson[MVP]

 

[Guest]

Thanks. This wasn't _precisely_ the answer that I needed, but you led me to
it. When I looked up the "notInList" command on Google, I found the
following page, which _was_ just what I was looking for.

http://support.microsoft.com/?kbid=197526

Apparently, this page isn't listed on MSDN, as I didn't find that page there.

I appreciate the assist!

Hi, folks. I'm certain that this has been covered many times, but I can't
find it.

I need to add a new record to a linked form, and then use the new record.
Using the Northwinds database as an example, I'm in the Order form. I need
to add a new order, for a new client.

In the Order form, the client is a combo box;

Ummm...

No. You don't.

Forms don't contain data, any more than my office window "contains"
the Owyhee Mountain range.

And the client is not a combo box. The client field contains data,
probably a numeric ClientID; the Form *displays* that value using a
Combo Box as a tool.

Don't mix levels!

you can't type a new entry
there. So I added a command button to create a new record in the Customers
form. So far, so good. But when I add the new Customer and come back to the
Orders form, the new customer isn't visible; apparently, the combo box list
was generated before the new Customer was added, and I have to close (or
requery) the Orders form before I can use the new Customer.

Is there a way to automate that "requery" and drop that new Customer right
into the Orders form? Thanks.

The simplest way is to use the NotInList event of the Combo Box; see
http://www.mvps.org/access and search for "notinlist". Failing that,
you can requery the combo box itself (without requerying the entire
Form). E.g.

Forms![Your Form Name]![Your Combo Name].Requery


John W. Vinson[MVP]