Solved

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

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

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access query with left expression 9 31
sort Time by AM and PM in query 2 18
Error in query expression 3 34
Operation must use an updatable query 4 24
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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…
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…

919 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

17 Experts available now in Live!

Get 1:1 Help Now