Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Product DB

  1. #1
    Tradesman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    42

    Product DB

    Im putting together a contact db ( see my other thread ) and now i have the bug would like to build my product db.



    i have a folder full of images 2500+

    I would like to refer to the images in an output.

    I cna only see how to link to a path in a seperate folder.

    however this looks like a manual process . i excel i would concantenate the code.

    Each Image starts with a 5 digit part no. but may be longer of there is more than one image.

    How can I refer to these - I can assume theres no more than 6 images per item.

    as i get more products I get more images - i get a data dump in xl;s which i could update the master file with.

    looks slightly complicated to me but im sure its possible.

    I also have a seperate file with detailed descriptions and Features and benefits.

    ideas please - or where to look

  2. #2
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,307
    What does your data actually look like, what are the file paths, and what is the file naming structure?

    Examples always help with this type of question.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Tradesman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    42
    ok minty i can save the images into the db folder so be something like

    C:\Users\stuart\Documents\Database\Product Images\12345.jpg

    its only for my local use for now-i want to use this and other data to produce a data sheet

  4. #4
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,307
    Okay - that looks like pretty much like I expected, however what happens when product code 12345 has another picture.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,339
    if you are going to put the final product on a shared network drive it should be fairly simple to figure out the path. I would, however, split the database into a front end/back end so each person can have their own copy of it.

    let's say a person had item 12345 selected. you could get a list of files this way:


    Code:
    dim sPath
    dim fs, fsFile, fsFolder
    dim iProd
    
    iprod = x 'this is where you'd put in your product number i.e. pulling it from a text control or list box on a form
    
    spath = replace(currentproject.path & "\", "\\", "\")
    set fs = createobject("scripting.filesystemobject")
    set fsfolder = fs.getfolder(spath)
    set fsfile = fsfolder.files
    for each file in fsfile
        if left(file.name,5) = iprod then
            debug.print file.name
        endif
    next file
    this just a simple looping mechanism to find files associated with your product number. Once you get that part working if you want to use it to update a table in your database you can to get image file names etc. I would avoid storing the path though because if your database and images are in the same places relative to each other you can get the 'starting' location with currentproject.path for each individual user.

  6. #6
    Tradesman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    42
    Hi Rpeare - thats way above my pay grade - i wouldnt know where to start.
    im guessing
    would you add this to a button on a form or report to find the images??

    Im hoping to develop
    1) a screen report showing various facts
    2) a print report eg product datasheet

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,892
    Tradesman,

    Most of us, if not all, had no idea of where to start.
    It's a little bit like flying an airplane. If your intent is to learn "how to bank left", it helps considerably to understand some concepts of flight generally; take off, landing, a few emergency procedures ....bla, bla, bla.
    Creating a report showing various facts requires some knowledge of database concepts; tables where data is stored; names of fields that represent facts...bla, bla,bla

    rpeare has provided a basic looping construct to show the fundamentals of retrieving and displaying image file names.
    If you plan on developing this database yourself, then you'll experience loops and logic as has been presented.
    If you plan on having someone else develop your database, then you'll have to provide a more detailed design.
    One of these choices will be at your pay grade if you proceed with this database.

    Database is not something you'll learn in a day. But you have a project in mind and if you approach it positively and can supply good questions and show your work effort, forum members will advise/assist.

    Good luck.

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,339
    I got a little lost in your explanation of what you want to do as well.

    Your original question seemed to me to be asking 'how can I find all files relating to a part number assuming the first part of the name always contains the part number'

    If that's the case it's a short step to be able to do that and, potentially, build into your database to display the image file via a button click or whatever. However you're now mentioning reporting. If you intend to embed the images in a report that's a different animal and you would have to start with knowing the upper and lower bound of how many images there are because you'd have to build the capability to display them into your report. Further, if your images are for specific things, say one image file has the part specifications, another image file has instructions on installation, another image has pricing and you want to embed those in a specific order in a report I would strongly suggest you follow a strict naming convention i.e.

    12345_Spec
    12345_Install
    12345_Pricing

    This way it might be easier to pull specific elements into specific parts of your report.

  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,339
    Tradesman.zip

    Enclosed is a very simple example using the code I posted before with a small change. After you bring up the file you want you can simply hit ctrl-p to print.

    The zip file contains a folder with a sample database and a subfolder labeled 'images' with 5 .png images as a sample. There is no reason this would not work for *any* file type your computer recognized (i.e. an excel spreadsheet, a word document, a .jpg, .gif, .ppt, etc) as long as you are labeling each file associated with the part with a prefix of five characters that mirror your part number. If your part number can be of variable length the same code will work you would just have to alter it slightly to take the first x characters of the file name instead of the first 5 (which it's currently hard coded to look at).

  10. #10
    Tradesman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    42
    Hi Rpeare, thanks for that it looks really good.

    Its kind of where I'm going - I would prefer to set up a matrix on screen to show the images of the item rather than them pop out indivually? is that possible in access.

    the rest of the info will just be text such as description size barcodes weight etc

  11. #11
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,339
    I do not understand what you're asking.

  12. #12
    Tradesman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    42
    Hi Sorry I was in a bit of rush!

    what I would like to end up with an onscreen data sheet - one that includes product images as well as data such as description etc.

    the drop down details is excellent ( but i would like the option to type the code in as I would do in excel)

    It would be great if the images populated the sheet - each image occupying the same size .

    I envisage a print sheet button incorporating all the data that could be printed out and sent to customers as an option.

  13. #13
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,339
    You really haven't help clear it up, I am going to go back to something orange (another user) uses all the time, what is your actual business practice?

    Can each part have a variable number of image files?
    What is the upper bound of image files for a part?
    What is the lower bound? (i.e. is it possible for a part to have no image files)
    If a part has multiple images that are just pictures of the part do you intend to show all of them or just one of them?
    If a part has multiple groups of items (specification sheet, image of the part, pricing information, installation instructions, etc) can the part have all, some, or none of those images present? or should each part have *all* the images available?

    Ordinarily if I was expecting a set group of images I would rely on code to fill the images on a report at run time but you haven't given enough information in any of your posts about how your business actually runs to point you in a direction. So, given all that, the way you can populate an image on a report at run time is:

    Create an image on a report, do not bind it to a specific image (unbound control)
    Name the control Image1

    in the ON LOAD event of the report

    use the code:

    Image1.Picture = CurrentProject.Path & "\Images\12345_Spec.png"

    if you are using the database I gave you with the dummy images.

    You can use this method to cycle through images for your item and populate unbound image controls. That's where the upper bound and lower bound come in to play and whether or not you want them to be in a specific order etc.

  14. #14
    Tradesman is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    42
    Hi Rpeare,

    thanks for your time in helping me.

    the datasheet is for products - mainly for quotations for retailers so they can set them up on their system.

    I currently have a basic spreadsheet that pulls the date in from 2 spreadsheets -but as you know excel doesnt do images very well. heres a screen grab.

    data comes from microsoft NAv which is very difficult to adapt.

    I would like to come up with a sheet like this plus images of the items - 4 images max.

    then if possible send it to a print out as pdf if possible-theres some extra bits id like to do once its working - pallet qtys etc

    Click image for larger version. 

Name:	screen grab.PNG 
Views:	10 
Size:	30.1 KB 
ID:	38454

  15. #15
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,339
    Urm... if the data is in Microsoft Nav, you should be able to link those data tables into MS access and create a report looking exactly like what you have, then you can just print the report out. Why go through the MS excel at all if the source data is in an ODBC data source?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 04-30-2015, 02:09 PM
  2. Replies: 6
    Last Post: 02-28-2015, 11:57 AM
  3. Replies: 2
    Last Post: 08-14-2014, 11:49 AM
  4. Replies: 4
    Last Post: 04-26-2013, 08:32 AM
  5. Replies: 3
    Last Post: 02-26-2013, 05:07 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums