Change Address Field data in a Table from a form using Combo Drop

[BillD]

I have Constituents database populated with approx. 12,000 electors. I have a
Form with the necessary fields to use for looking at or changing each
electors info. I use an unbound Combo Box Dropdown to search for and select
the Elector by name and one to search for and select the Elector by address.

I would like to be able to change the Address Information for any Elector by
using a Combo Dropdown List of Addresses from the Database Table. There are
about 10 fields that I would like to change info. for when I select the
address from the dropdown list. I created a Query called "AddressChange". I
used the Wizard to find Duplicate Addresses with >1 or 1. There is a Unique
Identifier field in the Database Table for each address called
"AddressInfoId".

I created a new unbound Combo Box using the AddressChange Query where I can
bring up a list of all the addresses in the VoterInformationTable. When I
select one of these Addresses, how do I get the Combo to replace the current
Address Fields Information in the Table with the new Address information from
the Dropdown list.
I think I could use the Update Event but I do not know what the wording
should be.
I can get the AddressInfoId to change by having the Control Source set to
"AddressInfoId" from the "VoterInformationTable" This changes the
"AddressInfoId" only but not the other Address fields.
Suggestions please.

 

[Klatuu]

Create an update query that will change the data based on what is in your
combo box. Run the query from the After Update event of th the combo box.

 

[BillD]

Thanks for the advice. The problem is I do not know programming. I use Access
features and copy code from features in Sample Databases. I did create an
update Query as you suggested, called "AddressUpdate". My Combo is called
"Combo316" The form the combo is in is called "VoterInformationTable". The
main Table that the Form is based on is also called "VoterInformationTable"
The Key field in the Table "VoterInformationTable" is "ID"
Could you write the After Update Event for me, it would be much appreciated.
I have tried to copy another After Update event and change it to work here
but it did not work. I ended up deleting all the addresses in the main
Table. I always use a TestCopy of the Main Database, so I just deleted the
complete Test Database and started with a new copy.

Bill Depow
Fredericton, NB Canada

 

[Klatuu]

Good info, but I need a bit more. Is Combo 316 the elector's name or his
address?
I would like to know the name of both name and address.
Are either of the combo boxes bound controls?
What is the name of the primary key field in your table? What is the name
of the control on your form that is bound to the key field?

 

[BillD]

"Combo316" gives me a dropdown list of addresses from the Query
"FindDupsVIT". This query is based on the table "VoterInformationTable" The
address fields for the query are as follows:
SELECT FindDupsVIT.AddressInfoId, FindDupsVIT.PED, FindDupsVIT.Poll,
FindDupsVIT.[St#], FindDupsVIT.StSuffix, FindDupsVIT.Street,
FindDupsVIT.[Street Type], FindDupsVIT.StreetDir, FindDupsVIT.[Apt#],
FindDupsVIT.City, FindDupsVIT.Prov, FindDupsVIT.[Postal Code],
FindDupsVIT.AddressWithoutPostalCode, FindDupsVIT.ProvDistrictDescE FROM
FindDupsVIT;
These fields should be updated in the "VoterInformationTable" when the
update event is run.

"Combo316" on the form"VoterInformationTable" has an unbound control.
"ID" the primary key field in the main table "VoterInformationTable".
I do not know what the name on the control form is that is bound to the key
field. I presume that it is "ID" because the form shows shows most all the
fields from the Table"VoterInformationTable". When the form is open, I can
search for a Name or select the next record from the table
"VoterInformationTable" .

The update query called "AddressUpdate" has yhe following SQL statement:
UPDATE FindDupsVIT SET FindDupsVIT.AddressInfoId =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.PED =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.Poll =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.[St#] =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.StSuffix =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.Street =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.[Street Type] =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.StreetDir =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.[Apt#] =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.City =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.Prov =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.[Postal Code] =
[Forms]![VoterInformationTable]![Combo316],
FindDupsVIT.AddressWithoutPostalCode =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.ProvDistrictDescE =
[Forms]![VoterInformationTable]![Combo316];

Hope this helps.
Sincere thanks,
Bill Depow

 

[Klatuu]

Sorry for the delay, things have been a bit hectic.
I have gotten lost in woods here.
I really don't need a lot of the detail. Basically, all that is necessary
once you have an elector selected, is to select the address from the combo.
If you want to write the code, here is what I need:

1. The name of the form
2. The name of the combo box control where you select the elector
3. The name of the combo box control where you select an address
4. The name of the field that the elector combo box is bound to or None if
not bound
5. The name of the field that the address combo box is bound to or None if
not bound
6. If either of the combos are unbound controls, the name of the controls
bound to those fields.
--
Dave Hargis, Microsoft Access MVP

"Combo316" gives me a dropdown list of addresses from the Query
"FindDupsVIT". This query is based on the table "VoterInformationTable" The
address fields for the query are as follows:
SELECT FindDupsVIT.AddressInfoId, FindDupsVIT.PED, FindDupsVIT.Poll,
FindDupsVIT.[St#], FindDupsVIT.StSuffix, FindDupsVIT.Street,
FindDupsVIT.[Street Type], FindDupsVIT.StreetDir, FindDupsVIT.[Apt#],
FindDupsVIT.City, FindDupsVIT.Prov, FindDupsVIT.[Postal Code],
FindDupsVIT.AddressWithoutPostalCode, FindDupsVIT.ProvDistrictDescE FROM
FindDupsVIT;
These fields should be updated in the "VoterInformationTable" when the
update event is run.

"Combo316" on the form"VoterInformationTable" has an unbound control.
"ID" the primary key field in the main table "VoterInformationTable".
I do not know what the name on the control form is that is bound to the key
field. I presume that it is "ID" because the form shows shows most all the
fields from the Table"VoterInformationTable". When the form is open, I can
search for a Name or select the next record from the table
"VoterInformationTable" .

The update query called "AddressUpdate" has yhe following SQL statement:
UPDATE FindDupsVIT SET FindDupsVIT.AddressInfoId =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.PED =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.Poll =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.[St#] =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.StSuffix =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.Street =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.[Street Type] =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.StreetDir =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.[Apt#] =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.City =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.Prov =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.[Postal Code] =
[Forms]![VoterInformationTable]![Combo316],
FindDupsVIT.AddressWithoutPostalCode =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.ProvDistrictDescE =
[Forms]![VoterInformationTable]![Combo316];

Hope this helps.
Sincere thanks,
Bill Depow

Good info, but I need a bit more. Is Combo 316 the elector's name or his
address?
I would like to know the name of both name and address.
Are either of the combo boxes bound controls?
What is the name of the primary key field in your table? What is the name
of the control on your form that is bound to the key field?

 

[BillD]

Sorry for the confusion.
The Name of the Form is "VoterInformationTable" I do not know what you mean
by The Name of the Combo Box Control where you select the Elector? Say I have
open the Form "VoterInformationTable". The Form opens automatically to the
first person alphabetically in the Table "VoterInformationTable". I have a
separate Combo Box for selecting another Elector from the Table
"VoterInformationTable". Sorry for the confusion but both the main Table and
the Form are both called "VoterInformationTable" When I am in the form the
Elector is already showing on the form. I can select any of the Electors from
the Table "VoterInformationTable" to show on the Form. The Primary Key Field
for the Table "VoterInformationTable is "ID" that is automatically generated
when entering new electors.
The address Combo Box "Combo316" is unbound. What do you mean when you asked
for the Name of the controls bound to those fields? The Address Combo Box
"Combo316" has a dropdown list based on a Query. The Row Source is the
fields in the Query "FindDupsVIT". The fields are the fields that we will be
using to update the VoterInformationTable electors record with.
The fields in the VoterInformationTable that should be updated
are:AddressInfoId, PED, Poll, St#, StSuffix, Street, Street Type, StreetDir,
Apt#, City, Prov, Postal Code, AddressWithoutPostalCode, ProvDistrictDescE
From
the Query "FindDupsVIT".

Hope I have explained this so you can understand the setup?

"Combo316" gives me a dropdown list of addresses from the Query
"FindDupsVIT". This query is based on the table "VoterInformationTable" The
address fields for the query are as follows:
SELECT FindDupsVIT.AddressInfoId, FindDupsVIT.PED, FindDupsVIT.Poll,
FindDupsVIT.[St#], FindDupsVIT.StSuffix, FindDupsVIT.Street,
FindDupsVIT.[Street Type], FindDupsVIT.StreetDir, FindDupsVIT.[Apt#],
FindDupsVIT.City, FindDupsVIT.Prov, FindDupsVIT.[Postal Code],
FindDupsVIT.AddressWithoutPostalCode, FindDupsVIT.ProvDistrictDescE FROM
FindDupsVIT;
These fields should be updated in the "VoterInformationTable" when the
update event is run.

"Combo316" on the form"VoterInformationTable" has an unbound control.
"ID" the primary key field in the main table "VoterInformationTable".
I do not know what the name on the control form is that is bound to the key
field. I presume that it is "ID" because the form shows shows most all the
fields from the Table"VoterInformationTable". When the form is open, I can
search for a Name or select the next record from the table
"VoterInformationTable" .

The update query called "AddressUpdate" has yhe following SQL statement:
UPDATE FindDupsVIT SET FindDupsVIT.AddressInfoId =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.PED =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.Poll =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.[St#] =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.StSuffix =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.Street =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.[Street Type] =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.StreetDir =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.[Apt#] =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.City =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.Prov =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.[Postal Code] =
[Forms]![VoterInformationTable]![Combo316],
FindDupsVIT.AddressWithoutPostalCode =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.ProvDistrictDescE =
[Forms]![VoterInformationTable]![Combo316];

Hope this helps.
Sincere thanks,
Bill Depow

Good info, but I need a bit more. Is Combo 316 the elector's name or his
address?
I would like to know the name of both name and address.
Are either of the combo boxes bound controls?
What is the name of the primary key field in your table? What is the name
of the control on your form that is bound to the key field?

 

[BillD]

I am still trying to change address fields in a table by using a Combo Box on
a Form. I would very much appreciate some help. I will try to explain my
situation.
I have a Table called "VoterInformationtable". In this table I have over
12,000 records of electors with addresses. The Table has separate fields for
St#,StSuffix,Street,Street Type,StreetDir,Apt#,City,Postal Code.
I have a Form called "VoterInformationTable". I have a Combo Box called
"Combo316" on the Form. I have a dropdown list on the Form that comes from a
query called "FindDupsVIT". It gives me a list of addresses from the Table
using the criteria ">=1" which is sorted by City and Street Number. I would
like to be able to change the existing address for an Elector's record in the
table by selecting an address from the Dropdown on the Combo Box. I do not
know how to write the "After Update Event". Could you provide some assistance
here?
Here is a list of the questions that you had previously asked.
1. The name of the form. The form is "VoterInformationTable"
2. The name of the combo box control where you select the elector. The Combo
Box is unbound. The Combo is "Combo48"
3. The name of the combo box control where you select an address. The Combo
Box is unbound. The Combo is "Combo316"
4. The name of the field that the elector combo box is bound to or None if
not bound. None-The Combo Box is not bound.
5. The name of the field that the address combo box is bound to or None if
not bound. None-The Combo Box is not bound.
6. If either of the combos are unbound controls, the name of the controls
bound to those fields. Where do I find these?

Thanking you for any assistance.
Bill Depow

Sorry for the delay, things have been a bit hectic.
I have gotten lost in woods here.
I really don't need a lot of the detail. Basically, all that is necessary
once you have an elector selected, is to select the address from the combo.
If you want to write the code, here is what I need:

1. The name of the form
2. The name of the combo box control where you select the elector
3. The name of the combo box control where you select an address
4. The name of the field that the elector combo box is bound to or None if
not bound
5. The name of the field that the address combo box is bound to or None if
not bound
6. If either of the combos are unbound controls, the name of the controls
bound to those fields.
--
Dave Hargis, Microsoft Access MVP

"Combo316" gives me a dropdown list of addresses from the Query
"FindDupsVIT". This query is based on the table "VoterInformationTable" The
address fields for the query are as follows:
SELECT FindDupsVIT.AddressInfoId, FindDupsVIT.PED, FindDupsVIT.Poll,
FindDupsVIT.[St#], FindDupsVIT.StSuffix, FindDupsVIT.Street,
FindDupsVIT.[Street Type], FindDupsVIT.StreetDir, FindDupsVIT.[Apt#],
FindDupsVIT.City, FindDupsVIT.Prov, FindDupsVIT.[Postal Code],
FindDupsVIT.AddressWithoutPostalCode, FindDupsVIT.ProvDistrictDescE FROM
FindDupsVIT;
These fields should be updated in the "VoterInformationTable" when the
update event is run.

"Combo316" on the form"VoterInformationTable" has an unbound control.
"ID" the primary key field in the main table "VoterInformationTable".
I do not know what the name on the control form is that is bound to the key
field. I presume that it is "ID" because the form shows shows most all the
fields from the Table"VoterInformationTable". When the form is open, I can
search for a Name or select the next record from the table
"VoterInformationTable" .

The update query called "AddressUpdate" has yhe following SQL statement:
UPDATE FindDupsVIT SET FindDupsVIT.AddressInfoId =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.PED =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.Poll =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.[St#] =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.StSuffix =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.Street =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.[Street Type] =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.StreetDir =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.[Apt#] =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.City =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.Prov =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.[Postal Code] =
[Forms]![VoterInformationTable]![Combo316],
FindDupsVIT.AddressWithoutPostalCode =
[Forms]![VoterInformationTable]![Combo316], FindDupsVIT.ProvDistrictDescE =
[Forms]![VoterInformationTable]![Combo316];

Hope this helps.
Sincere thanks,
Bill Depow

Good info, but I need a bit more. Is Combo 316 the elector's name or his
address?
I would like to know the name of both name and address.
Are either of the combo boxes bound controls?
What is the name of the primary key field in your table? What is the name
of the control on your form that is bound to the key field?
--
Dave Hargis, Microsoft Access MVP


:

Thanks for the advice. The problem is I do not know programming. I use Access
features and copy code from features in Sample Databases. I did create an
update Query as you suggested, called "AddressUpdate". My Combo is called
"Combo316" The form the combo is in is called "VoterInformationTable". The
main Table that the Form is based on is also called "VoterInformationTable"
The Key field in the Table "VoterInformationTable" is "ID"
Could you write the After Update Event for me, it would be much appreciated.
I have tried to copy another After Update event and change it to work here
but it did not work. I ended up deleting all the addresses in the main
Table. I always use a TestCopy of the Main Database, so I just deleted the
complete Test Database and started with a new copy.

Bill Depow
Fredericton, NB Canada

:

Create an update query that will change the data based on what is in your
combo box. Run the query from the After Update event of th the combo box.
--
Dave Hargis, Microsoft Access MVP


:

I have Constituents database populated with approx. 12,000 electors. I have a
Form with the necessary fields to use for looking at or changing each
electors info. I use an unbound Combo Box Dropdown to search for and select
the Elector by name and one to search for and select the Elector by address.

I would like to be able to change the Address Information for any Elector by
using a Combo Dropdown List of Addresses from the Database Table. There are
about 10 fields that I would like to change info. for when I select the
address from the dropdown list. I created a Query called "AddressChange". I
used the Wizard to find Duplicate Addresses with >1 or 1. There is a Unique
Identifier field in the Database Table for each address called
"AddressInfoId".

I created a new unbound Combo Box using the AddressChange Query where I can
bring up a list of all the addresses in the VoterInformationTable. When I
select one of these Addresses, how do I get the Combo to replace the current
Address Fields Information in the Table with the new Address information from
the Dropdown list.
I think I could use the Update Event but I do not know what the wording
should be.
I can get the AddressInfoId to change by having the Control Source set to
"AddressInfoId" from the "VoterInformationTable" This changes the
"AddressInfoId" only but not the other Address fields.
Suggestions please.