Change address Combo on form

G

Guest

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 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
 
G

Guest

Hi Bill,
I assume you show the msgbox In the before update of your combo.
If you do that in this way it must work

If msgbox("Are you sure you want to update this record?",vbyesno)=vbno then
cancel=true
endif

HTH Paolo
 
G

Guest

Paolo:
I tried putting your code before the End Sub statement. It did not work.
Where do I put this code. Please add to the code I sent in my question. I
would appreciate this greatly, as I am not a programmer.
Thanks-Bill

Paolo said:
Hi Bill,
I assume you show the msgbox In the before update of your combo.
If you do that in this way it must work

If msgbox("Are you sure you want to update this record?",vbyesno)=vbno then
cancel=true
endif

HTH Paolo


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 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...
 
D

Douglas J. Steele

Did you move your code to the BeforeUpdate event, as Paolo suggested?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


BillD said:
Paolo:
I tried putting your code before the End Sub statement. It did not work.
Where do I put this code. Please add to the code I sent in my question. I
would appreciate this greatly, as I am not a programmer.
Thanks-Bill

Paolo said:
Hi Bill,
I assume you show the msgbox In the before update of your combo.
If you do that in this way it must work

If msgbox("Are you sure you want to update this record?",vbyesno)=vbno
then
cancel=true
endif

HTH Paolo


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 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...
 
G

Guest

Sorry, I had put it in the AfterUpdate Event procedure.
Is this the way the BeforeUpdate Event should read.
I put it in the BeforeUpdate Event as shown below. It gives me a compile
error. Do you think my AfterUpdate may be causing this? Suggestions, thanks.

Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)
If msgbox("Are you sure you want to update this record?",vbyesno)=vbno
then
Cancel = True
End If

End Sub


Douglas J. Steele said:
Did you move your code to the BeforeUpdate event, as Paolo suggested?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


BillD said:
Paolo:
I tried putting your code before the End Sub statement. It did not work.
Where do I put this code. Please add to the code I sent in my question. I
would appreciate this greatly, as I am not a programmer.
Thanks-Bill

Paolo said:
Hi Bill,
I assume you show the msgbox In the before update of your combo.
If you do that in this way it must work

If msgbox("Are you sure you want to update this record?",vbyesno)=vbno
then
cancel=true
endif

HTH Paolo


:

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 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...
 
D

Douglas J. Steele

Is the word "then" on a line by itself? It should be at the end of the line
starting If MsgBox ....

You should be moving all of the code that was previously in the AfterUpdate
event into the BeforeUpdate event:

Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)

Dim SQL_Text As String

If MsgBox("Are you sure you want to update this record?", _
vbYesNo) = vbNo Then
Cancel = True
Else
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)
MsgBox "Completed"
End If

End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


BillD said:
Sorry, I had put it in the AfterUpdate Event procedure.
Is this the way the BeforeUpdate Event should read.
I put it in the BeforeUpdate Event as shown below. It gives me a compile
error. Do you think my AfterUpdate may be causing this? Suggestions,
thanks.

Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)
If msgbox("Are you sure you want to update this record?",vbyesno)=vbno
then
Cancel = True
End If

End Sub


Douglas J. Steele said:
Did you move your code to the BeforeUpdate event, as Paolo suggested?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


BillD said:
Paolo:
I tried putting your code before the End Sub statement. It did not
work.
Where do I put this code. Please add to the code I sent in my question.
I
would appreciate this greatly, as I am not a programmer.
Thanks-Bill

:

Hi Bill,
I assume you show the msgbox In the before update of your combo.
If you do that in this way it must work

If msgbox("Are you sure you want to update this record?",vbyesno)=vbno
then
cancel=true
endif

HTH Paolo


:

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 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...
 
G

Guest

Doug:
I have put all the code in the BeforeUpdate Event. Everything is removed
from the AfterUpdate Event.
I am now getting a message box that asks "Are you sure you want to update
this record" Options are Yes or No. If I select No then the box closes but I
cannot exit the form or click on any field without the box appearing again.
If I select yes for this first box then another box appears that says " You
are about update this record? Are you sure you want to update this record.
Yes or No. If I select No I get a Visual Basic Run-Time error '2501' The
RunSQL action was cancelled. I can select End or Debug.
Below is the exact code I have in the BeforeUpdate Event
Any suggestions. Thanks


Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)
Dim SQL_Text As String
If MsgBox("Are you sure you want to update this record?", _
vbYesNo) = vbNo Then
Cancel = True

Else

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)
MsgBox "Completed"
End If

End Sub

Douglas J. Steele said:
Is the word "then" on a line by itself? It should be at the end of the line
starting If MsgBox ....

You should be moving all of the code that was previously in the AfterUpdate
event into the BeforeUpdate event:

Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)

Dim SQL_Text As String

If MsgBox("Are you sure you want to update this record?", _
vbYesNo) = vbNo Then
Cancel = True
Else
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)
MsgBox "Completed"
End If

End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


BillD said:
Sorry, I had put it in the AfterUpdate Event procedure.
Is this the way the BeforeUpdate Event should read.
I put it in the BeforeUpdate Event as shown below. It gives me a compile
error. Do you think my AfterUpdate may be causing this? Suggestions,
thanks.

Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)
If msgbox("Are you sure you want to update this record?",vbyesno)=vbno
then
Cancel = True
End If

End Sub


Douglas J. Steele said:
Did you move your code to the BeforeUpdate event, as Paolo suggested?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Paolo:
I tried putting your code before the End Sub statement. It did not
work.
Where do I put this code. Please add to the code I sent in my question.
I
would appreciate this greatly, as I am not a programmer.
Thanks-Bill

:

Hi Bill,
I assume you show the msgbox In the before update of your combo.
If you do that in this way it must work

If msgbox("Are you sure you want to update this record?",vbyesno)=vbno
then
cancel=true
endif

HTH Paolo


:

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 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...
 
D

Douglas J. Steele

If you want to undo the data you've already entered, you need not only
Cancel = True but also Me.Undo. (In fact, you may need Me.Undo twice: once
for the current control, and once for the rest of the form).

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


BillD said:
Doug:
I have put all the code in the BeforeUpdate Event. Everything is removed
from the AfterUpdate Event.
I am now getting a message box that asks "Are you sure you want to update
this record" Options are Yes or No. If I select No then the box closes but
I
cannot exit the form or click on any field without the box appearing
again.
If I select yes for this first box then another box appears that says "
You
are about update this record? Are you sure you want to update this record.
Yes or No. If I select No I get a Visual Basic Run-Time error '2501' The
RunSQL action was cancelled. I can select End or Debug.
Below is the exact code I have in the BeforeUpdate Event
Any suggestions. Thanks


Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)
Dim SQL_Text As String
If MsgBox("Are you sure you want to update this record?", _
vbYesNo) = vbNo Then
Cancel = True

Else

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)
MsgBox "Completed"
End If

End Sub

Douglas J. Steele said:
Is the word "then" on a line by itself? It should be at the end of the
line
starting If MsgBox ....

You should be moving all of the code that was previously in the
AfterUpdate
event into the BeforeUpdate event:

Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)

Dim SQL_Text As String

If MsgBox("Are you sure you want to update this record?", _
vbYesNo) = vbNo Then
Cancel = True
Else
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)
MsgBox "Completed"
End If

End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


BillD said:
Sorry, I had put it in the AfterUpdate Event procedure.
Is this the way the BeforeUpdate Event should read.
I put it in the BeforeUpdate Event as shown below. It gives me a
compile
error. Do you think my AfterUpdate may be causing this? Suggestions,
thanks.

Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)
If msgbox("Are you sure you want to update this record?",vbyesno)=vbno
then
Cancel = True
End If

End Sub


:

Did you move your code to the BeforeUpdate event, as Paolo suggested?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Paolo:
I tried putting your code before the End Sub statement. It did not
work.
Where do I put this code. Please add to the code I sent in my
question.
I
would appreciate this greatly, as I am not a programmer.
Thanks-Bill

:

Hi Bill,
I assume you show the msgbox In the before update of your combo.
If you do that in this way it must work

If msgbox("Are you sure you want to update this
record?",vbyesno)=vbno
then
cancel=true
endif

HTH Paolo


:

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 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...
 
G

Guest

Doug:
I do not need to undue once I have selected Yes to the Message boxes. The
problem I am having is that If I select No, I get an error. If I select No, I
want to have the event close and just have the open form on the screen.

Thanks for your help.
Bill Depow, New Brunswick, Canada.

Douglas J. Steele said:
If you want to undo the data you've already entered, you need not only
Cancel = True but also Me.Undo. (In fact, you may need Me.Undo twice: once
for the current control, and once for the rest of the form).

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


BillD said:
Doug:
I have put all the code in the BeforeUpdate Event. Everything is removed
from the AfterUpdate Event.
I am now getting a message box that asks "Are you sure you want to update
this record" Options are Yes or No. If I select No then the box closes but
I
cannot exit the form or click on any field without the box appearing
again.
If I select yes for this first box then another box appears that says "
You
are about update this record? Are you sure you want to update this record.
Yes or No. If I select No I get a Visual Basic Run-Time error '2501' The
RunSQL action was cancelled. I can select End or Debug.
Below is the exact code I have in the BeforeUpdate Event
Any suggestions. Thanks


Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)
Dim SQL_Text As String
If MsgBox("Are you sure you want to update this record?", _
vbYesNo) = vbNo Then
Cancel = True

Else

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)
MsgBox "Completed"
End If

End Sub

Douglas J. Steele said:
Is the word "then" on a line by itself? It should be at the end of the
line
starting If MsgBox ....

You should be moving all of the code that was previously in the
AfterUpdate
event into the BeforeUpdate event:

Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)

Dim SQL_Text As String

If MsgBox("Are you sure you want to update this record?", _
vbYesNo) = vbNo Then
Cancel = True
Else
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)
MsgBox "Completed"
End If

End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Sorry, I had put it in the AfterUpdate Event procedure.
Is this the way the BeforeUpdate Event should read.
I put it in the BeforeUpdate Event as shown below. It gives me a
compile
error. Do you think my AfterUpdate may be causing this? Suggestions,
thanks.

Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)
If msgbox("Are you sure you want to update this record?",vbyesno)=vbno
then
Cancel = True
End If

End Sub


:

Did you move your code to the BeforeUpdate event, as Paolo suggested?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Paolo:
I tried putting your code before the End Sub statement. It did not
work.
Where do I put this code. Please add to the code I sent in my
question.
I
would appreciate this greatly, as I am not a programmer.
Thanks-Bill

:

Hi Bill,
I assume you show the msgbox In the before update of your combo.
If you do that in this way it must work

If msgbox("Are you sure you want to update this
record?",vbyesno)=vbno
then
cancel=true
endif

HTH Paolo


:

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 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...
 
G

Guest

Doug:
I changed the statement "Cancel = True" to "Cancel = False". The combo is
now closing when I choose "No" in the message box.

Thanks ever so much for your patience and help.
Bill Depow, New Brunswick, Canada

Douglas J. Steele said:
If you want to undo the data you've already entered, you need not only
Cancel = True but also Me.Undo. (In fact, you may need Me.Undo twice: once
for the current control, and once for the rest of the form).

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


BillD said:
Doug:
I have put all the code in the BeforeUpdate Event. Everything is removed
from the AfterUpdate Event.
I am now getting a message box that asks "Are you sure you want to update
this record" Options are Yes or No. If I select No then the box closes but
I
cannot exit the form or click on any field without the box appearing
again.
If I select yes for this first box then another box appears that says "
You
are about update this record? Are you sure you want to update this record.
Yes or No. If I select No I get a Visual Basic Run-Time error '2501' The
RunSQL action was cancelled. I can select End or Debug.
Below is the exact code I have in the BeforeUpdate Event
Any suggestions. Thanks


Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)
Dim SQL_Text As String
If MsgBox("Are you sure you want to update this record?", _
vbYesNo) = vbNo Then
Cancel = True

Else

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)
MsgBox "Completed"
End If

End Sub

Douglas J. Steele said:
Is the word "then" on a line by itself? It should be at the end of the
line
starting If MsgBox ....

You should be moving all of the code that was previously in the
AfterUpdate
event into the BeforeUpdate event:

Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)

Dim SQL_Text As String

If MsgBox("Are you sure you want to update this record?", _
vbYesNo) = vbNo Then
Cancel = True
Else
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)
MsgBox "Completed"
End If

End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Sorry, I had put it in the AfterUpdate Event procedure.
Is this the way the BeforeUpdate Event should read.
I put it in the BeforeUpdate Event as shown below. It gives me a
compile
error. Do you think my AfterUpdate may be causing this? Suggestions,
thanks.

Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)
If msgbox("Are you sure you want to update this record?",vbyesno)=vbno
then
Cancel = True
End If

End Sub


:

Did you move your code to the BeforeUpdate event, as Paolo suggested?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Paolo:
I tried putting your code before the End Sub statement. It did not
work.
Where do I put this code. Please add to the code I sent in my
question.
I
would appreciate this greatly, as I am not a programmer.
Thanks-Bill

:

Hi Bill,
I assume you show the msgbox In the before update of your combo.
If you do that in this way it must work

If msgbox("Are you sure you want to update this
record?",vbyesno)=vbno
then
cancel=true
endif

HTH Paolo


:

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 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...
 
G

Guest

Doug:
There still is a small problem in the Private Sub
cmb_Addr_change_BeforeUpdate.
When I select an address from the combo drop-down box to replace the address
fields on the form, a box comes up that says "Are you sure you want to update
this record" Yes or No. If I select No the action quits. This is what I
wanted. If I select Yes, then another box appears that says "You are about to
update 1 row" Options are Yes or No. If I select Yes then the action runs and
the fields are updated. It works Good. If I select No then I still get a
visual basic the run-time error '2501'.(The run SQLaction was cancelled).
Options are End or Debug. Here is the code I have in the BeforeUpdate Event.
Suggestions? Thanks.
Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)
Dim SQL_Text As String
If MsgBox("Are you sure you want to update this record?", _
vbYesNo) = vbNo Then
Cancel = False
Else

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 If
End Sub


Douglas J. Steele said:
If you want to undo the data you've already entered, you need not only
Cancel = True but also Me.Undo. (In fact, you may need Me.Undo twice: once
for the current control, and once for the rest of the form).

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


BillD said:
Doug:
I have put all the code in the BeforeUpdate Event. Everything is removed
from the AfterUpdate Event.
I am now getting a message box that asks "Are you sure you want to update
this record" Options are Yes or No. If I select No then the box closes but
I
cannot exit the form or click on any field without the box appearing
again.
If I select yes for this first box then another box appears that says "
You
are about update this record? Are you sure you want to update this record.
Yes or No. If I select No I get a Visual Basic Run-Time error '2501' The
RunSQL action was cancelled. I can select End or Debug.
Below is the exact code I have in the BeforeUpdate Event
Any suggestions. Thanks


Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)
Dim SQL_Text As String
If MsgBox("Are you sure you want to update this record?", _
vbYesNo) = vbNo Then
Cancel = True

Else

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)
MsgBox "Completed"
End If

End Sub

Douglas J. Steele said:
Is the word "then" on a line by itself? It should be at the end of the
line
starting If MsgBox ....

You should be moving all of the code that was previously in the
AfterUpdate
event into the BeforeUpdate event:

Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)

Dim SQL_Text As String

If MsgBox("Are you sure you want to update this record?", _
vbYesNo) = vbNo Then
Cancel = True
Else
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)
MsgBox "Completed"
End If

End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Sorry, I had put it in the AfterUpdate Event procedure.
Is this the way the BeforeUpdate Event should read.
I put it in the BeforeUpdate Event as shown below. It gives me a
compile
error. Do you think my AfterUpdate may be causing this? Suggestions,
thanks.

Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)
If msgbox("Are you sure you want to update this record?",vbyesno)=vbno
then
Cancel = True
End If

End Sub


:

Did you move your code to the BeforeUpdate event, as Paolo suggested?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Paolo:
I tried putting your code before the End Sub statement. It did not
work.
Where do I put this code. Please add to the code I sent in my
question.
I
would appreciate this greatly, as I am not a programmer.
Thanks-Bill

:

Hi Bill,
I assume you show the msgbox In the before update of your combo.
If you do that in this way it must work

If msgbox("Are you sure you want to update this
record?",vbyesno)=vbno
then
cancel=true
endif

HTH Paolo


:

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 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...
 
D

Douglas J. Steele

You have to put in error trapping, and ignore error 2501.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


BillD said:
Doug:
There still is a small problem in the Private Sub
cmb_Addr_change_BeforeUpdate.
When I select an address from the combo drop-down box to replace the
address
fields on the form, a box comes up that says "Are you sure you want to
update
this record" Yes or No. If I select No the action quits. This is what I
wanted. If I select Yes, then another box appears that says "You are about
to
update 1 row" Options are Yes or No. If I select Yes then the action runs
and
the fields are updated. It works Good. If I select No then I still get a
visual basic the run-time error '2501'.(The run SQLaction was cancelled).
Options are End or Debug. Here is the code I have in the BeforeUpdate
Event.
Suggestions? Thanks.
Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)
Dim SQL_Text As String
If MsgBox("Are you sure you want to update this record?", _
vbYesNo) = vbNo Then
Cancel = False
Else

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 If
End Sub


Douglas J. Steele said:
If you want to undo the data you've already entered, you need not only
Cancel = True but also Me.Undo. (In fact, you may need Me.Undo twice:
once
for the current control, and once for the rest of the form).

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


BillD said:
Doug:
I have put all the code in the BeforeUpdate Event. Everything is
removed
from the AfterUpdate Event.
I am now getting a message box that asks "Are you sure you want to
update
this record" Options are Yes or No. If I select No then the box closes
but
I
cannot exit the form or click on any field without the box appearing
again.
If I select yes for this first box then another box appears that says "
You
are about update this record? Are you sure you want to update this
record.
Yes or No. If I select No I get a Visual Basic Run-Time error '2501'
The
RunSQL action was cancelled. I can select End or Debug.
Below is the exact code I have in the BeforeUpdate Event
Any suggestions. Thanks


Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)
Dim SQL_Text As String
If MsgBox("Are you sure you want to update this record?", _
vbYesNo) = vbNo Then
Cancel = True

Else

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)
MsgBox "Completed"
End If

End Sub

:

Is the word "then" on a line by itself? It should be at the end of the
line
starting If MsgBox ....

You should be moving all of the code that was previously in the
AfterUpdate
event into the BeforeUpdate event:

Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)

Dim SQL_Text As String

If MsgBox("Are you sure you want to update this record?", _
vbYesNo) = vbNo Then
Cancel = True
Else
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)
MsgBox "Completed"
End If

End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Sorry, I had put it in the AfterUpdate Event procedure.
Is this the way the BeforeUpdate Event should read.
I put it in the BeforeUpdate Event as shown below. It gives me a
compile
error. Do you think my AfterUpdate may be causing this? Suggestions,
thanks.

Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)
If msgbox("Are you sure you want to update this
record?",vbyesno)=vbno
then
Cancel = True
End If

End Sub


:

Did you move your code to the BeforeUpdate event, as Paolo
suggested?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Paolo:
I tried putting your code before the End Sub statement. It did
not
work.
Where do I put this code. Please add to the code I sent in my
question.
I
would appreciate this greatly, as I am not a programmer.
Thanks-Bill

:

Hi Bill,
I assume you show the msgbox In the before update of your combo.
If you do that in this way it must work

If msgbox("Are you sure you want to update this
record?",vbyesno)=vbno
then
cancel=true
endif

HTH Paolo


:

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 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...
 
P

Pieter Wijnen

Change DoCmd.RunSQL (SQL_Text) to
CurrentDb.Execute SQL_Text, DAO.dbFailOnError

Pieter

Douglas J. Steele said:
You have to put in error trapping, and ignore error 2501.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


BillD said:
Doug:
There still is a small problem in the Private Sub
cmb_Addr_change_BeforeUpdate.
When I select an address from the combo drop-down box to replace the
address
fields on the form, a box comes up that says "Are you sure you want to
update
this record" Yes or No. If I select No the action quits. This is what I
wanted. If I select Yes, then another box appears that says "You are
about to
update 1 row" Options are Yes or No. If I select Yes then the action runs
and
the fields are updated. It works Good. If I select No then I still get a
visual basic the run-time error '2501'.(The run SQLaction was cancelled).
Options are End or Debug. Here is the code I have in the BeforeUpdate
Event.
Suggestions? Thanks.
Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)
Dim SQL_Text As String
If MsgBox("Are you sure you want to update this record?", _
vbYesNo) = vbNo Then
Cancel = False
Else

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 If
End Sub


Douglas J. Steele said:
If you want to undo the data you've already entered, you need not only
Cancel = True but also Me.Undo. (In fact, you may need Me.Undo twice:
once
for the current control, and once for the rest of the form).

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug:
I have put all the code in the BeforeUpdate Event. Everything is
removed
from the AfterUpdate Event.
I am now getting a message box that asks "Are you sure you want to
update
this record" Options are Yes or No. If I select No then the box closes
but
I
cannot exit the form or click on any field without the box appearing
again.
If I select yes for this first box then another box appears that says
"
You
are about update this record? Are you sure you want to update this
record.
Yes or No. If I select No I get a Visual Basic Run-Time error '2501'
The
RunSQL action was cancelled. I can select End or Debug.
Below is the exact code I have in the BeforeUpdate Event
Any suggestions. Thanks


Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)
Dim SQL_Text As String
If MsgBox("Are you sure you want to update this record?", _
vbYesNo) = vbNo Then
Cancel = True

Else

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)
MsgBox "Completed"
End If

End Sub

:

Is the word "then" on a line by itself? It should be at the end of
the
line
starting If MsgBox ....

You should be moving all of the code that was previously in the
AfterUpdate
event into the BeforeUpdate event:

Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)

Dim SQL_Text As String

If MsgBox("Are you sure you want to update this record?", _
vbYesNo) = vbNo Then
Cancel = True
Else
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)
MsgBox "Completed"
End If

End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Sorry, I had put it in the AfterUpdate Event procedure.
Is this the way the BeforeUpdate Event should read.
I put it in the BeforeUpdate Event as shown below. It gives me a
compile
error. Do you think my AfterUpdate may be causing this?
Suggestions,
thanks.

Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)
If msgbox("Are you sure you want to update this
record?",vbyesno)=vbno
then
Cancel = True
End If

End Sub


:

Did you move your code to the BeforeUpdate event, as Paolo
suggested?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Paolo:
I tried putting your code before the End Sub statement. It did
not
work.
Where do I put this code. Please add to the code I sent in my
question.
I
would appreciate this greatly, as I am not a programmer.
Thanks-Bill

:

Hi Bill,
I assume you show the msgbox In the before update of your
combo.
If you do that in this way it must work

If msgbox("Are you sure you want to update this
record?",vbyesno)=vbno
then
cancel=true
endif

HTH Paolo


:

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 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...
 
G

Guest

Pieter:
That did it. It finally works. Thanks so much. Now when I select an address
from the combo box, I get only one message box. When I select yes, the
address fields are updated. When I select No, the action is cancelled and I
am back to the persons form. I can now do any other task I want without an
annoying error popping up.
Bill Depow
Fredericton,New Brunswick, canada.

Pieter Wijnen said:
Change DoCmd.RunSQL (SQL_Text) to
CurrentDb.Execute SQL_Text, DAO.dbFailOnError

Pieter

Douglas J. Steele said:
You have to put in error trapping, and ignore error 2501.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


BillD said:
Doug:
There still is a small problem in the Private Sub
cmb_Addr_change_BeforeUpdate.
When I select an address from the combo drop-down box to replace the
address
fields on the form, a box comes up that says "Are you sure you want to
update
this record" Yes or No. If I select No the action quits. This is what I
wanted. If I select Yes, then another box appears that says "You are
about to
update 1 row" Options are Yes or No. If I select Yes then the action runs
and
the fields are updated. It works Good. If I select No then I still get a
visual basic the run-time error '2501'.(The run SQLaction was cancelled).
Options are End or Debug. Here is the code I have in the BeforeUpdate
Event.
Suggestions? Thanks.
Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)
Dim SQL_Text As String
If MsgBox("Are you sure you want to update this record?", _
vbYesNo) = vbNo Then
Cancel = False
Else

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 If
End Sub


:

If you want to undo the data you've already entered, you need not only
Cancel = True but also Me.Undo. (In fact, you may need Me.Undo twice:
once
for the current control, and once for the rest of the form).

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug:
I have put all the code in the BeforeUpdate Event. Everything is
removed
from the AfterUpdate Event.
I am now getting a message box that asks "Are you sure you want to
update
this record" Options are Yes or No. If I select No then the box closes
but
I
cannot exit the form or click on any field without the box appearing
again.
If I select yes for this first box then another box appears that says
"
You
are about update this record? Are you sure you want to update this
record.
Yes or No. If I select No I get a Visual Basic Run-Time error '2501'
The
RunSQL action was cancelled. I can select End or Debug.
Below is the exact code I have in the BeforeUpdate Event
Any suggestions. Thanks


Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)
Dim SQL_Text As String
If MsgBox("Are you sure you want to update this record?", _
vbYesNo) = vbNo Then
Cancel = True

Else

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)
MsgBox "Completed"
End If

End Sub

:

Is the word "then" on a line by itself? It should be at the end of
the
line
starting If MsgBox ....

You should be moving all of the code that was previously in the
AfterUpdate
event into the BeforeUpdate event:

Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)

Dim SQL_Text As String

If MsgBox("Are you sure you want to update this record?", _
vbYesNo) = vbNo Then
Cancel = True
Else
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)
MsgBox "Completed"
End If

End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Sorry, I had put it in the AfterUpdate Event procedure.
Is this the way the BeforeUpdate Event should read.
I put it in the BeforeUpdate Event as shown below. It gives me a
compile
error. Do you think my AfterUpdate may be causing this?
Suggestions,
thanks.

Private Sub cmb_Addr_change_BeforeUpdate(Cancel As Integer)
If msgbox("Are you sure you want to update this
record?",vbyesno)=vbno
then
Cancel = True
End If

End Sub


:

Did you move your code to the BeforeUpdate event, as Paolo
suggested?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Paolo:
I tried putting your code before the End Sub statement. It did
not
work.
Where do I put this code. Please add to the code I sent in my
question.
I
would appreciate this greatly, as I am not a programmer.
Thanks-Bill

:

Hi Bill,
I assume you show the msgbox In the before update of your
combo.
If you do that in this way it must work

If msgbox("Are you sure you want to update this
record?",vbyesno)=vbno
then
cancel=true
endif

HTH Paolo


:

I have an Access Database with a Table that includes
Constituents
Names
and
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 Fields with Combo on Form 5
Use Form to generate Query for a Report 3
Change address fields in Table using Unbound Combo Box 18
Change Address Field data in a Table from a form using Combo Drop 7
Link subform to combo box on form 3
Refresh the data in dropdown combo box 7
Open form using vba 6

Top