none
How to move to a specific record in a query record result RRS feed

  • Question

  • Hello, I have the following on click event procedure:

    Private Sub TextBoxNALCMBRID_Click()
    Dim strForm As String
    Dim strWhere As String
    strForm = "Branch 142 Membership"
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "Branch 142 Membership"
    stLinkCriteria = "[tblMembers.NALCMBRID]=" & Me![NALCMBRID]
    strWhere = "[NALCMBRID] = '" & [NALCMBRID] & "'"
    'DoCmd.OpenForm stDocName, , , stLinkCriteria
    'DoCmd.OpenForm "Branch 142 Membership", WhereCondition:="tblMembers.NALCMBRID=" & Me.NALCMBRID
    DoCmd.OpenForm strForm, , , acFormEdit
    End Sub

    I want to go to the specific NALCMBRID when I select it in a split form. The above code is taking me to the first record in the tblMembers for form: Branch 142 Membership.

    Saturday, April 21, 2018 2:04 PM

All replies

  • Check the help file for docmd.openform. Your answer is in the description of the OpenArgs parameter.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Saturday, April 21, 2018 6:12 PM
  • There's a lot of dead wood in there.  Prune it down to:

    Private Sub TextBoxNALCMBRID_Click()

        Const conForm = "Branch 142 Membership"
        Dim strWhere As String

        strWhere = "NALCMBRID = " & Me.NALCMBRID
        DoCmd.OpenForm conForm, WhereCondition:=strWhere

    End Sub

    This assumes that the NALCMBRID column is a number data type.  If it's text data type amend the code as follows:

         strWhere = "NALCMBRID = """ & Me.NALCMBRID & """"



    Ken Sheridan, Stafford, England

    Saturday, April 21, 2018 10:41 PM
  • Hi,

    You seem to using a mix of techniques in your code. For example, you have two variables strWhere and stLinkCriteria but you're not using any of them. You also have a few DoCmd.OpenForm lines commented out. Were you experimenting with these different techniques but couldn't get any of them to work? What happens if you tried the following?

    Private Sub TextBoxNALCMBRID_Click()
    
    DoCmd.OpenForm "Branch 142 Membership", , , "NALCMBRID=" & Me.NALCMBRID
    
    End Sub

    I would recommend avoiding using space characters in your form names.

    Hope it helps...

    Saturday, April 21, 2018 10:45 PM
  • Ken, thanks for the response. I tried your code. I got a run-time error 3079: The specified field 'NALCMBRID' could refer to more than one table listed in the FROM clause of your SQL statement. So, I selected the held button which gave me the following example:

    Because the statement does not specify which table OrderID belongs to, it produces this error. To complete this operation, fully qualify the field reference by adding a table name. For example:

    SELECT Orders.OrderID

    FROM Orders, [Order Details];

    So, I entered the statement line: Select tbl.Members.NALCMBRID. Then I got another error: Compile error: Expected Case. The explanation for this error was very confusing as it related to syntax provided for the SELECT I've seen.

    My use of the statement:  Dim stLinkCriteria As String and stLinkCriteria = "[tblMembers.NALCMBRID]=" & Me![NALCMBRID] where intended to define what table NALCMBRID can be found in.

    So, I'm guessing that the statement: strWhere = "NALCMBRID = """ & Me.NALCMBRID & """" has to define the table that the field NALCMBRID is in.  I have not been able to find the proper syntax referring to a table using the strWhere =.

    BTW, NALCMBRID is a text field.

    Thanks for the help.

    
    

     
    Tuesday, April 24, 2018 11:15 AM
  • Thanks for the response. I tried your code. I got a run-time error 3079: The specified field 'NALCMBRID' could refer to more than one table listed in the FROM clause of your SQL statement. So, I selected the held button which gave me the following example:

    Because the statement does not specify which table OrderID belongs to, it produces this error. To complete this operation, fully qualify the field reference by adding a table name. For example:

    SELECT Orders.OrderID

    FROM Orders, [Order Details];

    So, I entered the statement line: Select tbl.Members.NALCMBRID. Then I got another error: Compile error: Expected Case. The explanation for this error was very confusing as it related to syntax provided for the SELECT I've seen.

    My use of the statement:  Dim stLinkCriteria As String and stLinkCriteria = "[tblMembers.NALCMBRID]=" & Me![NALCMBRID] where intended to define what table NALCMBRID can be found in.

    So, I'm guessing that the statement: strWhere = "NALCMBRID = """ & Me.NALCMBRID & """" has to define the table that the field NALCMBRID is in.  I have not been able to find the proper syntax referring to a table using the strWhere =.

    Thanks for the help.

    Tuesday, April 24, 2018 11:29 AM
  • Where a query includes two columns of the same name then, in the SQL statement, the column name needs to be qualified by the table name to avoid ambiguity.  Normally such columns will be the primary and foreign keys of the two tables, and only one will be returned as a column by the query.  This means that, in the result table of the query, the column heading is the name of the column only, and is not qualified by the table name.  Consequently, when referencing the column in an expression, only the column name is used.  The fact that, in your case, the column heading is qualified by the table name suggests that both columns are being returned unnecessarily in the query's result table, in which case one can be deleted from the query.  Note that, in the case of an OUTER JOIN, the primary column from the referenced table should be returned, not the foreign key column of the referencing table.

    It is of course possible that two non-key columns of the same name from different tables are returned by a query, but have different values.  This would tend to suggest poor naming conventions, but where this is the case, one or both of the columns should, in the query, be given an alias which clearly differentiates the columns.  The column headings in the result table would then be the aliases, unqualified by a table name, and consequently can be referenced by the alias alone in an expression.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Tuesday, April 24, 2018 3:25 PM Typo corrected.
    Tuesday, April 24, 2018 3:24 PM
  • Ken thanks for the response. I made the following changes and it took me to the record in the tblMembers (form: Branch 142 Membership) when I selected the NALCMBRID in the split form: frmTotalAmtUnionDuesOwedByNALCMemberID or the datasheet:

    Private Sub TextBoxNALCMBRID_Click()
    Dim stLinkCriteria As String
    stDocName = "Branch 142 Membership"
    stLinkCriteria = "[tblMembers.NALCMBRID]=" & Me![NALCMBRID]
    strWhere = "[NALCMBRID] = '" & [NALCMBRID] & "'"
    DoCmd.OpenForm "Branch 142 Membership", WhereCondition:="tblMembers.NALCMBRID ='" & Me.NALCMBRID & "'"
    End Sub

    What does not make sense to me is why your prior:

    Private Sub MemberID_Click()
    DoCmd.OpenForm "Branch 142 Membership", WhereCondition:="tblMembers.MemberID=" & Me.MemberID
    End Sub

    did not work by replacing the: WhereCondition:="tblMembers.MemberID=" & Me.MemberID with: WhereCondition:="tblMembers.NALCMBRID=" & Me.NALCMBRID.

    MemberID is a primary key in the tblMembers.

    NALCMBRID is a text field in the tblMembers.  However, NALCMBRID is a number (not PK, Long Integer) field in the tblUnionDuesPayments.

    As always, thanks for your help.

    Tuesday, April 24, 2018 10:41 PM
  • What does not make sense to me is why your prior:

    Private Sub MemberID_Click()
    DoCmd.OpenForm "Branch 142 Membership", WhereCondition:="tblMembers.MemberID=" & Me.MemberID
    End Sub

    did not work by replacing the: WhereCondition:="tblMembers.MemberID=" & Me.MemberID with: WhereCondition:="tblMembers.NALCMBRID=" & Me.NALCMBRID.

    That was not what I said.  What I posted was:

    Private Sub TextBoxNALCMBRID_Click()

        Const conForm = "Branch 142 Membership"
        Dim strWhere As String

        strWhere = "NALCMBRID = " & Me.NALCMBRID
        DoCmd.OpenForm conForm, WhereCondition:=strWhere

    End Sub

    As I said before, the need to qualify the column name with the table name suggests two columns of the same name are being returned in the form's RecordSource query.  Why do you have two columns of the same name but different data types in two tables?  What attributes do these columns represent?


    Ken Sheridan, Stafford, England

    Wednesday, April 25, 2018 10:59 AM
  • Hi rdw1260,

    Is your issue solved?

    I find that you did not follow up this thread for long time.

    If your issue is solved then I suggest you to post your solution and mark it as an answer.

    If your issue is still exist then try to refer the solution given by the community members.

    If then also you have any further questions then let us know about it.

    We will try to provide further suggestions to solve the issue.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 3, 2018 9:22 AM
    Moderator