Using a form entry to update an existing record in a table

[NielsE]

I am developing a data base for humanitarian projects that each can operate
in several countries and each country can have several projects.
Users enter data through a form that is based on the main tblProjects and
three sub-forms. One of the sub-forms handle the entry of project
country(ies) using a combobox that is limited by the region chosen on the
main form (e.g. if Africa is the region selected on the main only African
countries are visible in combo). While the recordsource for the sub-form is
the tblProCountry that ensures the many-to-many relationship between projects
and countries, the Conutry combo (CountryName) takes its values from the
tblCountry. If the country is not in list it will be added. The tblCountry
has the field CountryName as primary key (no autonumber ID). The other field
in the tblCountry is Region, which is a lookup from the tblRegion where
Region is the only field and primary field (again no autonumber ID).

What I want to do is, in the case of country not in list, to enable the user
to add the relevant region to the newly enetered new contry. I can place a
new RegionCombo in the Country sub-form that take its values from the
tblRegion, but how do I, let's say, ensure that Africa goes into the region
field in the same record as Malawi in the tblCountry?
Thanks for any help in advance.
Niels

 

[Michel Walsh]

Hi,


John L. Viecas has a good example about that in his Microsoft Access 2003
Inside Out (pp 863-next... because it is not in the book index).


Ask if the user wish to enter a new country (can be a typo).

In the negative, use Response=acDataErrDisplay, to display the standard
error message, or display your own (or no) error message and use
Response=acDataErrContinue.

In the affirmative, add the data with a

DoCmd.RunSQL "INSERT INTO tablename(CountryName)
VALUES(FORMS!formName!ControlNameWithTheNewName) "


and use Response=acDataErrAdded



Hoping it may help,
Vanderghast, Access MVP

 

[NielsE]

Thanks Michel,
However, my problem is not to add a new country if it is not in the list.
What I want to do is if I, for example, add Malawi as a new country
Notinlist to the tblCountry to be have the table updated and then immediately
add Africa from another combo (which gets its dat from the tblRegion) to the
the region field in the Malawi record in the tblCountry. Hope this makes
sense and that someone can help me..
Thanks in advance.
Niels

 

[Michel Walsh]

Hi,


Your lists are based on table, right? not on a list of values. So,
indeed, my initial proposition *is* to add a record in a table. If you have
TWO fields, rather than just one? the syntax is quite the same:

DoCmd.RunSQL "INSERT INTO tablename(CountryName, RegionName)
VALUES(FORMS!formName!ControlNameWithTheNewName,
FORMS!formName!ControlNameWithTheAppropriateRegion) "



That SQL statement insert a new record in a table. It is only by the
"Response" value you pass back to Access that the list of the actual combo
box get updated.


Also note that the region must be known (or assumed, or set to NULL), in
theory, before you add the record (with its two fields).Sure, it is possible
to modify it later with an UPDATE statement, too.




Hoping it may help,
Vanderghast, Access MVP