Change address fields in Table using Unbound Combo Box

I have an Access Database populated with the names and addresses of approx.
12,000 names. The main Table is named "VoterInformationTable". The primary
Key Field is "ID". I also have a Form named "VoterInformationTable". I
created an unbound Combo Box on the form named "Combo316". Combo316 has a
dropdown list based on a Query named "FindDupsVIT". The Row Source is the
fields in the Query "FindDupsVIT". The fields are the fields that I will be
using to update the VoterInformationTable record with.
The fields in the VoterInformationTable that should be updated when I select
from the Combo dropdown
aAddressInfoId, PED, Poll, St#, StSuffix, Street, Street Type, StreetDir,
Apt#, City, Prov, Postal Code, AddressWithoutPostalCode, ProvDistrictDescE
From the Query "FindDupsVIT".

Can anyone help me write an after update event for Combo316?
I am not a programmer. I have been trying to get the Combo to update the
address fields for weeks without success. Any help appreciated.
Bill Depow- NB Canada

Be happy to help, Bill, but I would need to know what, exactly, you want to
do. Are you saying:
1. Select an item from the combo box
2. Make the record in the row source of the form that matches the record
identified in the Combo to form current record
3. Update the controls in the form based on the values in the columns of the
combo that is based on the query.

If this is essentially correct, here is a way to accomplish it using the
combo's After update event. First, I assume the ID field that is the primary
key field for the table is also a field in the query. If not, it needs to be
or you need to identify another unique field the two share.

As to the combo. It needs to have a column for each field in the query.
You will need to set the column count property of the combo to the number of
fields. It would be best if the ID field is the first column in the query
and you set the Bound Column property of the combo to 1. Now, a word about
that. Don't confuse the combo's Bound Column property with it's Control
Source property. The Control Source property identifies the the field in the
form's record source the control is bound to. The Bound Column property
identifies the column of the combo's item list that is returned if you
reference Me.MyCombo, for example. Then, you also need to understand the
disconnect between the property and the column collection. You would set the
Bound Column property to 1, but to reference it as a column, you would
reference it as Me.MyCombo.Column(0). The column collection index is zero
based.
Okay, with all the background out of the way, here is an example of the
most straight forward way to do it:

Private Sub Combo316_AfterUpdate()

'Find the matching record in the form
With Me.RecordsetClone
.FindFirst "[Id] = " & Me.Combo316
If .NoMatch Then
MsgBox "Voter Not Found"
Exit Sub
Else
Me.Bookmark = .BookMark
End If
End With

'We have the matching record - Populate the controls
With Me.
.SomeControl = .Combo316.Colun(1)
.AnotherControl = .Combo316.Column(2)
End With
End Sub

No need to go through them all, Just list all the controls to be populated
and the column from the combo that goes there.
--
Dave Hargis, Microsoft Access MVP


"BillD" wrote:

I have an Access Database populated with the names and addresses of approx.
12,000 names. The main Table is named "VoterInformationTable". The primary
Key Field is "ID". I also have a Form named "VoterInformationTable". I
created an unbound Combo Box on the form named "Combo316". Combo316 has a
dropdown list based on a Query named "FindDupsVIT". The Row Source is the
fields in the Query "FindDupsVIT". The fields are the fields that I will be
using to update the VoterInformationTable record with.
The fields in the VoterInformationTable that should be updated when I select
from the Combo dropdown
aAddressInfoId, PED, Poll, St#, StSuffix, Street, Street Type, StreetDir,
Apt#, City, Prov, Postal Code, AddressWithoutPostalCode, ProvDistrictDescE
From the Query "FindDupsVIT".

Can anyone help me write an after update event for Combo316?
I am not a programmer. I have been trying to get the Combo to update the
address fields for weeks without success. Any help appreciated.
Bill Depow- NB Canada

Thank you so very much for responding. This is a very important feature I
would like to add to my database.
I will go through the items you listed.
1. Yes, I will be selecting an item from the Combo Box dropdown list. I
created a query named "FindDupsVIT". This row source for the list is the
"FindDupsVIT" Query.
2. I do not totally understand your second item. The Form
"VoterInformationTable" has the "Combo316" and it also has separate Text
Fields with Name and Address for the current record on the form. I have
another Combo that changes the Elector's record when I select a new
Name(Record) from the Combo48 dropdown.
With Using "Combo316" I want to change the Address Fields in the main table
to match the Combo316 address fields selected from the dropdown list.
When the address fields are updated the new address will show in the form
text fields.
The "AddressInfoId" field a field that is common to the Query and the Table
and Form. I could not use the Primary Key field "ID" as there could be more
that one person living at an address.

The Combo does have a column for each field in the query.
The Bound Column is set to 1 which is the "AddressInfoId" The Column count
is 15.

Would you be kind enough to review your code and make any changes necessary
after reading the above.

Bill
"Klatuu" wrote:

Be happy to help, Bill, but I would need to know what, exactly, you want to
do. Are you saying:
1. Select an item from the combo box
2. Make the record in the row source of the form that matches the record
identified in the Combo to form current record
3. Update the controls in the form based on the values in the columns of the
combo that is based on the query.

If this is essentially correct, here is a way to accomplish it using the
combo's After update event. First, I assume the ID field that is the primary
key field for the table is also a field in the query. If not, it needs to be
or you need to identify another unique field the two share.

As to the combo. It needs to have a column for each field in the query.
You will need to set the column count property of the combo to the number of
fields. It would be best if the ID field is the first column in the query
and you set the Bound Column property of the combo to 1. Now, a word about
that. Don't confuse the combo's Bound Column property with it's Control
Source property. The Control Source property identifies the the field in the
form's record source the control is bound to. The Bound Column property
identifies the column of the combo's item list that is returned if you
reference Me.MyCombo, for example. Then, you also need to understand the
disconnect between the property and the column collection. You would set the
Bound Column property to 1, but to reference it as a column, you would
reference it as Me.MyCombo.Column(0). The column collection index is zero
based.
Okay, with all the background out of the way, here is an example of the
most straight forward way to do it:

Private Sub Combo316_AfterUpdate()

'Find the matching record in the form
With Me.RecordsetClone
.FindFirst "[Id] = " & Me.Combo316
If .NoMatch Then
MsgBox "Voter Not Found"
Exit Sub
Else
Me.Bookmark = .BookMark
End If
End With

'We have the matching record - Populate the controls
With Me.
.SomeControl = .Combo316.Colun(1)
.AnotherControl = .Combo316.Column(2)
End With
End Sub

No need to go through them all, Just list all the controls to be populated
and the column from the combo that goes there.
--
Dave Hargis, Microsoft Access MVP


"BillD" wrote:

I have an Access Database populated with the names and addresses of approx.
12,000 names. The main Table is named "VoterInformationTable". The primary
Key Field is "ID". I also have a Form named "VoterInformationTable". I
created an unbound Combo Box on the form named "Combo316". Combo316 has a
dropdown list based on a Query named "FindDupsVIT". The Row Source is the
fields in the Query "FindDupsVIT". The fields are the fields that I will be
using to update the VoterInformationTable record with.
The fields in the VoterInformationTable that should be updated when I select
from the Combo dropdown
aAddressInfoId, PED, Poll, St#, StSuffix, Street, Street Type, StreetDir,
Apt#, City, Prov, Postal Code, AddressWithoutPostalCode, ProvDistrictDescE
From the Query "FindDupsVIT".

Can anyone help me write an after update event for Combo316?
I am not a programmer. I have been trying to get the Combo to update the
address fields for weeks without success. Any help appreciated.
Bill Depow- NB Canada

Ok, I think I get a better drift. Boy, it sure would be nice if you used
names that mean something. Names like Comb316 and Comb48 only add to the
confusion.
I still get lost in the detail, but hopefull you can make it work for you.
If you Combo316 contains address info, how do you match the addess selected
in Combo316 to the current record in the form? That is the basic thing that
needs to be done. then the after update event would only have to populate
the appropriate controls on the form to the values in the columns in the
combo as I described.

--
Dave Hargis, Microsoft Access MVP


"BillD" wrote:

Thank you so very much for responding. This is a very important feature I
would like to add to my database.
I will go through the items you listed.
1. Yes, I will be selecting an item from the Combo Box dropdown list. I
created a query named "FindDupsVIT". This row source for the list is the
"FindDupsVIT" Query.
2. I do not totally understand your second item. The Form
"VoterInformationTable" has the "Combo316" and it also has separate Text
Fields with Name and Address for the current record on the form. I have
another Combo that changes the Elector's record when I select a new
Name(Record) from the Combo48 dropdown.
With Using "Combo316" I want to change the Address Fields in the main table
to match the Combo316 address fields selected from the dropdown list.
When the address fields are updated the new address will show in the form
text fields.
The "AddressInfoId" field a field that is common to the Query and the Table
and Form. I could not use the Primary Key field "ID" as there could be more
that one person living at an address.

The Combo does have a column for each field in the query.
The Bound Column is set to 1 which is the "AddressInfoId" The Column count
is 15.

Would you be kind enough to review your code and make any changes necessary
after reading the above.

Bill
"Klatuu" wrote:

Be happy to help, Bill, but I would need to know what, exactly, you want to
do. Are you saying:
1. Select an item from the combo box
2. Make the record in the row source of the form that matches the record
identified in the Combo to form current record
3. Update the controls in the form based on the values in the columns of the
combo that is based on the query.

If this is essentially correct, here is a way to accomplish it using the
combo's After update event. First, I assume the ID field that is the primary
key field for the table is also a field in the query. If not, it needs to be
or you need to identify another unique field the two share.

As to the combo. It needs to have a column for each field in the query.
You will need to set the column count property of the combo to the number of
fields. It would be best if the ID field is the first column in the query
and you set the Bound Column property of the combo to 1. Now, a word about
that. Don't confuse the combo's Bound Column property with it's Control
Source property. The Control Source property identifies the the field in the
form's record source the control is bound to. The Bound Column property
identifies the column of the combo's item list that is returned if you
reference Me.MyCombo, for example. Then, you also need to understand the
disconnect between the property and the column collection. You would set the
Bound Column property to 1, but to reference it as a column, you would
reference it as Me.MyCombo.Column(0). The column collection index is zero
based.
Okay, with all the background out of the way, here is an example of the
most straight forward way to do it:

Private Sub Combo316_AfterUpdate()

'Find the matching record in the form
With Me.RecordsetClone
.FindFirst "[Id] = " & Me.Combo316
If .NoMatch Then
MsgBox "Voter Not Found"
Exit Sub
Else
Me.Bookmark = .BookMark
End If
End With

'We have the matching record - Populate the controls
With Me.
.SomeControl = .Combo316.Colun(1)
.AnotherControl = .Combo316.Column(2)
End With
End Sub

No need to go through them all, Just list all the controls to be populated
and the column from the combo that goes there.
--
Dave Hargis, Microsoft Access MVP


"BillD" wrote:

I have an Access Database populated with the names and addresses of approx.
12,000 names. The main Table is named "VoterInformationTable". The primary
Key Field is "ID". I also have a Form named "VoterInformationTable". I
created an unbound Combo Box on the form named "Combo316". Combo316 has a
dropdown list based on a Query named "FindDupsVIT". The Row Source is the
fields in the Query "FindDupsVIT". The fields are the fields that I will be
using to update the VoterInformationTable record with.
The fields in the VoterInformationTable that should be updated when I select
from the Combo dropdown
aAddressInfoId, PED, Poll, St#, StSuffix, Street, Street Type, StreetDir,
Apt#, City, Prov, Postal Code, AddressWithoutPostalCode, ProvDistrictDescE
From the Query "FindDupsVIT".

Can anyone help me write an after update event for Combo316?
I am not a programmer. I have been trying to get the Combo to update the
address fields for weeks without success. Any help appreciated.
Bill Depow- NB Canada

The form is always displaying a record showing all the fields (including
Address fields) of that record from the main table. Each record has an "ID"
which is the primary key field. This "ID" is also showing on the form. I wish
I could send you a zipped datasbase so you could see what I mean. I just
presumed that since the combo was on the form that the combo would know what
record was showing.
Hope this helped.
Bill


"Klatuu" wrote:

Ok, I think I get a better drift. Boy, it sure would be nice if you used
names that mean something. Names like Comb316 and Comb48 only add to the
confusion.
I still get lost in the detail, but hopefull you can make it work for you.
If you Combo316 contains address info, how do you match the addess selected
in Combo316 to the current record in the form? That is the basic thing that
needs to be done. then the after update event would only have to populate
the appropriate controls on the form to the values in the columns in the
combo as I described.

--
Dave Hargis, Microsoft Access MVP


"BillD" wrote:

Thank you so very much for responding. This is a very important feature I
would like to add to my database.
I will go through the items you listed.
1. Yes, I will be selecting an item from the Combo Box dropdown list. I
created a query named "FindDupsVIT". This row source for the list is the
"FindDupsVIT" Query.
2. I do not totally understand your second item. The Form
"VoterInformationTable" has the "Combo316" and it also has separate Text
Fields with Name and Address for the current record on the form. I have
another Combo that changes the Elector's record when I select a new
Name(Record) from the Combo48 dropdown.
With Using "Combo316" I want to change the Address Fields in the main table
to match the Combo316 address fields selected from the dropdown list.
When the address fields are updated the new address will show in the form
text fields.
The "AddressInfoId" field a field that is common to the Query and the Table
and Form. I could not use the Primary Key field "ID" as there could be more
that one person living at an address.

The Combo does have a column for each field in the query.
The Bound Column is set to 1 which is the "AddressInfoId" The Column count
is 15.

Would you be kind enough to review your code and make any changes necessary
after reading the above.

Bill
"Klatuu" wrote:

Be happy to help, Bill, but I would need to know what, exactly, you want to
do. Are you saying:
1. Select an item from the combo box
2. Make the record in the row source of the form that matches the record
identified in the Combo to form current record
3. Update the controls in the form based on the values in the columns of the
combo that is based on the query.

If this is essentially correct, here is a way to accomplish it using the
combo's After update event. First, I assume the ID field that is the primary
key field for the table is also a field in the query. If not, it needs to be
or you need to identify another unique field the two share.

As to the combo. It needs to have a column for each field in the query.
You will need to set the column count property of the combo to the number of
fields. It would be best if the ID field is the first column in the query
and you set the Bound Column property of the combo to 1. Now, a word about
that. Don't confuse the combo's Bound Column property with it's Control
Source property. The Control Source property identifies the the field in the
form's record source the control is bound to. The Bound Column property
identifies the column of the combo's item list that is returned if you
reference Me.MyCombo, for example. Then, you also need to understand the
disconnect between the property and the column collection. You would set the
Bound Column property to 1, but to reference it as a column, you would
reference it as Me.MyCombo.Column(0). The column collection index is zero
based.
Okay, with all the background out of the way, here is an example of the
most straight forward way to do it:

Private Sub Combo316_AfterUpdate()

'Find the matching record in the form
With Me.RecordsetClone
.FindFirst "[Id] = " & Me.Combo316
If .NoMatch Then
MsgBox "Voter Not Found"
Exit Sub
Else
Me.Bookmark = .BookMark
End If
End With

'We have the matching record - Populate the controls
With Me.
.SomeControl = .Combo316.Colun(1)
.AnotherControl = .Combo316.Column(2)
End With
End Sub

No need to go through them all, Just list all the controls to be populated
and the column from the combo that goes there.
--
Dave Hargis, Microsoft Access MVP


"BillD" wrote:

I have an Access Database populated with the names and addresses of approx.
12,000 names. The main Table is named "VoterInformationTable". The primary
Key Field is "ID". I also have a Form named "VoterInformationTable". I
created an unbound Combo Box on the form named "Combo316". Combo316 has a
dropdown list based on a Query named "FindDupsVIT". The Row Source is the
fields in the Query "FindDupsVIT". The fields are the fields that I will be
using to update the VoterInformationTable record with.
The fields in the VoterInformationTable that should be updated when I select
from the Combo dropdown
aAddressInfoId, PED, Poll, St#, StSuffix, Street, Street Type, StreetDir,
Apt#, City, Prov, Postal Code, AddressWithoutPostalCode, ProvDistrictDescE
From the Query "FindDupsVIT".

Can anyone help me write an after update event for Combo316?
I am not a programmer. I have been trying to get the Combo to update the
address fields for weeks without success. Any help appreciated.
Bill Depow- NB Canada

An Unbound Combo has no knowledge of what is on the form.
That is what I was trying to get at earlier.
If I understand, Combo316 shows an address. Is it that you are selecting an
address for the current Voter?
--
Dave Hargis, Microsoft Access MVP


"BillD" wrote:

The form is always displaying a record showing all the fields (including
Address fields) of that record from the main table. Each record has an "ID"
which is the primary key field. This "ID" is also showing on the form. I wish
I could send you a zipped datasbase so you could see what I mean. I just
presumed that since the combo was on the form that the combo would know what
record was showing.
Hope this helped.
Bill


"Klatuu" wrote:

Ok, I think I get a better drift. Boy, it sure would be nice if you used
names that mean something. Names like Comb316 and Comb48 only add to the
confusion.
I still get lost in the detail, but hopefull you can make it work for you.
If you Combo316 contains address info, how do you match the addess selected
in Combo316 to the current record in the form? That is the basic thing that
needs to be done. then the after update event would only have to populate
the appropriate controls on the form to the values in the columns in the
combo as I described.

--
Dave Hargis, Microsoft Access MVP


"BillD" wrote:

Thank you so very much for responding. This is a very important feature I
would like to add to my database.
I will go through the items you listed.
1. Yes, I will be selecting an item from the Combo Box dropdown list. I
created a query named "FindDupsVIT". This row source for the list is the
"FindDupsVIT" Query.
2. I do not totally understand your second item. The Form
"VoterInformationTable" has the "Combo316" and it also has separate Text
Fields with Name and Address for the current record on the form. I have
another Combo that changes the Elector's record when I select a new
Name(Record) from the Combo48 dropdown.
With Using "Combo316" I want to change the Address Fields in the main table
to match the Combo316 address fields selected from the dropdown list.
When the address fields are updated the new address will show in the form
text fields.
The "AddressInfoId" field a field that is common to the Query and the Table
and Form. I could not use the Primary Key field "ID" as there could be more
that one person living at an address.

The Combo does have a column for each field in the query.
The Bound Column is set to 1 which is the "AddressInfoId" The Column count
is 15.

Would you be kind enough to review your code and make any changes necessary
after reading the above.

Bill
"Klatuu" wrote:

Be happy to help, Bill, but I would need to know what, exactly, you want to
do. Are you saying:
1. Select an item from the combo box
2. Make the record in the row source of the form that matches the record
identified in the Combo to form current record
3. Update the controls in the form based on the values in the columns of the
combo that is based on the query.

If this is essentially correct, here is a way to accomplish it using the
combo's After update event. First, I assume the ID field that is the primary
key field for the table is also a field in the query. If not, it needs to be
or you need to identify another unique field the two share.

As to the combo. It needs to have a column for each field in the query.
You will need to set the column count property of the combo to the number of
fields. It would be best if the ID field is the first column in the query
and you set the Bound Column property of the combo to 1. Now, a word about
that. Don't confuse the combo's Bound Column property with it's Control
Source property. The Control Source property identifies the the field in the
form's record source the control is bound to. The Bound Column property
identifies the column of the combo's item list that is returned if you
reference Me.MyCombo, for example. Then, you also need to understand the
disconnect between the property and the column collection. You would set the
Bound Column property to 1, but to reference it as a column, you would
reference it as Me.MyCombo.Column(0). The column collection index is zero
based.
Okay, with all the background out of the way, here is an example of the
most straight forward way to do it:

Private Sub Combo316_AfterUpdate()

'Find the matching record in the form
With Me.RecordsetClone
.FindFirst "[Id] = " & Me.Combo316
If .NoMatch Then
MsgBox "Voter Not Found"
Exit Sub
Else
Me.Bookmark = .BookMark
End If
End With

'We have the matching record - Populate the controls
With Me.
.SomeControl = .Combo316.Colun(1)
.AnotherControl = .Combo316.Column(2)
End With
End Sub

No need to go through them all, Just list all the controls to be populated
and the column from the combo that goes there.
--
Dave Hargis, Microsoft Access MVP


"BillD" wrote:

I have an Access Database populated with the names and addresses of approx.
12,000 names. The main Table is named "VoterInformationTable". The primary
Key Field is "ID". I also have a Form named "VoterInformationTable". I
created an unbound Combo Box on the form named "Combo316". Combo316 has a
dropdown list based on a Query named "FindDupsVIT". The Row Source is the
fields in the Query "FindDupsVIT". The fields are the fields that I will be
using to update the VoterInformationTable record with.
The fields in the VoterInformationTable that should be updated when I select
from the Combo dropdown
aAddressInfoId, PED, Poll, St#, StSuffix, Street, Street Type, StreetDir,
Apt#, City, Prov, Postal Code, AddressWithoutPostalCode, ProvDistrictDescE
From the Query "FindDupsVIT".

Can anyone help me write an after update event for Combo316?
I am not a programmer. I have been trying to get the Combo to update the
address fields for weeks without success. Any help appreciated.
Bill Depow- NB Canada

Yes, that is correct. I am selecting a new address for the current voter.

"Klatuu" wrote:

An Unbound Combo has no knowledge of what is on the form.
That is what I was trying to get at earlier.
If I understand, Combo316 shows an address. Is it that you are selecting an
address for the current Voter?
--
Dave Hargis, Microsoft Access MVP


"BillD" wrote:

The form is always displaying a record showing all the fields (including
Address fields) of that record from the main table. Each record has an "ID"
which is the primary key field. This "ID" is also showing on the form. I wish
I could send you a zipped datasbase so you could see what I mean. I just
presumed that since the combo was on the form that the combo would know what
record was showing.
Hope this helped.
Bill


"Klatuu" wrote:

Ok, I think I get a better drift. Boy, it sure would be nice if you used
names that mean something. Names like Comb316 and Comb48 only add to the
confusion.
I still get lost in the detail, but hopefull you can make it work for you.
If you Combo316 contains address info, how do you match the addess selected
in Combo316 to the current record in the form? That is the basic thing that
needs to be done. then the after update event would only have to populate
the appropriate controls on the form to the values in the columns in the
combo as I described.

--
Dave Hargis, Microsoft Access MVP


"BillD" wrote:

Thank you so very much for responding. This is a very important feature I
would like to add to my database.
I will go through the items you listed.
1. Yes, I will be selecting an item from the Combo Box dropdown list. I
created a query named "FindDupsVIT". This row source for the list is the
"FindDupsVIT" Query.
2. I do not totally understand your second item. The Form
"VoterInformationTable" has the "Combo316" and it also has separate Text
Fields with Name and Address for the current record on the form. I have
another Combo that changes the Elector's record when I select a new
Name(Record) from the Combo48 dropdown.
With Using "Combo316" I want to change the Address Fields in the main table
to match the Combo316 address fields selected from the dropdown list.
When the address fields are updated the new address will show in the form
text fields.
The "AddressInfoId" field a field that is common to the Query and the Table
and Form. I could not use the Primary Key field "ID" as there could be more
that one person living at an address.

The Combo does have a column for each field in the query.
The Bound Column is set to 1 which is the "AddressInfoId" The Column count
is 15.

Would you be kind enough to review your code and make any changes necessary
after reading the above.

Bill
"Klatuu" wrote:

Be happy to help, Bill, but I would need to know what, exactly, you want to
do. Are you saying:
1. Select an item from the combo box
2. Make the record in the row source of the form that matches the record
identified in the Combo to form current record
3. Update the controls in the form based on the values in the columns of the
combo that is based on the query.

If this is essentially correct, here is a way to accomplish it using the
combo's After update event. First, I assume the ID field that is the primary
key field for the table is also a field in the query. If not, it needs to be
or you need to identify another unique field the two share.

As to the combo. It needs to have a column for each field in the query.
You will need to set the column count property of the combo to the number of
fields. It would be best if the ID field is the first column in the query
and you set the Bound Column property of the combo to 1. Now, a word about
that. Don't confuse the combo's Bound Column property with it's Control
Source property. The Control Source property identifies the the field in the
form's record source the control is bound to. The Bound Column property
identifies the column of the combo's item list that is returned if you
reference Me.MyCombo, for example. Then, you also need to understand the
disconnect between the property and the column collection. You would set the
Bound Column property to 1, but to reference it as a column, you would
reference it as Me.MyCombo.Column(0). The column collection index is zero
based.
Okay, with all the background out of the way, here is an example of the
most straight forward way to do it:

Private Sub Combo316_AfterUpdate()

'Find the matching record in the form
With Me.RecordsetClone
.FindFirst "[Id] = " & Me.Combo316
If .NoMatch Then
MsgBox "Voter Not Found"
Exit Sub
Else
Me.Bookmark = .BookMark
End If
End With

'We have the matching record - Populate the controls
With Me.
.SomeControl = .Combo316.Colun(1)
.AnotherControl = .Combo316.Column(2)
End With
End Sub

No need to go through them all, Just list all the controls to be populated
and the column from the combo that goes there.
--
Dave Hargis, Microsoft Access MVP


"BillD" wrote:

I have an Access Database populated with the names and addresses of approx.
12,000 names. The main Table is named "VoterInformationTable". The primary
Key Field is "ID". I also have a Form named "VoterInformationTable". I
created an unbound Combo Box on the form named "Combo316". Combo316 has a
dropdown list based on a Query named "FindDupsVIT". The Row Source is the
fields in the Query "FindDupsVIT". The fields are the fields that I will be
using to update the VoterInformationTable record with.
The fields in the VoterInformationTable that should be updated when I select
from the Combo dropdown
aAddressInfoId, PED, Poll, St#, StSuffix, Street, Street Type, StreetDir,
Apt#, City, Prov, Postal Code, AddressWithoutPostalCode, ProvDistrictDescE
From the Query "FindDupsVIT".

Can anyone help me write an after update event for Combo316?
I am not a programmer. I have been trying to get the Combo to update the
address fields for weeks without success. Any help appreciated.
Bill Depow- NB Canada

Dave:
Here is the After Update Event from Combo48 that is used on the
"VoterInformationTable" Form to bring up a new record.

Sub Combo48_AfterUpdate()
' Find the record that matches the control.'
If IsNull(Me![Combo48]) Then End
Me.RecordsetClone.FindFirst "[ID] = " & Me![Combo48]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

I cannot get your code to work. Remember I just want to update(change) the
address fields for the record on the form using "Combo316". The "ID" for the
record is on the form but it is not in the query "FindDupsVIT". I used this
query to list the addresses in the table without having dulicates by setting
the criteria to 1 or 1.
Here is the Query in SQL:
SELECT DISTINCTROW VoterInformationTable.AddressInfoId,
Count(VoterInformationTable.AddressInfoId) AS NumberOfDups,
VoterInformationTable.PED, VoterInformationTable.Poll,
VoterInformationTable.[St#], VoterInformationTable.StSuffix,
VoterInformationTable.Street, VoterInformationTable.[Street Type],
VoterInformationTable.StreetDir, VoterInformationTable.[Apt#],
VoterInformationTable.City, VoterInformationTable.Prov,
VoterInformationTable.[Postal Code],
VoterInformationTable.AddressWithoutPostalCode,
VoterInformationTable.ProvDistrictDescE
FROM VoterInformationTable
GROUP BY VoterInformationTable.AddressInfoId, VoterInformationTable.PED,
VoterInformationTable.Poll, VoterInformationTable.[St#],
VoterInformationTable.StSuffix, VoterInformationTable.Street,
VoterInformationTable.[Street Type], VoterInformationTable.StreetDir,
VoterInformationTable.[Apt#], VoterInformationTable.City,
VoterInformationTable.Prov, VoterInformationTable.[Postal Code],
VoterInformationTable.AddressWithoutPostalCode,
VoterInformationTable.ProvDistrictDescE
HAVING (((Count(VoterInformationTable.AddressInfoId))1 Or
(Count(VoterInformationTable.AddressInfoId))=1));

Hope this helps
Bill Depow

"BillD" wrote:

Yes, that is correct. I am selecting a new address for the current voter.

"Klatuu" wrote:

An Unbound Combo has no knowledge of what is on the form.
That is what I was trying to get at earlier.
If I understand, Combo316 shows an address. Is it that you are selecting an
address for the current Voter?
--
Dave Hargis, Microsoft Access MVP


"BillD" wrote:

The form is always displaying a record showing all the fields (including
Address fields) of that record from the main table. Each record has an "ID"
which is the primary key field. This "ID" is also showing on the form. I wish
I could send you a zipped datasbase so you could see what I mean. I just
presumed that since the combo was on the form that the combo would know what
record was showing.
Hope this helped.
Bill


"Klatuu" wrote:

Ok, I think I get a better drift. Boy, it sure would be nice if you used
names that mean something. Names like Comb316 and Comb48 only add to the
confusion.
I still get lost in the detail, but hopefull you can make it work for you.
If you Combo316 contains address info, how do you match the addess selected
in Combo316 to the current record in the form? That is the basic thing that
needs to be done. then the after update event would only have to populate
the appropriate controls on the form to the values in the columns in the
combo as I described.

--
Dave Hargis, Microsoft Access MVP


"BillD" wrote:

Thank you so very much for responding. This is a very important feature I
would like to add to my database.
I will go through the items you listed.
1. Yes, I will be selecting an item from the Combo Box dropdown list. I
created a query named "FindDupsVIT". This row source for the list is the
"FindDupsVIT" Query.
2. I do not totally understand your second item. The Form
"VoterInformationTable" has the "Combo316" and it also has separate Text
Fields with Name and Address for the current record on the form. I have
another Combo that changes the Elector's record when I select a new
Name(Record) from the Combo48 dropdown.
With Using "Combo316" I want to change the Address Fields in the main table
to match the Combo316 address fields selected from the dropdown list.
When the address fields are updated the new address will show in the form
text fields.
The "AddressInfoId" field a field that is common to the Query and the Table
and Form. I could not use the Primary Key field "ID" as there could be more
that one person living at an address.

The Combo does have a column for each field in the query.
The Bound Column is set to 1 which is the "AddressInfoId" The Column count
is 15.

Would you be kind enough to review your code and make any changes necessary
after reading the above.

Bill
"Klatuu" wrote:

Be happy to help, Bill, but I would need to know what, exactly, you want to
do. Are you saying:
1. Select an item from the combo box
2. Make the record in the row source of the form that matches the record
identified in the Combo to form current record
3. Update the controls in the form based on the values in the columns of the
combo that is based on the query.

If this is essentially correct, here is a way to accomplish it using the
combo's After update event. First, I assume the ID field that is the primary
key field for the table is also a field in the query. If not, it needs to be
or you need to identify another unique field the two share.

As to the combo. It needs to have a column for each field in the query.
You will need to set the column count property of the combo to the number of
fields. It would be best if the ID field is the first column in the query
and you set the Bound Column property of the combo to 1. Now, a word about
that. Don't confuse the combo's Bound Column property with it's Control
Source property. The Control Source property identifies the the field in the
form's record source the control is bound to. The Bound Column property
identifies the column of the combo's item list that is returned if you
reference Me.MyCombo, for example. Then, you also need to understand the
disconnect between the property and the column collection. You would set the
Bound Column property to 1, but to reference it as a column, you would
reference it as Me.MyCombo.Column(0). The column collection index is zero
based.
Okay, with all the background out of the way, here is an example of the
most straight forward way to do it:

Private Sub Combo316_AfterUpdate()

'Find the matching record in the form
With Me.RecordsetClone
.FindFirst "[Id] = " & Me.Combo316
If .NoMatch Then
MsgBox "Voter Not Found"
Exit Sub
Else
Me.Bookmark = .BookMark
End If
End With

'We have the matching record - Populate the controls
With Me.
.SomeControl = .Combo316.Colun(1)
.AnotherControl = .Combo316.Column(2)
End With
End Sub

No need to go through them all, Just list all the controls to be populated
and the column from the combo that goes there.
--
Dave Hargis, Microsoft Access MVP


"BillD" wrote:

I have an Access Database populated with the names and addresses of approx.
12,000 names. The main Table is named "VoterInformationTable". The primary
Key Field is "ID". I also have a Form named "VoterInformationTable". I
created an unbound Combo Box on the form named "Combo316". Combo316 has a
dropdown list based on a Query named "FindDupsVIT". The Row Source is the
fields in the Query "FindDupsVIT". The fields are the fields that I will be
using to update the VoterInformationTable record with.
The fields in the VoterInformationTable that should be updated when I select
from the Combo dropdown
aAddressInfoId, PED, Poll, St#, StSuffix, Street, Street Type, StreetDir,
Apt#, City, Prov, Postal Code, AddressWithoutPostalCode, ProvDistrictDescE
From the Query "FindDupsVIT".

Can anyone help me write an after update event for Combo316?
I am not a programmer. I have been trying to get the Combo to update the
address fields for weeks without success. Any help appreciated.
Bill Depow- NB Canada

Are you aware of what the End statement does? I don't really believe that is
your purpose. The End statement abruptly stops all code execution and clears
out all variables in all modules.

Here is a rewrite of that code:

Sub Combo48_AfterUpdate()
' Find the record that matches the control.'
If Not IsNull(Me![Combo48]) Then
Wit Me.RecordsetClone
.FindFirst "[ID] = " & Me![Combo48]
Me.Bookmark = .Bookmark
End With
End Sub

Okay so at this point you have your record. Now, with Combo316, you find
the address you want. The code should look almost identical to find the
address record you want. Once you have the record you want, then it is just
a matter of populationg the address control on the form with the column in
the combo's row source that has the address.
--
Dave Hargis, Microsoft Access MVP


"BillD" wrote:

Dave:
Here is the After Update Event from Combo48 that is used on the
"VoterInformationTable" Form to bring up a new record.

Sub Combo48_AfterUpdate()
' Find the record that matches the control.'
If IsNull(Me![Combo48]) Then End
Me.RecordsetClone.FindFirst "[ID] = " & Me![Combo48]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

I cannot get your code to work. Remember I just want to update(change) the
address fields for the record on the form using "Combo316". The "ID" for the
record is on the form but it is not in the query "FindDupsVIT". I used this
query to list the addresses in the table without having dulicates by setting
the criteria to 1 or 1.
Here is the Query in SQL:
SELECT DISTINCTROW VoterInformationTable.AddressInfoId,
Count(VoterInformationTable.AddressInfoId) AS NumberOfDups,
VoterInformationTable.PED, VoterInformationTable.Poll,
VoterInformationTable.[St#], VoterInformationTable.StSuffix,
VoterInformationTable.Street, VoterInformationTable.[Street Type],
VoterInformationTable.StreetDir, VoterInformationTable.[Apt#],
VoterInformationTable.City, VoterInformationTable.Prov,
VoterInformationTable.[Postal Code],
VoterInformationTable.AddressWithoutPostalCode,
VoterInformationTable.ProvDistrictDescE
FROM VoterInformationTable
GROUP BY VoterInformationTable.AddressInfoId, VoterInformationTable.PED,
VoterInformationTable.Poll, VoterInformationTable.[St#],
VoterInformationTable.StSuffix, VoterInformationTable.Street,
VoterInformationTable.[Street Type], VoterInformationTable.StreetDir,
VoterInformationTable.[Apt#], VoterInformationTable.City,
VoterInformationTable.Prov, VoterInformationTable.[Postal Code],
VoterInformationTable.AddressWithoutPostalCode,
VoterInformationTable.ProvDistrictDescE
HAVING (((Count(VoterInformationTable.AddressInfoId))1 Or
(Count(VoterInformationTable.AddressInfoId))=1));

Hope this helps
Bill Depow

"BillD" wrote:

Yes, that is correct. I am selecting a new address for the current voter.

"Klatuu" wrote:

An Unbound Combo has no knowledge of what is on the form.
That is what I was trying to get at earlier.
If I understand, Combo316 shows an address. Is it that you are selecting an
address for the current Voter?
--
Dave Hargis, Microsoft Access MVP


"BillD" wrote:

The form is always displaying a record showing all the fields (including
Address fields) of that record from the main table. Each record has an "ID"
which is the primary key field. This "ID" is also showing on the form. I wish
I could send you a zipped datasbase so you could see what I mean. I just
presumed that since the combo was on the form that the combo would know what
record was showing.
Hope this helped.
Bill


"Klatuu" wrote:

Ok, I think I get a better drift. Boy, it sure would be nice if you used
names that mean something. Names like Comb316 and Comb48 only add to the
confusion.
I still get lost in the detail, but hopefull you can make it work for you.
If you Combo316 contains address info, how do you match the addess selected
in Combo316 to the current record in the form? That is the basic thing that
needs to be done. then the after update event would only have to populate
the appropriate controls on the form to the values in the columns in the
combo as I described.

--
Dave Hargis, Microsoft Access MVP


"BillD" wrote:

Thank you so very much for responding. This is a very important feature I
would like to add to my database.
I will go through the items you listed.
1. Yes, I will be selecting an item from the Combo Box dropdown list. I
created a query named "FindDupsVIT". This row source for the list is the
"FindDupsVIT" Query.
2. I do not totally understand your second item. The Form
"VoterInformationTable" has the "Combo316" and it also has separate Text
Fields with Name and Address for the current record on the form. I have
another Combo that changes the Elector's record when I select a new
Name(Record) from the Combo48 dropdown.
With Using "Combo316" I want to change the Address Fields in the main table
to match the Combo316 address fields selected from the dropdown list.
When the address fields are updated the new address will show in the form
text fields.
The "AddressInfoId" field a field that is common to the Query and the Table
and Form. I could not use the Primary Key field "ID" as there could be more
that one person living at an address.

The Combo does have a column for each field in the query.
The Bound Column is set to 1 which is the "AddressInfoId" The Column count
is 15.

Would you be kind enough to review your code and make any changes necessary
after reading the above.

Bill
"Klatuu" wrote:

Be happy to help, Bill, but I would need to know what, exactly, you want to
do. Are you saying:
1. Select an item from the combo box
2. Make the record in the row source of the form that matches the record
identified in the Combo to form current record
3. Update the controls in the form based on the values in the columns of the
combo that is based on the query.

If this is essentially correct, here is a way to accomplish it using the
combo's After update event. First, I assume the ID field that is the primary
key field for the table is also a field in the query. If not, it needs to be
or you need to identify another unique field the two share.

As to the combo. It needs to have a column for each field in the query.
You will need to set the column count property of the combo to the number of
fields. It would be best if the ID field is the first column in the query
and you set the Bound Column property of the combo to 1. Now, a word about
that. Don't confuse the combo's Bound Column property with it's Control
Source property. The Control Source property identifies the the field in the
form's record source the control is bound to. The Bound Column property
identifies the column of the combo's item list that is returned if you
reference Me.MyCombo, for example. Then, you also need to understand the
disconnect between the property and the column collection. You would set the
Bound Column property to 1, but to reference it as a column, you would
reference it as Me.MyCombo.Column(0). The column collection index is zero
based.
Okay, with all the background out of the way, here is an example of the
most straight forward way to do it:

Private Sub Combo316_AfterUpdate()

'Find the matching record in the form
With Me.RecordsetClone
.FindFirst "[Id] = " & Me.Combo316
If .NoMatch Then
MsgBox "Voter Not Found"
Exit Sub
Else
Me.Bookmark = .BookMark
End If
End With

'We have the matching record - Populate the controls
With Me.
.SomeControl = .Combo316.Colun(1)
.AnotherControl = .Combo316.Column(2)
End With
End Sub

No need to go through them all, Just list all the controls to be populated
and the column from the combo that goes there.
--
Dave Hargis, Microsoft Access MVP


"BillD" wrote:

I have an Access Database populated with the names and addresses of approx.
12,000 names. The main Table is named "VoterInformationTable". The primary
Key Field is "ID". I also have a Form named "VoterInformationTable". I
created an unbound Combo Box on the form named "Combo316". Combo316 has a
dropdown list based on a Query named "FindDupsVIT". The Row Source is the
fields in the Query "FindDupsVIT". The fields are the fields that I will be
using to update the VoterInformationTable record with.
The fields in the VoterInformationTable that should be updated when I select
from the Combo dropdown
aAddressInfoId, PED, Poll, St#, StSuffix, Street, Street Type, StreetDir,
Apt#, City, Prov, Postal Code, AddressWithoutPostalCode, ProvDistrictDescE
From the Query "FindDupsVIT".

Can anyone help me write an after update event for Combo316?
I am not a programmer. I have been trying to get the Combo to update the
address fields for weeks without success. Any help appreciated.
Bill Depow- NB Canada

Dave:

No, I am not fully aware of what an end statement does. I think that I
copied that code from a Sample Database from Microsoft.
Your code restated below did not seem to work. Possible 4th line typing
error should read "With Me.RecordsetClone". Also the debug had a problem with
"Me.Bookmark = .Bookmark"

Sub Combo48_AfterUpdate()
' Find the record that matches the control.'
If Not IsNull(Me![Combo48]) Then
Wit Me.RecordsetClone
.FindFirst "[ID] = " & Me![Combo48]
Me.Bookmark = .Bookmark
End With
End Sub

Now back to the Address change. Could you give me the After Update Code to
change the address fields. If it is complicated to use all 15 fields, lets
start with just 2 fields 1) The "AddressInfoId" field and 2) the "Street"
field.

If you could send me a sample code again, I will copy it to the After Update
Event and give it a try.
It would be less confusing for you if you had a Phone # that I can call you
at or can I send you an invitation to help on my computer or can I send you a
copy of the Database?
Bill

"Klatuu" wrote:

Are you aware of what the End statement does? I don't really believe that is
your purpose. The End statement abruptly stops all code execution and clears
out all variables in all modules.

Here is a rewrite of that code:

Sub Combo48_AfterUpdate()
' Find the record that matches the control.'
If Not IsNull(Me![Combo48]) Then
Wit Me.RecordsetClone
.FindFirst "[ID] = " & Me![Combo48]
Me.Bookmark = .Bookmark
End With
End Sub

Okay so at this point you have your record. Now, with Combo316, you find
the address you want. The code should look almost identical to find the
address record you want. Once you have the record you want, then it is just
a matter of populationg the address control on the form with the column in
the combo's row source that has the address.
--
Dave Hargis, Microsoft Access MVP


"BillD" wrote:

Dave:
Here is the After Update Event from Combo48 that is used on the
"VoterInformationTable" Form to bring up a new record.

Sub Combo48_AfterUpdate()
' Find the record that matches the control.'
If IsNull(Me![Combo48]) Then End
Me.RecordsetClone.FindFirst "[ID] = " & Me![Combo48]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

I cannot get your code to work. Remember I just want to update(change) the
address fields for the record on the form using "Combo316". The "ID" for the
record is on the form but it is not in the query "FindDupsVIT". I used this
query to list the addresses in the table without having dulicates by setting
the criteria to 1 or 1.
Here is the Query in SQL:
SELECT DISTINCTROW VoterInformationTable.AddressInfoId,
Count(VoterInformationTable.AddressInfoId) AS NumberOfDups,
VoterInformationTable.PED, VoterInformationTable.Poll,
VoterInformationTable.[St#], VoterInformationTable.StSuffix,
VoterInformationTable.Street, VoterInformationTable.[Street Type],
VoterInformationTable.StreetDir, VoterInformationTable.[Apt#],
VoterInformationTable.City, VoterInformationTable.Prov,
VoterInformationTable.[Postal Code],
VoterInformationTable.AddressWithoutPostalCode,
VoterInformationTable.ProvDistrictDescE
FROM VoterInformationTable
GROUP BY VoterInformationTable.AddressInfoId, VoterInformationTable.PED,
VoterInformationTable.Poll, VoterInformationTable.[St#],
VoterInformationTable.StSuffix, VoterInformationTable.Street,
VoterInformationTable.[Street Type], VoterInformationTable.StreetDir,
VoterInformationTable.[Apt#], VoterInformationTable.City,
VoterInformationTable.Prov, VoterInformationTable.[Postal Code],
VoterInformationTable.AddressWithoutPostalCode,
VoterInformationTable.ProvDistrictDescE
HAVING (((Count(VoterInformationTable.AddressInfoId))1 Or
(Count(VoterInformationTable.AddressInfoId))=1));

Hope this helps
Bill Depow

"BillD" wrote:

Yes, that is correct. I am selecting a new address for the current voter.

"Klatuu" wrote:

An Unbound Combo has no knowledge of what is on the form.
That is what I was trying to get at earlier.
If I understand, Combo316 shows an address. Is it that you are selecting an
address for the current Voter?
--
Dave Hargis, Microsoft Access MVP


"BillD" wrote:

The form is always displaying a record showing all the fields (including
Address fields) of that record from the main table. Each record has an "ID"
which is the primary key field. This "ID" is also showing on the form. I wish
I could send you a zipped datasbase so you could see what I mean. I just
presumed that since the combo was on the form that the combo would know what
record was showing.
Hope this helped.
Bill


"Klatuu" wrote:

Ok, I think I get a better drift. Boy, it sure would be nice if you used
names that mean something. Names like Comb316 and Comb48 only add to the
confusion.
I still get lost in the detail, but hopefull you can make it work for you.
If you Combo316 contains address info, how do you match the addess selected
in Combo316 to the current record in the form? That is the basic thing that
needs to be done. then the after update event would only have to populate
the appropriate controls on the form to the values in the columns in the
combo as I described.

--
Dave Hargis, Microsoft Access MVP


"BillD" wrote:

Thank you so very much for responding. This is a very important feature I
would like to add to my database.
I will go through the items you listed.
1. Yes, I will be selecting an item from the Combo Box dropdown list. I
created a query named "FindDupsVIT". This row source for the list is the
"FindDupsVIT" Query.
2. I do not totally understand your second item. The Form
"VoterInformationTable" has the "Combo316" and it also has separate Text
Fields with Name and Address for the current record on the form. I have
another Combo that changes the Elector's record when I select a new
Name(Record) from the Combo48 dropdown.
With Using "Combo316" I want to change the Address Fields in the main table
to match the Combo316 address fields selected from the dropdown list.
When the address fields are updated the new address will show in the form
text fields.
The "AddressInfoId" field a field that is common to the Query and the Table
and Form. I could not use the Primary Key field "ID" as there could be more
that one person living at an address.

The Combo does have a column for each field in the query.
The Bound Column is set to 1 which is the "AddressInfoId" The Column count
is 15.

Would you be kind enough to review your code and make any changes necessary
after reading the above.

Bill
"Klatuu" wrote:

Be happy to help, Bill, but I would need to know what, exactly, you want to
do. Are you saying:
1. Select an item from the combo box
2. Make the record in the row source of the form that matches the record
identified in the Combo to form current record
3. Update the controls in the form based on the values in the columns of the
combo that is based on the query.

If this is essentially correct, here is a way to accomplish it using the
combo's After update event. First, I assume the ID field that is the primary
key field for the table is also a field in the query. If not, it needs to be
or you need to identify another unique field the two share.

As to the combo. It needs to have a column for each field in the query.
You will need to set the column count property of the combo to the number of
fields. It would be best if the ID field is the first column in the query
and you set the Bound Column property of the combo to 1. Now, a word about
that. Don't confuse the combo's Bound Column property with it's Control
Source property. The Control Source property identifies the the field in the
form's record source the control is bound to. The Bound Column property
identifies the column of the combo's item list that is returned if you
reference Me.MyCombo, for example. Then, you also need to understand the
disconnect between the property and the column collection. You would set the
Bound Column property to 1, but to reference it as a column, you would
reference it as Me.MyCombo.Column(0). The column collection index is zero
based.
Okay, with all the background out of the way, here is an example of the
most straight forward way to do it:

Private Sub Combo316_AfterUpdate()

'Find the matching record in the form
With Me.RecordsetClone
.FindFirst "[Id] = " & Me.Combo316
If .NoMatch Then
MsgBox "Voter Not Found"
Exit Sub
Else
Me.Bookmark = .BookMark
End If
End With

'We have the matching record - Populate the controls
With Me.
.SomeControl = .Combo316.Colun(1)
.AnotherControl = .Combo316.Column(2)
End With
End Sub

No need to go through them all, Just list all the controls to be populated
and the column from the combo that goes there.
--
Dave Hargis, Microsoft Access MVP


"BillD" wrote:

I have an Access Database populated with the names and addresses of approx.
12,000 names. The main Table is named "VoterInformationTable". The primary
Key Field is "ID". I also have a Form named "VoterInformationTable". I
created an unbound Combo Box on the form named "Combo316". Combo316 has a
dropdown list based on a Query named "FindDupsVIT". The Row Source is the
fields in the Query "FindDupsVIT". The fields are the fields that I will be
using to update the VoterInformationTable record with.
The fields in the VoterInformationTable that should be updated when I select
from the Combo dropdown
aAddressInfoId, PED, Poll, St#, StSuffix, Street, Street Type, StreetDir,
Apt#, City, Prov, Postal Code, AddressWithoutPostalCode, ProvDistrictDescE
From the Query "FindDupsVIT".

Can anyone help me write an after update event for Combo316?
I am not a programmer. I have been trying to get the Combo to update the
address fields for weeks without success. Any help appreciated.
Bill Depow- NB Canada