Consulting

Results 1 to 7 of 7

Thread: Automation of process guidance, risk identification and product to use

  1. #1
    VBAX Regular
    Joined
    Aug 2019
    Posts
    56
    Location

    Automation of process guidance, risk identification and product to use

    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

    Click image for larger version. 

Name:	2020-10-06_14-09-42.jpg 
Views:	380 
Size:	78.5 KB 
ID:	27270
    Last edited by mml; 10-05-2020 at 11:05 PM.

  2. #2
    VBAX Regular
    Joined
    Aug 2019
    Posts
    56
    Location
    No responses .....golly gee Not the only one who thinks this is a tough project

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    VBAX Regular
    Joined
    Aug 2019
    Posts
    56
    Location
    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

  5. #5
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,812
    Location
    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"
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    Quote Originally Posted by mml View Post
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Newbie Leroj's Avatar
    Joined
    Mar 2020
    Posts
    1
    Location
    Quote Originally Posted by nnl View Post
    Thanks Bob
    Another Basic question is the form in Excel or Access. And what am I looking at with add in.

    Jet engine? 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •