[General] Database Design for a new Engineer

[nkasaba]

Good afternoon everyone,

My name is Nick and I am a fresh out-of-college Mechanical Engineer working for an automotive supplier. Unfortunately, my first task at my new job was to build a database to store information on our bench-marked parts. It's a small and simple project (and one that would probably take an experienced developer an hour), but I'm still pulling my hair out after a few days work! I am good with computers and the majority of the MS Office suite, but have never built a database or worked with code. I have been reading and reading and studying and trying to make my database work the right way, but am still having no luck in some areas. In fact, it took me the better part of a day just to get the code down for the 'notinlist' function, cascading combo boxes, and a command button to add a vehicle. I came to this website because I found much of the code I am using here and there were many, many threads I read regarding simple issues. I actually considered hiring a developer to help me make a good first impression but no one will work for the short amount of time this will take!




Some overview on the database:

- I have two MAIN tables - tblVehicleOverview and tblPartOverview. These two tables store exactly what you think they would; vehicle and part information. They are linked 1-1 by an auto numbered ID field.

- I have 1 form with two tabs. One is for, yep, you guessed it, vehicle info and the other for part info.

- The first tab of my form is working well (Vehicle Overview). I have managed to get my cascading combo-box's to work which will first select the make and then the vehicle model. I have also managed to get a command button that will add a new vehicle to the 'tblMakeModel' table I have created. All of my drop-down combo-box's relating back to my overview table are working great as well.



My issues begin in the second tab of my form, 'Part Overview'. For starters, I am pulling some information off the first tab (model year, basic vehicle info, component being bench marked, etc) into text box's so that the user will have that while entering the part information. All of it appears to work except for the 'component name' box - it shows up as the ID number instead of the actual name.

The second issue is the BIG one. All of my text box's/combo-box's in the 'part overview' tab are linked back to the 'tblPartOverview,' but all the textbox's display "#Name?". Even worse, these box's do not appear to be associating themselves with the vehicle record in the first tab! If I select something in one of the combo-box's that is working (for instance, exchanged type as 'fin & tube) then move to the next record, it all stays.

Because I am having a very difficult time describing this problem, I attached the file for anyone that may have the time to take a look at. Like I said, I'm sure anyone on this website could build this thing in an hour, so I thought it would be easier to get what I'm trying to say across by giving you guys/gals a link.

I'm very sorry for the long post and having to post a download link. I'm out of ideas and while I know the right way to create this database is to use Access, I make have to just go back to Excel because that's what I know I can make work. I read the rules to the forum and believe I followed them all, but if there is an issue, have a moderator delete this! If anyone could take a few minutes out of their day to take a look at my work, I'd appreciate it more than you would know.

[TheSmileyCoder]

I have taken a quick look at your uploaded db.

Just like your tables are linked through the ID field, you need to make a form for the parts overview, and add that into your Parts tab as a subform, and link it through the ID field.

I will also recommend not using names such as "Height (mm)", but instead name the field in the table intHeight or lngHeight (depending on what data type you assign besides number, long or integer) and then add in the description field "Height in mm" and your ofcourse free to write in the caption of the label Height (mm), but as I wrote, try to avoid it in field names.

I will try to find time to give it a more thorough look, but thats what I got so far.

[TheSmileyCoder]

Also could you please try to describe in more detail what the intended purpose of your application is? And try to remember the only thing I know maintenance on my car is where to put gasoline :P

[nkasaba]

Smiley,

Thanks a TON for taking a look at it. I'm going to re-read your post when I get home and can open up the file and try to do everything you suggested.

Purpose of this database: We will get a part into our office from a certain vehicle. This part may be a prototype part, our own part, etc. We want to put all of this information into a easy to organize database. Eventually, I'll work in reports (or just dump the data to Excel to make pivot tables) so other engineers can come in and view parts that we have bench marked by model, year, part #, etc. I also planned on expanding the current form to have buttons which will hyperlink to documents on our network when more thorough testing is completed (ppt files usually). This will all come in due time - I just need to get a draft together that works to present to management this week!

[TheSmileyCoder]

Could you potentially have several benchmarks for the same part type? 2 similar parts with different serial numbers?

[nkasaba]

Yes absolutely. That's why I thought auto-numbering was the way to go. For example, I think have I have two Corvette ZR1 parts in there already - same car, two different parts (two different records as well). Because some cars can have 3 or 4 different engine options, I figured it was better to just give every car/part combo its own record.