It is a whopper! create PDF then insert back into record and delete form.

[mrshgraham]

Wow, where to start. I am an inspector working for an insurance company that has a very inconvenient, inefficient work flow. about 15 years ago I used excel heavily and thought I could make this in Access using the little VBA that I knew, bit of a mistake there.

What I need to accomplish.
-I need the underwriters to fill out a form an Inspection Request

-The request to pull and compare the property latitude and longitude

-Compare the property latitude and longitude to the lat long of the inspectors home address

- Assign the request to the nearest inspector

-Have the inspector fill out a form - the inspection report, including attaching multiple photos

- Have the inspection report returned to the underwriter who requested it


Wow!

I have seen that attaching photos will take up a lot of room and cause issues. Would it work to have the report print to pdf, then insert the pdf into the record, then delete the photos from the system?

I am sorry, I bit off more than I can chew, any suggestions for the rest of this mess would be greatly appreciated as well.

I am sorry, and thank you!

 

[June7]

Embedding objects in database can quickly use up Access 2GB size limit. Most of us recommend leaving objects external and saving file path in text field.

Determining what is 'nearest' - for a start look at https://www.tek-tips.com/viewthread.cfm?qid=1310115

 

[theDBguy]

Sounds like a mouthful, we should take smaller bites then. June7 already gave you a way on one piece of the puzzle. We also need to clarify your current setup. Are you using a split configuration? Is the backend data file on a network share? How are the inspectors filling out the report? What do you mean by "returning" the report back to the underwriter? Are you thinking email?

 

[The_Doc_Man]

Here is a thought for you regarding the "picture" issue, telling you how I MIGHT approach a part of the problem.

There is such a thing as an image control. One of its properties is called, oddly enough, .Image, and you use it by loading the file spec of an image to that property. When you then display the image control in form view or report view, you don't see the spec, you see the image found in the file with that spec. You don't need to store the image file in the DB, you only need to store its name. But the name must unequivocally point to the right file, so that usually means storing the file in a sub-folder of the place where the database backend is kept - and renaming the files according to some internal numbering scheme, like a contract number plus sequence number, or something similar.

Your problem will probably include multiple images but I'll bet they will be variable in number. So that means that the detail section of the report has to contain the images. Which in turn means that you might need to thrash around your design a bit for the things that AREN'T images.

The Inspection Request can be done on an ordinary Access form storing Lat/Lon numbers as strings, though you might have issues if the Lat/Lon values include fractions of seconds - because in that case you would have to "roll your own" angles. The Access date/time routine can convert minutes and seconds but won't touch fractions and won't be happy with degrees/minutes/seconds for more than 23 degrees 59 minutes 59 seconds. So I see a home-grown pair of formatting routines - one for input, one for output. The good news is that you can search this forum for articles on geopositioning or latitude and longitude computations. That has come up before.

I absolutely agree with theDBguy that you have a mouthful to chew on. This is very clearly a case of "divide and conquer." Split the pieces-parts into smaller lumps and you will have a shot at paring this down to size. And once you have split the parts, you will certainly be able to ask a lot of questions about the individual goals and approaches.

 

[mrshgraham]

Thank you,
Yes I am planning on a Split configuration, and different privileges for the Underwriters vs. Inspectors.

I would like a central form, or Hub for Underwriters with two sub forms, One for newly completed inspections awaiting their review, and a second one for Inspections that are currently under review. a button a the top that says "submit new request" I borrowed the main screen from Northwinds, the Other two I created.

I would like a similar form for the inspectors, they will attach pictures to it, hit a submit button, then the Underwriters will see "New Inspections in their central form

 

[gemma-the-husky]

do you really assign the job to the nearest inspector. So the nearest guy to London gets every London job??

 

[gemma-the-husky]

Thank you,
Yes I am planning on a Split configuration, and different privileges for the Underwriters vs. Inspectors.

I would like a central form, or Hub for Underwriters with two sub forms, One for newly completed inspections awaiting their review, and a second one for Inspections that are currently under review. a button a the top that says "submit new request" I borrowed the main screen from Northwinds, the Other two I created.

I would like a similar form for the inspectors, they will attach pictures to it, hit a submit button, then the Underwriters will see "New Inspections in their central formView attachment 74892
View attachment 74893
View attachment 74894

I think you are considering this from the wrong aspect. You need to go back to your data, and decide every bit of data that you need to include. Then allocate these items to tables, so that you get a well normalised data structure.

Now you are ready to start designing your forms, and interface. Your thoughts regarding the data analysis will be informed by the interface you are considering, but you really don't want to get into designing your forms and so on until you feel the data structure is about right.

 

[The_Doc_Man]

I was hesitating to trot out my old advice, but Dave broached the subject so here goes:

Old programmer's rule #1: If you can't do it on paper, you can't do it in Access.

Stated another way, Access is an expert on one and ONLY one thing: Making database tables, queries, reports, forms, macros, and modules. It knows NOTHING about setting up inspections. You are the subject matter expert. So you need to build a road-map that lists everything you want to do and describes what you think is needed to get there from where you are. If you do this, you will be able to delineate the forms and reports you need and the tables and queries to support getting there. You will start programming and when you say, "Now what was it I was going to do about this part?" you can look at your road map. And if you don't have a road map, how will you EVER know that you have reached your intended destination?

Old programmer's rule #2: Access won't tell you anything you didn't tell it first.

You need to know everything that you wanted to get out of your app and THEN verify that you have a source for everything. If you need X, you need a source of X. If you need XYZ, you need sources for X, Y, and Z and the formula that combines them into XYZ. This sometimes means going backwards through your code to verify reliability of inputs to get the desired data. But that is what you need.

During the process of identifying what you want for output and what you need for corresponding input, you will identify the things that you need to keep in your database, which is exactly in line with Dave's advice. You will have to identify everything your database needs to track. For this process, I sometimes suggest use of a dry erase board, markers, and a pad of sticky notes to represent each table you identify. Draw lines between the notes to show relationships and data flow.

 

[MrHans]

With regards to the allocation of the nearest inspector the solution could be to use the Google Maps Distance API.

You can use this API to determine the distance between sets of coordinates. It will be a loop calculating the distance for each inspector and then returning the nearest one.
You can indeed search this forum for an example and for sure we can help you out as well.

But as said before you will definitely need to devide and conquer by working through your list of requirements one at the time.
And it starts with the roadmap of your process and designing the tables that you will need for it.

 

[mrshgraham]

Oh, Thank you, yes, I made a sort of "data map" and I have gotten a Google API, and have gotten a hold of an Excel doc. that has the lat. long. for every zip code in the state, and have put that into Access. I thought I would write some type of If statement for when Google returns null in the event it doesn't recognize the address. I found code for The Great Circle Distance, and dug out my old Trig book (FUN! I miss it). I have a login screen working and the tables created. I have bits of code borrowed from here and there, with the credits of course. Now I am going to attempt to glue it all together.

The last bits of information Ii was collecting/researching was about the photos, I had looked up file sizes of PDF's vs. jpg. and wondered if that may of been a good way to get the job done, but wasn't quite sure.

I am also not quite sure how to move some of the data around, for example, keeping track of the status, I have set up a table with a StatusID and each status "Request submitted, Assigned, Inspection Scheduled etc..." Once the Underwriter hits the submit button, I want the event to trigger the status to change. I am not sure if I should set this up like like Excel, If Status =1 then Status is "Pending" I am pretty foggy on this. Gluing this all together is going to be a mess.

gemma-the-husky, YES! we do assign inspections this way, I do the inspections for the entire east 1/4 of my state, and my poor supervisor has to pull each of them up on Google and try to figure out where they all are and who to give them to, about 200 per week.

Thank you all so much for your time and imput I am so gratefull for anything you have to offer.