Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2008

    Unanswered: Problem with Linking two forms using Record ID.

    I am trying to link a note's dialog form to an input form and for the life of me I cannot see the problem. I am using the code below.

    Private Sub CmdNotesInput_Click()
    On Error GoTo Err_CmdNotesInput_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "input_notes_form"
    stLinkCriteria = "[s_id]=" & Me![S_ID]

    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Exit Sub

    MsgBox Err.Description
    Resume Exit_CmdNotesInput_Click

    End Sub

    I am able to open the form and add data, but the record goes to next record. Ex. Main form opens on Record 1 and the Notes form opens on Record 2.

    The code works in my update form fine, but not in the input form. I am attempting to link both forms by the ID number (s_id).

  2. #2
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    Two things:

    Your syntax for the Criteria is correct if [s_ID] is Numeric; is it, in fact, defined as a Number in the underlying table?

    Access is very fussy about spacing in Criteria. When you place an expression using the equal sign in VBA, such as

    A + B=C

    Access will correct the spacing automatically, changing it to

    A + B = C

    But when you place the equal sign within quotes, Access leaves it exactly where you put it.

    Try changing

    stLinkCriteria = "[s_id]=" & Me![S_ID]


    stLinkCriteria = "[s_id] = " & Me![S_ID]

    being careful to leave a space on either side of the equal sign between [S_ID] and the following quotation mark.
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Sep 2008
    Yes, Missingling, the "s_id" is an autonumber (number). I added your suggested change and the notes still link to the next record. Ughh!

  4. #4
    Join Date
    Mar 2009
    Provided Answers: 14
    What's the sort order of the data source form the main form and the input_notes_form? If there are more than one record matching the criteria (more than one ID matching the current ID in the form) how do you determine which record will be the first one when you open input_notes_form?
    Try to specify the same sort order for the data sources (using queries for instance) of both forms or specify an additional condition in the criteria that ensures input_notes_form opens on the record exactly matching the one in the main form.

    Have a nice day!

  5. #5
    Join Date
    Nov 2007
    Adelaide, South Australia
    I see nothing wrong with the code at all. Except for the fact that it's machine generated :spew:

    I believe the problem is not in that code. Perhaps the other form has some code that does something when it's opened?

    For a form to go to record 2 when opened, there MUST be a record navigation command being issued. Why? Because if the filter syntax is wrong, the form would open to a new record, not record two. If the syntax is right then the form would open to record 1, whether it be the right record or the wrong record is irrelevant.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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