runtime error 13 type mismatch

[alanquick]

Its the Luddite again here, database is working ok but now want to add a few tweaks.

I only want certain people to be able to approve a quote and it almost works but when I add more than 1 ID I get a mismatch error.

This works fine:

Private Sub Approvedby_Click()
If Environ("USERNAME") <> "226709" Then
DoCmd.Requery
MsgBox "You are not authorised to approve this quote!", vbCritical


End If
End Sub

But when I add an 'Or' Element:
Private Sub Approvedby_Click()
If Environ("USERNAME") <> "226709" Or "226709" Then
DoCmd.Requery
MsgBox "You are not authorised to approve this quote!", vbCritical


the function works but I get the mismatch error.

Probably doing it wrong in the first instance so any help would be great

Luddite strikes again :-(

 

[isladogs]

I assume you meant 2 different values in the If ... Then line e.g. <>226709 And <> 234567

Anyway, you need to change the If ... Then line below

Private Sub Approvedby_Click()
If Environ("USERNAME") <> "226709" Or "226709" Then
DoCmd.Requery
MsgBox "You are not authorised to approve this quote!", vbCritical

One way of doing this is:

If Environ("USERNAME") <> "226709" And Environ("USERNAME") <>"234567" Then

Also what exactly are you requerying & why in this section?

 

[bob fitz]

Try:
If Environ("USERNAME") <> "226709" OR Environ("USERNAME") <> "226709" Then

 

[Gasman]

Try

If Environ("USERNAME") <> "226709" AND Environ("USERNAME") <> "226709" Then

I am assuming the second ID should be different as well?

Personally I would code it as

If Environ("USERNAME") = "226709" or Environ("USERNAME") = "226710" or Environ("USERNAME") = "226711" then
else
DoCmd.Requery
MsgBox "You are not authorised to approve this quote!", vbCritical
End If

However consider storing the IDs in a table and then do a DLookup for allowed ID. That way as more users are allowed/removed, you will not be changing code all the time, just data.?

HTH

 

[alanquick]

Yep works perfectly thanks. As to why I'm doing it in short I only want 3 people form the list of 20 employees to be able to choose an approver for the quotations, just seemed doing a requery worked and ensures that the combobox doesn't list an entry

Like i said, probably not the best way but it works

 

[Gasman]

Yep works perfectly thanks. As to why I'm doing it in short I only want 3 people form the list of 20 employees to be able to choose an approver for the quotations, just seemed doing a requery worked and ensures that the combobox doesn't list an entry

Like i said, probably not the best way but it works

I know where you are coming from, but then one leaves and another takes his place, and you have to remember where that code is to change the values. Also it could be in other places possibly? I'm not saying do not do the requery etc, just a different method to check the id?

I started off like that, then realised a little more work upfront saves time later on and makes the system more flexible.?

Just my two penneth.

 

[The_Doc_Man]

The normal way to do this is to have a user table hidden away somewhere. When you do the test, determine the username. Then look up that name in the user table and test for associated flags or codes defining the rights of the user in question. That way, if you add a third or fourth person - or have to REMOVE a person - you make one little change to the table entry and you are done.

 

[isladogs]

Agree with the last 2 posts

Suggest you add a boolean field 'Authorised'
Then your code might something like be:

Private Sub Approvedby_Click()
If Me.Authorised=True Then
'approved code goes here 
Else
'DoCmd.Requery '<== is this needed?
MsgBox "You are not authorised to approve this quote!", vbCritical
End If

 

[Gasman]

Also be aware that you should be using AND and not OR if you are checking for NOT, as one username will always fail even if all are valid

 

[alanquick]

OK Tried a few things and its not going according to plan :-(

Created a table called tblApprover, which has all the people that can approve quotes and their SAPID, which is the Environ ("USERNAME")
The combobox pulls the data from tblApprover
depending on how I write the code I can either allow everyone to approve the quote, or nobody has access to do it

This is so frustrating

 

[isladogs]

That's not what I meant in post #8

Add a boolean (Yes/No) field called Authorised to the existing staff table with a default value = 0 (false). Set the field value to -1 (true) for those who can approve quotes

Then you can use the code from #8 or something similar