Control Bound Form Transactions

[Brandy via AccessMonster.com]

I am in deperate need of help!

I am new to VBA code and have located the following code in order to create a
transaction for a form to update all changes at one time. The problem is, I
am using this form for NEW entries only, I do not want to show any data
previously entered on the table. Here is the code, any suggestions on how to
open the form and only show new records? I have changed the form properties
to Data Entry only, but that did not work this coding.

Any help you can provide is greatly appreciated.

Option Compare Database
Option Explicit

Private boolFrmDirty As Boolean
Private boolFrmSaved As Boolean

Private Sub Form_AfterDelConfirm(Status As Integer)
If Me.Saved = False Then Me.Saved = (Status = acDeleteOK)
End Sub

Private Sub Form_AfterUpdate()
Me.Saved = True
End Sub

Private Sub Form_Delete(Cancel As Integer)
If Me.Dirtied = False Then DBEngine.BeginTrans
Me.Dirtied = True
End Sub

Private Sub Form_Dirty(Cancel As Integer)
If Me.Dirtied = False Then DBEngine.BeginTrans
Me.Dirtied = True
End Sub


Private Sub Form_Open(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM CPI_Prod_Table", dbOpenDynaset)
Set Me.Recordset = rs


DoCmd.GoToRecord , , acNewRec

End Sub



Private Sub Form_Unload(Cancel As Integer)
Dim msg As Integer
If Me.Saved Then
msg = MsgBox("WOULD YOU LIKE TO SAVE ALL CHANGES?", vbYesNoCancel)
Select Case msg
Case vbYes
DBEngine.CommitTrans
Case vbNo
DBEngine.Rollback
Case vbCancel
Cancel = True
End Select
Else
If Me.Dirtied Then DBEngine.Rollback
End If
End Sub

Public Property Get Dirtied() As Boolean
Dirtied = boolFrmDirty
End Property

Public Property Let Dirtied(boolFrmDirtyIn As Boolean)
boolFrmDirty = boolFrmDirtyIn
End Property

Public Property Get Saved() As Boolean
Saved = boolFrmSaved
End Property

Public Property Let Saved(boolFrmSavedIn As Boolean)
boolFrmSaved = boolFrmSavedIn
End Property

 

[Mr B]

Hi, Barndy.

I think we need a little more info in order to really help you.

I assume that this is a bound form that is getting its data from either a
query or a table. Is this the case?

You do not state just how you are opening your form.

Are you just wanting to allow the user to determine if updates should be
saved?

 

[Brandy via AccessMonster.com]

Yes I want to allow users to determine if an update should be made. The form
is bound to a table. Basically the information they enter on the form is
Name, Work Item, # Items Worked, Start Time, End Time. Keep in mind this is
a multi user environment, I just want a blank form to open up so they can
enter data, i do not want them to see what others have entered.

The form is opened by a command button on a menu, I have set the form
properties to open "Data Entry" with No edits allowed, only new entries.

Hope this makes sense.
Thanks again for your help!

Hi, Barndy.

I think we need a little more info in order to really help you.

I assume that this is a bound form that is getting its data from either a
query or a table. Is this the case?

You do not state just how you are opening your form.

Are you just wanting to allow the user to determine if updates should be
saved?

I am in deperate need of help!

[quoted text clipped - 74 lines]

boolFrmSaved = boolFrmSavedIn
End Property

 

[Mr. B]

Brandy,

Try opening your data entry form specifying a new record like this:

DoCmd.OpenForm "NameOfYourForm", , , , acFormAdd

Just change the "NameOfYourForm" to the actual name of your form.

I will assume that you have a button for closing the form. In the OnClick
event of this button, use code like this:

dim strMsg as string
Dim vbResponse
if Me.Dirty then
strMsg = "Do you want to save your changes?"
vbResponse = msgbox(strMsg, vbYesNo + vbQuestion + vbDefaultButton1, _
"Save Changes?")
If vbResponse = vbYes then
'save changes
Me.Dirty = False
EndIf
EndIf
DoCmd.Close acForm, "NameOfYourForm"

Just one other thought, you may want to check for data entry in any controls
that required data entry to have a valid record before allowing the user to
save the record, but that is a whole different issue.

-----
HTH

Mr. B
askdoctoraccess dot com

Yes I want to allow users to determine if an update should be made. The form
is bound to a table. Basically the information they enter on the form is
Name, Work Item, # Items Worked, Start Time, End Time. Keep in mind this is
a multi user environment, I just want a blank form to open up so they can
enter data, i do not want them to see what others have entered.

The form is opened by a command button on a menu, I have set the form
properties to open "Data Entry" with No edits allowed, only new entries.

Hope this makes sense.
Thanks again for your help!

Hi, Barndy.

I think we need a little more info in order to really help you.

I assume that this is a bound form that is getting its data from either a
query or a table. Is this the case?

You do not state just how you are opening your form.

Are you just wanting to allow the user to determine if updates should be
saved?

I am in deperate need of help!

[quoted text clipped - 74 lines]

boolFrmSaved = boolFrmSavedIn
End Property

 

[bbrock via AccessMonster.com]

Ok that code worked to only show new record to be entered, but the problem is
I have a continuous form - so when the employee enters a record then proceeds
to the next, it automatically updates the table. I would like to have the
records entered on the OnClick Event.

Ok here is the only way I can get the form to work the way I want. The only
issue is - this code still shows all records in the database. I just want to
hide the records already saved on the database.

1. The Open Event:

Private Sub Form_Open(Cancel As Integer)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM CPI_Prod_Table", dbOpenDynaset)
Set Me.Recordset = rs


DoCmd.GoToRecord , , acNewRec

2. The Unload Event to prompt save changes is:

Private Sub Form_Unload(Cancel As Integer)
Dim msg As Integer
If Me.Saved Then
msg = MsgBox("WOULD YOU LIKE TO SAVE ALL CHANGES?", vbYesNoCancel)
Select Case msg
Case vbYes
DBEngine.CommitTrans
Case vbNo
DBEngine.Rollback
Case vbCancel
Cancel = True
End Select
Else
If Me.Dirtied Then DBEngine.Rollback
End If
End Sub

Brandy,

Try opening your data entry form specifying a new record like this:

DoCmd.OpenForm "NameOfYourForm", , , , acFormAdd

Just change the "NameOfYourForm" to the actual name of your form.

I will assume that you have a button for closing the form. In the OnClick
event of this button, use code like this:

dim strMsg as string
Dim vbResponse
if Me.Dirty then
strMsg = "Do you want to save your changes?"
vbResponse = msgbox(strMsg, vbYesNo + vbQuestion + vbDefaultButton1, _
"Save Changes?")
If vbResponse = vbYes then
'save changes
Me.Dirty = False
EndIf
EndIf
DoCmd.Close acForm, "NameOfYourForm"

Just one other thought, you may want to check for data entry in any controls
that required data entry to have a valid record before allowing the user to
save the record, but that is a whole different issue.

-----
HTH

Mr. B
askdoctoraccess dot com

Yes I want to allow users to determine if an update should be made. The form
is bound to a table. Basically the information they enter on the form is

[quoted text clipped - 25 lines]