Solved

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

Posted on 2013-01-25
14
330 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 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Btw, why are you using a combo box instead of a textbox for text selections with no user interaction?
0
 

Author Comment

by:DJPr0
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:DJPr0
Comment Utility
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 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
---> 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
Comment Utility
Thanks for explaining mbizup!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now