Ask Experts Questions for FREE Help !
Ask
    Sheba2014's Avatar
    Sheba2014 Posts: 3, Reputation: 1
    New Member
     
    #1

    Aug 5, 2014, 07:57 AM
    Form/ Table validation rule - not working
    Hello Scott, on my form I have a combo box cmbMgSO4 with values “Yes”; “No” If the user selects Yes, then the text box txtExtra must not be null. The user must provide the dose. For this, I have the validation rule:
    IIf([More MgSO4?]=”Yes”, Not IsNull([extra dosage]), IsNull([extra dosage]))
    This works until you violate it. Then after you correct the error, the validation text message wouldn’t go away. I understand that it is better to put the validation rule on the form using VB but I need assistance with the below code please. I wrote this code in the AfterUpdate event of [extra dosage] – i.e. the textbox txtExtra but it has bugs. Could you tell me what is wrong with it? Private Sub txtExtra_AfterUpdate()
    If [More MgSO4?] = “Yes” Then
    If Len([extra dosage].Text & vbNullString) = 0 Then
    MsgBox “Please provide the extra dose!”, vbOK + vbExclamation
    Else
    [extra dosage].Text = Me.txtextra.Text
    Len([extra dosage].Text) > 0
    End If
    Else Len([extra dosage].Text) = 0
    End If
    End Sub
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #2

    Aug 5, 2014, 08:15 AM
    First, Why use a combobox? I would use a Yes/No field With a default 0 (False). If the user checks that box, use the After Update event of the Checkbox to run code like:

    If Me.chkMSGO4 Then
    Me.ExtraDosage.SetFocus
    End iF

    So as soon as the user checks the box, focus is placed in the control. Next I would use the Lost Focus event of ExtraDosage like so:

    If IsNull(Me.ExtraDosage) Then
    MsgBox "You must enter an extra dosage amount!"
    Me.ExtraDosage.SetFocus
    End If

    So if the user tries to exit the control without entering a value, then they will be returned to the control until they do.

    P.S. I moved your post to the Access forum from Other Software, but otherwise you posted fine.
    Sheba2014's Avatar
    Sheba2014 Posts: 3, Reputation: 1
    New Member
     
    #3

    Aug 5, 2014, 10:56 AM
    Many thanks Scott. There were certain requirements insisted on (for this form) and using the combo box here is one of them. The application will be used as an analysis tool in the appraisal of Patient Care already administered. The actions and also documentations of the physicians will be taken into consideration. The stake holder wants to know if a decision was made by "Yes" and then the details in the next field. You know what they say, the customer is boss... I used this code and it seems to work. Thanks again:AfterUpdateIf Me.cmbMgSO4 = "Yes" Then Me.txtextra.SetFocusElse Me.cmbHyperTagent.SetFocus End IfLostFocusIf Me.cmbMgSO4 = "Yes" Then If IsNull(Me.txtextra) Then MsgBox "You must enter the extra dose amount!" Me.txtextra.SetFocus End If End If

    oh dear. I typed this lengthy response, clicked SUBMIT and lost it! I guess because I was not logged in..

    Many thanks Scott. The stakeholder insists on using the combo box as part of the design. The application will be used as an analysis tool in Patient Care appraisal. The decisions and documentation of the physicians is the point of focus. They want to see "Yes" when the form is printed out. You know the saying, the customer is boss...

    I used these codes and they seem to be working:

    AfterUpdate
    If Me.cmbMgSO4 = "Yes" Then
    Me.txtextra.SetFocus
    Else
    Me.cmbHyperTagent.SetFocus
    End If


    LostFocus
    If Me.cmbMgSO4 = "Yes" Then
    If IsNull(Me.txtextra) Then
    MsgBox "You must enter the extra dose amount!"
    Me.txtextra.SetFocus
    End If
    End If
    ScottGem's Avatar
    ScottGem Posts: 64,966, Reputation: 6056
    Computer Expert and Renaissance Man
     
    #4

    Aug 5, 2014, 11:14 AM
    If they prefer the combobox, as you say that's their choice. I would still be more inclined to use an Option Group than a combobox. But yes, the code will work the same as long as the test is correct. Glad you got it.

    P.S. Yes you have to sign in to AMHD first before you can post a response, but it does save what you typed until you sign in.

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Problem using a Pivot Table Form as a Sub-Form -- Access 2003 [ 3 Answers ]

I made a Pivot Table Form using the Pivot Table Wizard in Access 2003. It works great when opened.as a form. But when used as a sub-form, only the data columns appear. The Row Heading column is missing. The sub-form is not linked to a child field on the main form. Has anyone seen this...

How does magnesium obey the octet rule when reacting to form a compound? [ 1 Answers ]

How does magnesium obey the octet rule when reacting to form a compound?

Text box validation rule on a form [ 2 Answers ]

I have 2 fields on a form that if the sum of these 2 fields go over the limit I want to give the user an error message. On the 2nd field I am summing the 2 field together in the validation rule =+ <>480 , but it doesn't send the error message. I have also tried to add the form to the equation...

Form / subform and record junction table [ 2 Answers ]

Can you have a form with a subform and have both bound to the same table? I have a junction table for entering equipment issued to an employee. I want the header of the form to have a combobox to select the employee and I want the subform to view the equipment issued and be able to add new...

Which table to create main form [ 3 Answers ]

I have created the following tables per a response from Scottgem at to another user asking about creating a Access Database for a survey. My database has 26 questions with answers range being not very true to verry true with a couple simple yes no answers. Where I am stumped is how to display...


View more questions Search