Solved

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

Posted on 2013-01-25
14
375 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
[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
  • 8
  • 5
14 Comments
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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 250 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

739 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