Opening a file (PDF) from a subform (list) within a form

[voodoo_ca]

I have some orders that we scan and keep in a computer.


When the order is searched I have a simple "button" added on the current form that uses the value from the order (in this case a 4 digit number), adds that to a file location with .pdf after it and a pdf file opens.

This system is currently in place and working.

Within this same form I have a subform that lists parts orders that are associated with the main customer order.
I want to have the same functionality, where clicking a button opens a scanned pdf with the same name as the parts order - but because there is a possiblity of multiple parts orders in the subform, I dont know... 1. if this can be done 2. how you would select the part order that you want.

Note:
I wanted to add that I currently use a simple followhyperlink piece of code that opens a pdf file by using the customer number field as part of the file name. When the files are scanned now, we make sure they are numbered the same way.

Anyone have thoughts about this?

Thanks
Chad

[rpeare]

Unless your file name contains all of the parts that were ordered as well as the customer number I don't see a way you could do it. With your existing system (the one that works) you have a unique identifier as the file name, unless your 'new' system also contains a unique identifier as the file name (or combination of values that's unique) I don't think you'll be able to accomplish what you want. Are the PDF's created by your system or a different system? If they're created by your system why would you want to export to a PDF only to recall that PDF when you could simply reprint it or bring it up in print preview?

It sounds to me like you'd have to create a report in access to show you all orders for company/person X that contained Item A (your search item) rather than trying to bring up a pdf because, as you noted, a customer could have ordered item A 100 times, how would your code know which order you were interested in if it's not using the order number?

What you MIGHT be able to do is set up a dialog where you search for a list of order numbers based on the customer/part number, then use the ORDER NUMBER The same way you currently are. Basically you're refining your search if you don't know the order number but you know the customer or date or part they ordered.

[voodoo_ca]

Well each of the orders does have a unique number, so thats not a problem.
We take the hard copy of the pdf and scan them, so we can just name them whatever we need.

Here is an example of why I want to pull up the pdf for the customers:
Mrs smith calls in and has a problem with a product
We find her order information in the database
By clicking on a button I am now able to see her particular job sheet with details about what she has purchased
I WANT to be able to click on another button and see the pdf of the order that I would have made with a supplier to show me exactly what I ordered for her as a particular product.

I know visually its not pretty, but here is what the form looks like - on the bottom right is the subform I am talking about.
Maybe the visual helps...

Thanks
Chad

[rpeare]

I guess that's my point, all of the data (the stuff printed on your pdf's) is coming from your database, so why would you need to access a scanned document (images are extreme resource hogs and will bloat your database very, very quickly even if you're just viewing them and closing them) when you can pull up the information in your database or print something directly out of your database (reprint the order worst case). From the way I understand it this is what you're doing:

Create an Order
Print the Order to PDF with the Order Number In the Name of the file
Look up Order Number
Open PDF file with that Order Number in the file name

This is really, really unnecessary *if* all the data involved is coming from your database to start with. Are you concerned that the data for a specific order is changing AFTER you produce the PDF so that if you were looking at the data in your database vs what was produced at the time an invoice was created they may not match? If so you have a much bigger problem.

I'd really advise you not to try and pull up electronic documents through your database unless it's absolutely critical.

All that aside, what you'd likely have to do is create a couple of unbound controls one that contains the vendor ID one that contains the ITEM ID then use those as search criteria for your the datasheet view in the lower right portion of your screen.

[voodoo_ca]

As far as the way I am doing it now for the order - the paperwork contains much more detail than I have in my database.
Essentially it is used onsite and information is written on the order with details.
The pdf files are not embedded in my database, rather they are just in a folder in my computer.
When I was looking at showing scanned orders the first time I read about embedding them in the database and your comment is what I found - dont do it cause the database gets huge fast.

This is really the same thing for my vendor items.
There is more detail on the scanned pdf than I will ever want to enter into the database - most of them I may never need - but if I do need them, then I need to find them based on a customer.

so, all that aside...

More about the unbound control idea...
So my vision is similar to the way I work the pdf opening now.

1. I have a button on screen
2. on click, I want the button to take the PONumber from the subform on the bottom right and use that specific number to open a pdf with the same name.

Right now, it works fine becuase I use it for the order number and I only have 1 order number on the screen (in my screen shot that is 1)
When I have, say 3, different lines in the subform on the bottom right - how do I pick the correct one to open?
I am not sure how to search within that datasheet view that I have.


Heres a thought that I just had:
Can I have an box that scrolls through the listed PONumbers in the datasheet?
That way I could scroll to the right number, then click a button to open the pdf using that particular number.
Does that make sense?


Chad

[rpeare]

Are you actually performing data entry in that datasheet in the lower right corner? or is this form strictly for lookup?

If it's strictly for lookup you can change that datasheet view (subform) into a listbox (on the main form) and have it show the exact same information you have now, then in the ON CLICK event of the list box (assuming the BOUND COLUMN is the order number) you should be able to do with it exactly what you do now in that it opens up a PO with a name that matches the PO number.

[voodoo_ca]

It is also for entry - we just search for the customer, then add the vendor information for items that we puchased.
Does it make it harder if I am entering data too?

Thanks
Chad

[rpeare]

Slightly yes, I don't allow people access to my tables directly, ever (using a subform that's actually a datasheet view allows your users to touch the contents of a table directly), so I'd have to play around with the code. Can you upload a sample of your database so I do not have to re-create the wheel. Just make a copy of your database, put in some garbage information, compact and repair it then zip it up and upload it.

[voodoo_ca]

My database is a frontend / backend deal - so the copy that I have is just forms, no real data.
Can you work with that?

Im constantly learning about this stuff, so I really appreciate it.
How do you accomplish things (similar to what mine looks like) without allowing people access to your tables?

My database started 10+ years ago for a simple task of tracking customers appointments for an estimate - and I didnt start it.
When I took it over, people (along with myself) started asking for more features, allowing us to track so much more.
I have altered and changed things, but I really wasnt thinking about the bigger picture (like I know you should), so im sure things are so backwards and could be done so much better.
I would really love to take the information and start over - but when I look at the data it becomes daunting.

Thanks
Chad

[rpeare]

make a copy of the back end too and put in some garbage data to mimic what you are doing I can re-establish the links when I get it and go from there.

Personally I use unbound forms. I never use bound forms unless it's an application so simple it would be a waste of time to do the programming. In an unbound form the data you see is basically a snapshot of what's on your tables. For instance if I want to see a purchase order, I click on a list of PO's and the data related to that PO is brought up, it's not actually giving me access to the table, then on any data change, update, deletion etc There's a specific function tied to a button or other even that performs the function. It involves a lot more coding and likely not something you want to leap into because you have to program for EVERYTHING, using bound forms is a lot easier to set up but I flat out do not trust end users to touch the data and by using unbound forms I find it easier for me to restrict what they can and can not do.

[voodoo_ca]

Is there an easy way to erase my data but mainatin the structure?
I have lots of stuff (I am embarrased to show you what my database looks like) - for example 22,000 customers (name, address, city, phone, etc)
Just erase 99% of it and leave 5-10 records?

Chad

[rpeare]

I would make a copy of your back end, do not TOUCH your live data at all.

Then go through the COPY of the back end, delete any tables not directly related to your problem, delete the data from the tables that remain and put in some sample data that will allow me to see where you're going with this project.

[voodoo_ca]

Ok, I think I managed to skinny down the database enough with some information to make it work - I hope!

See if that works for you!

Update: I just realized that there is a "Table Leads" reference on the front end that doesnt exist in the backend - its not relevant so you can delete it.


Thanks
Chad

[rpeare]

the front end is missing a form called secondarycontacts subform5

[voodoo_ca]

Yeah, Sorry.
Its not releavant to what I am takling about.
It is just a way for me to keep additional phone numbers for a customer record (if they have any)
Chad