Change Address Fields with Combo on Form

B

BillD

I have an Access Database with a Table that includes Constituents Names and
Addresses. I use an unbound Combo to bring up a dropdown list of
Constituents. When I select a record from the list the form shows the info
for the new Constituent(person) selected.
I have another Unbound Combo on the form that I want to use to change the
address fields and a couple other fields for the person to the new address
selected.
Currently the dropdown list in the "cmb_addr_change" lists the address from
the Constituents Table. When I select an address from the list the fields for
the address are changed to the address fields selected. This works good. The
PROBLEM I am having is when I select an address from the drop-down list there
is a box that comes up before the fields for the record are changed. The box
states "Are you sure you want to update this record?" Yes or No. If I select
Yes the address fields are changed to the address selected and the box
closes. If I select No then a Run-Time error '2501' appears. Run SQL action
was canceled. How do I correct this? If I select No I just want the box to
close and not make the address changes to the record. Here is the code I have
in the After Update Event Procedure.
Private Sub cmb_Addr_change_AfterUpdate()

Dim SQL_Text As String

SQL_Text = "UPDATE VoterInformationTable "
SQL_Text = SQL_Text & " SET [PED] = " & Me.cmb_Addr_change.Column(1) & ","
SQL_Text = SQL_Text & " [Poll] = " & Me.cmb_Addr_change.Column(2) & ","
SQL_Text = SQL_Text & " [St#] = " & Me.cmb_Addr_change.Column(3) & ","
SQL_Text = SQL_Text & " [AddressInfoId] = " &
Me.cmb_Addr_change.Column(0) & ","
SQL_Text = SQL_Text & " [StSuffix] = '" & Me.cmb_Addr_change.Column(4) &
" ',"
SQL_Text = SQL_Text & " [Street] = '" & Me.cmb_Addr_change.Column(5) & "
',"
SQL_Text = SQL_Text & " [Street Type] = '" &
Me.cmb_Addr_change.Column(6) & " ',"
SQL_Text = SQL_Text & " [StreetDir] = '" & Me.cmb_Addr_change.Column(7)
& " ',"
SQL_Text = SQL_Text & " [Apt#] = '" & Me.cmb_Addr_change.Column(8) & " ',"
SQL_Text = SQL_Text & " [City] = '" & Me.cmb_Addr_change.Column(9) & " ',"
SQL_Text = SQL_Text & " [Prov] = '" & Me.cmb_Addr_change.Column(10) & "
',"
SQL_Text = SQL_Text & " [Postal Code] = '" &
Me.cmb_Addr_change.Column(11) & " ' "
SQL_Text = SQL_Text & " WHERE [ID]= " & (Me.txtLbl)

DoCmd.RunSQL (SQL_Text)
SQL_Text = ""
MsgBox "Completed"
End Sub
 
S

Steve Sanford

Hi Bill,

Here is your code modified. If you don't want the question "Are you sure...",
delete the lines I added EXECPT the lines:

CurrentDb.Execute SQL_Text, dbFailOnError 'update record
MsgBox "Update Completed"


Check out Help for the differences between "DoCmd.RunSQL " and
"CurrentDb.Execute".

'----beg code--------
Private Sub cmb_Addr_change_AfterUpdate()

Dim SQL_Text As String

'these are variants
Dim Msg, Style, Title, Response

Msg = "Are you sure you want to update this record?" ' Define message.
Style = vbYesNo + vbQuestion + vbDefaultButton2 ' Define buttons.
Title = "Update Address" ' Define title.

SQL_Text = "UPDATE VoterInformationTable "
SQL_Text = SQL_Text & " SET [PED] = " & Me.cmb_Addr_change.Column(1) & ","
SQL_Text = SQL_Text & " [Poll] = " & Me.cmb_Addr_change.Column(2) & ","
SQL_Text = SQL_Text & " [St#] = " & Me.cmb_Addr_change.Column(3) & ","
SQL_Text = SQL_Text & " [AddressInfoId] = " & Me.cmb_Addr_change.Column(0)
& ","
SQL_Text = SQL_Text & " [StSuffix] = '" & Me.cmb_Addr_change.Column(4) & "
',"
SQL_Text = SQL_Text & " [Street] = '" & Me.cmb_Addr_change.Column(5) & " ',"
SQL_Text = SQL_Text & " [Street Type] = '" & Me.cmb_Addr_change.Column(6)
& " ',"
SQL_Text = SQL_Text & " [StreetDir] = '" & Me.cmb_Addr_change.Column(7) &
" ',"
SQL_Text = SQL_Text & " [Apt#] = '" & Me.cmb_Addr_change.Column(8) & " ',"
SQL_Text = SQL_Text & " [City] = '" & Me.cmb_Addr_change.Column(9) & " ',"
SQL_Text = SQL_Text & " [Prov] = '" & Me.cmb_Addr_change.Column(10) & " ',"
SQL_Text = SQL_Text & " [Postal Code] = '" & Me.cmb_Addr_change.Column(11)
& " ' "
SQL_Text = SQL_Text & " WHERE [ID]= " & (Me.txtLbl)

' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
CurrentDb.Execute SQL_Text, dbFailOnError 'update record
MsgBox "Update Completed"
Else ' User chose No.
MsgBox "Update canceled by user" ' Cancel update.
End If

End Sub
'----end code--------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


BillD said:
I have an Access Database with a Table that includes Constituents Names and
Addresses. I use an unbound Combo to bring up a dropdown list of
Constituents. When I select a record from the list the form shows the info
for the new Constituent(person) selected.
I have another Unbound Combo on the form that I want to use to change the
address fields and a couple other fields for the person to the new address
selected.
Currently the dropdown list in the "cmb_addr_change" lists the address from
the Constituents Table. When I select an address from the list the fields for
the address are changed to the address fields selected. This works good. The
PROBLEM I am having is when I select an address from the drop-down list there
is a box that comes up before the fields for the record are changed. The box
states "Are you sure you want to update this record?" Yes or No. If I select
Yes the address fields are changed to the address selected and the box
closes. If I select No then a Run-Time error '2501' appears. Run SQL action
was canceled. How do I correct this? If I select No I just want the box to
close and not make the address changes to the record. Here is the code I have
in the After Update Event Procedure.
Private Sub cmb_Addr_change_AfterUpdate()

Dim SQL_Text As String

SQL_Text = "UPDATE VoterInformationTable "
SQL_Text = SQL_Text & " SET [PED] = " & Me.cmb_Addr_change.Column(1) & ","
SQL_Text = SQL_Text & " [Poll] = " & Me.cmb_Addr_change.Column(2) & ","
SQL_Text = SQL_Text & " [St#] = " & Me.cmb_Addr_change.Column(3) & ","
SQL_Text = SQL_Text & " [AddressInfoId] = " &
Me.cmb_Addr_change.Column(0) & ","
SQL_Text = SQL_Text & " [StSuffix] = '" & Me.cmb_Addr_change.Column(4) &
" ',"
SQL_Text = SQL_Text & " [Street] = '" & Me.cmb_Addr_change.Column(5) & "
',"
SQL_Text = SQL_Text & " [Street Type] = '" &
Me.cmb_Addr_change.Column(6) & " ',"
SQL_Text = SQL_Text & " [StreetDir] = '" & Me.cmb_Addr_change.Column(7)
& " ',"
SQL_Text = SQL_Text & " [Apt#] = '" & Me.cmb_Addr_change.Column(8) & " ',"
SQL_Text = SQL_Text & " [City] = '" & Me.cmb_Addr_change.Column(9) & " ',"
SQL_Text = SQL_Text & " [Prov] = '" & Me.cmb_Addr_change.Column(10) & "
',"
SQL_Text = SQL_Text & " [Postal Code] = '" &
Me.cmb_Addr_change.Column(11) & " ' "
SQL_Text = SQL_Text & " WHERE [ID]= " & (Me.txtLbl)

DoCmd.RunSQL (SQL_Text)
SQL_Text = ""
MsgBox "Completed"
End Sub
Click to expand...
 
B

billyd

Steve:
Your code worked perfectly. Thanks so much for your help. I am not a
programmer, so I have a problem understanding lines of code. I have been
trying to get this address update event to work for months. Other posts were
helpful but your answer is the only one I have had that gave me the complete
code changes.
There is now only one last item that I have to deal with concerning the
Address update. Hoping that you may have an easy solution.
One of the fields in the Address Update combo is called "Street Type" in
Column 6.
Some of the records have data in this field that is causing an error when I
select a street address to update. Example (rue de l') in the Street field.
The ' at the end of the field data is causing an error when the event is
executed. Any suggestions. All the fields in my table are set to not allow 0
length strings.


Steve Sanford said:
Hi Bill,

Here is your code modified. If you don't want the question "Are you sure...",
delete the lines I added EXECPT the lines:

CurrentDb.Execute SQL_Text, dbFailOnError 'update record
MsgBox "Update Completed"


Check out Help for the differences between "DoCmd.RunSQL " and
"CurrentDb.Execute".

'----beg code--------
Private Sub cmb_Addr_change_AfterUpdate()

Dim SQL_Text As String

'these are variants
Dim Msg, Style, Title, Response

Msg = "Are you sure you want to update this record?" ' Define message.
Style = vbYesNo + vbQuestion + vbDefaultButton2 ' Define buttons.
Title = "Update Address" ' Define title.

SQL_Text = "UPDATE VoterInformationTable "
SQL_Text = SQL_Text & " SET [PED] = " & Me.cmb_Addr_change.Column(1) & ","
SQL_Text = SQL_Text & " [Poll] = " & Me.cmb_Addr_change.Column(2) & ","
SQL_Text = SQL_Text & " [St#] = " & Me.cmb_Addr_change.Column(3) & ","
SQL_Text = SQL_Text & " [AddressInfoId] = " & Me.cmb_Addr_change.Column(0)
& ","
SQL_Text = SQL_Text & " [StSuffix] = '" & Me.cmb_Addr_change.Column(4) & "
',"
SQL_Text = SQL_Text & " [Street] = '" & Me.cmb_Addr_change.Column(5) & " ',"
SQL_Text = SQL_Text & " [Street Type] = '" & Me.cmb_Addr_change.Column(6)
& " ',"
SQL_Text = SQL_Text & " [StreetDir] = '" & Me.cmb_Addr_change.Column(7) &
" ',"
SQL_Text = SQL_Text & " [Apt#] = '" & Me.cmb_Addr_change.Column(8) & " ',"
SQL_Text = SQL_Text & " [City] = '" & Me.cmb_Addr_change.Column(9) & " ',"
SQL_Text = SQL_Text & " [Prov] = '" & Me.cmb_Addr_change.Column(10) & " ',"
SQL_Text = SQL_Text & " [Postal Code] = '" & Me.cmb_Addr_change.Column(11)
& " ' "
SQL_Text = SQL_Text & " WHERE [ID]= " & (Me.txtLbl)

' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
CurrentDb.Execute SQL_Text, dbFailOnError 'update record
MsgBox "Update Completed"
Else ' User chose No.
MsgBox "Update canceled by user" ' Cancel update.
End If

End Sub
'----end code--------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


BillD said:
I have an Access Database with a Table that includes Constituents Names and
Addresses. I use an unbound Combo to bring up a dropdown list of
Constituents. When I select a record from the list the form shows the info
for the new Constituent(person) selected.
I have another Unbound Combo on the form that I want to use to change the
address fields and a couple other fields for the person to the new address
selected.
Currently the dropdown list in the "cmb_addr_change" lists the address from
the Constituents Table. When I select an address from the list the fields for
the address are changed to the address fields selected. This works good. The
PROBLEM I am having is when I select an address from the drop-down list there
is a box that comes up before the fields for the record are changed. The box
states "Are you sure you want to update this record?" Yes or No. If I select
Yes the address fields are changed to the address selected and the box
closes. If I select No then a Run-Time error '2501' appears. Run SQL action
was canceled. How do I correct this? If I select No I just want the box to
close and not make the address changes to the record. Here is the code I have
in the After Update Event Procedure.
Private Sub cmb_Addr_change_AfterUpdate()

Dim SQL_Text As String

SQL_Text = "UPDATE VoterInformationTable "
SQL_Text = SQL_Text & " SET [PED] = " & Me.cmb_Addr_change.Column(1) & ","
SQL_Text = SQL_Text & " [Poll] = " & Me.cmb_Addr_change.Column(2) & ","
SQL_Text = SQL_Text & " [St#] = " & Me.cmb_Addr_change.Column(3) & ","
SQL_Text = SQL_Text & " [AddressInfoId] = " &
Me.cmb_Addr_change.Column(0) & ","
SQL_Text = SQL_Text & " [StSuffix] = '" & Me.cmb_Addr_change.Column(4) &
" ',"
SQL_Text = SQL_Text & " [Street] = '" & Me.cmb_Addr_change.Column(5) & "
',"
SQL_Text = SQL_Text & " [Street Type] = '" &
Me.cmb_Addr_change.Column(6) & " ',"
SQL_Text = SQL_Text & " [StreetDir] = '" & Me.cmb_Addr_change.Column(7)
& " ',"
SQL_Text = SQL_Text & " [Apt#] = '" & Me.cmb_Addr_change.Column(8) & " ',"
SQL_Text = SQL_Text & " [City] = '" & Me.cmb_Addr_change.Column(9) & " ',"
SQL_Text = SQL_Text & " [Prov] = '" & Me.cmb_Addr_change.Column(10) & "
',"
SQL_Text = SQL_Text & " [Postal Code] = '" &
Me.cmb_Addr_change.Column(11) & " ' "
SQL_Text = SQL_Text & " WHERE [ID]= " & (Me.txtLbl)

DoCmd.RunSQL (SQL_Text)
SQL_Text = ""
MsgBox "Completed"
End Sub
Click to expand...
Click to expand...
 
P

Pieter Wijnen

The reason is that the SQL uses (') to delimit the text fields
you therefore have to add an extra (') in the actual text to create a proper
SQL string

you have to change
SQL_Text = SQL_Text & " [Street Type] = '" & Me.cmb_Addr_change.Column(6) &
" ',"
to
SQL_Text = SQL_Text & " [Street Type] = '" &
VBA.Replace(Me.cmb_Addr_change.Column(6),"'","''") & " ',"

HtH

Pieter

billyd said:
Steve:
Your code worked perfectly. Thanks so much for your help. I am not a
programmer, so I have a problem understanding lines of code. I have been
trying to get this address update event to work for months. Other posts
were
helpful but your answer is the only one I have had that gave me the
complete
code changes.
There is now only one last item that I have to deal with concerning the
Address update. Hoping that you may have an easy solution.
One of the fields in the Address Update combo is called "Street Type" in
Column 6.
Some of the records have data in this field that is causing an error when
I
select a street address to update. Example (rue de l') in the Street
field.
The ' at the end of the field data is causing an error when the event is
executed. Any suggestions. All the fields in my table are set to not allow
0
length strings.


Steve Sanford said:
Hi Bill,

Here is your code modified. If you don't want the question "Are you
sure...",
delete the lines I added EXECPT the lines:

CurrentDb.Execute SQL_Text, dbFailOnError 'update record
MsgBox "Update Completed"


Check out Help for the differences between "DoCmd.RunSQL " and
"CurrentDb.Execute".

'----beg code--------
Private Sub cmb_Addr_change_AfterUpdate()

Dim SQL_Text As String

'these are variants
Dim Msg, Style, Title, Response

Msg = "Are you sure you want to update this record?" ' Define
message.
Style = vbYesNo + vbQuestion + vbDefaultButton2 ' Define buttons.
Title = "Update Address" ' Define title.

SQL_Text = "UPDATE VoterInformationTable "
SQL_Text = SQL_Text & " SET [PED] = " & Me.cmb_Addr_change.Column(1) &
","
SQL_Text = SQL_Text & " [Poll] = " & Me.cmb_Addr_change.Column(2) & ","
SQL_Text = SQL_Text & " [St#] = " & Me.cmb_Addr_change.Column(3) & ","
SQL_Text = SQL_Text & " [AddressInfoId] = " &
Me.cmb_Addr_change.Column(0)
& ","
SQL_Text = SQL_Text & " [StSuffix] = '" & Me.cmb_Addr_change.Column(4)
& "
',"
SQL_Text = SQL_Text & " [Street] = '" & Me.cmb_Addr_change.Column(5) &
" ',"
SQL_Text = SQL_Text & " [Street Type] = '" &
Me.cmb_Addr_change.Column(6)
& " ',"
SQL_Text = SQL_Text & " [StreetDir] = '" & Me.cmb_Addr_change.Column(7)
&
" ',"
SQL_Text = SQL_Text & " [Apt#] = '" & Me.cmb_Addr_change.Column(8) & "
',"
SQL_Text = SQL_Text & " [City] = '" & Me.cmb_Addr_change.Column(9) & "
',"
SQL_Text = SQL_Text & " [Prov] = '" & Me.cmb_Addr_change.Column(10) & "
',"
SQL_Text = SQL_Text & " [Postal Code] = '" &
Me.cmb_Addr_change.Column(11)
& " ' "
SQL_Text = SQL_Text & " WHERE [ID]= " & (Me.txtLbl)

' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
CurrentDb.Execute SQL_Text, dbFailOnError 'update record
MsgBox "Update Completed"
Else ' User chose No.
MsgBox "Update canceled by user" ' Cancel update.
End If

End Sub
'----end code--------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


BillD said:
I have an Access Database with a Table that includes Constituents Names
and
Addresses. I use an unbound Combo to bring up a dropdown list of
Constituents. When I select a record from the list the form shows the
info
for the new Constituent(person) selected.
I have another Unbound Combo on the form that I want to use to change
the
address fields and a couple other fields for the person to the new
address
selected.
Currently the dropdown list in the "cmb_addr_change" lists the address
from
the Constituents Table. When I select an address from the list the
fields for
the address are changed to the address fields selected. This works
good. The
PROBLEM I am having is when I select an address from the drop-down list
there
is a box that comes up before the fields for the record are changed.
The box
states "Are you sure you want to update this record?" Yes or No. If I
select
Yes the address fields are changed to the address selected and the box
closes. If I select No then a Run-Time error '2501' appears. Run SQL
action
was canceled. How do I correct this? If I select No I just want the box
to
close and not make the address changes to the record. Here is the code
I have
in the After Update Event Procedure.
Private Sub cmb_Addr_change_AfterUpdate()

Dim SQL_Text As String

SQL_Text = "UPDATE VoterInformationTable "
SQL_Text = SQL_Text & " SET [PED] = " &
Me.cmb_Addr_change.Column(1) & ","
SQL_Text = SQL_Text & " [Poll] = " & Me.cmb_Addr_change.Column(2) &
","
SQL_Text = SQL_Text & " [St#] = " & Me.cmb_Addr_change.Column(3) &
","
SQL_Text = SQL_Text & " [AddressInfoId] = " &
Me.cmb_Addr_change.Column(0) & ","
SQL_Text = SQL_Text & " [StSuffix] = '" &
Me.cmb_Addr_change.Column(4) &
" ',"
SQL_Text = SQL_Text & " [Street] = '" &
Me.cmb_Addr_change.Column(5) & "
',"
SQL_Text = SQL_Text & " [Street Type] = '" &
Me.cmb_Addr_change.Column(6) & " ',"
SQL_Text = SQL_Text & " [StreetDir] = '" &
Me.cmb_Addr_change.Column(7)
& " ',"
SQL_Text = SQL_Text & " [Apt#] = '" & Me.cmb_Addr_change.Column(8)
& " ',"
SQL_Text = SQL_Text & " [City] = '" & Me.cmb_Addr_change.Column(9)
& " ',"
SQL_Text = SQL_Text & " [Prov] = '" & Me.cmb_Addr_change.Column(10)
& "
',"
SQL_Text = SQL_Text & " [Postal Code] = '" &
Me.cmb_Addr_change.Column(11) & " ' "
SQL_Text = SQL_Text & " WHERE [ID]= " & (Me.txtLbl)

DoCmd.RunSQL (SQL_Text)
SQL_Text = ""
MsgBox "Completed"
End Sub
Click to expand...
Click to expand...
Click to expand...
 
B

billyd

I will give that a try. Thanks so much for your help.


Pieter Wijnen said:
The reason is that the SQL uses (') to delimit the text fields
you therefore have to add an extra (') in the actual text to create a proper
SQL string

you have to change
SQL_Text = SQL_Text & " [Street Type] = '" & Me.cmb_Addr_change.Column(6) &
" ',"
to
SQL_Text = SQL_Text & " [Street Type] = '" &
VBA.Replace(Me.cmb_Addr_change.Column(6),"'","''") & " ',"

HtH

Pieter

billyd said:
Steve:
Your code worked perfectly. Thanks so much for your help. I am not a
programmer, so I have a problem understanding lines of code. I have been
trying to get this address update event to work for months. Other posts
were
helpful but your answer is the only one I have had that gave me the
complete
code changes.
There is now only one last item that I have to deal with concerning the
Address update. Hoping that you may have an easy solution.
One of the fields in the Address Update combo is called "Street Type" in
Column 6.
Some of the records have data in this field that is causing an error when
I
select a street address to update. Example (rue de l') in the Street
field.
The ' at the end of the field data is causing an error when the event is
executed. Any suggestions. All the fields in my table are set to not allow
0
length strings.


Steve Sanford said:
Hi Bill,

Here is your code modified. If you don't want the question "Are you
sure...",
delete the lines I added EXECPT the lines:

CurrentDb.Execute SQL_Text, dbFailOnError 'update record
MsgBox "Update Completed"


Check out Help for the differences between "DoCmd.RunSQL " and
"CurrentDb.Execute".

'----beg code--------
Private Sub cmb_Addr_change_AfterUpdate()

Dim SQL_Text As String

'these are variants
Dim Msg, Style, Title, Response

Msg = "Are you sure you want to update this record?" ' Define
message.
Style = vbYesNo + vbQuestion + vbDefaultButton2 ' Define buttons.
Title = "Update Address" ' Define title.

SQL_Text = "UPDATE VoterInformationTable "
SQL_Text = SQL_Text & " SET [PED] = " & Me.cmb_Addr_change.Column(1) &
","
SQL_Text = SQL_Text & " [Poll] = " & Me.cmb_Addr_change.Column(2) & ","
SQL_Text = SQL_Text & " [St#] = " & Me.cmb_Addr_change.Column(3) & ","
SQL_Text = SQL_Text & " [AddressInfoId] = " &
Me.cmb_Addr_change.Column(0)
& ","
SQL_Text = SQL_Text & " [StSuffix] = '" & Me.cmb_Addr_change.Column(4)
& "
',"
SQL_Text = SQL_Text & " [Street] = '" & Me.cmb_Addr_change.Column(5) &
" ',"
SQL_Text = SQL_Text & " [Street Type] = '" &
Me.cmb_Addr_change.Column(6)
& " ',"
SQL_Text = SQL_Text & " [StreetDir] = '" & Me.cmb_Addr_change.Column(7)
&
" ',"
SQL_Text = SQL_Text & " [Apt#] = '" & Me.cmb_Addr_change.Column(8) & "
',"
SQL_Text = SQL_Text & " [City] = '" & Me.cmb_Addr_change.Column(9) & "
',"
SQL_Text = SQL_Text & " [Prov] = '" & Me.cmb_Addr_change.Column(10) & "
',"
SQL_Text = SQL_Text & " [Postal Code] = '" &
Me.cmb_Addr_change.Column(11)
& " ' "
SQL_Text = SQL_Text & " WHERE [ID]= " & (Me.txtLbl)

' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
CurrentDb.Execute SQL_Text, dbFailOnError 'update record
MsgBox "Update Completed"
Else ' User chose No.
MsgBox "Update canceled by user" ' Cancel update.
End If

End Sub
'----end code--------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

I have an Access Database with a Table that includes Constituents Names
and
Addresses. I use an unbound Combo to bring up a dropdown list of
Constituents. When I select a record from the list the form shows the
info
for the new Constituent(person) selected.
I have another Unbound Combo on the form that I want to use to change
the
address fields and a couple other fields for the person to the new
address
selected.
Currently the dropdown list in the "cmb_addr_change" lists the address
from
the Constituents Table. When I select an address from the list the
fields for
the address are changed to the address fields selected. This works
good. The
PROBLEM I am having is when I select an address from the drop-down list
there
is a box that comes up before the fields for the record are changed.
The box
states "Are you sure you want to update this record?" Yes or No. If I
select
Yes the address fields are changed to the address selected and the box
closes. If I select No then a Run-Time error '2501' appears. Run SQL
action
was canceled. How do I correct this? If I select No I just want the box
to
close and not make the address changes to the record. Here is the code
I have
in the After Update Event Procedure.
Private Sub cmb_Addr_change_AfterUpdate()

Dim SQL_Text As String

SQL_Text = "UPDATE VoterInformationTable "
SQL_Text = SQL_Text & " SET [PED] = " &
Me.cmb_Addr_change.Column(1) & ","
SQL_Text = SQL_Text & " [Poll] = " & Me.cmb_Addr_change.Column(2) &
","
SQL_Text = SQL_Text & " [St#] = " & Me.cmb_Addr_change.Column(3) &
","
SQL_Text = SQL_Text & " [AddressInfoId] = " &
Me.cmb_Addr_change.Column(0) & ","
SQL_Text = SQL_Text & " [StSuffix] = '" &
Me.cmb_Addr_change.Column(4) &
" ',"
SQL_Text = SQL_Text & " [Street] = '" &
Me.cmb_Addr_change.Column(5) & "
',"
SQL_Text = SQL_Text & " [Street Type] = '" &
Me.cmb_Addr_change.Column(6) & " ',"
SQL_Text = SQL_Text & " [StreetDir] = '" &
Me.cmb_Addr_change.Column(7)
& " ',"
SQL_Text = SQL_Text & " [Apt#] = '" & Me.cmb_Addr_change.Column(8)
& " ',"
SQL_Text = SQL_Text & " [City] = '" & Me.cmb_Addr_change.Column(9)
& " ',"
SQL_Text = SQL_Text & " [Prov] = '" & Me.cmb_Addr_change.Column(10)
& "
',"
SQL_Text = SQL_Text & " [Postal Code] = '" &
Me.cmb_Addr_change.Column(11) & " ' "
SQL_Text = SQL_Text & " WHERE [ID]= " & (Me.txtLbl)

DoCmd.RunSQL (SQL_Text)
SQL_Text = ""
MsgBox "Completed"
End Sub
Click to expand...
Click to expand...
Click to expand...
 
B

billyd

Pieter: That worked perfectly. WOW! I finally have an Address update Event
that works perfectly.
Thanks again
Bill Depow, Fredericton, New Brunswick, Canada

Pieter Wijnen said:
The reason is that the SQL uses (') to delimit the text fields
you therefore have to add an extra (') in the actual text to create a proper
SQL string

you have to change
SQL_Text = SQL_Text & " [Street Type] = '" & Me.cmb_Addr_change.Column(6) &
" ',"
to
SQL_Text = SQL_Text & " [Street Type] = '" &
VBA.Replace(Me.cmb_Addr_change.Column(6),"'","''") & " ',"

HtH

Pieter

billyd said:
Steve:
Your code worked perfectly. Thanks so much for your help. I am not a
programmer, so I have a problem understanding lines of code. I have been
trying to get this address update event to work for months. Other posts
were
helpful but your answer is the only one I have had that gave me the
complete
code changes.
There is now only one last item that I have to deal with concerning the
Address update. Hoping that you may have an easy solution.
One of the fields in the Address Update combo is called "Street Type" in
Column 6.
Some of the records have data in this field that is causing an error when
I
select a street address to update. Example (rue de l') in the Street
field.
The ' at the end of the field data is causing an error when the event is
executed. Any suggestions. All the fields in my table are set to not allow
0
length strings.


Steve Sanford said:
Hi Bill,

Here is your code modified. If you don't want the question "Are you
sure...",
delete the lines I added EXECPT the lines:

CurrentDb.Execute SQL_Text, dbFailOnError 'update record
MsgBox "Update Completed"


Check out Help for the differences between "DoCmd.RunSQL " and
"CurrentDb.Execute".

'----beg code--------
Private Sub cmb_Addr_change_AfterUpdate()

Dim SQL_Text As String

'these are variants
Dim Msg, Style, Title, Response

Msg = "Are you sure you want to update this record?" ' Define
message.
Style = vbYesNo + vbQuestion + vbDefaultButton2 ' Define buttons.
Title = "Update Address" ' Define title.

SQL_Text = "UPDATE VoterInformationTable "
SQL_Text = SQL_Text & " SET [PED] = " & Me.cmb_Addr_change.Column(1) &
","
SQL_Text = SQL_Text & " [Poll] = " & Me.cmb_Addr_change.Column(2) & ","
SQL_Text = SQL_Text & " [St#] = " & Me.cmb_Addr_change.Column(3) & ","
SQL_Text = SQL_Text & " [AddressInfoId] = " &
Me.cmb_Addr_change.Column(0)
& ","
SQL_Text = SQL_Text & " [StSuffix] = '" & Me.cmb_Addr_change.Column(4)
& "
',"
SQL_Text = SQL_Text & " [Street] = '" & Me.cmb_Addr_change.Column(5) &
" ',"
SQL_Text = SQL_Text & " [Street Type] = '" &
Me.cmb_Addr_change.Column(6)
& " ',"
SQL_Text = SQL_Text & " [StreetDir] = '" & Me.cmb_Addr_change.Column(7)
&
" ',"
SQL_Text = SQL_Text & " [Apt#] = '" & Me.cmb_Addr_change.Column(8) & "
',"
SQL_Text = SQL_Text & " [City] = '" & Me.cmb_Addr_change.Column(9) & "
',"
SQL_Text = SQL_Text & " [Prov] = '" & Me.cmb_Addr_change.Column(10) & "
',"
SQL_Text = SQL_Text & " [Postal Code] = '" &
Me.cmb_Addr_change.Column(11)
& " ' "
SQL_Text = SQL_Text & " WHERE [ID]= " & (Me.txtLbl)

' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
CurrentDb.Execute SQL_Text, dbFailOnError 'update record
MsgBox "Update Completed"
Else ' User chose No.
MsgBox "Update canceled by user" ' Cancel update.
End If

End Sub
'----end code--------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


:

I have an Access Database with a Table that includes Constituents Names
and
Addresses. I use an unbound Combo to bring up a dropdown list of
Constituents. When I select a record from the list the form shows the
info
for the new Constituent(person) selected.
I have another Unbound Combo on the form that I want to use to change
the
address fields and a couple other fields for the person to the new
address
selected.
Currently the dropdown list in the "cmb_addr_change" lists the address
from
the Constituents Table. When I select an address from the list the
fields for
the address are changed to the address fields selected. This works
good. The
PROBLEM I am having is when I select an address from the drop-down list
there
is a box that comes up before the fields for the record are changed.
The box
states "Are you sure you want to update this record?" Yes or No. If I
select
Yes the address fields are changed to the address selected and the box
closes. If I select No then a Run-Time error '2501' appears. Run SQL
action
was canceled. How do I correct this? If I select No I just want the box
to
close and not make the address changes to the record. Here is the code
I have
in the After Update Event Procedure.
Private Sub cmb_Addr_change_AfterUpdate()

Dim SQL_Text As String

SQL_Text = "UPDATE VoterInformationTable "
SQL_Text = SQL_Text & " SET [PED] = " &
Me.cmb_Addr_change.Column(1) & ","
SQL_Text = SQL_Text & " [Poll] = " & Me.cmb_Addr_change.Column(2) &
","
SQL_Text = SQL_Text & " [St#] = " & Me.cmb_Addr_change.Column(3) &
","
SQL_Text = SQL_Text & " [AddressInfoId] = " &
Me.cmb_Addr_change.Column(0) & ","
SQL_Text = SQL_Text & " [StSuffix] = '" &
Me.cmb_Addr_change.Column(4) &
" ',"
SQL_Text = SQL_Text & " [Street] = '" &
Me.cmb_Addr_change.Column(5) & "
',"
SQL_Text = SQL_Text & " [Street Type] = '" &
Me.cmb_Addr_change.Column(6) & " ',"
SQL_Text = SQL_Text & " [StreetDir] = '" &
Me.cmb_Addr_change.Column(7)
& " ',"
SQL_Text = SQL_Text & " [Apt#] = '" & Me.cmb_Addr_change.Column(8)
& " ',"
SQL_Text = SQL_Text & " [City] = '" & Me.cmb_Addr_change.Column(9)
& " ',"
SQL_Text = SQL_Text & " [Prov] = '" & Me.cmb_Addr_change.Column(10)
& "
',"
SQL_Text = SQL_Text & " [Postal Code] = '" &
Me.cmb_Addr_change.Column(11) & " ' "
SQL_Text = SQL_Text & " WHERE [ID]= " & (Me.txtLbl)

DoCmd.RunSQL (SQL_Text)
SQL_Text = ""
MsgBox "Completed"
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Change Address Fields using Combo Dropdown List 1
Change address Combo on form 13
Use Form to generate Query for a Report 3
Dynamic Naming of Form Button 3
Change Address Field data in a Table from a form using Combo Drop 7
Creating a Search using a combo box and then creating a report. 4
Change address fields in Table using Unbound Combo Box 18
Combo box 0

Top