ms access 2003 forms

Question

Hi

In an access form I have a "raised by" field with a drop down menu showing 6 names. At the bottom of the form, there is an "approved by" field also showing the names. I need to prevent users picking a name in the "raised by" field from also choosing the same name in the "approved by" field. If they try to enter the same name, I want a popup message saying that the same order cannot be approved by the person raising it. Does anyone have any suggestions?

Regards

---

LP

Answer 1

Sure: write your code in a private function, then call this function from the two controls' BeforeUpdate event, setting Cancel to True if the same. Off the cuff:

private function RaiserIsApprover() as Boolean
dim blnSame as boolean
blnSame = (Me.cboRaiser = Me.cboApprover)
if blnSame then MsgBox "Yo! Not allowed.", vbIconExclamation
RaiserIsApprover = blnSame
end function

private sub cboRaiser.BeforeUpdate(Cancel as Boolean)
  Cancel = RaiserIsApprover
end sub

---

-Tom. Microsoft Access MVP

Answer 2

Hi

In an access form I have a "raised by" field with a drop down menu showing 6 names. At the bottom of the form, there is an "approved by" field also showing the names. I need to prevent users picking a name in the "raised by" field from also choosing the same name in the "approved by" field. If they try to enter the same name, I want a popup message saying that the same

It seems to me you have to validate each field against the other.  Assuming the form is bound, you could reasonably use the form's BeforeUpdate event to make one simple check; e.g.:

Private Sub Form_BeforeUpdate(Cancel As Integer)

    If Me.ApprovedBy = Me.RaisedBy Then
        Cancel = True
        MsgBox "The same order cannot be approved by the person raising it.", vbExclamation, "Not Permitted"
    End If

End Sub

That approach waits until the user actually attempts to save the record, so it allows the user some freedom up to that point.

Alternatively, you could use the BeforeUpdate event of each combo box to validate against the other:

 

Private Sub RaisedBy_BeforeUpdate(Cancel As Integer)

    If Me.RaisedBy = Me.ApprovedBy Then
        Cancel = True
        MsgBox "The same order cannot be approved by the person raising it.", vbExclamation, "Not Permitted"
    End If

End Sub

Private Sub ApprovedBy_BeforeUpdate(Cancel As Integer)

    If Me.ApprovedBy = Me.RaisedBy Then
        Cancel = True
        MsgBox "The same order cannot be approved by the person raising it.", vbExclamation, "Not Permitted"
    End If

End Sub

That approach catches the conflict the moment the user makes a choice in one of the combo boxes, so provides more instant feedback.  However, it allows the user less freedom to put conflicting values in the combo boxes, as for example in interchanging the values, before actually trying to save the record.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 3

Hi

I cannot get Tom's idea to work

I copied and pasted Dirk' second pice of code. I get a "compile error: method or data number not found". do I need to replace anything in the code?

---

LP

Answer 4

I cannot get Tom's idea to work

I copied and pasted Dirk' second pice of code. I get a "compile error: method or data number not found". do I need to replace anything in the code?

Tom's approach would certainly work if suitably implemented; it's a streamlined version of what I'm doing in my second code sample.  In either case, though, you have to replace the control names in the code with the actual control names on your form.  So where I had "RaisedBy" and "ApprovedBy" in the code I posted, you need to replaced those names with the actual names of the controls on your form.  If those control names have embedded spaces, as for example "Raised By" or "Approved By", you can replace the space with an underscore, as in "Private Sub Raised_By_BeforeUpdate" or "Me.Raised_By".

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

Answer 5

Hi

i have made the changes and I am not getting an error message. But I can close the form even though i have picked the same name in approved by as in raised by. so it's not working. Do I need to change the  Me in "If Me.ApprovedBy = Me.RaisedBy Then"?

Regards

---

LP

Answer 6

Problem solved. Thank you

---

LP