Help with Cancel using ADO Control

[AccessGuruCarl]

Hello All,

New to VB -
I'm trying to convert an MS Access application into a VB front-end with an access back-end, Working with ADO controls.
How do I program the cancel button so that the user can click it to 'not' save his edits or new record data and return the record to it's prior settings.

Thanks

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[aggeliki23]

What exactly want to do? Having a form that the user inserts new records or a form that displays records by users criteria and you want the user edit the exist record?
If you want a form inserting records and the cansel button not save changes, design a save command button and a addnew command button and use in your program not ado data control but adodb control in your code. You can do this by Project Menu->References->Microsoft ActiveX Data Objects 2.0 Library.
In the form load event set textboxes propery enable to false and define your adodb connection to your database and open it.
When the user click in the addnew button set the textboxes enable property to true. Define your recordset and sql query and open it with the recordset. Then set the datasource of textboxes to your recordset and add a new record without update. In the save command button click event you have to assing the recordset's fields with the textboxes. for example rs!fieldNameOfYourTable=text1.text etc. Then you update the recordSet and you display a messagebox that the record saved. As a conclusion, the record will save if the user click the save command button only. If you want to use a cansel command button just unload the form when the user clicks on it. And no changes will update your recordset. For any questions notify me.

 

[AccessGuruCarl]

Thanks for the post...

I'll try putting it to use.

But my main problem, which I didn't explain that well is:

Say the user is on record 22 of n
They click add new, begin entering data...
Then choose cancel... How do I return to record 22!

Thanks

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[dglienna]

Don't add the new record until the user saves it. Just clear the textboxes, and let the user add (or edit) the info.

If they don't save it, it's lost. When they do save, that's when you actually add it.

-David
2006 & 2007 Microsoft Most Valuable Professional (MVP)
2006 Dell Certified System Professional (CSP)

 

[JoeAtWork]

I program both in Access and VB6.

If I program in Access, I use bound forms because that is what makes Access so easy to use. The tradeoff is that I sometimes find it difficult to do semi-complex validation.

When I need complete control, I use VB6 without data controls. It requires more coding - I have to fill in all the controls when a record loads, clear them when a user begins adding a new record, write code for even the most basic validation (like checking that a required field has a value). In effect, all the things that an Access bound form does automatically. But the benefit is that I get absolute control of the UI. The few times I tried to use data controls in VB I felt like I was in a wrestling match with my controls!

I realize that as an Access programmer it probably feels more natural to have controls bound to a data control. However, I agree with the other responders that you are better of writing your own code for populating the controls, as you will find the data control too limited and unflexible.

 

[SBerthold]

Set the ADODC's Properties:
Cursorlocation = 3 - adUseClient
CursorType = 3 - adOpenStatic

Do something like this:

Private Sub cmdUpdate_Click()
Adodc1.Recordset.Update
End Sub

Private Sub cmdCancel_Click()
With Adodc1.Recordset
.CancelUpdate
If Not (.BOF And .EOF) Then
.MovePrevious
.MoveNext
Else
.AddNew
End If
End With
End Sub

 

[aggeliki23]

Something else maybe helps you, when you add a new record the record adds after the last record of your table. So either you save this record either not, you don't want to be in the last record but in record you were before adding, except that the record you were before was the last.
Well, if for example the 22 record is not the last record a good thought is to declare a variable as integer to hold the number of the record you want.
dim RecNo as integer
recNo = rs.AbsolutePosition

when you cansel
rs.move recNo

and you return to the record you want.
I hope helped you.