Re: Change Address Fields with Combo on Form

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


"Pieter Wijnen" wrote:

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" <billyd@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D11FD740-BF43-4818-ACA5-235DBAF5427B@xxxxxxxxxxxxxxxx
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" wrote:

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" wrote:

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



.