PDA

View Full Version : Automation of process guidance, risk identification and product to use



mml
10-05-2020, 09:11 PM
Hello all
I am looking for advice on automating a process to reduce manual typing . I am a University health employee who receives undergrad client requests
The current process is an Undergrad fills out a paper request ( chit ) , this manually typed into a spread sheet . Yearly we accept over 5000 request and that is 5000 manual entries !

The chit contains info as : Undergrad ID , Date request submitted , Date of appt, Duration of appt , Interpreter required and Module ( mod A mod be Mod c etc )
I have 250 undergrads that need to make client requests. Each Undergrad has access to a networked PC and have their own email addresses .

I had thought to do this via Google forms however the form ends up being so long that it becomes cumbersome. The automation works ok though

I then thought that the a user form may be the best solution with the request sent to a shared file . I have a few queries on this :

1. is it best to place the user form on each desktop
2. how to mange more than one Undergrad accessing the User form at one time
3. What are the risks of a user form on a networked PC

This has to be low budget as there is no budget to speak of only the MS . I am not opposed to MS Accces BUT the licence woudl only be for those who are auctioning the request .

If anyone could provide some guidance or alternatives I would be grateful

27270

mml
10-13-2020, 08:44 PM
No responses .....golly gee Not the only one who thinks this is a tough project

Bob Phillips
10-14-2020, 10:26 AM
Simplest way IMO is to build an addin with the form and distribute that, and save the data on a centralised Access database (you don't even need an Access license for each student, as you can read/update the database directly from Excel using the Jet engine.

mml
10-14-2020, 01:13 PM
Thanks Bob
Another Basic question is the form in Excel or Access .And what am I looking at with add in .

Jet engine ? So I can understand fully what key words should I be using or sites to gain a better understanding of the process

SamT
10-14-2020, 01:27 PM
Network Access... I would create a VBA UserForm for data Entry in an xlsb workbook for them to download and email back. I would have as much validation code in the book as possible and store the Data on a hidden sheet. The submit button on the UserForm should email only the data from the UserForm. Structurally format the Email to suit your code needs. Saving the data on board is so Students can keep the original book with data, you won't need it.

At your end, when receiving the Data back, all you need is that data from the UserForm. Once a day import the data from all RCVD email with some VBA.

You control the UserForm and you control the Email.

Get a unique email address just for these submissions. Later you can add more submission types to it by using Unique "Subjects"

Bob Phillips
10-14-2020, 02:36 PM
Thanks Bob
Another Basic question is the form in Excel or Access .And what am I looking at with add in .

Jet engine ? So I can understand fully what key words should I be using or sites to gain a better understanding of the process

The form would be in Excel. The addin is similar to a VBA enabled workbook, saved as an .xlam. You would distribute to all users.

To access the database, you need to design your database, build it (that user will need a license), connect, and use ADO and SQL. I could try and knock up a simple example for you tomorrow, and you/we could build on that.

Leroj
10-28-2020, 04:28 PM
Thanks Bob
Another Basic question is the form in Excel or Access. And what am I looking at with add in.

Jet engine? Cpaas (https://voximplant.com/blog/cpaas) system? So I can understand fully what key words should I be using or sites to gain a better understanding of the process
I guess you are right. That makes much sense, my opinion.