Form/Subform/ComboBox Requery

Access 2000 – Issue Database
I have the following:

Tables:
Issue – stores issue information. Primary Key is IssueID.
Provider – stores provider information. Primary Key is ProviderID. Relates
to ProvIssDetail table (one-to-many)
ProvIssDetail – fields are ProvDetailID (Primary Key), ProviderID, and
IssueID. IssueID field relates to Issue table Primary Key (one-to-many).

Forms:
Form - Enter Issue - user enters new issue information
Sub form - Provider – user enters providers associated with the issue. Can
be up to two providers. The record source is a query of Provider and
ProvIssDetail tables. The Link Child and Master fields are IssueID.
Continuous form.
Form – Add Provider – user enters provider if not found in list on combo box
in Provider sub form.

What I would like to happen:
When entering the issue, the user will select a provider from a combo box on
the subform. If the provider is not on the list, the user double clicks the
Provider Name field and the Add Provider form will pop up. The user enters
the provider information, closes the form and the selects the name from the
Provider combo on the subform. The user will then select another provider if
applicable. The max that can be entered on the subform should be two
providers. The user then tabs on through the main form to finish entering
the issue information.

I have limited knowledge of vba. Can this be accomplished using macros or
is vba best?

"Karen" wrote

When entering the issue, the user will
select a provider from a combo box on
the subform. If the provider is not on
the list, the user double clicks the
Provider Name field and the Add Pro-
vider form will pop up. The user enters
the provider information, closes the form
and the selects the name from the
Provider combo on the subform. The user
will then select another provider if
applicable. The max that can be entered
on the subform should be two providers.
The user then tabs on through the main
form to finish entering the issue information.
I have limited knowledge of vba. Can
this be accomplished using macros or
is vba best?

You will need to do this with VBA. But, you can use the NotInList event of
the Combo Box to ask if the user wishes to add the new Provider. I believe
there is an example you might adapt in the Developer Solutions database
which you can download from
http://msdn.microsoft.com/library/de...dbdownload.asp

Larry Linson
Microsoft Access MVP

I have created all the forms. I just can't get the Provider field on the
subform to requery once I add the new provider. I looked in the developer
sample database and did not see the "On Close" code mentioned in the topic
"Add a New Record to List". I know that the list needs to requery, but I am
not sure how to do it.

"Larry Linson" wrote:


"Karen" wrote

When entering the issue, the user will
select a provider from a combo box on
the subform. If the provider is not on
the list, the user double clicks the
Provider Name field and the Add Pro-
vider form will pop up. The user enters
the provider information, closes the form
and the selects the name from the
Provider combo on the subform. The user
will then select another provider if
applicable. The max that can be entered
on the subform should be two providers.
The user then tabs on through the main
form to finish entering the issue information.
I have limited knowledge of vba. Can
this be accomplished using macros or
is vba best?

You will need to do this with VBA. But, you can use the NotInList event of
the Combo Box to ask if the user wishes to add the new Provider. I believe
there is an example you might adapt in the Developer Solutions database
which you can download from
http://msdn.microsoft.com/library/de...dbdownload.asp

Larry Linson
Microsoft Access MVP