Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-01-30
10
Medium Priority
?
955 Views
Last Modified: 2013-01-31
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
Comment
Question by:JoeMommasMomma
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38836915
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
 
LVL 77

Expert Comment

by:peter57r
ID: 38838629
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
 

Author Comment

by:JoeMommasMomma
ID: 38840335
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 77

Expert Comment

by:peter57r
ID: 38840379
The code should all be on one line.
It's just the size of the  box here that makes it wrap.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38840383
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
 

Author Comment

by:JoeMommasMomma
ID: 38841455
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38841642
where?

and better if you upload a copy of your db
0
 

Author Comment

by:JoeMommasMomma
ID: 38841777
it's too big to do that.  I thinking it's a relationship issue?  would macro be easier?
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 38841787
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
 

Author Comment

by:JoeMommasMomma
ID: 38841963
i've attached it. thanks again.
Database21.accdb
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

660 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question