Problems with Images and Duplicate Entries

[cprobertson1]

Hello again folks!

I run a database of all the standard tooling at my work for the CNC machines, each machine has 20-100 tools assigned to it that never come out the machine: "standard tooling" (and there are about 40 machines - so, a LOT of standard tools!)


Anyway, to make a long story short, I have two problems.

1) - Each tool has an image associated with it. I've procedurally generated the filepath to make things simpler (This way when I have multiple tools with the same manufacturing code it automatically grabs the image for it if it's there, elsewise it inserts an error-picture)

Now this VBA code ultimately sets the "picture" property of an image to the filepath it generated - which is fine if you're just browsing through the database (due to it setting the picture property using the "Private Sub Form_Current()")

I think this is where the problem's coming from - because when I print, it puts the same image on every page regardless of the data there - which may be the end effect of it not opening the form as it prints it (i.e the form is never "current")

Any suggestions?


2) I have duplicate tools in many machines (for instance, almost every machine has a 8mm spotting drill, a group of eight tapping drills, and a probe or two) - when I fill out the details for one, is it possible to copy (some of) those details across to any other tool with the same manufacturing code? Is it a simple case of parsing the database of Manufacturing Codes and just telling it to copy the relevant fields when it comes across a duplicate (i.e a nested for block so it checks every item in the list against every other item) (efficiency isn't a huge concern, only four people use the database and this is more of a one-off scenario to be done before reports are printed off)


Many thanks! And sorry for the wall of text!

 

[Simon_MT]

This what I had on the Detail Section of the report:

Function Exhibitions_PriceImageDetail()
    Call GetPicture
End Function

Function GetPicture()
    With CodeContextObject
        If GetPictureExist = True Then
            .[ImageControl].Visible = True
            .[ImageControl].Picture = GetPicturePath
        Else
            .[ImageControl].Visible = False
        End If
    End With
End Function

Simon

 

[cprobertson1]

Hello again!

Thanks for the quick reply (and sorry for not getting back to you sooner! Been on the shop floor all day!)

I'm not 100% sure I'm following you - how will I make the program actually call the function (we'll call it "get_picture" for the sake of argument) for each record when I tell it to print all records?

My main problem is that it's calling the function once at the very beginning and then keeping that image (i.e not calling get_picture()) for the rest. I could make a kinda hack-ey solution using a for loop and parsing them individually, but I doubt that's the right way to actually do it (and will probably come back to haunt me later)

Many thanks!

 

[spikepl]

Forms are not designed for printing - reports are, and have many facilities not available in forms. You can save a form as a report (under menu File, AFAIK), and so get the report made quickly.

"when I fill out the details for one, is it possible to copy (some of) those details across to any other tool with the same manufacturing code?"

Not entirely clear what you want exactly. When you add a new tool, how is the thing to know where else to add it? Alternatively, you have a table of tools with description and alldata, a table of machines, and then a table tblToolsForMachine, a table into which, for each machine, you select a copy of the data for a given tool.

 

[cprobertson1]

Forms are not designed for printing - reports are

- Ah! Good point! Should have realised

Not entirely clear what you want exactly. When you add a new tool, how is the thing to know where else to add it?

That's the thing, there aren't new tools: and if there are then every machine will get one.

Best explained by example:

CNC Machines #1,31,32,33, 40 and 42 all have the following tool:
"40mm End Mill - Sandvik - R390-31941350 - Insert code 815621 - Screw code 39525"
and the database is organised such that each machine has a list of tools associated with it (i.e CNC 1, tool 1,2,3,4, CNC 2, tool 2,4,5,6 - in this case tools 2 and 4 are common to both machines: but they're in different pockets each machine)

The problem is that each of these tools have lots of supplementary data (i.e the diameter, screw codes, insert codes etc etc etc) - which needs copied to any tool that shares the same manufacture number (R390-some_numbers) in the first example.

Alternatively, you have a table of tools with description and alldata, a table of machines, and then a table tblToolsForMachine, a table into which, for each machine, you select a copy of the data for a given tool.

- now that's not a bad idea! It might be a lot of work though, as it'd mean restructuring the database (the problem is that before I arrived here it was stored in excel tables and I shoved it into a database to try and condense it down a bit - so everything is still mostly unique entries.

I could also keep one entry per tool and simply store the list of machines and pockets for those machines (i.e CNC1-23 for pocket 23 of CNC1) and pull the data that way using a form or a report - but again that means restructuring the entire database.

The final option is to recursively parse and compare each entry in the database to every other entry and then fix any empty values by copying it from the first entry it comes across with the same manufacturer number. It'd be slow and woefully inefficient but it might save me time (at least in the short run).

Ideally there shouldn't be many changes to the machines after this - once we have a list of everything, any changes will be to individual tools and machines. This is just to figure out what we're missing and bring everything up to date!

Thanks for your help btw

 

[cprobertson1]

Hello again! Sorry to double-post

Problem 2 (duplicate data) has been sorted using the parsing method and a python script on the SQL back end itself (took about ten minutes to code - python is by far the simplest language I've used - mayhaps not the most efficient, but great for short one-time things )

Problem 1 though...

I now have that form being used for the report: but I'm again not sure how to make access call the get_picture() function every time it goes to print a page? As I said, it's doing it once for the first image and then not changing it again for the next page (i.e it's being called once at the start and that's it)

I'll play around and let you know how I get on xD

 

[cprobertson1]

Eww - TRIPLE Posting... not cool man... not cool...


I've solved my problem by rewriting the code from scratch and making it work in a different way.

It now concatenates the image directory from the manufacturing code and manufacturer, stores that in the record, and then the image on the report simply references that directory.

The way it used to work was that it called a function to concatenate the directory, load the image, and set it down in the report (more or less in one step) - and since it was a function it had to be called whenever you wanted to change the image (which was fine when form_current() was used, but not when printing)

So anyway, thanks for all your help folks!