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

T

TristanM

New member
Local time
Today, 07:59
Joined
Dec 23, 2008
Messages
6
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 :o.

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 :D.
 
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 :cool:

-dK
 
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
 
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
 
TristanM said:
Because of this I can't guarrantee that end users won't try to edit directly in the table rather than using a form.
Click to expand...
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.
 
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
 
TristanM said:
To be honest it'd be handy to have them whilst originally populating the database.
Click to expand...
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.
Click to expand...
The answer is no you can't have that within a table, but you can set it up on a form.
 
TristanM said:
...is it possible to have the table fields dependant on other table fields?
Click to expand...

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
 
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
 
Again - hadn't thought about / properly encountered the datasheet view of a form. That could well do the trick.

Thanks again
 
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.
 
neileg said:
A datasheet view won't support combos either. What you need is a continuous form.
Click to expand...
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.
 
I'm not sure if anyone actually answered the question correctly. The answer is NO, tables do not support cascading combos. The reason is that for cascading combos to work, you need to be able to reference a form field in the query of the dependent combo and you need a requery in the AfterUpdate event of the parent combo to force the second combo to requery. Neither of these are possible with a table.

To simulate this, you can use a form in datasheet view. It looks like a table and it provides the event handling you need to implement the cascade.

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 :)
 
Pat Hartman said:
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 :)
Click to expand...

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...
 
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.
 
You must log in or register to reply here.

Similar threads

R
Delete record in dependent cascading combo box
Replies
4
Views
142
RFreund
R
C
Solved Is it possible to use (like) in a query for a field in another table
Replies
11
Views
261
DHookom
DHookom
T
How do i Set the Selected Field value In a 2nd combo box with a field in 1st combo box once a field is selected in the first combo box?
Replies
2
Views
255
Pat Hartman
P
A
Having trouble with a combo box
Replies
17
Views
544
Pat Hartman
P
S
Speeding up the loading of multiple cascading combo boxes
2
Replies
39
Views
1,307
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom