Cascading combo box - like fileds in a table...possible?

[TristanM]

Hi all,

Access noobie so please bear with me and apologies in advance for whatever basic db concepts I've not yet come across or totally grasped .

Is it possible to have a drop-down list in a field, the contents of which is dependent on the entry in another field on the same record.

I know this function is possible if using forms to populate a table using hte Cascading Combo boxes but can a similar thing be achieved when working in the Table view?

Many thanks for any help ... hope this foray away from my comfort zone of Excel will be fruitful .

 

[dkinley]

You could ... but why? The thing is, you have are creating a look-up field in the table. This could be bad for business and considered a no-no. I will edit this post once I find the link to say why

-dK

 

[TristanM]

Thanks for your reply,

Why... good question ... probably because I'm used to editing records in a datasheet-like view in Excel.
THe thing is so are the other people who might use this db. Because of this I can't guarrantee that end users won't try to edit directly in the table rather than using a form. In such cases they could enter data that might be inconsistent with other fields - by making the available entries dependent on entries in other fields I'm hoping to stem somewhat this possibility.

I'm reluctant to lock everything down at the moment until I'm more familiar with Access myself.

Have a feeling it's probably not best practice but interested to see if it is actually possible - is it so disimilar to having the table entries restricted using a Lookup query - seems to me just an extension of that.

Ta T

 

[dkinley]

True - however; just want you to be forwarned with risks involved. I am still looking for the link. I had seen it before and I know that Bob Larson had posted within a couple of weeks ago in response to another post.

-dK

 

[boblarson]

True - however; just want you to be forwarned with risks involved. I am still looking for the link. I had seen it before and I know that Bob Larson had posted within a couple of weeks ago in response to another post.

-dK

That would be:

The Evils of Lookup Fields In Tables

 

[boblarson]

Because of this I can't guarrantee that end users won't try to edit directly in the table rather than using a form.

Actually, you can take quite a few precautions to keep them out of the tables directly.

1. You can hide the tables

2. You can disable the shift key for opening so they can't get to behind the scenes stuff

3. You can disable the F11 key so they can't open the database window or Nav Pane (if in 2007)

And more.

 

[dkinley]

That would be:

The Evils of Lookup Fields In Tables

Aha! Bookmarking it now!

You da man.

-dK

 

[TristanM]

Okay - that's a pretty good and convincing list.

Certainly there's bits in there which I hadn't considered. So is it recommended that they never be used? To be honest it'd be handy to have them whilst originally populating the database. I can then export and reimport the data after having changed the lookup fields to regular (number in this case) ones and now i've typed that and gone through the process in my head that seems an awfully tedious thing to do.

hmmmm

So if I accept that I have been forwarned and that there are risks involved...is it possible to have the table fields dependant on other table fields? We still haven't solved that question. Would be nice to know though I'm going to rethink whether i will implement it or not now.

Thanks for keeping tuned in to the thread.

Tris

 

[boblarson]

To be honest it'd be handy to have them whilst originally populating the database.

That is acceptable. There are several MVP's who prefer to use that method during initial development and then remove them.

So if I accept that I have been forwarned and that there are risks involved...is it possible to have the table fields dependant on other table fields? We still haven't solved that question.

The answer is no you can't have that within a table, but you can set it up on a form.

 

[dkinley]

...is it possible to have the table fields dependant on other table fields?

Is it possible? If doing look-ups. If you want calculated on-the-fly sort of stuff, no. Is it advisable? Again, no.

Not to put you into a corner, but your choices seem to be either create a form or do the import/export.

I would like to point out that you can create a form and use Datasheet View. This would look similar to an Excel spreadsheet to give the almost same look and feel, but yet excercise greater control. Here, you could use the combo boxes to give you the field dependence you want without risking using the lookup fields of the table. Again, it is your project and you have the final say-so - just free advice.

-dK

 

[boblarson]

Is it advisable? Again, no.

Is is possible - No. (Access isn't like SQL Server which has triggers to do this sort of thing).

 

[TristanM]

Okey-dokey. Thanks for clearing that up .. will stop getting frustrated that I can't implement it and actually get on with creating this db.

Thanks for your help guys - s'been an education. Will probably have some more posts coming over the next few weeks as this gets bigger and the reality of what I can achieve far outstrips my actual expertise ... the only way to learn.

Ta again

Tris

 

[dkinley]

Is is possible - No. (Access isn't like SQL Server which has triggers to do this sort of thing).

Hehehe, you went to quick on me ... I amended for clarification.

-dK

Tristan, by the way ... welcome to the forums! Here are some other links that may help you get past the smaller hurdles .....

http://www.techonthenet.com/access/index.php
http://www.functionx.com/vbaccess/

Good luck with your project!

 

[TristanM]

Again - hadn't thought about / properly encountered the datasheet view of a form. That could well do the trick.

Thanks again

 

[neileg]

A datasheet view won't support combos either. What you need is a continuous form. This can be made to look just like a spreadsheet if that's your preference.

 

[boblarson]

A datasheet view won't support combos either. What you need is a continuous form.

Sorry Neil but that isn't true. You can use combo's (including their events) in datasheet views. I use them all the time that way.

 

[wiklendt]

You've had ample advice on the evils of lookup fields in tables so I won't bore you with more advice. Listen to what you gotten so far and welcome aboard

this is a point on which i am confused.

i make some of my FKs based on another table in the DB via the lookup feature in table design. how else is it possible to easily enter foreign key data into a table unless you use these lookups? and by the way, i don't seem to have a problem querying these fields, in my query design, i include both the tables required to get the data...

 

[boblarson]

I'm not sure if anyone actually answered the question correctly.

I did - twice (post 9 and 11).

 

[wiklendt]

You should always use forms for data entry - never work directly in tables. Use combos on your forms to do the lookups. It is not that combos are bad - combos are good. What is bad is defining them at the table level. There are bugs in the way Access uses criteria against fields with table level lookups and other bugs in the way the fields are referenced in VBA. They are a crutch and since you are capable of creating a query with a join, you don't need them at all.

so, let me make sure i understand - if i want a combo for one of my fields, which won't cause probelms, but also for ease of data entry i must do the following:

in my table design, create the field as a text or number or date - anything that isn't a lookup. then, when i make the form, for the control bound to the field, i convert it to a combo box, and make the source... what... a table with the values i want in them? doesn't that kind of make an unrelated table situation? in a relational database?

see, it's just that people are quick to say "don't use lookups" but they don't actually say what you SHOULD use, or HOW...

 

[boblarson]

so, let me make sure i understand - if i want a combo for one of my fields, which won't cause probelms, but also for ease of data entry i must do the following:

in my table design, create the field as a text or number or date - anything that isn't a lookup. then, when i make the form, for the control bound to the field, i convert it to a combo box, and make the source... what... a table with the values i want in them?

Yes, that is true but it is a fact that the same thing is used for a lookup in a field at table level.

doesn't that kind of make an unrelated table situation? in a relational database?

No, that isn't true.
What you CAN do, is if you set up the tables with lookups at first and then create your forms, it will automatically create the combo for you. THEN, and this is the big important piece of info - REMOVE the lookup at table level after your form is created. You can also set up the combo manually if you want (and I do that all of the time) but it is really okay to use them initially to set up things. The thing is that you don't want them after the form is in use because it will only cause problems. In fact there are several MVP's who do it that way as well.

Does that help?