Pass values from FORM1 to FORM2 query parameter

[gprabhav]

I am trying to pass a value from FORM1 OnClick button event to FORM2 query parameter.


Somehow it is not accepting the value.

In FORM1, I am passing mr_number (textbox value) as follow:

Private Sub CommandAdd_Click()
On Error GoTo CommandAdd_Click_Err

    On Error Resume Next
    DoCmd.GoToRecord , "", acNewRec
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If
    DoCmd.OpenForm "FORM2", acNormal, "", , acEdit, acNormal, Me.mr_number
   
        
CommandAdd_Click_Exit:
    Exit Sub

---------------------

In FORM2, I am accepting the value in Form_Load as follow:

Private Sub Form_Load()
        If Not IsNull(Me.OpenArgs) Then
           Me.mr_number = Me.OpenArgs
        End If
End Sub

----------------------

My Query's where clause is:

WHERE (((Pt.mr_number)=[Forms]![FORM2]![mr_number]));

----------------------

I do not receive any error. Just that an empty FORM2 pops up.

[RuralGuy]

By the time set the mr_number control, the query has already run. Try adding a Me.Requery to your code.

[gprabhav]

Still doen't work.

The call from FORM1 is :
DoCmd.OpenForm "FORM2", acNormal, "", , acEdit, acNormal, Me.mr_number.Value

In FORM2:

Private Sub Form_Load()
        If Not IsNull(Me.OpenArgs) Then
           Me.mr_number = Me.OpenArgs
        End If
        Me.Requery
End Sub

[RuralGuy]

Why not try using the WhereCondition argument of the OpenForm command instead?
DoCmd.OpenForm "FORM2", , , "mr_number = " & Me.mr_number, acEdit

[rzw0wr]

You have not passed anything to the form.

The openform statement you are using is for opening a form not to pass a variable with.

Dale

[RuralGuy]

The OP is passing the value in the OpenArgs argument of the OpenForm command.

[rzw0wr]

I hope it is OK to disagree, ruralGuy.
My understanding of "Passing" is that the variable is not in the form to be opened and that you are trying to give the form/record a new piece of data.
The openarg statement will pass or tell a form to open with a variable if this variable is in the underlying table/query of the form.
In outher words it is not like passing data from a function back to a calling sub.

Is this a correct assumption?

Dale

[RuralGuy]

If you did a MsgBox OpenArgs in the OnLoad event you would see that in fact the "value" has been passed to the next form. Are we just discussing semantics? It is of course OK to disagree with me or anyone else.

[RuralGuy]

@gprabhav - Not that it will effect the results but is the "mr_number" a field it the table and is it a numeric field as opposed to a string (text) field?

[rzw0wr]

Thank you RuralGuy.
I have been duly corrected.

Dale

[RuralGuy]

I take it you have not used OpenArgs yet? I use it a lot.

[rzw0wr]

RuralGuy,
I am still very new to Access.
I have the concept however, I lack the knowledge and experience that comes with using it.

I usally have the right idea but the wrong approach.

And no, I have never used the openarg statement yet.
Since I had the wrong idea about what it did.
I will need to check it out more closely.

Thanks,
Dale

[gprabhav]

RuralGuy,
IT WORKED !!! Thanks !!!
The Problem was mr_number (which is TEXT) was loosing its value after updating the database (SQL-SERVER) from FORM1. So null value was passed to FORM2.
The filter in FORM2 became [mr.number] = ''

Here is the final WORKING code.

Private Sub CommandAdd_Click()
On Error GoTo CommandAdd_Click_Err
    
    Dim sWHERE As String
    sWHERE = "[mr_number] = '" & Me.mr_number & "'"
    
    On Error Resume Next
    DoCmd.GoToRecord , "", acNewRec
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If
    
    DoCmd.OpenForm "FORM2", acNormal, , sWHERE
    
    
                      
CommandAdd_Click_Exit:
    Exit Sub

[RuralGuy]

Excellent! Thanks for posting back with your success and solution. I marked this thread as Solved for you.