MS Access 2010 form field value based on another field value

i have a field called "steps" (combo box) and another called "percencomplete" (text box) in a table. On a form I want the value in the "percencomplete" to autopopulate based on what is chosen in the "steps" (called cbosteps on the form) field upon update. So I have created a table that has a column of all the values that would possibly be in the "steps" field (field name= Stepname) with another column of corresponding percentage values that are intended for the "percencomplete" field (field named also percencomplete).  I named it "tblALLSTEPS".  Then I tried ;

Private Sub cboStep_AfterUpdate()
   On Error Resume Next
   PercenComplete.RowSource = "Select tblALLSTEPS.percencomplete " & _
            "FROM tblALLSTEPS " & _
            "WHERE tblALLSTEPS.stepname = '" & cboStep.Value & "' " & _
            "ORDER BY tblALLSTEPS.percencomplete;"
End Sub

It didn't work. Any ideas how to make it work?
JoeMommasMommaAnalystAsked:
Who is Participating?
 
jerryb30Commented:
I assume you only have one record percentcomplete for any one stepname

me.percentcomplete = dlookup("percentComplete", "tblAllSteps", "stepName = '" & me.cboStep & "'")
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
(1)
Eyeball the row source of your Steps combo box, and make sure the value that ultimately goes into percentcomplete is in there.  You can make it invisible if you want by making sure the ColumNWidths number for that column is zero.

For example, say this is the 5th column.

(2)
In the AfterUpdate event of cboSteps, write VBA code that goes something like this:

Private Sub cbosteps_AfterUpdate()
Me.percentcomplete = Me.cboSteps.column(4)
End Sub

Note that the 4 value is base-zero, so the 5th column means a 4 goes here.
0
 
JoeMommasMommaAnalystAuthor Commented:
thanks so much
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.

All Courses

From novice to tech pro — start learning today.