Change Address Fields using Combo Dropdown List

[Guest]

I have a Form based on a Table called "VoterInformationTable". The Form has
Name and Address Text Fields from the Table. I use Combo 48 to bring up a
new record on the Form. I have another Combo called "cmb_Addr_Change" that I
want to use to change the address of a record to another address I select
from the combo dropdown List.
A friend wrote the following code. I am getting a run-time error '3144'
Syntax error in update statement cmb_Addr_change.
Could someone check the statement below for errors.

Private Sub Cmb_Addr_Change_AfterUpdate()
Dim SQL_Text As String

If IsNull(Me![Cmb_Addr_Change]) Then End

SQL_Text = "UPDATE VoterInformationTable "
SQL_Text = SQL_Text + " SET [AddressInfoId] =
Me.cmb_Addr_Change.Column1),"
SQL_Text = SQL_Text + " [PED] = Me.cmb_Addr_Change.Column(2),"
SQL_Text = SQL_Text + " [Poll] = Me.cmb_Addr_Change.Column(3),"
SQL_Text = SQL_Text + " [Street] = Me.cmb_Addr_Change.Column(4),"
SQL_Text = SQL_Text + " [City] = Me.cmb_Addr_Change.Column(5),"
SQL_Text = SQL_Text + " [St#] = Me.Cmb_Addr_Change.Column (6),"
SQL_Text = SQL_Text + " [StSuffix] = Me.cmb_Addr_Change.Column(7),"
SQL_Text = SQL_Text + " [Street Type] = Me.cmb_Addr_Change.Column(8),"
SQL_Text = SQL_Text + " [StreetDir] = Me.cmb_Addr_Change.Column(9),"
SQL_Text = SQL_Text + " [Apt#] = Me.cmb_Addr_Change.Column(10),"
SQL_Text = SQL_Text + " [Prov] = Me.cmb_Addr_Change.Column(11),"
SQL_Text = SQL_Text + " [Postal Code] = Me.cmb_Addr_Change.Column(12),"
SQL_Text = SQL_Text + " [AddressWithoutPostalCode] =
Me.cmb_Addr_Change.Column(13),"
SQL_Text = SQL_Text + " [ProvDistrictDescE] =
Me.cmb_Addr_Change.Column(14),"
SQL_Text = SQL_Text + " WHERE [ID]= Me.TxtLbl"

DoCmd.RunSQL (SQL_Text)

End Sub

 

[John W. Vinson]

I have a Form based on a Table called "VoterInformationTable". The Form has
Name and Address Text Fields from the Table. I use Combo 48 to bring up a
new record on the Form. I have another Combo called "cmb_Addr_Change" that I
want to use to change the address of a record to another address I select
from the combo dropdown List.
A friend wrote the following code. I am getting a run-time error '3144'
Syntax error in update statement cmb_Addr_change.
Could someone check the statement below for errors.

This seems a convoluted way to do this - but I suspect that the error is
because Text fields require quotemarks, and you should be concatenating the
value in the combo columns rather than including the control reference in the
SQL. Try

SQL_Text = "UPDATE VoterInformationTable "
SQL_Text = SQL_Text + " SET [AddressInfoId] = " & _
Me.cmb_Addr_Change.Column1) & ","
SQL_Text = SQL_Text + " [PED] = '" & Me.cmb_Addr_Change.Column(2) & "',"
SQL_Text = SQL_Text + " [Poll] = '" & Me.cmb_Addr_Change.Column(3) & "',"

and so on; omit the ' delmiters for fields that are not of Text datatype.

John W. Vinson [MVP]