LookUp Fields...?

[djclntn]

I'm thinking about redesigning my dealership database using LookUp Fields. Can someone tell me what might be some of the Pros & Cons with using LookUp Fields? In the scheme of things will I be able to use these LookUp Field & Forms? Furthmore, will I be able to set up cascading Combo Boxes in Forms using these Lookup Fields? Dave

[pbaldy]

I wouldn't:

http://access.mvps.org/access/lookupfields.htm

[djclntn]

pd, Might you be willing to take a peak of my query & tell me what's wrong. I'm trying to link a few Tables to the tblVehicles in a query. In the tblVehicles I put all of the PK's from the other Tables. -- https://www.box.com/s/flg2pdxovfmi1ijk7mjr

[orange]

I agree with Paul, and I'm sure I have mentioned this in other posts.

NO LOOKUP FIELD IN TABLES

If you are going to do lookups, create proper Lookup Tables and join such Lookup tables to other tables as required.

tblEmployee
EmpId PK
EmpFName
EmpLName
EmpJobCode FK

tblJobCodes
JobCode PK
JobName

To List all Employees and their Jobs

Select EmpFName
, EmpLName
, JobName
FROM  tblEmployee INNER JOIN tblJobCodes ON
tblEmployee.EmpJobCode = tblJobCodes.JobCode

[djclntn]

Orange, Always good to hear from you. Yea, I bagged the idea with going with LookUp Fields. I saved the db in mdb format; I wondered if you'd look at a query. I didn't think LookUp fields was a good route to take -- https://www.box.com/s/fbc64ozv44lgk5iwgbvw

[orange]

In your own words What is your query trying to do?
What do you want to see?

[djclntn]

I want to have a Query whereby I can create a Form which has multiple Combo Boxes:

-Make
-Model
-Year
-ExteriorColor
-SalesRep
-Location

*Now, I want to have 2-cascading Combo Boxes (which I spent over a week trying to implement into my other “Forms” & is why I striped the database & am starting from scratch).

First cascading cbx:
When I choose “Ford” from the ‘Make’ cbx -- in the ‘Model’ cbx I only want to see/pick the models accossiated with Ford. E.g. Focus, F-150, Mustang.

Second cascading cbx:
This “dealership” has 5-different Location each having 4-5 SalesRep per location. So, in my ’Location’ when I pick, “Williamstown, NJ” I only want to choose/see the ‘SalesRep’ from “Williamstown, NJ.”

Orange, I already have this “dealership” dbs set up whereby when I choose any ‘Model’ from my Model cbx the “Make” automatically pops up into the ‘Make field. The problem is, when I originally built this dbs I didn’t envision having e.g. 17-Models for Chevrolet, 15-Models for Ford, etc. Stupid me, I thought I could easily change my cbx over to cascading cbx, but I need to start from fresh.

I’m going to link the original dbs, if you want to view

https://www.box.com/s/55kq93gd86xbqwm6rmfj P.S. I truly respect June who I believe you know. She has this dbs, but I haven't heard anything back from her since the other night. She's probably is busy or haven't had time to get to it, but I don't want June thinking that i'm throwing her under the bus -- I'm NOT! June is GREAT, but it's academic anyway because the last dbs I sent her is totally not normalize.

[orange]

See this post, I know John responded to it last night and it seems very similar. (Cascading combos)
http://www.access-programmers.co.uk/...d.php?t=238622

As I have said before, getting the tables and relationships set up is key to getting anything in or out of a database.

Combo boxes get their data from a record source or a value list , and that record source is not necessarily the same for any other combo box or for the Form itself.

I will look at you database but my approach will be to get normalized tables and a data model (tables and relationships) that fit your business rules. Then, look at forms, combos etc.

[ssanfu]

In the mean time, attached is an example of cascading combo boxes I did years ago so I could understand them. (It is in A2K format)

The first button, 'Enter Data" opens a form that uses combo boxes. The second button, "Display Structure OnScreen", uses list boxes. Basically the same thing, since combo boxes and list boxes are the same thing except a list box is permanently in drop down mode.

I used a lot of paper to get the data correct, and built forms to make entering data easier.

Maybe it will give you some ideas....

[djclntn]

Steve,

In all honesty, VBA programming is completely Greek to me; I don't understand it...Period. When I graduated high school in the late 80’s being physical handicapped I was talked into going to college for computers. I was leery about the idea because I do not have a good/strong mathematical background/mind which I believe is imperative in computer programing. Well, after 3-semesters I totally bombed out. I think that when it comes to working with “applications” I’m fairly good at, however when it comes to “programing” that is my biggest downfall. when it comes to databases. Some people think I’m smart, but for the life of me I cannot wrap my mind around the concept of Data Referential Integrity which I know is the core of database design. My man Orange, always tries drilling into my head how important this is to understand, but no matter how much I read & no matter how many videos I watch (which is my best way of learning; watching videos vs. reading) I just cannot grasp the concept.

June who I so highly respect because she has helped me out of so many jams & taught me so much has a copy of this database, but I think that we’re having some miscommunication issues. In the past with some databases I think that June did away with some Primary & Foreign Keys (AutoNumber) & instead used field names as PK. I don’t mean to be long-winded here, but let me give you one quick example.

When I originally setup my dealership database to save data entry time I had June set it up whereby when enter any vehicle’s ’Model’ then automatically the “Make’ of the model would come up in the “make” field. So, essentially June made the ”Make” field as the PK. June did as I requested. Now I come to realize that I’ve collected like over 50-models in my ’Model’ cbx in my Form. So, if I want to choose “New Beatle” which is a Volkswagon I have to scroll all the way down at the bottom of the 'Model' cbx to find “New Beatle." Yea, I could just type in “New Beatle” but I get a kick out of using drop down menus in my Forms. Then, I got the idea, why can’t I turn the ‘Make’ field into a cbx so that when I choose a make, say, “Ford” in my ‘Models’ cbx will only display models which are Ford's. Well, the way my database is structured as I briefly explained above it’s not going to work because the ‘Make’ is dependent on the ‘Model’ so in order to turn the ‘Make’ field into a cbx I somehow have to detach the ‘Make’ field from the ‘Model’. It’s all screwed up Steve & I feel like I’m talking in circles here. I know what I want, I just don’t know how to create it in VBA. Last I heard is that I think orange is working on my database first to Normalize the database & then work from there.

I hope not to have bored you. Steve, I’m totally homebound/physically disabled as you may or may not know. I can’t use my hands but I typed & used a touch screen by wearing a stick on the end & I peck away at the keys. I don’t play any computer games nor do I do any “Facebook.” For some strange reason I enjoy doing Data Entry – working with Queries, Forms etc. which is meaningless to the outside world. My only problem working with databases is that I know how to use some of the applications, but when it comes to programming/VBA it’s totally Greek to me.

Thanks for your time (if you read this far),

Dave

P.S.
You probably don’t want to get involved with checking out my database & making suggestions since June & orange seem to be helping me, but you’re always welcome at your discretion.

[pbaldy]

From your PM (Access questions should stay on the forum so all may benefit):

Hey pd,
Happy Holidays! If it’s not a bother could you kindly look at my Form, “Vehicles_Entries?” I made both the ‘Make’ & the ‘Model’ flds into cbx. The ‘Model’ fld is a cascading cbx based upon which Make is selected. E.g. if I select “Ford” as the Make, then in my ‘Model’ cbx I ought to only see the Ford’s Model. It’s asking for a Parameter Value????https://www.box.com/s/89bf0m161xfhnix7g5rd
Thanks pd :-)

The parameter prompt is because the criteria in the model SQL points to a different form. The SQL is also goofed up and the requery is missing, but I'll let you play with that.

[djclntn]

"The SQL is also goofed up and the requery is missing, but I'll let you play with that."
Sorry to have bothered you...

[pbaldy]

I was not bothered, just moving the discussion to where I know the forum owner wants it. Did you get it sorted out?

[djclntn]

No Pd,& sorry for sounding smart with you. I've been at this for weeks & weeks with help from June & Orange & for the life of me I just can't get it to fly. My folks thinks I'm nuts because they keep hearing this video: http://datapigtechnologies.com/flash...combobox2.html ALL day long! I'm just really frustrated, but something in my just won't give up. Might you be interesting in looking at my database? No hard feeling I would hope...

[djclntn]

pd, Disregard my last PM. I understand your message & see your point.