Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2012
    Posts
    12

    Unanswered: Need to open form and pass a value

    I have a form called "frmLABEL-nac-print" that has a field called P_S_NUM. I need to pass the value from this form to a form called "frmSenset" that also has a field called P_S_NUM. I am using the statement below.

    DoCmd.OpenForm "frmSenset", , "[P_S_NUM] = " & [frmLABEL-nac]![P_S_NUM], acFormAdd, acWindowNormal

    When I run my form, I receive a run-time error that VB can't find the field in my expression.

    HELP !

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you open the form "frmSenset" from "frmLABEL-nac-print" use:
    Code:
    DoCmd.OpenForm "frmSenset", , , "P_S_NUM = " & Me.P_S_NUM.Value, acFormAdd, acWindowNormal
    If you do not open the form "frmSenset" from "frmLABEL-nac-print" use:
    Code:
    DoCmd.OpenForm "frmSenset", , , "P_S_NUM = " & Forms("frmLABEL-nac-print").Controls("P_S_NUM").Value, acFormAdd, acWindowNormal
    Notes:

    1. If you have a form called "frmLABEL-nac-print", you cannot abbreviate its name in "[frmLABEL-nac]" like you did.

    2. You should refrain from using non-alphanumeric characters in the names of the objects (the underscore is OK).

    3. You do not need to enclose every name within square brackets: this is usually necessary when you do not comply to 2. hereabove only.

    4. The WhereCondition is the fourth parameter of the OpenForm method, not the third one.

    5. Are you sure you need to use "acFormAdd" for the "OpenDataMode" (fifth) parameter? This seems contradictory with the use of a "WhereCondition" parameter.

    6. If the column "P_S_NUM" is not defined as a numeric data type, you need to use single or double quotes in the WhereCondition, like this:
    Code:
    DoCmd.OpenForm "frmSenset", , , "P_S_NUM = '" & Me.P_S_NUM.Value & "'", acFormAdd, acWindowNormal
    Have a nice day!

  3. #3
    Join Date
    Jan 2012
    Posts
    12

    Question to your reply

    First, thanks for the great examples and clarifications on my code. You are really helping me shack off the rust. I think I am almost there with this coding. there is still an issue.


    The form "frmLABEL-nac" uses a SQL table called SerNumSQL and the form "frmSenset" uses a table called EGGTestData. The form "frmSenset" is called by event procedure after update from the PART field on the form "frmLABEL-nac'. The 4 fields on the "frmSenset" are ID, P_S_NUM, Senset, Testdate.

    ID is auto Identity in SQL
    P_S_NUM is getting its value from the OpenForm Method which you are helping me with.
    Senset will get its value from the user at the production line via KB entry.
    Testdate is =Now() function

    I am using the first example you provided.

    DoCmd.OpenForm "frmSenset", , , "P_S_NUM = " & Me.P_S_NUM.Value, acFormAdd, acWindowNormal


    Now, when I run the "frmLABEL-nac" form I no longer receive the run-time error 2465. The "frmSenset" is opening, however there is nothing in the P_S_NUM textbox. The field P_S_NUM is defined as nvarchar(10) in SQL. So I realize I need to use point 6 in your suggestion as follows:

    DoCmd.OpenForm "frmSenset", , , "P_S_NUM = '" & Me.P_S_NUM.Value & "'", acFormAdd, acWindowNormal

    After running the form with the code from point 6, I still have nothing in the textbox for P_S_NUM on the form "frmSenset".

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by john757uv View Post
    After running the form with the code from point 6, I still have nothing in the textbox for P_S_NUM on the form "frmSenset".
    When the OpenDatamode parameter of the OpenForm method is set to AcFormAdd, the form opens showing only a blank record. This is what I evoked in point 5. You get what you asked for, if I may say so
    Have a nice day!

  5. #5
    Join Date
    Jan 2012
    Posts
    12

    You are correct

    How should I open it acNormal or just leave it blank?

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Sinndho View Post

    5. Are you sure you need to use "acFormAdd" for the "OpenDataMode" (fifth) parameter? This seems contradictory with the use of a "WhereCondition" parameter.
    Not only seems contradictory, but in fact is contradictory! You cannot open a Form to only enter New Records yet open it to a given Record! And if you entered this in the Code module, having a Where clause, the Intelisense function will not even offer acFormAdd as a possible OpenDataMode option.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Jan 2012
    Posts
    12

    Opening the form

    I understand what you are saying about theopening the form in Add mode. How should I open the form?. I thought I was blanking it out with the textbox using the OpenformAdd but I removed it and I still nam not seeing anything in my textbox. This is the line of code:

    DoCmd.OpenForm "frmSenset", , , "P_S_NUM = '" & Me.P_S_NUM.Value & "'", acFormNormal, acWindowNormal

  8. #8
    Join Date
    Jan 2012
    Posts
    12

    Passing value for record not to find somthing

    I am trying to pass the value of P_S_NUM to the form "frmSenset" to populate the textbox not to find a specific record. Forgive my psedocode but here is the goal.

    FormA.textbox(What ever is here) to FormB.textbox(put here)

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Come to think of it, if what you want to achieve is:

    1. Open the form "frmSenset" on a new (blank) record.

    2. Supply the value for "P_S_NUM" from the value of its "P_S_NUM" counterpart in the form "frmLABEL-nac-print".

    Here's a solution:

    1. To open "frmSenset", in the module of "frmLABEL-nac-print":
    Code:
        DoCmd.OpenForm "frmSenset", , , , acFormAdd, , Me.P_S_NUM.value
    2. in the module of "frmSenset"
    Code:
    Private Sub Form_Load()
    
        If Not IsNull(Me.OpenArgs) Then Me.P_S_NUM.value = Me.OpenArgs
        
    End Sub
    If you want the supplied value be kept for every subsequent records when the form "frmSenset" remains open, save the value of OpenArgs and use its Form_Current event handler instead of the Form_Load event handler:
    Code:
    Option Compare Database
    Option Explicit
    
    Private m_SuppliedValue As Variant
    
    Private Sub Form_Current()
    
        If Not IsNull(m_SuppliedValue) Then Me.P_S_NUM.value = m_SuppliedValue
        
    End Sub
    
    Private Sub Form_Load()
    
        m_SuppliedValue = Me.OpenArgs
        
    End Sub
    Have a nice day!

  10. #10
    Join Date
    Jan 2012
    Posts
    12

    I will give this a try

    Thank you, I will try this. Sounds like you nailed it.

  11. #11
    Join Date
    Jan 2012
    Posts
    12

    It worked

    It worked. You are a great resource. Thanks for the help.

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •