Results 1 to 9 of 9
  1. #1
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383

    If vbYes and vbNo Not Working

    I am trying to prevent a user from entering data without a Bank Account,here is my code: It opens BankInformation if I choose NO?
    Results in a blank record. I also need to set Bank to Null if New Record and if they answer NO, then they must choose a bank form list.



    If Me.NewRecord = True Then

    Dim Programmermsgbox As String
    Programmermsgbox = MsgBox("WARNING:New Bank Account Detected,Answer Yes and Create a New Bank Account or else Enter a Bank!", vbInformation + vbYesNo, "FReg")
    If vbYes Then
    DoCmd.OpenForm "BankInformation"
    Else: If vbNo Then Me.Bank.SetFocus
    Cancel = True
    End If

  2. #2
    SoftwareMatters is offline Access VBA Developers
    Windows XP Access 2003
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    274
    You're nearly there but you need to test the variable you created as follows:

    Dim Programmermsgbox As String
    Programmermsgbox = MsgBox("WARNING:New Bank Account Detected,Answer Yes and Create a New Bank Account or else Enter a Bank!", vbInformation + vbYesNo, "FReg")
    If Programmermsgbox = vbYes Then
    DoCmd.OpenForm "BankInformation"
    Else
    Me.Bank.SetFocus
    Cancel = True
    End If

  3. #3
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    Thanks, just one more thing I left out,sorry! This code is being run off the main form and I need for it to reference the subform control.
    Main form is named CheckRegister and subform is named FReg Control of course is Bank. subform is where the control Bank is located.

  4. #4
    SoftwareMatters is offline Access VBA Developers
    Windows XP Access 2003
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    274
    I think you need to setfocus on the subform first and then the control as follows:

    Me!FReg.SetFocus
    Me!FReg.Form!Bank.SetFocus

  5. #5
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383

    Here is end result of code.

    Running into code circles here! In order for me to keep the same bank account on the subform I have to use this code.
    Me.Bank.DefaultValue = """" & Me.Bank.Value & """"

    And it works. This code is now run off the main forms OnCurrent Event:
    Dim Programmermsgbox As String
    If Me.NewRecord = True Then


    Programmermsgbox = MsgBox("WARNING:New Bank Account Detected,Answer Yes and Create a New Bank Account or else Enter a Bank!", vbInformation + vbYesNo, "FReg")
    If Programmermsgbox = vbYes Then
    DoCmd.OpenForm "BankInformation"
    Else

    Forms!CheckRegister.Form.SetFocus
    Me.[Bank].SetFocus

    End If
    End If

    And it works.

    SO, here is my dilemma... Code runs and works fine except that subform is keeping the Bank account info in the the Bank field when it should be empty. If you just go to a new record on the subform then it should keep the same Bank account. Not sure how to accomplish both! One form tells it one thing and the other tells it something else.
    All of this because I have to keep the same bank account for each record in the same subform but if a new main form record, then enter a new bank account and NOT retain the old one. CONFUSED Yet?
    Attached Thumbnails Attached Thumbnails MainFormSubform.jpg  
    Last edited by burrina; 01-03-2013 at 08:52 AM. Reason: Picture to explain

  6. #6
    SoftwareMatters is offline Access VBA Developers
    Windows XP Access 2003
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    274
    yes slightly confused. I may be missing something but why do you need account on the subform if it is the same as the main form?

  7. #7
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    Actually I don't but I do need to set focus back on the subform Bank control. The other on main form was just for user friendly,removed it. Sorry for the confusion.
    All I need now is too set the focus to the control Bank and not the default that it goes too which is BalDate on the subform FReg
    To be clear again Bank is on the main form. The subforms is keeping the same bank for every record you enter into it which is what I want. But if I want a new Bank then I just need to set the focus on that control and the value of it should be empty on a new record. Conflicting code is the problem. So, you now want to use a new Bank account for the records on the subform and all is well with the world. So if new record for main form, subform bank should be null, code prevents this as is. Not sure how to have cake and eat it too.

  8. #8
    SoftwareMatters is offline Access VBA Developers
    Windows XP Access 2003
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    274
    I think I understand what you are saying and what you need but what I don't understand is why the bank can't be on the main form if it is always the same for each record on the subform. It seems to me that is what should be changed?

  9. #9
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    Main form is based on table that holds the banks and subform is based on another table that houses the registry info. I have tried just using the subform but it would not work staying with the same bank and hence the main form. Everything works now except for that focus on the Bank control on the subform. This is a minor issue but one that could be a problem for the user. As you know, it's one thing to make a db work, but it's another to try and prevent user error. I have enough trouble trying to prevent my own errors.!

    Power went out here, off net for a bit. I am getting close, just need to finish the scheduler and I am home free.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Top 3 is not working
    By jyellis in forum Queries
    Replies: 7
    Last Post: 10-04-2012, 12:29 PM
  2. IsNull not working
    By vbjohn in forum Access
    Replies: 19
    Last Post: 02-29-2012, 02:21 PM
  3. Grouping not working
    By jgelpi16 in forum Reports
    Replies: 3
    Last Post: 08-24-2010, 11:50 AM
  4. Working with PDF's
    By Mitch87 in forum Access
    Replies: 1
    Last Post: 02-19-2010, 11:24 AM
  5. Why isn't this working?
    By adiecidue in forum Queries
    Replies: 4
    Last Post: 04-27-2009, 10:29 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