Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS Access How to control a drop down state when another field has text?

Posted on 2013-01-25
14
Medium Priority
?
386 Views
Last Modified: 2013-01-30
I have a drop down with 3 selections:
Working
Complete
Cancelled

No user interaction with this drop down is desired (locked).

How do I want to automate when inputting or updating a record:

If a task name is inputted - drop down = Working

If a completion date is inputted - drop down = Complete

If the Cancelled radio button is selected - drop down = Cancelled
0
Comment
Question by:DJPr0
  • 8
  • 5
14 Comments
 
LVL 49

Assisted Solution

by:Dale Fye
Dale Fye earned 1000 total points
ID: 38818576
Just use the AfterUpdate event of each of those other controls.

Private Sub txt_TaskName_AfterUpdate

    if me.txt_TaskName <> me.txt_TaskName.OldValue then
        me.comboName = "Working"
    end if

End Sub

Use similar code for the other controls.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38818588
Create a function in your form's code:

Function GetDDL() as String
      dim s as string
      if "" & Me.TaskName <> "" then s = "working"
      if   isDate(Me.txtCompletionDate) = true  then s = "Complete"
      if Me.Cancelled = true then s = "Cancelled"
      GetDDL = s
End function

Open in new window



And call it from each of those controls after update event and the form's current event like this:

   Me.cboMyCombo = GetDDL

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 38818606
Btw, why are you using a combo box instead of a textbox for text selections with no user interaction?
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:DJPr0
ID: 38818638
That's true mbizup, I don't need the combo - will the code above still work for a text box?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38818641
Yes.

For the function I posted, this code is the same, but I'm just using more reasonable naming conventions:

Function GetStatus() as String
      dim s as string
      if "" & Me.TaskName <> "" then s = "working"
      if   isDate(Me.txtCompletionDate) = true  then s = "Complete"
      if Me.Cancelled = true then s = "Cancelled"
      GetDDL = s
End function

Open in new window



And again, call it from the AfterUpdate event of all of those controls and from your form's Current Event:

Me.txtStatus = GetStatus()

Open in new window


Tha AfterUpdate event makes the status change when the user updates the other controls; the current event will get it set properly for existing records as the user navigates through the form's records.
0
 

Author Comment

by:DJPr0
ID: 38818855
Create a function in your form's code:
If this is not a module - I'm not sure where to place this code.


Error when opening the form:
Compile error:
Expected variable or procedure, not module

Steps I performed:
Added a module called GetStatus
Function GetStatus() As String
      Dim s As String
      If "" & Me.ProjectName <> "" Then s = "working"
      If IsDate(Me.AendDate) = True Then s = "Complete"
      If Me.Cancelled = True Then s = "Cancelled"
      GetDDL = s
End Function

Added in form On Current:
Me.Status = GetStatus()


Added After Update for ProjectName
Me.Status = GetStatus()

Didn't add the other Actual End Date & Cancelled due to Project wasn't working.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38818956
Ok... the GetStatus function needs to be placed in the same module as all of your form events (in the 'code behind the form').  Place it immediately after your form's current event for example.

Once you move the function to its proper place, delete your GetStatus module


________
and as an aside...
>>> Added a module called GetStatus
You should never give a module the same name as a function or sub contained in it - if you do, Access will be unable to distinguish between your module name and your function names and you will get the error you mentioned.
0
 

Author Comment

by:DJPr0
ID: 38819179
Working partially - the original text in the status field is deleted when cycling threw the records.

Problem: not listing as working when text is in the Project field. All status fields are blank.

Code:

Function GetStatus() As String
      Dim s As String
      If "" & Me.ProjectName <> "" Then s = "working"
      If IsDate(Me.AendDate) = True Then s = "Complete"
      'If Me.Cancelled = True Then s = "Cancelled"
      GetDDL = s
End Function

Open in new window



Private Sub Project_Name_AfterUpdate()
Me.Status = GetDDL         'Also tryed: Me.txtStatus = GetStatus()
End Sub

Private Sub Form_Current()
Me.Status = GetDDL
End Sub

Open in new window

0
 
LVL 61

Accepted Solution

by:
mbizup earned 1000 total points
ID: 38819201
Try this correction to the function:

Function GetStatus() As String
      Dim s As String
      If "" & Me.ProjectName <> "" Then s = "working"
      If IsDate(Me.AendDate) = True Then s = "Complete"
      'If Me.Cancelled = True Then s = "Cancelled"
      GetStatus = s
End Function

Open in new window


And correct any control names so that the code uses the same names as the controls on your form.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38819220
Also, place the following line in your form's code at the very top, right under Option Compare Database:

Option Explicit

Open in new window


This will raise compile errors for typos, field names etc which you will need to fix... (doing this ensures that all of your variables and control names are defined, and helps avoid problems due to using the wrong names).
0
 

Author Comment

by:DJPr0
ID: 38819954
Thanks mbizup works fine!

Why didn't GetDDL = s work?

(If GetDDL is a variable which equals s)


One more thing:
This radio button is on another form:
If Me.radioCancelled = True Then s = "Cancelled"

How can I look at the radio button on another form?
(this form displays details of the other form)

It works on the detail form - but if a user clicks on this record on the main form it reverts to working.

Would this work? (for the main form)
If Me.status = "Cancelled" do nothing
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38822012
Hi DJPro - sorry about the delay.

>>> Why didn't GetDDL = s work?

The purpose of a VBA function is to return a value.  The value it returns is set within the function itself, by using the name of the function as a variable.  So when I changed the name of the function to GetStatus, I forgot to also change the GetDDL = s to GetStatus = s.  Without and line for GetStatus = s, the function was returning *nothing* (see my note in the code):

Function GetStatus() As String
      Dim s As String
      If "" & Me.ProjectName <> "" Then s = "working"
      If IsDate(Me.AendDate) = True Then s = "Complete"
      If Me.Cancelled = True Then s = "Cancelled"
      GetDDL = s   '<--- this needs to match the name of the function so that the function will return a value (It needed to be changed to GetStatus
End Function

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 38822014
---> How can I look at the radio button on another form?
You'd use the full path including the form name:

instead of Me.RadioButtonName

Use

Forms!YourFormName.YourRadioButtonName
0
 

Author Closing Comment

by:DJPr0
ID: 38835414
Thanks for explaining mbizup!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…
Suggested Courses

876 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