Message Box on After Update Event Help

[samjh]

Hi,

I have a form called frmCommitments, where each user will enter commitments.

Each user has specific Cost Centres that they work on. This is noted within the Cost Centre table, within 3 different columns, Finance Analyst, Finance Technician and Finance Assistant.

When the user (who will log in when using the database and their names will be typed in, in the same way as appear in the above table) select a cost centre within the frmCommitments, I would like it to check if that users name appears against that cost centre, and if not, inform them that this is not one of their cost centres do they wish to continue. If they say no, I just want it to go back to Cost Centre combo box so they can select a different cost centre, if they say yes, because this may happen, then I just want them to be able to close the message box and have nothing happen.

I'm really not sure how to write this in VBA, any help would be great. Thanks.

 

[John Big Booty]

You'd probably be better off do this sort of thin in the Before Update event rather than the After Update event.

As air code you'll need something along the lines of;

If IsNull([URL="http://www.techonthenet.com/access/functions/domain/dlookup.php"]DLookup[/URL]([COLOR="Green"]insert appropriate criteria[/COLOR])) Then     [COLOR="Green"]'If DLookUp returns null then the cost centre is not linked to user [/COLOR]
     If MsgBox("This Cost Centre is not one of yours. Do you wish to continue", vbYesNo, "Warning") = VbNo Then
          Cancel = True    [COLOR="Green"]'Stop record being save[/COLOR]
          Exit Sub            [COLOR="Green"]'Exit subroutine[/COLOR]
     End If
End If

 

[samjh]

Hi,
Thanks for your reply, however within the Dlookpu part there are three different columns that need to be looked up, how do I manage that, I'm thinking that it could be:
If IsNull (DLookup(...,..,.)) OR (DLookup(...,...,...)) OR (DLookup(...,...,..)) Then ....
Would that be right?

 

[samjh]

Hi, sorry but I'm now getting an error message saying that there is a Syntax Error in query expression 'Finance Assistant = 'Admin', I've put that line of my code below, not sure what I've done wrong.

If IsNull (DLookup("[cmbCostCentre]","tblCostCentre","FinanceAssistant = ' " & CurrentUser() ) ) Then

 

[John Big Booty]

Try;

If IsNull (DLookup("[cmbCostCentre]","tblCostCentre","FinanceAssistant = ' " & CurrentUser() [COLOR="Red"]& "'"[/COLOR]) ) Then

 

[samjh]

Hi,

I am now getting a Data Type Mismatch in criteria expression, I;ve copied the code below. I'm just not sure whta it wrong wtith it.

Private Sub cmbCostCentre_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("[cmbCostCentre]", "tblCostCentre", "FinanceAssistant = '" & CurrentUser() & "'")) Then
If MsgBox("This CostCentre is not one of your. Do you wish to continue", vbYesNo, "Warning") = vbNo Then
Cancel = True
Exit Sub
End If
End If

End Sub

VBA is highlighting the bit in red to say that it is where the error is. I donlt know if the problem is that there are currently to users set up for the database, so the only user is me and I'm showing up as Admin.

Thanks for any help you can give.

 

[John Big Booty]

What data is being stored in the field FinanceAssistant?

What data is being returned by CurrentUser()?

 

[samjh]

Sorry you saying that has just made me realise that the Finance Asistance was a lookup field which has 2 columns and the first column is the bound one as that is the ID from the Finance Assistant table but what I see is that actual name which is column 2 in the lookup.

Not sure how I look that up.

 

[samjh]

I fixed it, I took the ID out of the table, as the person's name is unique anyway, and it worked. Thank you very much for your help.

I do have one last wquestion on this, and that is that the lookup needs to look up three different columns, as there is a finance Assistant, Finance Technician and a Finance Analyst, and I would want it to check all three of these names against the user?

Thank you.

 

[John Big Booty]

Are you using Table Level Look Ups? If so you may want to rethink this and possibly normalise your data structure, you may find a tutorial useful.

 

[samjh]

Hi,

thanks for the advise, unfortunately I don't have time to change my database, as I now need it to be up and running for testing. This is definately something I will look at should I ever build another one though. Also I assume it will still work using the table level lookups?