Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2012
    Posts
    3

    Unanswered: Problems passing OpenArgs between forms

    Hello everyone. I'm developing a database for one of our surveys that we use in our research. The survey has quite a number of variables and I've had to separate out our survey into 5 different tables. All of these tables are linked together through a common variable called InterviewCode. To help our researchers with their data entry, I've developed some forms that look exactly like the survey's their seeing.

    I've worked them out so there is one master form wherein researchers would enter in demographics and the InterviewCode for that respondent (say Testing6). Beneath are buttons to the subforms. To keep down data entry errors, I would like for the InterviewCode entered in the main form to be copied into the InterviewCode variable in each of the subforms. Do do this, I've coded each button like this:

    Code:
    Private Sub State_Level_Data_Click()
    
    DoCmd.OpenForm "StateInterview_StateLvl_Form", acNormal, , , , , "[Interviewcode] = " & Me.InterviewCode
    
    End Sub
    Which works fine, as long as the "StateInterview_StateLvl" table already has that particular InterviewCode in the table. For example, if we haven't entered in any data previously for Testing6 the form will be blank.

    To resolve this, I've tried to "catch" the InterviewCode passed through from the main form with this code:

    Code:
    Private Sub Form_load()
    Dim InterviewCode As String
    
    If Not IsNull(Me.OpenArgs) Then
    If DCount("*", "InterviewTbl1_2_Data", "[InterviewCode] = " & Me.InterviewCode = 0) Then
    DoCmd****nCommand acCmdRecordsGoToNew
    Me.InterviewCode = Me.OpenArgs
    Else
    Me.RecordsetClone.FindFirst Me.OpenArgs       'this generates a runtime 3070 error
    Me.Bookmark = Me.RecordsetClone.Bookmark
    End If
    End If
    End Sub
    But it doesn't work. Instead I get a runtime error 3070 and the form pulls up the last record enetered, not a blank one. I hope I've been clear with what's going on and what I'm trying to achieve. I'm at my wits end with this problem and hope one or more of you guys could give me a hand.

    Thank you so very much for your help!

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    If these are true Subforms, simply set their Link Master Fields/Link Child Fields to the InterviewCode Fields and Access will handle this for you automatically. That's how Main Forms/Subforms work.

    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

  3. #3
    Join Date
    Apr 2012
    Posts
    3
    Sorry, let me clarify. These are not true subforms, as there are too many variables to include in a single form (that's a long story in and of itself). Rather, the button (let's call it State Level Data) from the main form opens another form called "StateInterview_StateLvl_Form". This is the button that contains the OpenArgs code and the "StateInterview_StateLvl_Form" open command contains the subform code.

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Code:
    DCount("*", "InterviewTbl1_2_Data", "[InterviewCode] = " & Me.InterviewCode = 0)
    This is your problem. Instead, try
    Code:
    DCount("*", "InterviewTbl1_2_Data", "[InterviewCode] = '" & Me.InterviewCode & "'") = 0
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Apr 2012
    Posts
    3
    That you for the help weejas. That seemed to solve one of my problems, but now I get a vba runtime error 2046: "The command or action 'RecordsGoToNew' isn't available now."

    Here's the code as it currently sits

    Code:
    Private Sub Form_load()
    Dim InterviewCode As String
    
    If Not IsNull(Me.OpenArgs) Then
    If DCount("*", "InterviewTbl1_2_Data", "[InterviewCode] = '" & Me.InterviewCode & "'") = 0 Then
    DoCmd . RunCommand acCmdRecordsGoToNew       'this is the code that errors
    Me.InterviewCode = Me.OpenArgs
    Else
    Me.RecordsetClone.FindFirst "[InterviewCode] = " & Me.OpenArgs
    Me.Bookmark = Me.RecordsetClone.Bookmark
    End If
    End If
    
    
    End Sub
    Any thoughts?
    Last edited by Solvt; 05-02-12 at 16:40.

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Check the MS knowledge base for the order in which Form events happen. I have a feeling that you're trying to set the focus on a control before it's been drawn.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Form_Load is the correct event to do this kind of thing, so that's not the problem, here.

    I'm not sure that this is what's popping the error message, but these lines of code are inconsistent


    1. If DCount("*", "InterviewTbl1_2_Data", "[InterviewCode] = '" & Me.InterviewCode & "'") = 0 Then
    2. Me.RecordsetClone.FindFirst "[InterviewCode] = " & Me.OpenArgs

    The first line is only correct if the InterviewCode field is defined as Text. The second line of code is only correct if the InterviewCode field is defined as a Number. Obviously it cannot be both, and the code needs to be corrected, depending on the Datatype of InterviewCode.

    The next question would be, can the Record Source of the
    InterviewTbl1_2_Data Form have Records added? Can you add a Record if you open the Form independently?

    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

Posting Permissions

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