If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
When "Splitting" isn't enough
I've split my test run and most everything works great. However, how do I
provide a multi-user environment that does not allow each user to view/access existing records (to prevent accidental data overwritting, etc.), but at the same time allow the user to "Go Back" to a previous page (form) to make edits while entering data? I've performed all the trial-by-error methods that I can think of, in addition to reading the "Access Bible", but I can't seem to get over this last major hurdle. Please help before I hurdle myself over something very high and steep! Mucho thanks!!! |
#2
|
|||
|
|||
When "Splitting" isn't enough
We're not there. We don't know how your data is structured, nor how you've
organized/structured your forms for data entry. It sounds like you want to prevent existing records from being modified, but allowing existing records to be edited "while entering data". These seem mutually exclusive. Once you (Access) leave a form, the data is saved. Could you consider working with a tab control, so the form isn't actually "left" when you go from tab to tab? Regards Jeff Boyce Office/Access MVP "Geogeek" wrote in message ... I've split my test run and most everything works great. However, how do I provide a multi-user environment that does not allow each user to view/access existing records (to prevent accidental data overwritting, etc.), but at the same time allow the user to "Go Back" to a previous page (form) to make edits while entering data? I've performed all the trial-by-error methods that I can think of, in addition to reading the "Access Bible", but I can't seem to get over this last major hurdle. Please help before I hurdle myself over something very high and steep! Mucho thanks!!! |
#3
|
|||
|
|||
When "Splitting" isn't enough
"=?Utf-8?B?R2VvZ2Vlaw==?=" wrote in
: I've split my test run and most everything works great. However, how do I provide a multi-user environment that does not allow each user to view/access existing records (to prevent accidental data overwritting, etc.), but at the same time allow the user to "Go Back" to a previous page (form) to make edits while entering data? Number of approaches: 1) Install Access User-level security; remove all rights from users and base all your forms on queries with RWOP set. These queries should filter records according to "WHERE EnteredBy = [Username]" As long as the form or the default values set the EnteredBy field correctly, they will be able to see records they have entered and no other ones. 2) Have your users enter data into temporary tables, and then use an append query when they have finished to push all the data into the real tables. Remember to empty the temporary tables; or better create a new mdb to hold them each time and delete if afterwards. 3) Avoid the problem altogether by educating your users about not messing up other people's records. Read them the data protection act, or whatever it takes. IME, human solutions always work better than technological ones. There are advantages and disadvantages about each approach. There are bound to be other solutions too. Only you can decide what your business needs are. Hope that helps Tim F |
#4
|
|||
|
|||
When "Splitting" isn't enough
O.K., I'll try to summarize the essentials:
Data is structured into 18 tables, each containing specific properties (i.e. drilling, construction, testing, etc) for a water well. All supporting tables have been joined to the primary table using a db key number (AutoNumber) in the back end only. Forms, for the most part, reflect the corresponding table, and are joined to each other using basic macros attached to events (AfterUpdate, OnOpen, etc). For example, after entering data in the first form, the user clicks the "Next" button which closes the current and opens the second form. This structure is repeated for each preceeding form until the last form, which returns the user to the main switchboard. The forms are joined so that I can display the "Well Name" at the top of the form after it is entered in the first form (purely for looks). My primary objectives are the following: 1. Allow the user to enter data for a well with the ability to return to a previous form to make a correction, or to even review (QC) what they have entered; 2. Create a data entry environment in which the user can't accidently start entering data into an existing record (overwriting?). After splitting the DB and removing the navigation buttons in the front end (to prevent access to other records) I've encountered the following problem: Enter data for one well and close the program; then, if I open the front end again, the forms displays the data I just entered, and is overwritten if I enter anything new. I thought of using the "tab" approach, but I thought that would require the data to be stored in 1 table, which didn't seem very efficient. If I'm wrong about this, please let me know. Any ideas??...thanks Jeff! "Jeff Boyce" wrote: We're not there. We don't know how your data is structured, nor how you've organized/structured your forms for data entry. It sounds like you want to prevent existing records from being modified, but allowing existing records to be edited "while entering data". These seem mutually exclusive. Once you (Access) leave a form, the data is saved. Could you consider working with a tab control, so the form isn't actually "left" when you go from tab to tab? Regards Jeff Boyce Office/Access MVP "Geogeek" wrote in message ... I've split my test run and most everything works great. However, how do I provide a multi-user environment that does not allow each user to view/access existing records (to prevent accidental data overwritting, etc.), but at the same time allow the user to "Go Back" to a previous page (form) to make edits while entering data? I've performed all the trial-by-error methods that I can think of, in addition to reading the "Access Bible", but I can't seem to get over this last major hurdle. Please help before I hurdle myself over something very high and steep! Mucho thanks!!! |
#5
|
|||
|
|||
When "Splitting" isn't enough
Tim,
I really appreciate your suggestions! I tried something similar to your second (2) idea, except I couldn't figure out how to get around the issue appending without the structure. Each record must have a unique DB key, and my employer insists that it be auto-generated. As such, I can't append to the back end (or something like it) because if I use an AutoNumber in the front end, each record would have a DB key no. equal to 1. Likewise, if I locate the AutoNumber in the back end only, it gets removed whenever a record is appended. I tried getting around this by using the Lookup Wizard in the primary table (this is before I split the DB) to a query that concatenated two controls in the same table, but this won't work as this operation creates a combo box. I'm interested in your first (1) idea, but could you explain what a RWOP set is? Finally, in addition to the "overwritting" issue, I thought it would be good to prevent access to other records since I'm using so many forms (18) for the data entry. I didn't want the user to have to select (or make sure) the correct well each time they opened the preceeding form. Any more thoughts? Thanks Tim!! "Tim Ferguson" wrote: "=?Utf-8?B?R2VvZ2Vlaw==?=" wrote in : I've split my test run and most everything works great. However, how do I provide a multi-user environment that does not allow each user to view/access existing records (to prevent accidental data overwritting, etc.), but at the same time allow the user to "Go Back" to a previous page (form) to make edits while entering data? Number of approaches: 1) Install Access User-level security; remove all rights from users and base all your forms on queries with RWOP set. These queries should filter records according to "WHERE EnteredBy = [Username]" As long as the form or the default values set the EnteredBy field correctly, they will be able to see records they have entered and no other ones. 2) Have your users enter data into temporary tables, and then use an append query when they have finished to push all the data into the real tables. Remember to empty the temporary tables; or better create a new mdb to hold them each time and delete if afterwards. 3) Avoid the problem altogether by educating your users about not messing up other people's records. Read them the data protection act, or whatever it takes. IME, human solutions always work better than technological ones. There are advantages and disadvantages about each approach. There are bound to be other solutions too. Only you can decide what your business needs are. Hope that helps Tim F |
#6
|
|||
|
|||
When "Splitting" isn't enough
One main form, one tab control, 'n' tabs (one for each table).
One form for each table. Add each table's form as a subform on "its" tab in your main form. The main form holds the main info, including well#. The subforms (on tabs) are related, Child-to-Parent, to the main form's well#. (and I am unable to offer suggestions on your data structure, but there's the possibility that your data would benefit from further normalization). Regards Jeff Boyce Office/Access MVP "Geogeek" wrote in message ... O.K., I'll try to summarize the essentials: Data is structured into 18 tables, each containing specific properties (i.e. drilling, construction, testing, etc) for a water well. All supporting tables have been joined to the primary table using a db key number (AutoNumber) in the back end only. Forms, for the most part, reflect the corresponding table, and are joined to each other using basic macros attached to events (AfterUpdate, OnOpen, etc). For example, after entering data in the first form, the user clicks the "Next" button which closes the current and opens the second form. This structure is repeated for each preceeding form until the last form, which returns the user to the main switchboard. The forms are joined so that I can display the "Well Name" at the top of the form after it is entered in the first form (purely for looks). My primary objectives are the following: 1. Allow the user to enter data for a well with the ability to return to a previous form to make a correction, or to even review (QC) what they have entered; 2. Create a data entry environment in which the user can't accidently start entering data into an existing record (overwriting?). After splitting the DB and removing the navigation buttons in the front end (to prevent access to other records) I've encountered the following problem: Enter data for one well and close the program; then, if I open the front end again, the forms displays the data I just entered, and is overwritten if I enter anything new. I thought of using the "tab" approach, but I thought that would require the data to be stored in 1 table, which didn't seem very efficient. If I'm wrong about this, please let me know. Any ideas??...thanks Jeff! "Jeff Boyce" wrote: We're not there. We don't know how your data is structured, nor how you've organized/structured your forms for data entry. It sounds like you want to prevent existing records from being modified, but allowing existing records to be edited "while entering data". These seem mutually exclusive. Once you (Access) leave a form, the data is saved. Could you consider working with a tab control, so the form isn't actually "left" when you go from tab to tab? Regards Jeff Boyce Office/Access MVP "Geogeek" wrote in message ... I've split my test run and most everything works great. However, how do I provide a multi-user environment that does not allow each user to view/access existing records (to prevent accidental data overwritting, etc.), but at the same time allow the user to "Go Back" to a previous page (form) to make edits while entering data? I've performed all the trial-by-error methods that I can think of, in addition to reading the "Access Bible", but I can't seem to get over this last major hurdle. Please help before I hurdle myself over something very high and steep! Mucho thanks!!! |
#7
|
|||
|
|||
When "Splitting" isn't enough
Hmm...I think you may be on to something here. I'll do a test run and post
my results. Thanks! "Jeff Boyce" wrote: One main form, one tab control, 'n' tabs (one for each table). One form for each table. Add each table's form as a subform on "its" tab in your main form. The main form holds the main info, including well#. The subforms (on tabs) are related, Child-to-Parent, to the main form's well#. (and I am unable to offer suggestions on your data structure, but there's the possibility that your data would benefit from further normalization). Regards Jeff Boyce Office/Access MVP "Geogeek" wrote in message ... O.K., I'll try to summarize the essentials: Data is structured into 18 tables, each containing specific properties (i.e. drilling, construction, testing, etc) for a water well. All supporting tables have been joined to the primary table using a db key number (AutoNumber) in the back end only. Forms, for the most part, reflect the corresponding table, and are joined to each other using basic macros attached to events (AfterUpdate, OnOpen, etc). For example, after entering data in the first form, the user clicks the "Next" button which closes the current and opens the second form. This structure is repeated for each preceeding form until the last form, which returns the user to the main switchboard. The forms are joined so that I can display the "Well Name" at the top of the form after it is entered in the first form (purely for looks). My primary objectives are the following: 1. Allow the user to enter data for a well with the ability to return to a previous form to make a correction, or to even review (QC) what they have entered; 2. Create a data entry environment in which the user can't accidently start entering data into an existing record (overwriting?). After splitting the DB and removing the navigation buttons in the front end (to prevent access to other records) I've encountered the following problem: Enter data for one well and close the program; then, if I open the front end again, the forms displays the data I just entered, and is overwritten if I enter anything new. I thought of using the "tab" approach, but I thought that would require the data to be stored in 1 table, which didn't seem very efficient. If I'm wrong about this, please let me know. Any ideas??...thanks Jeff! "Jeff Boyce" wrote: We're not there. We don't know how your data is structured, nor how you've organized/structured your forms for data entry. It sounds like you want to prevent existing records from being modified, but allowing existing records to be edited "while entering data". These seem mutually exclusive. Once you (Access) leave a form, the data is saved. Could you consider working with a tab control, so the form isn't actually "left" when you go from tab to tab? Regards Jeff Boyce Office/Access MVP "Geogeek" wrote in message ... I've split my test run and most everything works great. However, how do I provide a multi-user environment that does not allow each user to view/access existing records (to prevent accidental data overwritting, etc.), but at the same time allow the user to "Go Back" to a previous page (form) to make edits while entering data? I've performed all the trial-by-error methods that I can think of, in addition to reading the "Access Bible", but I can't seem to get over this last major hurdle. Please help before I hurdle myself over something very high and steep! Mucho thanks!!! |
#8
|
|||
|
|||
When "Splitting" isn't enough
My memory is a little fuzzy from when I did something similar a couple of
years ago. When you open the form, check the form property NewRecord. If it is false, change the allowedit property to false. Put a button that will allow the user to switch to edit mode if needed. Another possibility is to put your form into a subform on another form and set the enable and lock properties as needed. You also could lock each individual control on the form depending on your needs. I hope this gives you some more things to try. "Geogeek" wrote: I've split my test run and most everything works great. However, how do I provide a multi-user environment that does not allow each user to view/access existing records (to prevent accidental data overwritting, etc.), but at the same time allow the user to "Go Back" to a previous page (form) to make edits while entering data? I've performed all the trial-by-error methods that I can think of, in addition to reading the "Access Bible", but I can't seem to get over this last major hurdle. Please help before I hurdle myself over something very high and steep! Mucho thanks!!! |
#9
|
|||
|
|||
When "Splitting" isn't enough
"=?Utf-8?B?R2VvZ2Vlaw==?=" wrote in
: I really appreciate your suggestions! I tried something similar to your second (2) idea, except I couldn't figure out how to get around the issue appending without the structure. Each record must have a unique DB key, and my employer insists that it be auto-generated. As such, I can't append to the back end (or something like it) because if I use an AutoNumber in the front end, each record would have a DB key no. equal to 1. No: autonumbers are out so you'll have to write your own. There are lots of possibilities: interrogate the (real) backend for a new key number each time; use a compound key using a serial number that increments for each user with the user name itself -- this can be managed entirely within the front end; forget the keys in the front end, allow the back end to allocate them at append-time. If you have a complex data model with lots of foreign keys, then this is probably not a good idea! One of my favourites is to use a combination of 1 + 2: each FE has some database properties like NextSerialNum and TopSerialNum; you can set this in the UI so it's easy; each user gets a widely separated number range so as to minimise collisions. Try 120000 to 130000, 220000 to 230000 and so on. If your users are likely to insert over ten thousand records, just space them a bit more! Long integers go up to 4E9 so you should be okay for a bit. at append time, you get the NextSerialNum and attempt to INSERT a new record. If it succeeds, you use this record as your new record. If it fails (unlikely, but someone else might have inserted a rogue somewhere), you increment the NextSerialNum and try again. If it hits TopSerial then you error out and tell the user to ask the Admin for a new chunk of numbers. There are lots of alternatives. Try googling for Access Custom Autonumbers, but keep an eye out for multi-user safe versions. One final thought: are you sure there are no natural keys already in the whatever-you-are-storing that will a priori guarantee uniqueness? Just because your boss wants an artificial key, it doesn't mean he knows what he is talking about! All the best Tim F |
#10
|
|||
|
|||
When "Splitting" isn't enough
In addition to all of the other ideas...
One thing you could try is setting a global flag (in the first form) indicating that you are starting a new entry. Then, on opening each subsequent form, check this flag - if it is set, allow the user to make edits; if it is not set, don't allow edits. Carl Rapson "Geogeek" wrote in message ... I've split my test run and most everything works great. However, how do I provide a multi-user environment that does not allow each user to view/access existing records (to prevent accidental data overwritting, etc.), but at the same time allow the user to "Go Back" to a previous page (form) to make edits while entering data? I've performed all the trial-by-error methods that I can think of, in addition to reading the "Access Bible", but I can't seem to get over this last major hurdle. Please help before I hurdle myself over something very high and steep! Mucho thanks!!! |
Thread Tools | |
Display Modes | |
|
|