Page 2 of 2 FirstFirst 12
Results 16 to 29 of 29
  1. #16
    mintech is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Location
    Belize
    Posts
    16
    The following is the code I was trying to use:

    Private Sub License_Exp_Date_BeforeUpdate(Cancel As Integer)
    'Make field Required if license type is Temporary
    If ([License_Type] = "Temporary") Then
    [License_Exp_Date].Required = True


    End If
    End Sub

  2. #17
    mintech is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Location
    Belize
    Posts
    16
    Private Sub License_Exp_Date_BeforeUpdate(Cancel As Integer)
    'Make field Required if license type is Temporary
    If ([License_Type] = "Temporary") Then
    [License_Exp_Date].Required = True
    End If
    End Sub

    This is what I tried most recently.

  3. #18
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You were advised to have this code in the form before update event but you have put it on the control, plus it doesn't look anything like the code in post 11.
    I also inquired about combo columns and you replied with how many values the list has - not the same thing. I can guess and you can try, but I figure you'll be back saying my code doesn't work and I'll say that's because I had no idea how many columns in the combo and which one is bound and which one contains the list item values.

    How about we cut to the chase - you provide some sort of compacted and zipped db copy for us to take a look at?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #19
    mintech is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Location
    Belize
    Posts
    16
    I think you are missing something. I didn't even know how to use this code. I youtubed some things and tried. I did it all using Access, no VBA. I also am not certain about the concept of combo columns beyond null or not null. As I am doing this db, I am refreshing/relearning this thing.

    Okay, I'll send the dB shortly

  5. #20
    mintech is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Location
    Belize
    Posts
    16
    Where does this code go. in which Sub?

  6. #21
    mintech is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Location
    Belize
    Posts
    16

    zipped file

    I have attached the zipped file. You will observe that it's mostly straight Access I have been using.

    Thanks

    Quote Originally Posted by Micron View Post
    You were advised to have this code in the form before update event but you have put it on the control, plus it doesn't look anything like the code in post 11.
    I also inquired about combo columns and you replied with how many values the list has - not the same thing. I can guess and you can try, but I figure you'll be back saying my code doesn't work and I'll say that's because I had no idea how many columns in the combo and which one is bound and which one contains the list item values.

    How about we cut to the chase - you provide some sort of compacted and zipped db copy for us to take a look at?
    Attached Files Attached Files

  7. #22
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Where does this code go.
    I cannot fathom why you'd ask that.

    post 8: IMHO it might be better to do the validation in the form's BeforeUpdate event

    post 10: I agree that the form BeforeUpdate is the place to validate this

    post 18: You were advised to have this code in the form before update event but you have put it on the control

    I did it all using Access, no VBA
    The code is VBA

    I might be missing something, but I suspect I'm not the only one. I realize that for someone like me this is relatively easy and is basic stuff. But if you're going to post for help, at least give the impression you're reading the information you've been given. If it doesn't make sense, ask for clarification otherwise it appears that you read it and forget it, or don't read it at all. Either way, the impression is not a good one. I'm still here if you want to keep at it, but have to insist that you post something rather than continuing to dance around this.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #23
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Open your form in design view.
    Make sure the form (Teacher form?) is what's showing in the drop down of the property sheet.
    On the property sheet, click the ellipses ... on the Before Update event property line. Paste the following between the Sub and End Sub statements:

    Code:
    If Me.Lic_Type = "Temporary" And IsNull(Me.Lic_Exp_Date) Then
           MsgBox "An expiration date is required if the license type is Temporary."
           Cancel = True
    End If
    Do the same for the form Current event:

    Code:
    If Me.Lic_Type = "Temporary" And IsNull(Me.Lic_Exp_Date) Then
           MsgBox "An expiration date is required if the license type is temporary."
           Cancel = True
    End If
    There are other ways to do this rather than repeat the code, but in this case I think this is the most expedient approach. The reason for also having this in the Current event is that you have existing records which probably should be validated. Also the validation will occur regardless of whether or not the combo value has changed because it's possible that your db design might allow others to remove the required date without editing the form.

    Hope that helps.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #24
    mintech is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Location
    Belize
    Posts
    16
    Thanks buddy. I have read all, and I have tried them. I've learnt a lot where this is concerned in last 24 hours. Thanks.

    I only asked where because each one (field) has a Sub - Hence I was asking if it was at the Lic Type or at Lic Exp Dt - I'll try both.

    I do understand why it goes in Current event also; thank you, I would have never guessed.

    I appreciate it. And yes, I won't stop til I get it so if it's all the same to you, I shall try your recommendations now and get back to you asap. Thanks again, and stay safe

  10. #25
    mintech is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Location
    Belize
    Posts
    16
    Okay. I've implemented this. The error message does show up. However, once I hit okay, it then still allows the user to move forward without entering an expiration date. I've pasted the code below.

    Option Compare Database


    Private Sub Form_Current()
    If Me.Lic_Type = "Temporary" And IsNull(Me.Lic_Exp_Date) Then
    MsgBox "An expiration date is required if the license type is Temporary."
    Cancel = True
    End If
    End Sub


    Private Sub License_Exp_Date_BeforeUpdate(Cancel As Integer)
    If Me.Lic_Type = "Temporary" And IsNull(Me.Lic_Exp_Date) Then
    MsgBox "An expiration date is required if the license type is Temporary."
    Cancel = True
    End If
    End Sub

  11. #26
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Wow. Listen, with all due respect, how many times do we need to say "put the code in the FORM's before update? I do not know how to be more explicit or precise than any attempt I've made to make that clear, and there have been several. The last being
    Make sure the form (Teacher form?) is what's showing in the drop down of the property sheet.
    On the property sheet, click the ellipses ... on the Before Update event property line. Paste the following between the Sub and End Sub statements:
    You are still putting it in the control before update event. It should look like this (please read carefully)
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    If Me.Lic_Type = "Temporary" And IsNull(Me.Lic_Exp_Date) Then
        MsgBox "An expiration date is required if the license type is Temporary."
        Cancel = True
    End If
    
    End Sub
    I did limited testing, but it did seem to do what you wanted. And oh, by the way - delete License_Exp_Date_BeforeUpdate sub. You do not want it as well as the other two.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #27
    mintech is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Location
    Belize
    Posts
    16
    Thank you. I think it worked.
    I really appreciate it.

    Have a good one. And be safe.

  13. #28
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Glad to know you finally got it solved.
    Good luck with your project, and yes, keep your hands clean and your distance safe!

  14. #29
    mintech is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Location
    Belize
    Posts
    16
    Thanks buddy. You too.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Conditional required fields
    By sunnyday in forum Programming
    Replies: 4
    Last Post: 06-06-2016, 07:59 PM
  2. Replies: 4
    Last Post: 06-29-2015, 01:56 PM
  3. Replies: 10
    Last Post: 06-18-2015, 11:52 AM
  4. Replies: 1
    Last Post: 12-07-2012, 02:14 AM
  5. Conditional required field
    By jaffar2000 in forum Forms
    Replies: 10
    Last Post: 06-05-2012, 06:28 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums