Forms for requests and reviews

[Scullinox]

I've been tasked to create a semi-automated system where a group of 10-20 users submit formal requests for access to documents that are reviewed by a group of 3-4 users. Records for the requests and the results of reviews need to be retained but access to the documents in question will be handled manually. My initial thought was to use an access database with two forms. A user makes a request using form 1 which stores the details of their request in a table when submitted. The reviewer would then open form 2, which would show them the details of the users request and give them the ability to approve or deny the request. When the reviewer hits submit the results of their review are entered into the same table and they can look for other un reviewed requests that require their attention.



I don't have a ton of experience with access so I wanted to check here to see if this is a feasible plan. Also, can I implement access control so only some users have access to Form 2 to perform reviews? Thank you for your time.

[Micron]

You'd create one form, not two, and hide the things that you don't want the sender to see and unhide them for the reviewer. That would be accomplished in pretty much the same way as if you were hiding forms/reports.

tblReviews would need an 'approved' field, which I would make a date/time field. Null means no, a date (and time if desired) means yes. Yes/No T/F doesn't tell you much. I suggest that you create your tables, set the relationships then post a pic of the relationships window before putting any data in tables.

Access security can keep most people from seeing what they shouldn't so you'll probably be OK with that.

[Scullinox]

The relationships window just shows the Table recording the inputs from the forms with no relationships to anything else so I may have set it up incorrectly. I have two access files, one of them has the table and the submission form. I used the "New Data Source" to bring the table into the second file and create a linked table. The second file is password protected and has the review form that fills in the rest of the table info.

It is functional in that the requests submitted in file 1 show up in file 2 and the reviews in file 2 show up in file 1. Again I don't have any experience with data bases so I don't know if this is a normal/good way to do it. I don't know that anything prevents the requester from manually writing to the table that their request was approved.

[Micron]

You're sort of using a split db approach but you definitely are off on the wrong foot. For one thing, you cannot do this with one table.
Also, one file should have forms/reports/queries/code (fe = front end) - the other only tables. You link fe to be tables. Access security can be a big subject depending on how secure you want it to be so you need to research that. That is how you keep people from seeing or playing with things. With what you have now I could probably tell you what the password is within 3 minutes of receiving your db with the links. Maybe start with some reading here (Isladogs is a frequent contributor here)

https://www.mendipdatasystems.co.uk/...594431956.html

[CarlettoFed]

In the meantime, you could attach the file, so that we can use the structure you have created to get an idea of how to act.

[Scullinox]

Sorry both documents are 1300kb somehow despite not having any info in the tables. its telling me the upload limit is 400kb

[pbaldy]

I deleted an empty moderated post. It looked like you were trying to attach something, so this may help:

https://www.accessforums.net/showthread.php?t=70301

[Scullinox]

Sorry for the delay I'm working on a couple other things today. Here are the files I'm using now. Export Control Document Review.zipExport Control Document Requests.zip

[Scullinox]

password on the review file is "review"

[CarlettoFed]

You should clarify:
- what are the various fields of the "Export Control Log" table for?
- which are mandatory in the case of the Request and in the case of the Review.
- for those of type "Short Text" which is the maximum number of characters needed, the default setting of 255 I think is excessive in most cases and would be a waste of disk space.

[Micron]

M$ documentation states that changing the number of characters allowed in a short text field has little effect on space used. Access does not reserve space beyond what is required to hold the data. The field size property primarily governs what you can store in the field.

[CarlettoFed]

Thank you for the clarification.