Solved OpenArgs prompting "Enter Parameter Value"? (1 Viewer)

[The_Doc_Man]

You would have to check the OpenArgs BEFORE that function IF the argument potentially would affect the outcome of that test.

 

[Minty]

Thank you. I'm confused by the multiple quotes. Why a single, double on the left and a double, single, double on the right?

@Gasman is offline currently so here goes - The quotes are there to add a single quote around each end of your string.
This is commonly referred to as escaping the value.

Numbers don't need them 1.345
Strings do 'My String Value'
Dates do #2021-05-01#

You have to do this for any values passed into a function, or concatenated into a command string.

 

[The_Doc_Man]

Thank you. I'm confused by the multiple quotes. Why a single, double on the left and a double, single, double on the right?

Ah, I see... you don't fully understand quote handling.

When you have this, case.... XYZ = Somefunction( "X" ) then what happens is that there is a formal parameter as part of the declaration of Somefunction and it probably expects a string as the actual parameter. VBA sees "X" and strips the outermost quotes, then passes the remainder, X, as the value of the parameter.

Let's look at a Domain Aggregate function, DLOOKUP. Let's say that you wanted to find something based on a text value. So you would write this

XYZ = DLOOKUP( "[IDNumber]", "NameTable", "[UName]='George'")

Simple enough, right? So the outermost quotes get stripped and then the first argument goes in as [IDnumber], the second goes in as NameTable, and the third goes in as [Uname]='George' - and your lookup has what it needs. The text value 'George' has to be in quotes because it is a literal value, not a variable named George. But what if the name George wasn't a constant? You do this via

XYZ = DLOOKUP( "[IDNumber]", "NameTable", "[UName]='" & Me.TheName & "'")

So the first two arguments go in the same way BUT the third argument has to get processed differently. You CANNOT pass it in using this form: "[UName]='Me.TheName'" because the Me.TheName reference is just a constant. But you wanted it treated as a variable, as something that needed to be evaluated.

So you have to concatenate a non-constant value into the string. Let us say that the name is Fred, just for snorts & giggles. So the evaluation works like this: FIRST, Access sees a bunch of quoted strings for IDNumber and NameTable, which we already discussed. Nothing changed there. Next it sees a quoted string, which it doesn't care about, but then it sees Me.TheName (NOT in quotes) and THAT is a resolvable reference, a variable with Me. as a qualifier for location. So it resolves it to Fred. Then, VBA sees another quoted string and the ending parenthesis and the end of the line.

Now, in this first pass through the string, VBA's parser had to do something other than simply stripping quotes, so it makes a second pass to see what else it can resolve. When it reaches this intermediate, it has more work. It sees "[UName]='" & Fred & "'" - and it knows that those ampersands require resolution as do the paired quote sequences. So the quotes get stripped, the ampersands get stripped, and the final appearance of that part of the line becomes [UName]='Fred' - and VBA can pass that to the function.

 

[gemma-the-husky]

Is this sorted?
note that openargs is a string

so this will try to assign a string to contract no.
ContractNo = Me.OpenArgs

if contract no is numeric you may need
ContractNo = clng(me.openargs)

always assuming openargs exists, is not null, is not more complex than a single value and so on.

 

[Pat Hartman]

The single quote on the left is within a larger string. That string ends with the double quote. Then the text value is concatenated and now you have to concatenate the ending single quote which must be enclosed in double quotes to make it a string.

What do you think you get when you use --- Me.Name in a form?

Which leads to -- If Globals.UserAccess(Me.Name) = False Then

If you know that Me.Name will return the name of a form, do you really have global variables defined for each and every form?

 

[CJ_London]

Would I run the OpenArgs before or after that function?

something like

If Globals.UserAccess(Me.Name) = False Then
        MsgBox "You do not have the required permissions to access this feature.", vbOKOnly, "Access Denied"
        DoCmd.CancelEvent
        Cancel = True
elseif Me.OpenArgs<>""
         Me.RecordSource = "select * from tblContracts Where ContractNo='" & Me.OpenArgs & "'"
End If

 

[mistyinca1970]

Thank you for all the replies. I'm going to try to redo it with the quotes and see if I can get the syntax right. I'll report back...

 

[mistyinca1970]

something like

If Globals.UserAccess(Me.Name) = False Then
        MsgBox "You do not have the required permissions to access this feature.", vbOKOnly, "Access Denied"
        DoCmd.CancelEvent
        Cancel = True
elseif Me.OpenArgs<>""
         Me.RecordSource = "select * from tblContracts Where ContractNo='" & Me.OpenArgs & "'"
End If

OK. This didn't work. It still popped up the "Access Denied" box, but then once clicked OK, it popped up the frmUpdateStatus Anyway. So I'll go back to using the OpenArgs on current and keep the function without the If.

But I was able to successfully pass the Contract No using the quotes, which is my preference because I prefer not to have the ID (which is really a useless field anyway since its not the primary key, but that is a vestige of the original direction I went with this when created).

So thank you everyone for your help with that! It does give me a little more insight into how it is reading the concatenated values in my DB. I've stated before, I don't really know SQL or VB...trying to pick it up on these streets, LOL

 

[Pat Hartman]

What are you expecting --- Globals.UserAccess(Me.Name) ---- to return?
Put a stop on that line of code. Then print it in the immediate window to see what you are getting.

 

[mistyinca1970]

What are you expecting --- Globals.UserAccess(Me.Name) ---- to return?
Put a stop on that line of code. Then print it in the immediate window to see what you are getting.

This function tests to see what the user access and type are in whichever form is (attempted) being opened.

Public Function UserAccess(FormName As String) As Boolean
    UserAccess = Nz(DLookup("HasAccess", "tblUserAccess", "UserAccess_ID=" & TempVars("UserType") & " AND FormName='" & FormName & "'"), False)
  
End Function

Forgive me, but I don't know how to put a stop on that line of code.

 

[Pat Hartman]

This function tests to see what the user access and type are in whichever form is (attempted) being opened.

Is this a function you wrote? Are you sure this is not where your security error is occuring?

To put a stop on a line of code -
open the vba window and navigate to the line where you want the stop
Click in the left margin.
save and close the form
open the form
when it stops, print the value in the immediate window
click on the stop indicator to remove it.

 

[mistyinca1970]

Is this a function you wrote? Are you sure this is not where your security error is occuring?

Yes, well edited from one on a tutorial found online. It works on all my other open forms: They all deny access when appropriate.

So Update: I'm no longer getting the security error. Turns out I had inadvertently combined the "if" argument and both on the "on current" instead of in the "on open." I didn't realize it right away. Now that I put the same argument in the on open event, it works...SORT OF.

The security issue is fine. It is now correctly denying access. But a new issue is now happening.

So when I attempt to open the form logged in as someone without the required access, the Access Denied box pops up as it should. But when you click OK, a macro error (2501) pops up. The debug sends me to this line of code (as you can see I also tried adding in the default arguments as well but to no avail):

DoCmd.OpenForm "frmUpdateStatus", acNormal, "", "", acFormEdit, acDialog, Me.txtContractNo

This is only happening on this button. And what is different for this one is that it uses VB to open the form, while the others use (no rotten tomatoes please) the macro Open Form.

I verified this by changing one that was previously the macro below, and changed it to the DoCmd.OpenForm and it now pops up the macro error following clicking the OK button on the "Access Denied" message. But none of the Open Form macro buttons encounter this issue. I'm at a loss as to why it would happen in the VB coding because the On open event handles what happens when the open form action is canceled (the "Access Denied" pop up). And the VB version is performing the same action as the macro below.

 

[Gasman]

No idea what 2501 is, but if you do not use the arguments and they are optional, just leave them out.

Acnormal,,acformedit

On my phone so typing as little as needed,

 

[mistyinca1970]

No idea what 2501 is, but if you do not use the arguments and they are optional, just leave them out.

Acnormal,,acformedit

On my phone so typing as little as needed,

I did...previously, it was ",,,,," etc. I only added them in this instance to see if that helped the error.

Incidentally 2501 is "The OpenForm action was canceled" which in every other instance (macro instead of vb) is handled by the "Access Denied" msg box.

 

[mistyinca1970]

This is only happening on this button. And what is different for this one is that it uses VB to open the form, while the others use (no rotten tomatoes please) the macro Open Form.

I verified this by changing one that was previously the macro below, and changed it to the DoCmd.OpenForm and it now pops up the macro error following clicking the OK button on the "Access Denied" message. But none of the Open Form macro buttons encounter this issue. I'm at a loss as to why it would happen in the VB coding because the On open event handles what happens when the open form action is canceled (the "Access Denied" pop up). And the VB version is performing the same action as the macro below.

Still perplexed as to why the VB code would result in the issue when the macro does not; however, I added an On Error Resume Next and that took care of it in the "Denied Access" scenario, and it functions properly when logged in as a user with access as well.

 

[Gasman]

In post 19 all you would need to do is show message, then close the form?
I have never used the cancel event, but that is likely your problem?

 

[Pat Hartman]

When you use DoCmd.OpenForm/OpenReport and the form or report might not open, you MUST includ an error trap in the calling routine. Usually the error will be 2501 but I have gotten different errors. Set the error routine to ignore 2501 but to display the number and text of any other error you get just in case there is another error you want to ignore.

 

[The_Doc_Man]

OK, a macro error (2501) pops up.

Error 2501 is "OpenForm action was denied." This CAN be caused by an error but also can be caused if the Form_Open routine passes back a value of 1 (or -1) in the "Cancel" argument that is part of the Form_Open event call.

EDIT: After reading

I added an On Error Resume Next and that took care of it

I will add the comment that this is the wrong time to cancel errors. You need to find the reason for the denial, correct that, and leave errors enabled. Otherwise you will have a potential multitude of errors that will not be reported. You only turn off errors in very rare situations.