Link to home
Create AccountLog in
Avatar of pdvsa
pdvsaFlag for United States of America

asked on

Opening form on OpenArgs, get a msgbox


I am opening frmProjects_Extended from another form called frmProjects.  
I am getting a msg box for Trade_No.  I dont know why.  
See anything wrong?  Thank you....

Private Sub txtTrade_No_Click()

            DoCmd.OpenForm "frmProjects_Extended", , , , , acDialog, OpenArgs:=Me.Trade_No

End Sub


Private Sub Form_Load()

      If Nz(Me.OpenArgs, "") <> "" Then
'         'If Not Me.NewRecord Then
            Me.Filter = "[Trade_No] = " & Me.OpenArgs
            Me.FilterOn = True
     End If
End Sub
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

[Trade_no] is not in the forms recordsource, so you can’t filter on it.

 You’ll need to add that field trip forms records or not filter on it by disabling the code in the Onload event.


I am getting a msg box for Trade_No.  I dont know why.

try go to VBA codes of your form: frmProjects_Extended:

and do a search for MsgBox , it could be triggered by some focus or changed event.
Avatar of pdvsa


Hi Jim,

[Trade_No] is actually in the record source. I am sure of this.  I just double checked it.  Its wanting a parameter value.  The msg box displays the [Trade_No] and the forms filter property does display the [Trade_No] in the property sheet.  Let me know what else you might think it is. Thank you 

Avatar of pdvsa


Hi Ryan, i do not have any msgbox codes in the form.  Only the load event.

[Trade_No] is actually in the record source.

What if you remove it?

1.    Open the called forms record source on its own. Do you get a prompt for trade_no?

2.   If not, comment out the code in the forms on load event and test again.    Make sure the forms filter and order by  properties are blank. 

3.   If you still do not get a prompt, then you must have the field  misspelled in the onload code.


 Watch out for The letter O versus a zero


Avatar of pdvsa


Ok so I commented out the forms On Load event and I do not get the msg box prompt.  I guarantee it is not misspelled.  I deleted [Trade_No] and added it back on the form just in case there was something wrong with the field.

Does is matter if both forms record source are based on the same table?

Avatar of pdvsa



I do not know what is happening.  I have uploaded a pared down db and would be grateful for your checking what I did.  Its driving me nuts. thank you for the help.  

Avatar of Mark Edwards
Mark Edwards
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account

What Mark posted above should solved the issue, or you can try open your form in this way:

DoCmd.OpenForm "frmProjects_Extended", , , "[Trade_No] = '" & Replace(Me.Trade_No, "'", "''") & "'", , acDialog

Open in new window

By doing above, you no need to look into the OpenArgs property.


Does it happen when OpenArgs hold a null string ? (Null value and Null string are not the same thing).

If so, you might want to refine your If statement:

 If (Nz(Me.OpenArgs, "") & "") <> "" Then

Open in new window

Or with build in constants (this is more explicit):

 If (Nz(Me.OpenArgs, vbNullString) & vbNullString) <> vbNullString Then

Open in new window

You can make the solution as complicated as you want, but in your form open event your filter is trying to put a string as a filter value for [Trade_No] without quote characters around it, thus Access thinks the OpenArgs values is a field or control reference that it can't find, resulting in the pop-up asking for a value for what looks like one of your Trade_No values sent as an OpenArgs value..
[Trade_No] = TP20275787
User generated image
Put quotes around your filter value to fix the issue.  
Me.Filter = "[Trade_No] = '" & Me.OpenArgs & "'"

Open in new window

gives you:
[Trade_No] = 'TP20275787'
Works if OpenArgs is null, too. (Sets the filter value to an empty string)
[Trade_No] = ''
Avatar of pdvsa


Hi experts, sorry I though I had responded earlier.  It works great now.  Thanks so much.  Lesson learned about strings.