Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1148
  • Last Modified:

Access 2010 table-field in drop-down that limits choices based on other field

I am trying to limit the available values in a drop-down to show choices based on the value of another field.  I have a table in Access 2010 named Tasks that has a column called "WorkOut".Presently that field's display control is a combo box that is a table/query. The Row source is another table called Workouts which consists of only 2 columns; WokoutsID  and WorkoutName. In the tasks table there is another column called "Step" that is also a combo box that is a table/query. The row source is a table Called "Steps" which has 3 columns; ID, StepName and WorkOutId. In this WorkOutID column I have a look up to the WorkOuts table. I have a one to many relationship; the WorkOuts.WorkOutsID (unique) to the steps.WorkOutsId. I have 8 WorkOuts and each workout has anywhere from 1 to 13 steps.  I want the drop down for the Step field in my Task table to only show the steps for what is specified in the WorkOut field. please let me know if you need any further info
0
JoeMommasMomma
Asked:
JoeMommasMomma
  • 4
  • 4
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
place this codes in the afterupdate event of combo WorkOut

private sub cboWorkOut_afterUpdate()

me.cboSteps.rowsource="select ID, StepName, WorkOutId from steps where WorkOutID=" & Me.cboWorkOut


end sub

if workoutID is Text Data Type use this

private sub cboWorkOut_afterUpdate()

me.cboSteps.rowsource="select ID, StepName, WorkOutId from steps where WorkOutID='" & Me.cboWorkOut & "'"


end sub
0
 
peter57rCommented:
Note that cap1's response assumes you are doing this in a form.

That is because what you are asking MUST be done in a form.  It can't be done just entering data ina table.
0
 
JoeMommasMommaAnalystAuthor Commented:
Yes it is a form. I inserted the code but I get a compile error that reads "method or data member not found" and .cboWorkOut & "'" is highlighted in the debugger. Any ideas?
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
peter57rCommented:
The code should all be on one line.
It's just the size of the  box here that makes it wrap.
0
 
Rey Obrero (Capricorn1)Commented:
is the name of your combo box ".cboWorkOut " ?
if not then change
.cboWorkOut

with the name of your combo box

also do the same for "cboSteps"
0
 
JoeMommasMommaAnalystAuthor Commented:
Hello, I have it on one line and I applied your naming conventions so the code would work. combo box for workout is cboWorkout and the one for steps is cboSteps. I forgot to mention that in the debugger, the little yellow arrow on the right is pointing to Private Sub cboWorkOut_afterUpdate() and these words are highlighted in yellow after I get the error pop-up.  .cboWorkOut is not highlighted in yellow, but it is automatically selected by the debugger. thanks for your help. I have attached a screen shot.
0
 
Rey Obrero (Capricorn1)Commented:
where?

and better if you upload a copy of your db
0
 
JoeMommasMommaAnalystAuthor Commented:
it's too big to do that.  I thinking it's a relationship issue?  would macro be easier?
0
 
Rey Obrero (Capricorn1)Commented:
you don't need to upload the whole thing,
create a blank db and import only the relevant objects tables, form, query


or

just see this link(tutorial)

http://www.fontstuff.com/access/acctut10.htm
0
 
JoeMommasMommaAnalystAuthor Commented:
i've attached it. thanks again.
Database21.accdb
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now