Solved: Cancel the data update

[nepotist]

I have a table with about 7 to 8 fields and I have created a from based on it , it is a data update from. when the user is trying to update/modify any current information.
I have a command button.so that user can click it and save the changes.
once the form is closed the data is automatically updated.
I want to ask the user if he/she want to makes the changes if yes then close other wise exit with out any changes to database.

The thing is no matter what you finally will be closing the form and the data will be updated.

The other way i was thinking was using the recordset ... and
then

answer = msgbox (,,vbyesno)
if yes run the module for update other wise exitbut then still i will be closing the form and the data would be updated.

can any one help me out with this
Did I confuse you guys??

thank you

[nepotist]

I have a thought but dont know if this is possible.
When the form with current information is loaded, I would like to create variable and assign the current data base values to them like
a = me.a.value
and I would create a module when the user wishes to update it.
But the thing is that how can all call one varaible from the load sub routine to the module.
How can the variables with there values be shared in the subroutine as well as in the module ???

[CreganTur]

If you have a Data Input Form (where your Data Input property under the Data tab is set to Yes) that is directly linked to a table, then new data is automatically written to the table whenever you move to a different record, or when you save the new record.

If you want to allow your user to Cancel their changes, then in this case you're going to want to delete the record that was just added to the table via a SQL Delete query- WHERE condition points to that record's PK.

make sense?

[nepotist]

Private Sub cmdUpdate_Click()
Dim SegmentID As Integer
Dim Station As Integer, fstation As Integer
Dim road As String, froad As String
Dim lanes As Integer, flanes As Integer
Dim leng As Single, fleng As Single
Dim clanes As Integer, fclanes As Integer

Dim rstlink As Recordset
Dim db As Database
Set db = CurrentDb()
Set rstlink = db.OpenRecordset("tblconcurrencysegments", dbOpenDynaset)

SegmentID = Me.SegmentID.Value

With rstlink!SegmentID = SegmentID
On Error Resume Next
Station = rstlink!Station
road = rstlink!RoadName
lanes = rstlink!NumberLanes
End With

fstation = Me!Station
froad = Me!RoadName
flanes = Me!NumberLanes
fleng = Me!Length
fclanes = Me!ConstructedLanes


Dim answer As String
MsgBox "You are about to modify the database. Do you wish to proceed?", vbYesNo, "Warning!", , vbCritical
answer = MsgBox("You are about to modify the database. Do you wish to proceed?", vbYesNo, "Warning!", , vbCritical)

If answer = vbNo Then

With rstlink!SegmentID = SegmentID
.Edit
!Station = Station
!RoadName = road
!NumberLanes = lanes

.Update
End With
rstlink.Close
With Me!SegmentID = SegmentID
.Edit
!Station = Station
!RoadName = road
!NumberLanes = lanes
!Length = leng
!ConstructedLanes = clanes
.Update
End With

MsgBox "You have Aborted the update.", vbOKOnly

Else
With rstlink!SegmentID = SegmentID
.Edit
!Station = fstation
!RoadName = froad
!NumberLanes = flanes

.Update
End With
rstlink.Close

MsgBox "Roadway Information Update Complete", vbOKOnly
End If

This what I did. First I pull the values from the recorset which would be the initial values in when the data update form loads up(That being the initial values in the database) then If the user updates the values in the form , there values are then again assigned to variables. once the user clicks the update button, a msgbox pops up withe yes or no, depening up on there answer I would reassing the fileds of the record set.

I believe this is one way to do it. but it is giving me a error, actually when i debug it, it is skipping the yes or no messagebox dont know why. and gives me message that that I have aborted the update. which should actually pop up when the user says no.

:dunno:dunno:dunno:help:help:help

[Carl A]

VBNo is a Integer value you have answer dimensioned as a string

[nepotist]

Well i modifed the answer as integer and rerun the programm and still I am not able to view the messsage box asking the user yes or no.

I went as ahead and deleted the title of the msgbox and its type in the code line, guess what I am able to view the msgbox now. but the wired thing is that irrespective of the response(that is yes or no) the code always takes the option of no and does what it is asked to do if the answer is no.

I dont know why would a title and type of message box create such issue. and why the code is behaving like this.
I am using office 2007, and can this have any thing to do with this. It wouldnt make any sense though

Please help

[Carl A]

You have two lines for the msgbox which is redundant, also the msgbox format is incorrect: should be

answer = MsgBox("You are about to modify the database. Do you wish to proceed?", vbYesNo + vbCritical, "Warning!")

[nepotist]

Dam.. Such a dumb... Thanks a ton dude.. u saved my time.

:bow:

[nepotist]

In a data entry form I notice that you could actually scroll the mouse button to go to next record. Is there any way to disable this

[Carl A]

There is KB article that address this
http://www.vbaexpress.com/kb/getarticle.php?kb_id=890