Change address fields in Table using Unbound Combo Box

[Guest]

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
are:AddressInfoId, 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

 

[Guest]

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.

 

[Guest]

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

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

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
are:AddressInfoId, 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

 

[Guest]

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

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

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

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
are:AddressInfoId, 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

 

[Guest]

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

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

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

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


:

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
are:AddressInfoId, 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

 

[Guest]

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

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

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

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
:

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


:

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
are:AddressInfoId, 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

 

[Guest]

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

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

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

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


:

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
:

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


:

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
are:AddressInfoId, 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

 

[Guest]

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

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

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

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


:

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


:

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
:

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


:

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
are:AddressInfoId, 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

 

[Guest]

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

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

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

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


:

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


:

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


:

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
:

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


:

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
are:AddressInfoId, 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

 

[Guest]

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

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

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

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

:

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


:

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


:

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


:

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
:

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


:

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
are:AddressInfoId, 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

 

[Guest]

Wit Me.RecordsetClone
The above typing error Wit should be With or
With Me.RecordsetClone

I use this method a lot, so I know it works. It is just syntax. That is
why the compiler croaks on Me.Bookmark = .Bookmark. It doesn't know what
..BookMark belongs too because the With has not been correctly established.

Read VBA Help on the End statement.
--
Dave Hargis, Microsoft Access MVP

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

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

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

:

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

:

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


:

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


:

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


:

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
:

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


:

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
are:AddressInfoId, 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

 

[Guest]

Dave:
I tried your statement again after correcting the spelling of "Wit" to
"With". The statement will not change the record. I still get errors. I tried
changing the following
The following seem to be rejected by the program.
1) Line 3 -"Then With" seemed to cause an error. "Then End" worked OK
2) Line 5 -" .Bookmark" seemed to cause an error.
"Me.RecordsetClone.Bookmark" worked OK
3) Line 6 = "Then With" seemed to cause an error. If I delete this then the
statement worked OK

Only when I make the above changes does your statement change the record
selected by the Combo48.

Bill

The above typing error Wit should be With or
With Me.RecordsetClone

I use this method a lot, so I know it works. It is just syntax. That is
why the compiler croaks on Me.Bookmark = .Bookmark. It doesn't know what
.BookMark belongs too because the With has not been correctly established.

Read VBA Help on the End statement.
--
Dave Hargis, Microsoft Access MVP

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

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


:

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

:

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

:

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


:

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


:

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


:

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
:

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


:

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
are:AddressInfoId, 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

 

[Guest]

I don't know what you are doing and it sure seems like you are not entering
what I posted. There is no such line as Then With
The with is on a totally separate line.

If the code doesn't look like this:
Sub Combo48_AfterUpdate()
' Find the record that matches the control.'
If Not IsNull(Me![Combo48]) Then
With Me.RecordsetClone
.FindFirst "[ID] = " & Me![Combo48]
Me.Bookmark = .Bookmark
End With
End Sub

Then you are not doing it correctly. Also, if ID is not a numeric field, it
needs the correct delimiters.

Now, If you want some help, I will be glad to stick with you and getting it
to work. If you just want to argue, I have no interest.
The reason the End line is working at all is because Comb48 is not null.

If you doubt the technique works, would you like a few dozen samples from my
apps where it works just fine?
--
Dave Hargis, Microsoft Access MVP

Dave:
I tried your statement again after correcting the spelling of "Wit" to
"With". The statement will not change the record. I still get errors. I tried
changing the following
The following seem to be rejected by the program.
1) Line 3 -"Then With" seemed to cause an error. "Then End" worked OK
2) Line 5 -" .Bookmark" seemed to cause an error.
"Me.RecordsetClone.Bookmark" worked OK
3) Line 6 = "Then With" seemed to cause an error. If I delete this then the
statement worked OK

Only when I make the above changes does your statement change the record
selected by the Combo48.

Bill

Wit Me.RecordsetClone

The above typing error Wit should be With or
With Me.RecordsetClone

I use this method a lot, so I know it works. It is just syntax. That is
why the compiler croaks on Me.Bookmark = .Bookmark. It doesn't know what
.BookMark belongs too because the With has not been correctly established.

Read VBA Help on the End statement.
--
Dave Hargis, Microsoft Access MVP

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

:

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


:

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

:

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

:

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


:

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


:

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


:

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
:

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


:

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
are:AddressInfoId, 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

 

[Guest]

Dave:
Please do not even consider that I am arguing. I apologize for any wording
that may have been considered argumental. I was just stating a fact. I do not
know what I am doing wrong.

Please accept my apology.

Could you send me a sample address change code if you have everything you
need. Please put code statements in quotes so I will not misinterpret.

Thanks again,
Bill

I don't know what you are doing and it sure seems like you are not entering
what I posted. There is no such line as Then With
The with is on a totally separate line.

If the code doesn't look like this:
Sub Combo48_AfterUpdate()
' Find the record that matches the control.'
If Not IsNull(Me![Combo48]) Then
With Me.RecordsetClone
.FindFirst "[ID] = " & Me![Combo48]
Me.Bookmark = .Bookmark
End With
End Sub

Then you are not doing it correctly. Also, if ID is not a numeric field, it
needs the correct delimiters.

Now, If you want some help, I will be glad to stick with you and getting it
to work. If you just want to argue, I have no interest.
The reason the End line is working at all is because Comb48 is not null.

If you doubt the technique works, would you like a few dozen samples from my
apps where it works just fine?
--
Dave Hargis, Microsoft Access MVP

Dave:
I tried your statement again after correcting the spelling of "Wit" to
"With". The statement will not change the record. I still get errors. I tried
changing the following
The following seem to be rejected by the program.
1) Line 3 -"Then With" seemed to cause an error. "Then End" worked OK
2) Line 5 -" .Bookmark" seemed to cause an error.
"Me.RecordsetClone.Bookmark" worked OK
3) Line 6 = "Then With" seemed to cause an error. If I delete this then the
statement worked OK

Only when I make the above changes does your statement change the record
selected by the Combo48.

Bill

Wit Me.RecordsetClone
The above typing error Wit should be With or
With Me.RecordsetClone

I use this method a lot, so I know it works. It is just syntax. That is
why the compiler croaks on Me.Bookmark = .Bookmark. It doesn't know what
.BookMark belongs too because the With has not been correctly established.

Read VBA Help on the End statement.
--
Dave Hargis, Microsoft Access MVP


:

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

:

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


:

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

:

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

:

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


:

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


:

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


:

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
:

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


:

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

 

[Guest]

Sure, Bill. Sorry if I misunderstood.
Here is one
********************************************
Private Sub cboContract_AfterUpdate()

If IsNull(Me.cboContract) Then
MsgBox "No Contract Selected"
Else
With Me.RecordsetClone
.FindFirst "[ContractID] = " & Me![cboContract]
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
Me.cboContract = Null

End Sub
****************************************************

Here is another where the field I am searching is a text field
******************************************************
Private Sub TxtUName_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeUName] = '" & Me.TxtUName & "'"
Me.Bookmark = .Bookmark
End With
Me.TxtPwd.SetFocus
End Sub
******************************************************

Now, just for info purposes, the With ...End With does a couple of things.
It reduces the code you have to type in, It makes it clear what object you
are working with, and it actuall is a performance enhancer because Access
doesn't have to find and relating the propert or method to the object for
every line. Her is now it would look without the With...End With:

Private Sub TxtUName_AfterUpdate()

Me.RecordsetClone.FindFirst "[EmployeeUName] = '" & Me.TxtUName & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.TxtPwd.SetFocus
End Sub

Notice all the lines are related to Me, so you could do it this way:
With Me
.RecordsetClone.FindFirst "[EmployeeUName] = '" & .TxtUName & "'"
.Bookmark = .RecordsetClone.Bookmark
.TxtPwd.SetFocus
End With

Or you could see it written like this:

Dim rst As Recorset
Set rst = Me.RecordsetClone
rst.FindFirst "[EmployeeUName] = '" & Me.TxtUName & "'"
Me.Bookmark = rst.Bookmark
Me.TxtPwd.SetFocus
Set rst = Nothing

Each variation actually does exactly the same thing.
--
Dave Hargis, Microsoft Access MVP

Dave:
Please do not even consider that I am arguing. I apologize for any wording
that may have been considered argumental. I was just stating a fact. I do not
know what I am doing wrong.

Please accept my apology.

Could you send me a sample address change code if you have everything you
need. Please put code statements in quotes so I will not misinterpret.

Thanks again,
Bill

I don't know what you are doing and it sure seems like you are not entering
what I posted. There is no such line as Then With
The with is on a totally separate line.

If the code doesn't look like this:
Sub Combo48_AfterUpdate()
' Find the record that matches the control.'
If Not IsNull(Me![Combo48]) Then
With Me.RecordsetClone
.FindFirst "[ID] = " & Me![Combo48]
Me.Bookmark = .Bookmark
End With
End Sub

Then you are not doing it correctly. Also, if ID is not a numeric field, it
needs the correct delimiters.

Now, If you want some help, I will be glad to stick with you and getting it
to work. If you just want to argue, I have no interest.
The reason the End line is working at all is because Comb48 is not null.

If you doubt the technique works, would you like a few dozen samples from my
apps where it works just fine?
--
Dave Hargis, Microsoft Access MVP

Dave:
I tried your statement again after correcting the spelling of "Wit" to
"With". The statement will not change the record. I still get errors. I tried
changing the following
The following seem to be rejected by the program.
1) Line 3 -"Then With" seemed to cause an error. "Then End" worked OK
2) Line 5 -" .Bookmark" seemed to cause an error.
"Me.RecordsetClone.Bookmark" worked OK
3) Line 6 = "Then With" seemed to cause an error. If I delete this then the
statement worked OK

Only when I make the above changes does your statement change the record
selected by the Combo48.

Bill

:

Wit Me.RecordsetClone
The above typing error Wit should be With or
With Me.RecordsetClone

I use this method a lot, so I know it works. It is just syntax. That is
why the compiler croaks on Me.Bookmark = .Bookmark. It doesn't know what
.BookMark belongs too because the With has not been correctly established.

Read VBA Help on the End statement.
--
Dave Hargis, Microsoft Access MVP


:

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

:

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


:

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

:

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

:

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


:

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


:

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


:

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
:

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.

 

[Guest]

Dave:
I copied the "Private Sub cboContract_AfterUpdate()" statement to my After
Update Event. I changed the following to work with my database.
Private Sub Combo316_AfterUpdate()

If IsNull(Me.Combo316) Then
MsgBox "No Voter Selected"
Else
With Me.RecordsetClone
.FindFirst "[AddressInfoId] = " & Me![Combo316]
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
Me.Combo316 = Null

End Sub

Dave - The statement works to change the address but it also changes the
Voter name and Voter ID. The seems to change all the text fields to a new
voter's record.
Could the problem be the query that is a find duplicates query with criteria

=1.

Sure, Bill. Sorry if I misunderstood.
Here is one
********************************************
Private Sub cboContract_AfterUpdate()

If IsNull(Me.cboContract) Then
MsgBox "No Contract Selected"
Else
With Me.RecordsetClone
.FindFirst "[ContractID] = " & Me![cboContract]
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
Me.cboContract = Null

End Sub
****************************************************

Here is another where the field I am searching is a text field
******************************************************
Private Sub TxtUName_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeUName] = '" & Me.TxtUName & "'"
Me.Bookmark = .Bookmark
End With
Me.TxtPwd.SetFocus
End Sub
******************************************************

Now, just for info purposes, the With ...End With does a couple of things.
It reduces the code you have to type in, It makes it clear what object you
are working with, and it actuall is a performance enhancer because Access
doesn't have to find and relating the propert or method to the object for
every line. Her is now it would look without the With...End With:

Private Sub TxtUName_AfterUpdate()

Me.RecordsetClone.FindFirst "[EmployeeUName] = '" & Me.TxtUName & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.TxtPwd.SetFocus
End Sub

Notice all the lines are related to Me, so you could do it this way:
With Me
.RecordsetClone.FindFirst "[EmployeeUName] = '" & .TxtUName & "'"
.Bookmark = .RecordsetClone.Bookmark
.TxtPwd.SetFocus
End With

Or you could see it written like this:

Dim rst As Recorset
Set rst = Me.RecordsetClone
rst.FindFirst "[EmployeeUName] = '" & Me.TxtUName & "'"
Me.Bookmark = rst.Bookmark
Me.TxtPwd.SetFocus
Set rst = Nothing

Each variation actually does exactly the same thing.
--
Dave Hargis, Microsoft Access MVP

Dave:
Please do not even consider that I am arguing. I apologize for any wording
that may have been considered argumental. I was just stating a fact. I do not
know what I am doing wrong.

Please accept my apology.

Could you send me a sample address change code if you have everything you
need. Please put code statements in quotes so I will not misinterpret.

Thanks again,
Bill

I don't know what you are doing and it sure seems like you are not entering
what I posted. There is no such line as Then With
The with is on a totally separate line.

If the code doesn't look like this:
Sub Combo48_AfterUpdate()
' Find the record that matches the control.'
If Not IsNull(Me![Combo48]) Then
With Me.RecordsetClone
.FindFirst "[ID] = " & Me![Combo48]
Me.Bookmark = .Bookmark
End With
End Sub

Then you are not doing it correctly. Also, if ID is not a numeric field, it
needs the correct delimiters.

Now, If you want some help, I will be glad to stick with you and getting it
to work. If you just want to argue, I have no interest.
The reason the End line is working at all is because Comb48 is not null.

If you doubt the technique works, would you like a few dozen samples from my
apps where it works just fine?
--
Dave Hargis, Microsoft Access MVP


:

Dave:
I tried your statement again after correcting the spelling of "Wit" to
"With". The statement will not change the record. I still get errors. I tried
changing the following
The following seem to be rejected by the program.
1) Line 3 -"Then With" seemed to cause an error. "Then End" worked OK
2) Line 5 -" .Bookmark" seemed to cause an error.
"Me.RecordsetClone.Bookmark" worked OK
3) Line 6 = "Then With" seemed to cause an error. If I delete this then the
statement worked OK

Only when I make the above changes does your statement change the record
selected by the Combo48.

Bill

:

Wit Me.RecordsetClone
The above typing error Wit should be With or
With Me.RecordsetClone

I use this method a lot, so I know it works. It is just syntax. That is
why the compiler croaks on Me.Bookmark = .Bookmark. It doesn't know what
.BookMark belongs too because the With has not been correctly established.

Read VBA Help on the End statement.
--
Dave Hargis, Microsoft Access MVP


:

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

:

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


:

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

:

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

:

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


:

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


:

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


:

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

 

[Guest]

= 1 is the same as saying > 0

I thought the object was to look up a voter using combo48, then change the
voter's address by using combo316.
--
Dave Hargis, Microsoft Access MVP

Dave:
I copied the "Private Sub cboContract_AfterUpdate()" statement to my After
Update Event. I changed the following to work with my database.
Private Sub Combo316_AfterUpdate()

If IsNull(Me.Combo316) Then
MsgBox "No Voter Selected"
Else
With Me.RecordsetClone
.FindFirst "[AddressInfoId] = " & Me![Combo316]
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
Me.Combo316 = Null

End Sub

Dave - The statement works to change the address but it also changes the
Voter name and Voter ID. The seems to change all the text fields to a new
voter's record.
Could the problem be the query that is a find duplicates query with criteria

=1.

Sure, Bill. Sorry if I misunderstood.
Here is one
********************************************
Private Sub cboContract_AfterUpdate()

If IsNull(Me.cboContract) Then
MsgBox "No Contract Selected"
Else
With Me.RecordsetClone
.FindFirst "[ContractID] = " & Me![cboContract]
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
Me.cboContract = Null

End Sub
****************************************************

Here is another where the field I am searching is a text field
******************************************************
Private Sub TxtUName_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeUName] = '" & Me.TxtUName & "'"
Me.Bookmark = .Bookmark
End With
Me.TxtPwd.SetFocus
End Sub
******************************************************

Now, just for info purposes, the With ...End With does a couple of things.
It reduces the code you have to type in, It makes it clear what object you
are working with, and it actuall is a performance enhancer because Access
doesn't have to find and relating the propert or method to the object for
every line. Her is now it would look without the With...End With:

Private Sub TxtUName_AfterUpdate()

Me.RecordsetClone.FindFirst "[EmployeeUName] = '" & Me.TxtUName & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.TxtPwd.SetFocus
End Sub

Notice all the lines are related to Me, so you could do it this way:
With Me
.RecordsetClone.FindFirst "[EmployeeUName] = '" & .TxtUName & "'"
.Bookmark = .RecordsetClone.Bookmark
.TxtPwd.SetFocus
End With

Or you could see it written like this:

Dim rst As Recorset
Set rst = Me.RecordsetClone
rst.FindFirst "[EmployeeUName] = '" & Me.TxtUName & "'"
Me.Bookmark = rst.Bookmark
Me.TxtPwd.SetFocus
Set rst = Nothing

Each variation actually does exactly the same thing.
--
Dave Hargis, Microsoft Access MVP

Dave:
Please do not even consider that I am arguing. I apologize for any wording
that may have been considered argumental. I was just stating a fact. I do not
know what I am doing wrong.

Please accept my apology.

Could you send me a sample address change code if you have everything you
need. Please put code statements in quotes so I will not misinterpret.

Thanks again,
Bill

:

I don't know what you are doing and it sure seems like you are not entering
what I posted. There is no such line as Then With
The with is on a totally separate line.

If the code doesn't look like this:
Sub Combo48_AfterUpdate()
' Find the record that matches the control.'
If Not IsNull(Me![Combo48]) Then
With Me.RecordsetClone
.FindFirst "[ID] = " & Me![Combo48]
Me.Bookmark = .Bookmark
End With
End Sub

Then you are not doing it correctly. Also, if ID is not a numeric field, it
needs the correct delimiters.

Now, If you want some help, I will be glad to stick with you and getting it
to work. If you just want to argue, I have no interest.
The reason the End line is working at all is because Comb48 is not null.

If you doubt the technique works, would you like a few dozen samples from my
apps where it works just fine?
--
Dave Hargis, Microsoft Access MVP


:

Dave:
I tried your statement again after correcting the spelling of "Wit" to
"With". The statement will not change the record. I still get errors. I tried
changing the following
The following seem to be rejected by the program.
1) Line 3 -"Then With" seemed to cause an error. "Then End" worked OK
2) Line 5 -" .Bookmark" seemed to cause an error.
"Me.RecordsetClone.Bookmark" worked OK
3) Line 6 = "Then With" seemed to cause an error. If I delete this then the
statement worked OK

Only when I make the above changes does your statement change the record
selected by the Combo48.

Bill

:

Wit Me.RecordsetClone
The above typing error Wit should be With or
With Me.RecordsetClone

I use this method a lot, so I know it works. It is just syntax. That is
why the compiler croaks on Me.Bookmark = .Bookmark. It doesn't know what
.BookMark belongs too because the With has not been correctly established.

Read VBA Help on the End statement.
--
Dave Hargis, Microsoft Access MVP


:

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

:

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


:

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

:

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

:

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


:

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


:

Ok, I think I get a better drift. Boy, it sure would be nice if you used

 

[Guest]

Yes, that is correct. I look up a voter using Combo48 and want to change the
address fields using Combo316.
Could you give me further instructions as to how to change the address
fields.

= 1 is the same as saying > 0

I thought the object was to look up a voter using combo48, then change the
voter's address by using combo316.
--
Dave Hargis, Microsoft Access MVP

Dave:
I copied the "Private Sub cboContract_AfterUpdate()" statement to my After
Update Event. I changed the following to work with my database.
Private Sub Combo316_AfterUpdate()

If IsNull(Me.Combo316) Then
MsgBox "No Voter Selected"
Else
With Me.RecordsetClone
.FindFirst "[AddressInfoId] = " & Me![Combo316]
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
Me.Combo316 = Null

End Sub

Dave - The statement works to change the address but it also changes the
Voter name and Voter ID. The seems to change all the text fields to a new
voter's record.
Could the problem be the query that is a find duplicates query with criteria

=1.

Sure, Bill. Sorry if I misunderstood.
Here is one
********************************************
Private Sub cboContract_AfterUpdate()

If IsNull(Me.cboContract) Then
MsgBox "No Contract Selected"
Else
With Me.RecordsetClone
.FindFirst "[ContractID] = " & Me![cboContract]
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
Me.cboContract = Null

End Sub
****************************************************

Here is another where the field I am searching is a text field
******************************************************
Private Sub TxtUName_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeUName] = '" & Me.TxtUName & "'"
Me.Bookmark = .Bookmark
End With
Me.TxtPwd.SetFocus
End Sub
******************************************************

Now, just for info purposes, the With ...End With does a couple of things.
It reduces the code you have to type in, It makes it clear what object you
are working with, and it actuall is a performance enhancer because Access
doesn't have to find and relating the propert or method to the object for
every line. Her is now it would look without the With...End With:

Private Sub TxtUName_AfterUpdate()

Me.RecordsetClone.FindFirst "[EmployeeUName] = '" & Me.TxtUName & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.TxtPwd.SetFocus
End Sub

Notice all the lines are related to Me, so you could do it this way:
With Me
.RecordsetClone.FindFirst "[EmployeeUName] = '" & .TxtUName & "'"
.Bookmark = .RecordsetClone.Bookmark
.TxtPwd.SetFocus
End With

Or you could see it written like this:

Dim rst As Recorset
Set rst = Me.RecordsetClone
rst.FindFirst "[EmployeeUName] = '" & Me.TxtUName & "'"
Me.Bookmark = rst.Bookmark
Me.TxtPwd.SetFocus
Set rst = Nothing

Each variation actually does exactly the same thing.
--
Dave Hargis, Microsoft Access MVP


:

Dave:
Please do not even consider that I am arguing. I apologize for any wording
that may have been considered argumental. I was just stating a fact. I do not
know what I am doing wrong.

Please accept my apology.

Could you send me a sample address change code if you have everything you
need. Please put code statements in quotes so I will not misinterpret.

Thanks again,
Bill

:

I don't know what you are doing and it sure seems like you are not entering
what I posted. There is no such line as Then With
The with is on a totally separate line.

If the code doesn't look like this:
Sub Combo48_AfterUpdate()
' Find the record that matches the control.'
If Not IsNull(Me![Combo48]) Then
With Me.RecordsetClone
.FindFirst "[ID] = " & Me![Combo48]
Me.Bookmark = .Bookmark
End With
End Sub

Then you are not doing it correctly. Also, if ID is not a numeric field, it
needs the correct delimiters.

Now, If you want some help, I will be glad to stick with you and getting it
to work. If you just want to argue, I have no interest.
The reason the End line is working at all is because Comb48 is not null.

If you doubt the technique works, would you like a few dozen samples from my
apps where it works just fine?
--
Dave Hargis, Microsoft Access MVP


:

Dave:
I tried your statement again after correcting the spelling of "Wit" to
"With". The statement will not change the record. I still get errors. I tried
changing the following
The following seem to be rejected by the program.
1) Line 3 -"Then With" seemed to cause an error. "Then End" worked OK
2) Line 5 -" .Bookmark" seemed to cause an error.
"Me.RecordsetClone.Bookmark" worked OK
3) Line 6 = "Then With" seemed to cause an error. If I delete this then the
statement worked OK

Only when I make the above changes does your statement change the record
selected by the Combo48.

Bill

:

Wit Me.RecordsetClone
The above typing error Wit should be With or
With Me.RecordsetClone

I use this method a lot, so I know it works. It is just syntax. That is
why the compiler croaks on Me.Bookmark = .Bookmark. It doesn't know what
.BookMark belongs too because the With has not been correctly established.

Read VBA Help on the End statement.
--
Dave Hargis, Microsoft Access MVP


:

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

:

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


:

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

:

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

:

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


:

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

 

[Guest]

In the After Update event of combo316, populate the control in the record
that carries the foreign key of the addess. (That would be the primary key
of the record from the address table you are using).

Me.txtAddress = Me.Combo316

That assumes the key value is in the bound column, otherwise, you will have
to specify the column it is in.
--
Dave Hargis, Microsoft Access MVP

Yes, that is correct. I look up a voter using Combo48 and want to change the
address fields using Combo316.
Could you give me further instructions as to how to change the address
fields.

= 1 is the same as saying > 0

I thought the object was to look up a voter using combo48, then change the
voter's address by using combo316.
--
Dave Hargis, Microsoft Access MVP

Dave:
I copied the "Private Sub cboContract_AfterUpdate()" statement to my After
Update Event. I changed the following to work with my database.
Private Sub Combo316_AfterUpdate()

If IsNull(Me.Combo316) Then
MsgBox "No Voter Selected"
Else
With Me.RecordsetClone
.FindFirst "[AddressInfoId] = " & Me![Combo316]
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
Me.Combo316 = Null

End Sub

Dave - The statement works to change the address but it also changes the
Voter name and Voter ID. The seems to change all the text fields to a new
voter's record.
Could the problem be the query that is a find duplicates query with criteria
=1.

:

Sure, Bill. Sorry if I misunderstood.
Here is one
********************************************
Private Sub cboContract_AfterUpdate()

If IsNull(Me.cboContract) Then
MsgBox "No Contract Selected"
Else
With Me.RecordsetClone
.FindFirst "[ContractID] = " & Me![cboContract]
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
Me.cboContract = Null

End Sub
****************************************************

Here is another where the field I am searching is a text field
******************************************************
Private Sub TxtUName_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EmployeeUName] = '" & Me.TxtUName & "'"
Me.Bookmark = .Bookmark
End With
Me.TxtPwd.SetFocus
End Sub
******************************************************

Now, just for info purposes, the With ...End With does a couple of things.
It reduces the code you have to type in, It makes it clear what object you
are working with, and it actuall is a performance enhancer because Access
doesn't have to find and relating the propert or method to the object for
every line. Her is now it would look without the With...End With:

Private Sub TxtUName_AfterUpdate()

Me.RecordsetClone.FindFirst "[EmployeeUName] = '" & Me.TxtUName & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.TxtPwd.SetFocus
End Sub

Notice all the lines are related to Me, so you could do it this way:
With Me
.RecordsetClone.FindFirst "[EmployeeUName] = '" & .TxtUName & "'"
.Bookmark = .RecordsetClone.Bookmark
.TxtPwd.SetFocus
End With

Or you could see it written like this:

Dim rst As Recorset
Set rst = Me.RecordsetClone
rst.FindFirst "[EmployeeUName] = '" & Me.TxtUName & "'"
Me.Bookmark = rst.Bookmark
Me.TxtPwd.SetFocus
Set rst = Nothing

Each variation actually does exactly the same thing.
--
Dave Hargis, Microsoft Access MVP


:

Dave:
Please do not even consider that I am arguing. I apologize for any wording
that may have been considered argumental. I was just stating a fact. I do not
know what I am doing wrong.

Please accept my apology.

Could you send me a sample address change code if you have everything you
need. Please put code statements in quotes so I will not misinterpret.

Thanks again,
Bill

:

I don't know what you are doing and it sure seems like you are not entering
what I posted. There is no such line as Then With
The with is on a totally separate line.

If the code doesn't look like this:
Sub Combo48_AfterUpdate()
' Find the record that matches the control.'
If Not IsNull(Me![Combo48]) Then
With Me.RecordsetClone
.FindFirst "[ID] = " & Me![Combo48]
Me.Bookmark = .Bookmark
End With
End Sub

Then you are not doing it correctly. Also, if ID is not a numeric field, it
needs the correct delimiters.

Now, If you want some help, I will be glad to stick with you and getting it
to work. If you just want to argue, I have no interest.
The reason the End line is working at all is because Comb48 is not null.

If you doubt the technique works, would you like a few dozen samples from my
apps where it works just fine?
--
Dave Hargis, Microsoft Access MVP


:

Dave:
I tried your statement again after correcting the spelling of "Wit" to
"With". The statement will not change the record. I still get errors. I tried
changing the following
The following seem to be rejected by the program.
1) Line 3 -"Then With" seemed to cause an error. "Then End" worked OK
2) Line 5 -" .Bookmark" seemed to cause an error.
"Me.RecordsetClone.Bookmark" worked OK
3) Line 6 = "Then With" seemed to cause an error. If I delete this then the
statement worked OK

Only when I make the above changes does your statement change the record
selected by the Combo48.

Bill

:

Wit Me.RecordsetClone
The above typing error Wit should be With or
With Me.RecordsetClone

I use this method a lot, so I know it works. It is just syntax. That is
why the compiler croaks on Me.Bookmark = .Bookmark. It doesn't know what
.BookMark belongs too because the With has not been correctly established.

Read VBA Help on the End statement.
--
Dave Hargis, Microsoft Access MVP


:

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

:

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


:

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

:

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

:

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?