Multiple Cascading Search Form To open attachments

[jjake]

Hello,

I have 4 Tables

tblPumpManufacturer
PumpManufacturerID
PumpManufacturer

tblPumpModel
PumpModelID
PumpModel

tblPumpManualType
PumpManualTypeID
PumpManualType

tblPumpManuals
PumpManufacturerID/PumpManufacturer (Lookup)
PumpModelID/PumpModel (Lookup)
PumpManualTypeID/PumpManualType (Lookup)
Attachment (Attachment Field) - PDF's

I have a search form with 3 combo boxes

frmPumpManualSearch
cboPumpManualType - PumpManualTypeID/PumpManualType (Row Source)
cboPumpManufacturer - PumpManufacturerID/PumpManufacturer (Row Source)
cboPumpModel - PumpModelID/PumpModel (Row Source)


With a button at the bottom [cmdSearch]

I would like to be able to select my pump manual type, then the manufacturer which would then filter the model in cboPumpModel

When i click search it would open the PDF associated to the data matching all 3 combo boxes.

e.g data

Owners Manual (Type)
Wilcox Pump (Manufacturer)
PX8 (Model)

The manual would open that matched this criteria.

Thanks

 

[moke123]

Personally i would'nt use an attachment field. I would store the path to the file in my db and and use application.followhyperlink to open it.

 

[jjake]

My reason for using the attachment field is I felt it would be easier down the road for other users to add manuals to the database without the risk of storing the file in the wrong folder or adding the wrong hyperlink to the database.

 

[moke123]

depending on how you do it you may not even have to store a path. it all depends. For instance you could store all the pdfs in one folder and just store the name of the pdf. Then you only have one path and you concatenate the name onto it.

generally, depending on size and quantity, attachment fields can bloat a database rather fast and are slightly more complicated to code for (at least for me.)

 

[CJ_London]

for cascading combo's assuming this is a single form

for the cboPumpManufacturer rowsource put something like

SELECT ID, Desc FROM tblManufacturers WHERE ManualTypeID=[cboPumpManualType]

then in the cboPumpManualType after update event put

cboPumpManufacturer.requery

do similar for cboPumpModel

 

[jjake]

I got the cascade working, Now I just need the attachment part

 

[CJ_London]

I'm with Moke on this one, I don't use attachments for the reasons stated. so regret I can't help. But look to the bottom of this thread and you will see a number of threads on the same subject

 

[jjake]

So If I were to take the hyperlink option, how would I open the path that met all 3 criterias?

 

[Minty]

The best way would be to adopt a naming and storage convention that assures you of uniqueness.

So you may want to store the pdf's by manufacturer(folder) and then pump model (filename) . This won't work if the pumps have weird characters in them, so you may want to simply impose an unique ID per record and store that as the file name.

In either example you can then simply build the path and filename from the combo and have a button to open it.

I would also store the UNC network path as a field in a master table and look that up as a constant, rather than hard coding it. If you ever need to move your stored documents on the network you can simply change the one value in the table without any code changes.

 

[jjake]

The best way would be to adopt a naming and storage convention that assures you of uniqueness.

So you may want to store the pdf's by manufacturer(folder) and then pump model (filename) . This won't work if the pumps have weird characters in them, so you may want to simply impose an unique ID per record and store that as the file name.

In either example you can then simply build the path and filename from the combo and have a button to open it.

I would also store the UNC network path as a field in a master table and look that up as a constant, rather than hard coding it. If you ever need to move your stored documents on the network you can simply change the one value in the table without any code changes.

Well I don't think the Link option is going to work for me. I have it working BUT every file I try to open gives me the following response


Run-time error 53 File not found


My files are all saved on a shared drive across our network along with the split database. From the research I have been doing, it's a security related issue.

Any other opinions on the attachment option

 

[moke123]

have you done a debug.print or another method to ensure that the path is correct? a common mistake is no trailing slash on the path before the filename is concatenated.

ie.
C:\Users\DB\PdfFilesYourPDF.pdf
vs.
C:\Users\DB\PdfFiles\YourPDF.pdf

 

[jjake]

have you done a debug.print or another method to ensure that the path is correct? a common mistake is no trailing slash on the path before the filename is concatenated.

ie.
C:\Users\DB\PdfFilesYourPDF.pdf
vs.
C:\Users\DB\PdfFiles\YourPDF.pdf

S:\CST MAINTENANCE\Maintenance Database\Saved Data DO NOT EDIT\Attachments\Manuals and specs\Pump Manuals\Pneumatic\Yamada\Owners Manuals\NDP5-15OM

This is my path.

 

[CJ_London]

no file suffix like .pdf?

 

[jjake]

I added that but I still get the same error.

 

[jjake]

Nevermind, got it!