Access Enable= True and Enabled=False how to implement depending upon a value

Fred Fisher
Fred Fisher used Ask the Experts™
on
I am working on a music database with two main tables, tblTitles and tblSongs and other lookup tables.  The main form 'frmTitles'  has a continuous subform 'subfrmSongs'.  On frmTitles I have a field "Status".  It is a lookup field (using a combo box) and the valid selections are "Active", "For Sale" and "Sold".  Now if the status is "Active" or "For Sale" I want all fields enabled except for "SoldTo", SoldDate" and SoldPrice".  On the other hand if the status is "Sold" I want only the Status, SoldTo, SoldDate and SoldPrice enabled with all of the other fields disabled.

Finally, when the form is first opened I want the status determined from the value in Status and the fields setup according to the above.  I have tried using Case statements and If - End If but I just am missing something.

Private Sub Status_AfterUpdate()
 If Me.Status = "Sold" Then
       
          ' if the user has entered Sold
           
            Me.MediaType.Enabled = False
            Me.RollType.Enabled = False
            Me.RollOrigin.Enabled = False
            Me.RecutSource.Enabled = False
            Me.TitleNumber.Enabled = False
            Me.Title.Enabled = False
            Me.GroupPerformer.Enabled = False
            Me.Genre.Enabled = False
            Me.PurchasedFrom.Enabled = False
            Me.PurchaseDate.Enabled = False
            Me.CurrentMarketValue.Enabled = False
            Me.Condition.Enabled = False
            Me.RollRating.Enabled = False
            Me.DateDigitalFileRecorded = False
                     
           
        Else
               ' if the user has entered Active Or For Sale
             
               Me.SoldTo.Enabled = False
               Me.SoldDate.Enabled = False
               Me.SoldPrice.Enabled = False
           
        End If
End Sub

In the Sub Form_Current()
               Me.MediaType.Enabled = True
               Me.RollType.Enabled = True
               Me.RollOrigin.Enabled = True
               Me.RecutSource.Enabled = True
               Me.TitleNumber.Enabled = True
               Me.Title.Enabled = True
               Me.GroupPerformer.Enabled = True
               Me.Genre.Enabled = True
               Me.PurchasedFrom.Enabled = True
               Me.PurchaseDate.Enabled = True
               Me.CurrentMarketValue.Enabled = True
               Me.Condition.Enabled = True
               Me.RollRating.Enabled = True
               Me.DateDigitalFileRecorded = True
               Me.SoldTo.Enabled = True
               Me.SoldDate.Enabled = True
               Me.SoldPrice.Enabled = True
End Sub

What am I missing?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
Fred,

   First, you'll want to create one routine, i.e.:

Private Sub SetFormControls()

 and then call it as needed (AfterUpdate, OnCurrent, OnLoad, or where ever).  This saves you from repeating the code over and over.

 Second, you need both half's of the conditions.  

  If Me.MediaType.Enabled = False if sold, then you need to make it True if it's not sold.   So:


Private Sub SetFormControls()

  If Me.Status = "Sold" Then
        
          ' if the user has entered Sold
            
            Me.MediaType.Enabled = False
            Me.RollType.Enabled = False
            Me.RollOrigin.Enabled = False
            Me.RecutSource.Enabled = False
            Me.TitleNumber.Enabled = False
            Me.Title.Enabled = False
            Me.GroupPerformer.Enabled = False
            Me.Genre.Enabled = False
            Me.PurchasedFrom.Enabled = False
            Me.PurchaseDate.Enabled = False
            Me.CurrentMarketValue.Enabled = False
            Me.Condition.Enabled = False
            Me.RollRating.Enabled = False
            Me.DateDigitalFileRecorded = False

            Me.SoldTo.Enabled = True
            Me.SoldDate.Enabled = True
            Me.SoldPrice.Enabled = True
            
        Else
               ' if the user has entered Active Or For Sale
             Me.MediaType.Enabled = True
            Me.RollType.Enabled = True
            Me.RollOrigin.Enabled = True
            Me.RecutSource.Enabled = True
            Me.TitleNumber.Enabled = True
            Me.Title.Enabled = True
            Me.GroupPerformer.Enabled = True
            Me.Genre.Enabled = True
            Me.PurchasedFrom.Enabled = True
            Me.PurchaseDate.Enabled = True
            Me.CurrentMarketValue.Enabled = True
            Me.Condition.Enabled = True
            Me.RollRating.Enabled = True
            Me.DateDigitalFileRecorded = True
             
            Me.SoldTo.Enabled = False
            Me.SoldDate.Enabled = False
            Me.SoldPrice.Enabled = False
           
        End If

Open in new window

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
and start with calling SetFormControls() from the OnCurrent event.

Jim.
Fred FisherPhotographer

Author

Commented:
Well I have it partially working.  If the status is Active or For Sale the SoldTo, SoldDate and SoldPrice are disabled as planned.  However when I change Active or For Sale to Sold Nothing happens, the SoldTo, SoldDate and SoldPrice are not enabled nor are the other controls disabled.  To add insult to injury the field DateDigitalFileRecorded is filling with the date 12/10/1899 when that field should be blank.

The SetFormControls() sub is being run currently from Status After Update Event Procedure and the forms On Current and On Load events.  Any thoughts?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Fred FisherPhotographer

Author

Commented:
Opps the date was 12/29/1899 not 12/10/1899.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<If the status is Active or For Sale the SoldTo, SoldDate and SoldPrice are disabled as planned. >>

  OK, so time to learn a little debugging.    In the code, you can either use breakpoints on a line (F9 key when the cursor is in the line, or click the gray bar to the left of the line), or you can place a

 STOP

 in the code as it's own line.   For the present, I would use STOP (because you can save the code that way - breakpoints don't survive after a form is closed).  Now when you execute, execution will stop on those.   From that point on, you can:

1. Press F8 to execute one line of code.    If the line is a call, you will step into the routing (you'll be in that procedure).  
2. You can skip over a call with Shift/F8.   Your still single stepping, but you won't step into another routine.
3. As your stepping through the code, you can:

a. Hover over variables and reference to see the value
b. Type things in the debug window such as:

? strMyVariable

 with a return.   The ? prints the value of strMyVariable in the window (? is shorthand for Debug.Print strMyVariable).

 You can also set variables:

 strMyVariable = "Active"

4. You can skip through a routine to its end with Ctrl/Shift/F8.

5. and when you want to stop single stepping, press F5 and execution will continue.

Give that a go so you can see what's happening.

The SetFormControls() sub is being run currently from Status After Update Event Procedure and the forms On Current and On Load events.

   The afterupdate and the oncurrent should be sufficent.  OnCurrent fires every time a record gets the focus, so it will occur on the first record after the OnLoad is complete.

To add insult to injury the field DateDigitalFileRecorded is filling with the date 12/10/1899 when that field should be blank.

 Date/time fields are stored in most systems as an offset to a base date (a 'delta' time).    The base date/time that Access uses is 12/10/1899.  So what that is telling you is you have a numeric zero in the field.

Jim.
Fred FisherPhotographer

Author

Commented:
It has been 15 years since I worked with Access and I am learning all over again.  I was unaware of the Stop command that is better than using a breakpoint at this stage.  Part of the issue is the value I needed was not "Sold" but "2" since this was the value of Sold in the lookup table tblStatus and that is what is use in tblTitles.  Now it is working like a champ.   On last bit.  When Status = "2" (Sold) I also want the subfrmSongs to be Greyed out as well.    

I have tried Me.subfrmSongs.Enabled = False but the subfrmSongs remains active.  I know I need to reference is as a control on frmTitles.

What is the missing piece here.  Thanks a million for the help.  Do you know of any Access books that have examples using actual code?  I have the Access 2016 Bible which helps but it is a bit short on real world code examples.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
<< Part of the issue is the value I needed was not "Sold" but "2" since this was the value of Sold in the lookup table tblStatus and that is what is use in tblTitles.  >>

 Sorry, it wasn't clear if you were actually using those values or a lookup key....I should have mentioned this.   I figured though the debugging would have let you seen that if it were the case.

<<I have tried Me.subfrmSongs.Enabled = False but the subfrmSongs remains active.  I know I need to reference is as a control on frmTitles.>>

 That would be correct assuming 'subfrmSongs' is the name of the subform control and not the subform (they can be different)....you should not be able to enter the subform control after that.  

 However you will find that it's often best to let the user enter the control, but not allow them to do anything.  So:

  Me.subfrmSongs.Form.AllowAdditions = False
  Me.subfrmSongs.Form.AllowEdits = False
  Me.subfrmSongs.Form.AllowDeletions = False

 leaving them with the ability to navigate, but do nothing else.

<<Thanks a million for the help.  Do you know of any Access books that have examples using actual code?  I have the Access 2016 Bible which helps but it is a bit short on real world code examples.>>

  The computer book market has more or less died out.   The last I purchased was the Access Developers Handbook:

https://www.amazon.com/Access-2000-Developers-Handbook-Desktop/dp/0782123708
https://www.amazon.com/Access-2000-Developers-Handbook-Enterprise/dp/0782123724

  It's a two volume set with approx 2,500 pages between the two.  Lots of code (came with a CD) and examples.    But at this point, it is out of date a bit.   Can't really recommend anything newer.   Besides, what works for one might not work for another book wise.

  I would usually suggest dropping down to the local book store when a question like this gets asked and suggesting one just browse 4 or 5 and see what might work the best for them.

Jim.
Fred FisherPhotographer

Author

Commented:
Yes, unfortunately book stores have pretty much gone the way of the dodo as well.  Most of the only "videos" I find tedious, slow and most of the time important steps are left out.  Well I will keep slogging on, at some point I should hit critical mass and I will start to remember many of the things that I used to know!!!  Thanks for the assist!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial