Protecting records

[jman]

Is it possible to lock a record from being modified after information has
been entered? My employer wants to make sure that after a record is entered
and a unique ID is assigned (via autonumbering), that the record will never
be able to be changed.

I don't know if this is possible, but he also wants to be able to modify
that record in the future and have it automatically assigned a different ID #
(while keeping the record that the original ID refers to in tact).

I feel like the first request is more plausible then the second, but I don't
know enough about Access to answer either one. Any help would be greatly
appreciated

 

[strive4peace]

Yes, you can prevent users from changing data by always using forms to
add and modify record. You can set the form AllowEdits property to No.

If the user wants to "change" a record, you can have a modify button that

1. creates a new record based on the current record
2. lets them change it

Your table structure needs to be modified to add the following fields:

DateCreate, date, DefaultValue --> =Now()
RecordID_, Long Integer, DefaultValue --> null

since I do not know what kind of data you have, I have used RecordID as
a generic identifier. Personally, I put underscore on the end of the ID
field to indicate the parent ID

We can help you further if you provide us with more information


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[jman]

Thanks for your help. I have set all the allowedits properties to no and
that's exactly what I wanted for that part.

I have also created the fields you said I should create. The situation you
describe (creating a new record based on a previous record without modifying
the previous record) is exactly what I need so please tell me what I need to
do next. If it's easier for you to tell me in general terms, I'm sure I can
figure it out. If it's easier for you to tell me in specifics please let me
know what information you need and I'll let you know to the best of my
knowledge.

 

[mscertified]

"by always using forms to add and modify record".

In order to enforce this you will need to implement user-level security.

Data can be modified in many ways other than by forms. For instance by a
query or by a user simply opening the table directly in datasheet view.
A devious user could even set the Allowedits property back to Yes.

Dorian

 

[strive4peace]

Here is BASIC SQL SYNTAX:

SELECT fieldlist
FROM tablename
IN anotherdatabase.mdb
WHERE conditions
GROUP BY fieldlist
HAVING conditions for fields that are grouped
ORDER BY fieldlist;


What you want to do is APPEND a record to your table based on the
current record displayed on your form

~~~~~~~~~~~ APPEND ~~~~~~~~~~~

An Append Query is a select query preceeded by

INSERT INTO Tablename (field1, field2, etc )

'~~~~~~~~~~~~~~~~~ for instance (and this does not use a table as it
supplies actual values)

dim strSQL as string

strSQL = "INSERT INTO Tablename " _
& " (TextField, NumField, DateField ) " _
& " SELECT '" & strValue & "', " _
& numValue & ", " _
& "#" & datValue & "#" _
& ";"

debug.print strSQL

currentdb.execute strSQL, dbFailOnError
currentdb.tabledefs.refresh
DoEvents

'~~~~~~~~~~~~~~~

** debug.print ***

debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)

2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)

3. cut the SQL statement from the debug window
(select, CTRL-X)

4. paste into the SQL window of the Query
(CTRL-V)

5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL

'~~~~~~~~~~~~~

Since I do not know your fields, you can make the appropriate
substitutions. Obviously, you do not append to your autonumber ID
(IDfield) or DateCreate fields

but you WILL fillin the RecordID_ field with the value of your current
autonumber ID so that you know where the record came from.

after you append the record in code and refresh the table definitions,
you need to find that record, display it, and AllowEdits.

'~~~~~~~~~~~~~~~~~`

'save current record if changes were made
If me.dirty then me.dirty = false

Dim mRecordID As Long
mRecordID = dMax("IDfield", _
"Tablename")
Me.RecordsetClone.FindFirst "IDfield = " & mRecordID

If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Me.AllowEdits = true
End If
'~~~~~~~~~~~~~~~~~

then, on the form OnCurrent event, which happens when the record is changed:

'~~~~~~~~~~~~~~~~~
Me.AllowEdits = false
'~~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*