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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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



"NielsE" <NielsE@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:195277A1-23DC-47E5-A286-5420F000B8F1@xxxxxxxxxxxxxxxx
> 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" wrote:
>
>> 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" <NielsE@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:1028F624-7D0A-408D-AA2F-6887A917741F@xxxxxxxxxxxxxxxx
>> >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
>> >
>>
>>
>>


.

Relevant Pages

  • Re: My only near-negative
    ... What has this to do with your insistence that I abide by rules of a country that I am not abiding in? ... No dictionary of mine lists it. ... It does not have a definition for whittering. ... Why would you suppose that I was denying being aware of "antiques trade" as an expression? ...
    (alt.marketing.online.ebay)
  • Re: think when starting: formats (dates, places, ...)
    ... If I'd been starting out now, I'd have used the ISO format - year, ... produce lists of events sorted by place. ... "town/village, country subdivision, country", which means that the list ... the spelling of placenames changes, ...
    (soc.genealogy.britain)
  • Re: think when starting: formats (dates, places, ...)
    ... If I'd been starting out now, I'd have used the ISO format - year, ... produce lists of events sorted by place. ... "town/village, country subdivision, country", which means that the list ... the spelling of placenames changes, ...
    (soc.genealogy.britain)
  • Re: Dynamic Menus in Lists
    ... you've hit the essence and spotted the possible problem (duplicate ... I tend to use two different lists myself but this ... > list would contain a country and state columns. ... You would use Web Part Connections to filter ...
    (microsoft.public.sharepoint.windowsservices)
  • Re: Using a form entry to update an existing record in a table
    ... Thanks Michel, ... my problem is not to add a new country if it is not in the list. ... Notinlist to the tblCountry to be have the table updated and then immediately ... >> main form (e.g. if Africa is the region selected on the main only African ...
    (microsoft.public.access.formscoding)