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

mistyinca1970

Member
Local time
Today, 06:44
Joined
Mar 17, 2021
Messages
117
Good afternoon,

I'm trying to pass a value from one form to another. The originating form has the following code:
Code:
Private Sub btnUpdateStatus_Click()
    DoCmd.OpenForm "frmUpdateStatus", , , , , , Me.txtContractNo
End Sub

and when we open frmUpdateStatus a box asking for a parameter value like this:
enterparameter.PNG

And here's the on current code for frmUpdateStatus
Code:
Private Sub Form_Current()
If Not IsNull(Me.OpenArgs) Then
        Me.RecordSource = "select * from tblContracts Where ContractNo=" & Me.OpenArgs
    End If
End Sub

I don't know what I'm doing wrong. This is the same process I used to pass values between forms in another database and it works. This particular one the originating form is in a navigation form. Would that be affecting it?

Thank you,
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:44
Joined
Sep 21, 2011
Messages
14,041
So what is txtcontractno?
If text you would need single quotes?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:44
Joined
Oct 29, 2018
Messages
21,357
Also, check your form properties, such as Order By and Filter.
 

Cronk

Registered User.
Local time
Tomorrow, 00:44
Joined
Jul 4, 2013
Messages
2,770
The parameter prompt box is asking for EM. Are you changing the form's recordsource on open?
 

mistyinca1970

Member
Local time
Today, 06:44
Joined
Mar 17, 2021
Messages
117
OK I sort of found a solution, but I still don't understand why it didn't work. I changed the value that I'm passing from the Contract No (which is the primary key, short text featuring letters and dashes; the "EM" in the parameter value prompt is coming from) to just an ID that is numerical.

Can OpenArgs only be used with numerical values?
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:44
Joined
Sep 21, 2011
Messages
14,041
Does that give you a clue? as to where the EM comes from now?
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:44
Joined
Sep 21, 2011
Messages
14,041
OK I sort of found a solution, but I still don't understand why it didn't work. I changed the value that I'm passing from the Contract No (which is the primary key, short text featuring letters and dashes; the "EM" in the parameter value prompt is coming from) to just an ID that is numerical.

Can OpenArgs only be used with numerical values?
No, but you have to have the correct syntax for sql?
That is what I was hinting at.
 

mistyinca1970

Member
Local time
Today, 06:44
Joined
Mar 17, 2021
Messages
117
No, but you have to have the correct syntax for sql?
That is what I was hinting at.
OK, so would that be...
Code:
Private Sub btnUpdateStatus_Click()
    DoCmd.OpenForm "frmUpdateStatus", , , , , , 'Me.txtContractNo'
End Sub

...single quotes around Me.txtContractNo ?
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:44
Joined
Sep 21, 2011
Messages
14,041
No, around the variable in the sql😔
Code:
ContractNo = '" & Me.OpenArgs & "'"
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:44
Joined
Feb 28, 2001
Messages
26,999
The question is, what does the "OpenArgs" value look like. For the form you are trying to open, if you have a Form_Open, Form_Load, or Form_Current event that allows you to set a quick breakpoint, set it at the earliest of those that you have and then, when you take the breakpoint, do this:

DEBUG.PRINT ME.OPENARGS

to see what it tells you.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:44
Joined
Feb 19, 2013
Messages
16,553
suggest move your code from the current event to the open event. the current event fires after data is loaded
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:44
Joined
Feb 28, 2001
Messages
26,999
OK I sort of found a solution, but I still don't understand why it didn't work. I changed the value that I'm passing from the Contract No (which is the primary key, short text featuring letters and dashes; the "EM" in the parameter value prompt is coming from) to just an ID that is numerical.

Can OpenArgs only be used with numerical values?
I missed this earlier somehow. Per this comment, "EM" is actually a text segment in the Contract No field, so the first thing that is asked for is EM. Which means the problem is in how you quote the Me.txtContractNo in the DoCmd.OpenForm, because as originally passed what is happening is that the FormOpen code that is looking at the OpenArgs argument thinks it is a field or variable named EM. Which it can't find, so it asks for it.
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:44
Joined
Sep 21, 2011
Messages
14,041
OK I sort of found a solution, but I still don't understand why it didn't work. I changed the value that I'm passing from the Contract No (which is the primary key, short text featuring letters and dashes; the "EM" in the parameter value prompt is coming from) to just an ID that is numerical.

Can OpenArgs only be used with numerical values?
OK, I will try and explain what I think was happening.

You passed "EM-22-104" as OpenArgs
If you had tested for that fine, but you just slung into a sql statement that would look like
Code:
Me.RecordSource = "select * from tblContracts Where ContractNo=EM-22-104"

So Access does not know what EM is, so asks you.?
If you had entered 126 in the prompt, you would have ended up with zero, as Access would subtract the 22 and then the 104?

To keep that from looking like maths formula, you need to surround the text with normally a single quote '
When the value is numeric, then you just supply as is.

So, there was nothing wrong with the OpenArgs as you passed it, just that you were using it incorrectly in the sql statement.?

Pay attention to this, as you will need to remember this syntax for not only sql, but Domain functions as well

HTH
 

mistyinca1970

Member
Local time
Today, 06:44
Joined
Mar 17, 2021
Messages
117
suggest move your code from the current event to the open event. the current event fires after data is loaded
I'm running a function on open to check for user access (certain forms in this DB are blocked from users whose access level doesn't allow them). Would I run the OpenArgs before or after that function?

Code:
Private Sub Form_Open(Cancel As Integer)
    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
    End If
End Sub
 

mistyinca1970

Member
Local time
Today, 06:44
Joined
Mar 17, 2021
Messages
117
No, around the variable in the sql😔
Code:
ContractNo = '" & Me.OpenArgs & "'"
Thank you. I'm confused by the multiple quotes. Why a single, double on the left and a double, single, double on the right?
 

Users who are viewing this thread

Top Bottom