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

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?
Fred FisherPhotographerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
and start with calling SetFormControls() from the OnCurrent event.

Jim.
0
Fred FisherPhotographerAuthor 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?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Fred FisherPhotographerAuthor Commented:
Opps the date was 12/29/1899 not 12/10/1899.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.
0
Fred FisherPhotographerAuthor 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.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<< 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.
0
Fred FisherPhotographerAuthor 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!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.