Good morning.
I'm very new to Access, and as such have already viewed several excellent start-up videos on YouTube, but haven't been able to get a feeling that what I want to achieve with Access is possible or not. So I'd very much appreciate some input as to my requirement below.
Essentially, I head up the tech side in a small CAD bureau (or drawing office if you prefer) and I'm looking to have a database that keeps track of all the drawings we issue to our clients. The information stored will be relatively basic, such as the revision number, date issued, who issued it and the drawing status. The intention is to link an Access database into AutoCAD via VBA (which is perfectly doable and I'm proficient in both AutoCAD and VBA) and allow the user to add and read information regarding to current and previous projects through a Userform such as in the one below that I've cobbled together.
What I'm struggling with, is how to set up the database properly. In the image above, the Client combo box is populated from a list of Client named directories on the server. When that changes the Project combo box is populated from a list of Project named sub folders within the Client folder. Similarly, the discipline list is populated by the next level of sub folders (namely 'electrical', 'mechanical', 'drainage', etc), and a change to that list fills the Drawing Number box with the files in the discipline folder, all of which are named exactly as the drawing they contain (one drawing per file).
What I'd like to happen, is for the user to be able to click on one of the drawings in the list and the boxes to the right populate with all the historical info about that drawing. Drawings can go through any number of revisions so it's very important that we can keep track of it all.
On the flip side, the other part of the Userform automatically takes information from the Drawing (as listed above) plus the user login name, and once the form is satisfied and the user click a button, the drawing is prepared for issue to the Client. At this point I need the userform to update the database accordingly with the new information.
So there we have it. Creating a simple database in Access to store 'Drawing Info' is easy enough, just by following the tutorials. But what I'm struggling with is how to add the necessary sub-layers (for want of a better word) for Clients -> Client Projects -> Project Disciplines (this field is a fixed list of options) -> Discipline Drawings (taken from available filenames) -> Drawing Info. So that whenever a combo box is changed, the code can go back up the hierarchy of the database levels and repopulate the form accordingly. Incidentally, I already have a program that generates new Client / new Project folders and then creates all the standard sub-folders required for a job, so the database needs to be able to cope with adding new clients and projects, as well as drawing and drawing info.
I hope the above is clear enough. Just to clarify a point, this inquiry is about creating the Access database, not the VBA code to read and write to it.
All and any help with this would be very much appreciated.
Kind regards
Graham