?
Solved

MS Access 2010 form field value based on another field value

Posted on 2013-02-06
3
Medium Priority
?
697 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
[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
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

649 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