Bounds fields trouble

[accessnewb]

Hi,
I have a form with bound fields. So in form view, if I click on previous and next buttons you can view the different records present in the DB.



The problem is, if I change any field value then the new value is automatically stored in the db. For example, I have a field called "FirstName" and the name is "Lisa". If I change "Lisa" to "Elisa", then it automatically gets updated in the DB. I want it to get updated only on click of a button. So to achieve that, I made all the fields unbound and used an insert query on click of button.

But now the problem is, I cannot view the records in the table since the field is unbound. For that the field needs to be bound. If I click the next and previous buttons now, then all the records show only the values I typed.

Is there any way to save records only on click of a button but also view all the records by using hte next and previous buttons without making the fields bound to a column in a table??

PS: The title is supposed to be "Bound fields trouble", but there is no way for me to edit a title

[boblarson]

You don't need to use an unbound form to do any of this. Bind the form back to what it used to be. And then, in the form's General Declarations section put

blnSave As Boolean

And then in the form's BEFORE UPDATE event put:

If Not blnSave Then
   Cancel = True
   If Msgbox("Do you wish to cancel this record?", vbYesNo + vbQuestion) = vbYes Then
      Me.Undo
   End If
End If

And then in your save button's click event put

blnSave = True
If Me.Dirty Then Me.Dirty = False

That will then not let the record be saved if the button hasn't been clicked but it will let it save if it is clicked.

[accessnewb]

Hey,
I did what u said and I get this error when the form loaded "The expression On Load you entered as the event property setting produced the following error: Statement invalid outside Type block"

[boblarson]

Hey,
I did what u said and I get this error when the form loaded "The expression On Load you entered as the event property setting produced the following error: Statement invalid outside Type block"

What line is highlighted? What did you put inside the On Load event? I didn't say to put anything in the On Load event.

[accessnewb]

oh I already had code in the onLoad event, but all it did was assign a local variable to a global variable:

txtAccessType = strUserType

But after I got the error, I commented whatever code I had in the onLoad event and I still got the error

[boblarson]

Oh I think I know what is happening. I had put

blnSave As Boolean

when it should have been

Private blnSave As Boolean

[accessnewb]

it works now! Thanks!

[accessnewb]

umm..just one more question

Now when I get the popup "Do you wish to save this record?" and I click "No", then I get another popup saying "You can't save this record at the time. Microsoft access may have encountered an error while trying to save a record. If you close this object now, the data changes you made will be lost. Do you want to close the object anyway?"

Is there something which can be done about the 2nd popup? Will this code work:

DoCmd.SetWarnings False

[boblarson]

No, don't use the SetWarnings. So, I'm trying to envision why you would be getting the second message since it should be canceling the update and the undoing any changes. What exactly are the steps you are taking which then leads to this second message?

[accessnewb]

If I enter some values into the fields and then close the form without saving then I get the msgBox asking: "Do you wish to cancel this record?". If I click "yes" or "no" on this, then I get the 2nd msgBox.

Also, I didn't edit the code which u gave. So not sure y I should be getting the 2nd message.

[accessnewb]

umm...anyone??

[boblarson]

I've been playing around with this and have been having the same issue. My fix is to just remove the X from the form and put a close button on it and in the close you use Me.Undo and then close the form. The only time they would then get that second message is if they didn't close the form before closing the database and that would just be a minor annoyance.

[accessnewb]

Thanks a lot! I was searching for a solution to prevent the msg box to pop up when closing the db and found this code

Private Sub Form_Error(DataErr As Integer, Response As Integer) 
Select Case DataErr 
Case 2169 
    Response = acDataErrContinue 
Case Else 
    Response = acDataErrDisplay 
End Select 
End Sub

Now the annoying message box doesn't come up, but then access closes no matter what button I press. Hmmm....