[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MS Access 2010 form field value based on another field value

Posted on 2013-02-06
3
Medium Priority
?
702 Views
Last Modified: 2013-02-06
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?
0
Comment
Question by:JoeMommasMomma
3 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38861792
(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
 
LVL 26

Accepted Solution

by:
jerryb30 earned 2000 total points
ID: 38861827
I assume you only have one record percentcomplete for any one stepname

me.percentcomplete = dlookup("percentComplete", "tblAllSteps", "stepName = '" & me.cboStep & "'")
0
 

Author Closing Comment

by:JoeMommasMomma
ID: 38861932
thanks so much
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
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 …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

872 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