Too easy to change data

[Guest]

I have a split database that tracks employees, hours, jobs and project
numbers. I am a relatively new user. I design/make changes on the database
on a stand alone computer and email it to the user where they store both ends
on the server.

The problem that the user is having is the relative ease with which changes
can be made to the data. There is one user, and I can't permanently lock the
data because if she makes an error or a legit change needs to be made she has
to make it.

The filter by selection works on the stand alone computer where I design and
make changes to the database. However, it does not work on the networked
computer. In the process of trying to use filter selection, data gets
changed. We're now restoring a backup backend but I'm left with the problem
of trying to lock and unlock the database.

I'm thinking of a button that the user has to click to be able to make
changes, otherwise the data is locked. Or, is there another method?

Either way, please advise a solution to this problem.

Thank you!

Pamala

 

[Pamala via AccessMonster.com]

Additional: I forgot to mention that I'm using Access 2003 and that I have
one form for entering data and another for viewing data. It's the form for
viewing data that I need to be able to lock & unlock.

Thanks
Pamala

 

[Guest]

Additional: I forgot to mention that I'm using Access 2003 and that I have
one form for entering data and another for viewing data. It's the form for
viewing data that I need to be able to lock & unlock.

Thanks
Pamala

 

[Joan Wild]

Can't you just set the viewing form properties:
Allow Edits - No
Allow Filters - Yes
Allow Additions - No
That would allow filtering, but not editing.

 

[Guest]

Except that she does need to make changes from time to time because of
errors, either data entry or interpreting the time card wrong.

Pamala

Can't you just set the viewing form properties:
Allow Edits - No
Allow Filters - Yes
Allow Additions - No
That would allow filtering, but not editing.

 

[Joan Wild]

OK, but you said you have a separate form for entering vs viewing; you could
just open the form for entering from the view form.

--
Joan Wild
Microsoft Access MVP

Except that she does need to make changes from time to time because of
errors, either data entry or interpreting the time card wrong.

Pamala

 

[Guest]

I'm sorry, I don't understand. Here's the scenario, she enters timecards in
one form title "New Timecard", she can only see the timecards entered in that
session. The other form "All Timecards" is where she goes to view all the
timecards and make changes to a timecard when necessary. She finds the
timecard that needs correcting and makes changes. Can I make a macro that
turns "Allow Edits" off and on when necessary and attach that macro to a
button that she clicks as needed? This user can not be allowed to change the
properties herself. The db would be locked to additions and changes but
allow filtering (as you suggested), she would find the card that requires
editing, click the button to "allow edits", make the change, then click
another button to turn off "allow edits". If so, how do I do that?

I appreciate your patience while I try to explain what I need. Thank you!
Pamala

 

[Joan Wild]

OK, I didn't understand that your second form was only for additions.

In your All Timecards form you can add a command button to toggle the allow
edits, however it is still in the users hands to actually do this.
Therefore it's still pretty easy for them to make changes inadvertently if
they don't toggle it back to read-only.

Add a command button to the form. In the code below its name is Command4;
change Command4 to whatever the name of your command button is. Open the
property sheet for the command button and go to the Event tab. In the On
Click property choose Event Procedure. Then click on the build button (...)
to the right. Between the lines put...

Select Case Me.Command4.Caption
Case "Edit"
Me.AllowEdits = False
Me.Command4.Caption = "Read Only"
Case "Read Only"
Me.AllowEdits = True
Me.Command4.Caption = "Edit"
Case Else
Me.AllowEdits = False
Me.Command4.Caption = "Read Only"
End Select

Hit Debug, Compile.. and then hit the Save button and close the window. Go
to form view and test it.

I am not sure this will work for you, as I don't know how you have locked
the db to additions and changes, but give it a try.

 

[tina]

you might try the following: add a command button to the form and create an
event procedure in its' OnClick event, and add the following code to the
procedure, as

Me.AllowEdits = True

create an event procedure in the *form's* Current event, and add the
following code, as

Me.AllowEdits = False

the user can click the button to "unlock" the current record for editing. as
soon as she moves to another record, the form is automatically locked again.
so each time she wants to edit a record, she must click the command button
to unlock it.

if you don't know how to create an event procedure, go to
http://home.att.net/~california.db/instructions.html and click the
CreateEventProcedure link for illustrated instructions.

hth

 

[Guest]

Hi Joan, this is what I'm looking for but I must still be doing something
wrong. I also forgot to mention that the two forms are actually a form with
a subform, maybe that's what is causing my problems. The main form has the
name, wage, date, and the subform has the departments, job numbers, straight
time hours, time and a half and double time.

I went into the properties for the main form and changed Allow Edits,
Deletions and Additions to NO and left allow filter as Yes. I also did this
for the subform. When I go into All Timecards I can't select a name, but I
can change the hours or the department or job number, but only once, then it
doesn't allow any changes. I can filter by form, but not filter by selection
(which is fine). Thinking that the two no's might be cancelling each other
out, I went back to the main form design and changed Allow Edits, Deletions
and Additions back to yes, but left the subform as No. But, when I go back
into All Timecards, I can still make one change.

Which is why when I add the code you suggested it doesn't work. What piece
am I missing? I tried compacting and repairing the database but that didn't
help.

This is driving me nuts and I appreciate all your help.

Pamala

 

[Joan Wild]

Hi Joan, this is what I'm looking for but I must still be doing something
wrong. I also forgot to mention that the two forms are actually a form
with
a subform, maybe that's what is causing my problems.

That certainly makes a difference. But I'm confused now. You have forms
named New Timecard and All Timecards. Which is the mainform and which is
the subform?

The main form has the
name, wage, date, and the subform has the departments, job numbers,
straight
time hours, time and a half and double time.

I went into the properties for the main form and changed Allow Edits,
Deletions and Additions to NO and left allow filter as Yes. I also did
this
for the subform. When I go into All Timecards I can't select a name

That's because you set Allow Edits to No - that means that nothing can be
editted on the form, including selecting in a combobox.

, but I
can change the hours or the department or job number, but only once, then
it
doesn't allow any changes.

That doesn't make sense to me, since you set allow edits to no for the
subform.

I think to keep it simple, you should create two forms (each with a
subform). One is for viewing, and one for editing. You can just add a
button to the viewing form to open the editing form for the current record.
i.e.
DoCmd.OpenForm "NameOfFormForEditing",, "EmployeeID = " & Me!EmployeeID

For the viewing form, instead of setting the allow edits property for the
form, you can set the locked property for applicable controls to no - this
will prevent changes to the data.

On your editing form you can add a command button to return to the viewing
form...
If Me.Dirty then Me.Dirty = False
DoCmd.OpenForm "Name of Form For Viewing"

 

[Guest]

Hi, I'm going to try your latest suggestion, but in the meantime I thought
I'd better explain my db a little better in case that changes your
suggestions.

Two forms, New Timecard and All Timecards. Two tables, Timecard Master and
Timecard Detail. New Timecard opens and Timecards are entered. You can only
see the timecards entered in that session, closing the window and reopening
it and it starts at 1 of 1 record again. All Timecards opens to 1 of XXXX
records. Additions can be made in this screen, but I've encouraged the user
to use New Timecard for regular data entry and All Timecards for edits.

Timecard Master is the main form and Timecard Detail is the subform. TM has
the employee name, rate (because we wanted to be able to use the database for
historical data) and date. TD has the department, job, and project numbers
(if applicable) and hours worked (standard, time and a half and double time)
and a running sum of each.

I think I need to leave allow edits turned off so I'll teach the user to use
filter by form instead of filter by selection. That's ok.

Is it possible there is a glitch, update etc that could be causing it to
allow one edit before locking the record? I tried to go into the table to
lock the record (when changing the properties didn't work) and put in the
code to unlock the record as was suggested but it didn't appear to lock the
record. Unless I'm misunderstanding the concept behind locking the record?

Hopefully this makes sense, I'm off to try your latest suggestion.
Thanks again,
Pamala

 

[Joan Wild]

It's the terminology that is causing the miscommunication here. Data is
stored in tables (and no where else). A form is just an interface to this
data.

As I understand it you have two tables Timecard Master and Timecard Detail.
You have two forms, New Timecard and All Timecards.

But, you then go on to say that Timecard Master is the main form and
Timecard Detail is the subform. That is not correct. Your *tables* are
named that, not your forms.

Bound forms have a recordsource. The record source can be a table, a saved
query, or a SQL statement. If you open a form in design view and go to View
Properties, on the Data tab of the property sheet you'll see the Record
Source property - that will tell you the record source for your form. A
form may have a subform on it. A subform is stored as a separate form
object in the database. It will have its own recordsource. Again you can
open the subform in design view and check its recordsource property.

Reading between the lines, I surmise the following:
You have two tables: Timecard Master and Timecard Detail. These are related
(one master to many detail).

You have a form called New Timecard. Its recordsource is Timecard Master.
The New Timecard form has a subform on it. The name of this subform is not
apparent to me, however its recordsource is Timecard Detail. What is the
name of this subform (as listed in the database window)?

You also have a form called All Timecards. Its recordsource is Timecard
Master. The All Timecards form has a subform on it. The name of this
subform is not apparent to me, however its recordsource is TimeCard Detail.
What is the name of this subform?

New Timecard opens and Timecards are entered. You can only
see the timecards entered in that session, closing the window and
reopening
it and it starts at 1 of 1 record again.

OK. The reason for this is that the New Timecard form is set to Data
Entry - Yes.

All Timecards opens to 1 of XXXX
records. Additions can be made in this screen, but I've encouraged the
user
to use New Timecard for regular data entry and All Timecards for edits.

OK, if you want to ensure that additions are not made using this form, you
can set its Allow Additions property to No.

You may be having difficulty because both the All Timecards and the New
Timecard forms are using the same subform. You can get it to work, if you
create a separate subform for the two forms.

You can make it easy for the user to find a record in All Timecards, if you
add a combobox at the top of the form for the user to select the record they
want to go to. (let the wizard walk you through it).

You should be able to use the code I posted earlier to toggle the allow
edits on the All Timecards form.

 

[Mark Baker]

I have just found this string and used the suggestion made by Tina in my
database and it works beautifully, however, to go a little bit further on
this subjuect - I would like to be able to make mass chages in the database
without having to press the button all the time, however, if I create a copy
of the form without the restricted editing ability I would then have 2 forms
to maintain as development of the database happens (and it does happen
regularly). Ideally I would like to have 1 form and I would be able to
overwrite the editing restrictions by use of a password - any suggestions?

Cheers

Mark

 

[Mark Baker]

On top of that, I have discovered that its a bit trickier than I thought! I
have sub-forms within the form I am using, i can lock the sub-forms easily
enough, but I as they are in datasheet view there is nowhere to put the buton
to unlock them in that form itself. How would I reference a buton to the
subform while it is actually situated in the main form?

cheers

 

[Access Newbie Nick]

How about showing it it continuous form view, and arrange it nicely to look
like a datasheet, or better than a datasheet. This view makes it easier to
make changes and you can add a button to the header or footer. However if you
really want to use datasheet i think it is possible providing you specify the
control or object to lock.