473,327 Members | 2,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Making a single record in a table read-only

On issue of an Access 2007 database in runtime mode I need to include in one of its tables a particular record that will be read-only for the life of the database whilst all other records in that table can be added, edited or deleted by the user. Is there a method in Access to do this(?) or maybe I have to use VBA to enter the data every time the table is opened, but this seems messy.
Feb 9 '13 #1

✓ answered by zmbd

This is a real PITA you have there, with other DB you could have triggers to test the data, and in V2010 you now have data level macros that can act like a trigger and check the record too... however, this is more FYI than useful for your situation.

Seth has some fairly useful options for you. I like the on current event check in the forms.

So, what about it the user gets to the table directly (yep it happens)

So my thought for possible work-around:
Hide the data table either thru the properties (or via
naming convention, prefix usys_, that will set the table to act like a system table). Set the hidden property for any queries you base on this table too. The normal user who opens the DB is not usually going to have the "show hidden..." or "show system..." options set for the navigation. Then in your forms you will have to either base the forms on queries/recordsets that exclude the "locked" record, or check for this record as Seth has mentioned.

Second thought, establish a second table that duplicates the data table; however, it will only contain your one record. Then as above, set the property of this table to Hidden as above. Now you can then create a union query between the two tables for use in reports and to display the data in forms (i.e. combobox, listbox, non-updatable text fields) as union queries in MSAcess are entirely non-updatable ((Why is my query read-only?) your "primary" record is safe. For any of the remaining data manipulation, you use the visible data table. Drawback with this method... you will not be able to include this hidden table record as part of a relationship between tables for 1:M etc... you can however include the union query on the key with the option for right/left-joins. This could get messy and not something I've done.

My Third thought is a variation upon a theme, call it 2A, Create the hidden table as in 2; however, keep that record within your main datatable. Now you can use that hidden record to reset the main table record using a variety of means in VBA, on open/close events, etc...

7 2464
Seth Schrock
2,965 Expert 2GB
I don't believe that there is a way to set a single record as read-only. However, you can make it effectively so by testing for it in your forms, update queries and delete queries. On any form that links to that table, you could use the OnCurrent event to test for that primary key and then set the form's Allow Edits, Allow Deletions to false. Update and delete queries could have the WHERE clause exclude it specifically so that it never can be changed.
Feb 9 '13 #2
zmbd
5,501 Expert Mod 4TB
This is a real PITA you have there, with other DB you could have triggers to test the data, and in V2010 you now have data level macros that can act like a trigger and check the record too... however, this is more FYI than useful for your situation.

Seth has some fairly useful options for you. I like the on current event check in the forms.

So, what about it the user gets to the table directly (yep it happens)

So my thought for possible work-around:
Hide the data table either thru the properties (or via
naming convention, prefix usys_, that will set the table to act like a system table). Set the hidden property for any queries you base on this table too. The normal user who opens the DB is not usually going to have the "show hidden..." or "show system..." options set for the navigation. Then in your forms you will have to either base the forms on queries/recordsets that exclude the "locked" record, or check for this record as Seth has mentioned.

Second thought, establish a second table that duplicates the data table; however, it will only contain your one record. Then as above, set the property of this table to Hidden as above. Now you can then create a union query between the two tables for use in reports and to display the data in forms (i.e. combobox, listbox, non-updatable text fields) as union queries in MSAcess are entirely non-updatable ((Why is my query read-only?) your "primary" record is safe. For any of the remaining data manipulation, you use the visible data table. Drawback with this method... you will not be able to include this hidden table record as part of a relationship between tables for 1:M etc... you can however include the union query on the key with the option for right/left-joins. This could get messy and not something I've done.

My Third thought is a variation upon a theme, call it 2A, Create the hidden table as in 2; however, keep that record within your main datatable. Now you can use that hidden record to reset the main table record using a variety of means in VBA, on open/close events, etc...
Feb 9 '13 #3
NeoPa
32,556 Expert Mod 16PB
In simple terms - this is not possible.

For sensible (properly targeted) advice on your actual situation we would need some information on it. Seth and Z have both made attempts to assist, but without a good understanding of your real requirements it's very hard to make such advice suitable for your needs.
Feb 10 '13 #4
I think the solution lies with Seth's second thought but have yet to test it. Just to explain the problem in more detail - one of the fields in a Bookings form is 'Account Holder' whose data comes from the table of Account Holders (and their addresses, bank details etc) as a drop-down list. One of the Account Holders is named 'Private Customer' presence of which is tested for in order to bypass the actions associated with invoicing etc. It is therefore important that it remains in the table verbatim.
It may not be the most elegant way of achieving this decision process but it is already embedded into the design as requested by the trialing operator.
Feb 11 '13 #5
NeoPa
32,556 Expert Mod 16PB
That seems appropriate to me certainly.

I would make one minor modification to the advice in that I'd say that the AllowEdits and AllowDeletions should always be set in the Form_Current() event procedure. How they are set will depend on the record. If you only ever set them to False then they will stay that way for other records. I'm sure this is also what Seth meant, but people often take instructions literally. Especially, as is often the case, when they don't understand what they are doing as well as the person giving those instructions ;-)
Feb 11 '13 #6
Thanks for that (collective) advice. Also my apologies to ZMBD for attributing his 'thoughts' to Seth. It is all useful and I must now set about testing and implementing accordingly.
Feb 11 '13 #7
I went with ZMBD's first thought which works fine. I set up a query that excluded the record 'Private Customer' then used a form driven by that query to provide the front end for the operator to enter new account details into the Accounts Table. Obviously that form doesn't include a record for the Account named 'Private Customer'. The Bookings Form for which the Accounts Table provides a look up list of Account holders it still refers back to the original Accounts Table and 'Private Customer' is still available in the list.
In Runtime mode, user access to that table is not possible.
Another problem solved - thanks!
Feb 27 '13 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Stewart Graefner | last post by:
I have been unable to locate anywhere the answer to my problem. I would like to be able to download/import a single record. This record is stored in many different related (one to one) tables. I...
3
by: Peter Morris [Droopy Eyes Software] | last post by:
Hi all When I bind to multiple records I use a DataList. This allows the web-designer to add code like <%#DataBinder.Eval(Container.DataItem, "Name")%> Whenever I want to retrieve a single...
1
by: Chris | last post by:
I would like to know how to display data column for a single record contains many fields. Likes the first row shows first 5 fields, 2nd row show next 5 fields, etc... I tried to use datagrid,...
1
by: Khamal | last post by:
Hi.. I need to diplay a single record from mysqldb..' i just create the dataset... ----------------------- myConnectionString = "Database=altia;Data Source=development;User...
6
by: zoro | last post by:
Hi, I am looking for the recommended way to retrieve several values from a single record, i.e. a typical lookup scenario. An example would be a query that needs to retrieve user_name, user_addres,...
22
by: RayPower | last post by:
I'm having problem with using DAO recordset to append record into a table and subsequent code to update other tables in a transaction. The MDB is Access 2000 with the latest service pack of JET 4....
2
by: jamieda | last post by:
I have a multiple items form displaying the contents of a table. It has a primary key and the records are ordered by this. I want to be able to manually select a record in the form and then...
0
by: AmateurDBer | last post by:
Hello, I'm looking for Allen. I hate to bother you again, but I am now trying to e-mail the single record from the same form mentioned before (using a macro button) (reference thread about...
1
by: HH | last post by:
To append a single record, without getting a warning if a record already exists, I found out the following syntax works fine as Query in MS Access: first create a simple 'products' table... ...
2
by: Greg (codepug | last post by:
acCmdPrint allows me to print using the Windows Dialog. Since I want to be able to select the printer of my choice, this works well. I put his behind a button on a single form. The problem is,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.