Question Saving data other than in tables

[magister011]

I am creating a database using Access 2003 at work. I want to be able to create a place where I can temporarily place data that I have worked on in a form, edit it but not place it permantly into the table until I tell it too. I believe that this is something that FoxPro does. I am making this database for many users and I don't want the data going directly going into the tables, until the user is ready. I have searched the internet and have not found an answer. Any help would be greatly appreciated.

Thanks
Magister011

 

[Scooterbug]

There are ways...but why would you want to do that? There are ways to validate data and such before a record is saved. There are also ways to undo changes before the data is updated in the table.

 

[pbaldy]

The most common solution is probably a local table. What you're describing is commonly called "unbound", so searching on that may pull up some relevant threads for you. It is significantly more effort than letting Access do the work.

 

[pbaldy]

Sorry Scooter; your post wasn't there when I started typing, then I got distracted.

 

[Scooterbug]

it's all good....mine was probably a bit vague anyways

 

[magister011]

Scooterbug,
How would I be able to validate the data before a change took place. I am just trying to prevent an accident from happening to a change in the tables.

 

[magister011]

pbaldy,
I searched for "unbound" but since I am unfamiliar with this command I am not sure of what I am looking for. Any hints?

 

[rob.low]

Hi there

This is what i use on the forms "before update event" a message box opens asking if you want to save the changes you have made

not sure if it is what you are after ?

have a look at the following code.

'code start
Dim intResponse As Integer
Dim strMsg As String
strMsg = "Data on the form has changed. Do you want to save changes?"
intResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Confirm Action")
Select Case intResponse
Case vbNo
DoCmd.RunCommand acCmdUndo

Case vbYes
DoCmd.RunCommand acCmdSaveRecord
End Select
'code end

Regards
Rob

 

[boblarson]

Case vbYes
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
End Select

Rob:

It would be good to get rid of any DoMenuItem commands as those are definitely out of date. Microsoft keeps them around just for backwards compatibility. The current one would be:

DoCmd.RunCommand acCmdSaveRecord

 

[rob.low]

Rob:

It would be good to get rid of any DoMenuItem commands as those are definitely out of date. Microsoft keeps them around just for backwards compatibility. The current one would be:

DoCmd.RunCommand acCmdSaveRecord

Thanks bob I will Remember that one

regards
Rob
PS have changed the code i have posted to show the way you have showed me.

 

[Scooterbug]

Scooterbug,
How would I be able to validate the data before a change took place. I am just trying to prevent an accident from happening to a change in the tables.

It all depends on what is being validated. For example, are you looking to check to make sure that there aren't any blank fields?

It would all depend on the data that you want to validate. There is also a validation rule property. I honestly have never used them...so dont know much about them. If I need to validate, I do it via code.

As an example, to check to see if a field has been left blank, you can do the following:

If Nz(me.TextBox1, "") = "" Then
   Msgbox "TextBox1 is Empty. Please enter data before continuing"
   exit sub
End If

You could put the validation code on the Before Update, then add the code Rob posted to make sure that the user really wants to save.

 

[magister011]

Thanks everyone,

I was able to get it taken care of with Rob's "before update event" code.

Magister011

 

[magister011]

Ok, I spoke too soon! I tried the a message box with the 'before update' event. Here is the code I used:

'code start
Dim intResponse As Integer
Dim strMsg As String
strMsg = "Data on the form has changed. Do you want to save changes?"
intResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Confirm Action")
Select Case intResponse
Case vbNo
DoCmd.RunCommand acCmdUndo

Case vbYes
DoCmd.RunCommand acCmdSaveRecord
End Select
'code end

I ended up with a 2115 error whenever I chose 'Yes' and the DeBugger would stop on the bold line. If you have any suggestions. I would sure appreciate it. I have been struggling with this for 3 days at work.

Magister011

 

[pbaldy]

What exactly is that error?

 

[boblarson]

If you are in the Before Update event you don't use a save command because it is in the middle of saving the record already.

 

[magister011]

The error message that I get is:

Run-time error '2115':

The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsoft Office Access from saving the data in the field.

 

[boblarson]

The error message that I get is:

Run-time error '2115':

The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsoft Office Access from saving the data in the field.

Remove the

DoCmd.RunCommand acCmdSaveRecord


as you don't need it. If they want to save the record, you just let the Before Update event happen as normal. If they don't you issue a

Cancel = True

and then
Me.Undo
the form.

 

[magister011]

Thanks, I will try that.

 

[magister011]

Thank you, It worked and all is good.

Magister011