We help IT Professionals succeed at work.

Opening form on OpenArgs, get a msgbox

pdvsa
pdvsa asked
on
Experts,

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....

frmProjects:
Private Sub txtTrade_No_Click()

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

End Sub


frmProjects_Extended:

Private Sub Form_Load()

      If Nz(Me.OpenArgs, "") <> "" Then
'         'If Not Me.NewRecord Then
            Me.Filter = "[Trade_No] = " & Me.OpenArgs
            Me.FilterOn = True
       Else
     
     End If
End Sub
Comment
Watch Question

Jim Dettman (EE MVE)President / Owner
Fellow 2019
Most Valuable Expert 2017

Commented:

[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.


Jim



Ryan ChongSoftware Team Lead

Commented:
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.
pdvsaProject finance

Author

Commented:

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 

pdvsaProject finance

Author

Commented:

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

Ryan ChongSoftware Team Lead

Commented:

[Trade_No] is actually in the record source.


What if you remove it?

Jim Dettman (EE MVE)President / Owner
Fellow 2019
Most Valuable Expert 2017

Commented:

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.


Jim

Jim Dettman (EE MVE)President / Owner
Fellow 2019
Most Valuable Expert 2017

Commented:

 Watch out for The letter O versus a zero


Jim

pdvsaProject finance

Author

Commented:

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?

pdvsaProject finance

Author

Commented:

EE_OpenArgsIssue.accdb


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.  

Chief Technology Officer
Commented:
Try putting single quote marks around your filter value in your Form_Open event:
            Me.Filter = "[Trade_No] = '" & Me.OpenArgs & "'"

Open in new window

Trade_No is a string.
Ryan ChongSoftware Team Lead

Commented:

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

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


EE_OpenArgsIssue_b.accdb

Fabrice LambertConsulting
Distinguished Expert 2017

Commented:

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

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


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


Mark EdwardsChief Technology Officer

Commented:
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
OpenArgs-Popup.PNG
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] = ''
pdvsaProject finance

Author

Commented:

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