Opening a form based on data in another form RRS feed

  • Question

  • Windows 7, ACCESS 10, SQL Server

    The initial form has information regarding customers, like Master_ID, First Name, Last Name, etc. There may be one customer on the from or there may be several.  I can click on a Master ID text box in the tabular form and save that Master_ID in a Global called GBL_Master_ID. That part works.

    Next I click a button that has the following in the on_click event:

              Dim transferSQL As String

            transferSQL = "SELECT dbo_Master_Accounts.Master_ID, "
            transferSQL = transferSQL + "dbo_Master_Accounts.FirstName, "
            transferSQL = transferSQL + "dbo_Master_Accounts.LastName, "
            transferSQL = transferSQL + "dbo_Transaction_Table.Date_of_Transaction, "
            transferSQL = transferSQL + "Format([dbo_Transaction_Table]![Time_of_Transaction],'hh:nn:ss ampm') "
            transferSQL = transferSQL + "AS TranTime, dbo_Transaction_Table.Sku, dbo_Transaction_Table.Description, "
            transferSQL = transferSQL + "Right([dbo_Transaction_Table]![Description],6) AS tranAccnt, "
            transferSQL = transferSQL + "[dbo_Transaction_Table]![ArAmt]*-1 AS Amnt "
            transferSQL = transferSQL + "FROM dbo_Master_Accounts INNER JOIN dbo_Transaction_Table ON dbo_Master_Accounts.Master_ID = dbo_Transaction_Table.Account_Number "
            transferSQL = transferSQL + "WHERE (((dbo_Transaction_Table.Description) Like '%Transfer To%') AND ((dbo_Transaction_Table.Account_Number)=" & Chr$(34) & GBL_Master_Id & Chr$(34) & "))"
            transferSQL = transferSQL + "ORDER BY dbo_Transaction_Table.Date_of_Transaction"

           DoCmd.OpenForm "TransferbyNumFM", _
               Form.RecordSource = transferSQL, _
                wherecondition:="Master_ID=" & "GBL_Master_Id"

    Note that the variable GBL_Master_ID is both in the DoCmd and as a variable in the query. 

    The problem is the the form opens but there is no data in the form Just #Name where the data should be.  I've checked the immediate window and GBL_Master ID has the proper ID.  The records source for the form is blank, but each of the textboxes has a recordsource if the corresponding field in the query.  Been working on this for two days straight and just getting more confused.  Tried to makes this a sclear a spossible, but let me know if more information is needed.

    Thanks in advance



    Tuesday, March 15, 2016 7:27 PM

All replies

  • Hello,

    I guess you don't need apostrophe: wherecondition:="Master_ID=" & "GBL_Master_Id"

    if GBL_Master_Id is numeric - change to this: wherecondition:="Master_ID=" & GBL_Master_Id


    Friday, March 18, 2016 12:10 AM