Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2010
    Posts
    21

    Unanswered: Access VBA for ordering names that exist as foreign keys

    Hi All,

    Kind of clueless here,

    I want to order by company names but in the table they exist as foreign keys and ultimately numbers.

    I am considering this but I am wondering if it would work?



    Private Sub Form_Open(Cancel As Integer)
    Dim Strsql As String

    strSQL = "SELECT tbl_ShipOrders.ship_order_id, tbl_ShipOrders.receiver_id, tbl_ShipOrders.sender_id, Sender.company AS Sender_name, Receiver.company AS Receiver_name, " & _
    "FROM (tbl_ShipOrders " & _
    "INNER JOIN tbl_Customers AS Sender " & _
    "ON Sender.custid = tbl_ShipOrders.Sender_ID) " & _
    "INNER JOIN tbl_Customers AS Receiver " & _
    "ON Receiver.custid = tbl_ShipOrders.Receiver_ID " & _

    strOrder = Request.QueryString("order")
    strDirection = UCase(Request.QueryString("direction"))

    If strOrder = "" Then
    strOrder = "Idate"
    strDirection = "ASC"
    End If

    Select Case strOrder

    Case "ship_id":
    Strsql = Strsql & " ORDER BY ship_order_id " & strDirection
    Case "sender":
    Strsql = Strsql & " ORDER BY LOWER(Sender_Name)" & strDirection
    Case "Receiver":
    Strsql = Strsql & " ORDER BY LOWER(Receiver_Name) " & strDirection

    End Sub


    Actually, I have taken this from an asp form so some parts obviously won't work, not quite sure how to adapt it to VBA

  2. #2
    Join Date
    May 2009
    Posts
    258
    Hello ben,

    If you don't need it to be variably ordered and there will be no parameters, I'd take the query out of VBA and store it as an actual query, where updating it will be much easier. With that, you could use the following:
    Code:
    SELECT tbl_ShipOrders.ship_order_id, tbl_ShipOrders.receiver_id, tbl_ShipOrders.sender_id, Sender.company AS Sender_name, Receiver.company AS Receiver_name,
    FROM (tbl_ShipOrders
    INNER JOIN tbl_Customers AS Sender
    ON Sender.custid = tbl_ShipOrders.Sender_ID)
    INNER JOIN tbl_Customers AS Receiver
    ON Receiver.custid = tbl_ShipOrders.Receiver_ID
    ORDER BY Sender.company, Receiver.company
    After it's defined, use DAO with the current database to open it into a recordset and process.

    Regards,

    Ax

  3. #3
    Join Date
    Feb 2010
    Posts
    21
    Thanks Ax

    I managed it in the end, thanks for your reply

    Ben

Posting Permissions

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