Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-01-25
14
Medium Priority
?
381 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 48

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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

715 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