Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    476

    Asking for more help with Combo Boxes

    I'm still struggling with setting up a few Combo Boxe(s) in a Form.



    In my Wholesalers Vehicles dbs I have 3-flds which I want to be in Combo Boxes in a Form:

    - Make
    - Model
    - Dealership

    Now, I never have any luck in creating Combo Boxes from flds within the Table/query itself because e.g. if I have 10-records/vehicles with the Make, '"Ford" then listed in my 'Makes' Combo Box it'll have 10-"Ford" whereas I just want "Ford" listed once. Likewise with 'Dealerships', if I have 25-records/vehicles at "Harrys Autos" then in the 'Dealership' Combo Box it'll have 25-"Harrys Autos." What I've done to I've thought to get around this delimna (although it's probably not the best solution) is I've created a qurery where the 'Make' is only listed once. Likewise with seperate queries for 'Model' & 'Dealership'. Next when I create a Combo Box for 'Make' I used the Make qeury whereby I only get the 'Make' listed once in the Combo Box. I did the same for creating a Combo Box for, 'Make' & 'Dealership'. Most important to note, for each Combo Box I made sure I have the Control Sourse linked up to the approiate Combo Boxes. When I create the 'Make' Combo Box the Control Source is 'Make' & the same with 'Model' & 'Dealership' I'd learn & remembered that important step from someone here. This right/wrong method has worked for me in the past with other dbs, HOWEVER!!! -- when I go to the Form is not editable/the data is locked, why? Might anyone know what's wrong?

    Any help I'll be greatful for.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Want to provide the project for analysis? Probably has to do with the form's recordsource and jointypes.

    If you select model, no need to select make. Only Ford makes Mustang so if you select Mustang then you know the make (Ford). However, more than one dealer can sell the same model.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    476
    Hey June :-)

    I neglected to realized & state, this query in which I'm trying to turn into a Form with Combo Boxes is a Union Query from 3-other linked Tables from 3-different seperate Dealerships dbss. I'm trying to have one main frame (a hub)/a wholesaler dbs which has all of the Vehicles info & what dealership each vehicle was sold to (or which dealership purchase what vehicle(s)). From there I've created several seperate dealership dbss which individually has the customers, customers' employers, salesRep, etc which I wanted to avoid having in my wholesalers table/dbs. Therefore, without including 'Customers' & 'SalesRep' etc. flds into my wholesalers Vehicles Table hinders me I believe from linking the wholesalers vehicle Table to individual dealerships dbs. Unless I include the customers, customers-employers, & salesReps fields into the wholesalers vehicles Table...? Except, I really don't like that idea; I just wanted the wholesalers Table/dbs to contain all of the vehicle info & what dealership purchased what vehicle at what price. But, when Iv want to link that vehicle table to each individual dealership dbs of course you know that with link tables you cannot add fields like, customers, salesRep. Am I making ANY sense here?

    In some sense I like thechallenge & I create these different ideas in my head, exceptvI'm NOT good at Access so maybe I'm just biting off way too much then I can chew

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    UNION query is not editable. Can't use it as RecordSource for a data entry form.

    Not sure what you mean by 'link tables you cannot add fields like customer, salesRep'. Table joins in queries should not be an issue, even if the 'table' is a link to table in another db.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    476
    June,

    I just spent over 45-minutes trying to explain more details to you & I lost it. I'm starting over, but probably won't get bback to you until tomorrow

  6. #6
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    476
    Let me try to explain by what I mean by link tables. I have 1-dbs which is call a wholesaler dbs which has a Vehicles table that has ALL of the vehicles info as-well-as the dealership who purchase each vehicle/record from this “wholesaler.”. This wholesale database also has a Dealership table with the dealership’s info which isn’t really relevant. Thus far I have 3-dealerships; e.g. Sam’s Auto, Bill’s Autos & Bob’s Autos.
    I also have 3-different databases; one for Sam’s Auto’s, Bill’s Autos, & Bob’s Auto’s. The Vehicles Table from the Wholesale’s dbs is linked to each of these 3-individucally dealership one of these 3-dealersips dbs & in a query I filter the dealership which concurs with the dealership’s dbs. E.g. Sam’s Autos dbs only has the vehicles purchased by Sam’s auto’s – likewise with Bill’s Autos & Bob’s Autos. Now you may be wondering why I want ALL of the Vehicles Tables to be linked. I could have the wholesaler charge a flat rate/fee to ALL of the dealership purchases (vehicles). However (not knowing what I’m doing & bothering your poor soul) I want the Wholesalers fee to come from a percent of what the ‘dealership’ sale each vehicle because each dealship ewill be charge a different percentage from the wholesaler. Maybe that’s not how it’s done in the real world, but as a challenge this is what I’m aiming at.
    There are 3-elements (or flds) which I don’t want in the wholesaler vehicle table, but are crucial in my 3-individual dealerships dbs & there are the Customers, Customer’s employers, & the SalesReps which are different (data) in ALL 3-dealerships’ dbs. I know that flds cannot be added to linked tables, therefore my question is, how (or can I) incorporate these 3-flds into each of my 3-individually dealerships dbs. And if you say I can JOIN them, can you please be very very specific

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You can add fields to the 'linked' tables. That is an edit done in the backend. Can even be done programmatically from the frontend (a procedure I have).

    The 3 dealer dbs can link to the wholesale db and they can also link to each other.

    Linked tables are treated same as any table when building queries. If there is a pk/fk relationship then tables can be joined in a query and related data displayed together.

    I am not understanding your confusion. I know you have been building queries in your development efforts.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    476
    Hi June,

    <You can add fields to the 'linked' tables. That is an edit done in the backend.> Backend...?
    I just linked the main Vehicles Table from the Wholesaler dbs to one one of my dealers dbs & when I tried to open the Vehicles Table in Design View to add the 2-flds, 'CustomersID' & 'VehiclesID' flds I get a messege "Table 'Vehicles' is a linked table whose design can't be modified. If you want to add or remove or change properties or data types, you must do it in the source table."

    Remember & excuse me from repeating myself, but in my Wholesalers dbs Vehicles Tbl (the Source tbl) I don't have the 'CustomersID' nor the 'SalesRepID' flds , however don't I need to have them in my individual dealer dbs in order to join tbls in queries. I'll go back to a question I had, but didn't asked last night, do I need to put in the 'CustomersID' & 'SalesRepID into the Wholesalers dbs Vehicles Tbl (the Source tbl) even thought I rather not?

    I want to say one thing, I NEVER doubt or question anyone's intelligence\insight here for a second! If anything, probably it's "me" many times lacking the proper communication skills.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The 'backend' is the database linking to. This is commonly a reference used in 'split' database configuration. Backend has only tables and frontend has the user interface objects - queries, forms, reports, code modules.

    Wholesaler db links to dealer db. Open dealer db to edit its tables.

    Dealer db links to wholesaler db. Open wholesaler db to edit its tables.

    Don't think you need customers and salesreps IDs in the wholesalers db.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    476
    June,

    I'm sorry, we're not connecting here for some reason; it's ME -- not you.

    No, I don't think nor do I wish to have the customerID & the SalesRepID in the Wholesaler db. HOWEVER, none of the dealership Vehicles Tables which are linked back to the Wholesale db will allow me to make any changes. I cannot make any changes in the indiv. dealship tables because they're linked back to the Wholesales dbs. I'm not mad so please don't take me the wrong way; I'm frustrated in you (NOT with/at) saying that linked tables can be change which i believe you, but it's not happening for me. I can't help to think that somewhere we're getting our wires cross -- I'm not explaining something to you right...?

    How can I tell or show you my deepest appreciation

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    DjcIntn,

    How are you doing? Seems you're plugging away and struggling. I think you may have hit the nail on the head here
    In some sense I like thechallenge & I create these different ideas in my head, except I'm NOT good at Access so maybe I'm just biting off way too much then I can chew
    Working through a few simpler databases with well designed tables would make the world of difference - in my view.

    I think June7 and others would agree than most of the problems stem from poorly designed tables; next is poor communications of what exactly is the problem or opportunity; and the symptom is people getting too involved in the details of Access before they clearly understand the problem or task at hand.

    It's amazing how clear the requirements are when the poster writes definitions of the problem/opportunity and then each of the Entities involved, then the rules elated to each

    Just my $.02

    Anyway, enough soap box, good luck with your project.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Which way is the linking? For example, my split db has accdb file of only tables that sits on the network file server (backend). Each user has copy of the accdb interface file with queries, forms, reports, code (frontend) on their C drive. The frontend has links to the tables in backend. If I want to modify the tables in the backend I go open the backend file. The table I want to modify cannot be in use by any object of any of the frontend copies at that time. Meaning if a query, form, or report is open that uses that table, I won't be able to modify that table structure.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    476
    Ok, now I think that we might be getting somewhere.

    I have the Wholesale Vehicle linked to each indiv. dealership. Let me clairify what I just written, when I have Dealership-A dps open I import/link the Vehicle table from the Wholesaler db. Likewise the Dealership-B & Dealership-C db. However, ought I be going in the reverse direction, importing/linking each dealership Vehicle Table ino the Wholesaler db? Then do a UNION ALL query with the 3-indiv. Dealerships if I want all of the vehicles together? Is that the route I need to take? To be honest with you June, I already have my Wholesale db set up as such, but I wanted to see if I could go in the other direction whereby the wholesaler db would be the mainframe or backend & the indiv dealers would feed off of the Wholesalers dbs, but it's not working that way. I say this because in my Wholersale db I'm not going to have a seperate dealership db for every dealership I have in the Wholesaler db, but I guess I'll cross that bridge when I get there. Like, right now I have 3-seperate dealerships feeding off of the Wholesalers db, but I have other dealerships that I'm not intending on creating a seperate dbs. Orignally after creating my first individual dealership I got the idea of creating this wholesaler db which would expand my inventory just deal directly/only with the dealers & do away with dealing with Customers, SalesRep, etc THINKING how less complicated & nice that would be easier on me. Well, somewhere along the line while working in the Wholesaler db I found myself missing doing the indiv. dealership db with the Customers, SalesReps, so I started A second dealership db & now have 3 dealership dbs, but I'm not planning on creating any more indiv. dealership dbs. Probably what I just wrote has no barring.

    Maybe I'll catch youtomorrow, thanks

  14. #14
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    476
    Hey Orange
    just noticed your post; godd to hear from you. How are you? As you can see I'm still a rock or a stone head in being a very poor dbs designer. Usually in real life people say that I tend to look too far ahead, but with database design I seem to be the opposite. Here's a rhetorical question I often ask myself, if you don't understand how to playthe game or what the rules are then maybe you ought not be playing the game. In other words, if I can't get the fundamentals of Access/database designer then maybe...? Or, if you don't know how to swim then you should be in the pool. No Orange, I'm really not looking for a respons. I cannot argue with anything you said & I'll take your advisement in working through simplier dbs-- have any ideas? You know it's like a guessing game when itcomes to posting, you don'twant to tell too much & what you don't think is iimportant me be very important. Gotto run

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What is purpose of Wholesaler db? Is it only tables or are there forms and reports? Why does it need to link to the dealership dbs? You state 'wholesaler dbs which has all of the Vehicles info'. If Wholesaler has all the vehicles, why does it need to see the vehicle records in the dealership dbs?

    I am not clear on why you have multiple 'dealership' dbs.

    Regardless of who links to whom, should be able to open the db that has the tables and modify table design. I do not understand why you are encountering error when you attempt this.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 9
    Last Post: 06-04-2014, 10:45 AM
  2. Combo Boxes on a From
    By donnan33 in forum Access
    Replies: 14
    Last Post: 01-17-2012, 08:19 AM
  3. using two combo boxes
    By mcguires99 in forum Forms
    Replies: 13
    Last Post: 11-14-2011, 01:19 PM
  4. Help with combo boxes?
    By 107295 in forum Access
    Replies: 5
    Last Post: 02-09-2011, 01:03 AM
  5. combo boxes
    By labrams in forum Forms
    Replies: 0
    Last Post: 04-20-2006, 09:28 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
  •  
Other Forums: Microsoft Office Forums