Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2007
    Posts
    102

    Unanswered: Need Where Proper Where statement in query

    Hello (and help greatly needed from experts),

    I have an Order form supported by a query of two tables (Customers and Orders). There is a subform within the Form for entering in the new Order infor, so I need both tables in the query. A customer is first selected through a combo box on the Order frm to begin new Order. Unfortunately, I can't bring up a newly added customer name IF that Customer doesn't have a prior order. Otherwise the combo can bring up any customer. I'm including some of the code that I think effects the results.

    Query SQL statement:


    Code: ( text )
    SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Customers.FirstName, Customers.LastName, Orders.OrderDate, Orders.ShipDate, Orders.FreightCharge, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipStateOrProvince, Orders.ShipZIPCode, Orders.ShipCountry, Customers.CompanyName, Customers.BillingAddress, Customers.City, Customers.StateOrProvince, Customers.ZIPCode, Customers.Country, Customers.ContactTitle, Orders.ShipPhoneNumber, Orders.ShipFaxNumber, Orders.PurchaseOrderNumber, Customers.Notes FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;


    What actually happens is when a new customer is selected the cursor skips the first three controls and jumps to the 4th (the first 3 are , firstname, lastname, customerid). Those fields match the row query for selecting a customer i
    Code: ( text )
    SELECT Customers.CustomerID, [FirstName] & " " & [LastName] FROM Customers ORDER BY [FirstName] & "," & [LastName];
    n the combo box:

    This same combo box has also an AfterUpdate event:

    Code: ( text )
    Private Sub custname_AfterUpdate()
    Private Sub custname_AfterUpdate()
    Me.FilterOn = True
    Me.Filter = "CustomerID = " & Me.custname
    Me.Requery
    End Sub



    I think I need a Where statement that will allow me to bring up a customer when there isn't a prior order associated. Can anyone help me formulate one? Or if I'm wrong on my assumption, point me in the right direction. I tried a left join instead and it didn't work. thanks in advance

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Sounds like you have two forms, the main form for customer info, and the subform for order info. Therefore, the main form only needs the Customer table in it's RecordSource query. Then the Order table will be used in the subform. Because the Order table has the customer number as one of its fields (CustomerID), then in the SubForm control on the main form, you can use customerID in the Master Link and Child Link properties. That is where the two tables will be linked for the use of these two forms, not in the query for the main form.

  3. #3
    Join Date
    Aug 2007
    Posts
    102

    That sounds great except for

    Hi Vic,

    I think I see where you're going. I should clear this up quickly. the Main form (has a subform for OrdersDetails(quantity, unit, price,,ect. and Products info) the controls that obtain data for the Orders table, such as Orders subtotal, Orders total, Order date, ect are included on the Main Form, along with the Customer name, Shipping details, so both the Customer and Order tables get updated through that query. Sorry if I confused the issue.
    imrosie

  4. #4
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    I would suggest looking at a couple of different ways to handle this situation.
    1. Split the customer fields off from the Order fields, and have a separate form for each. What that would give you is one main form with three subforms.
    2. The query for the main form you have now will have to include all the customer record, and then any order records that match. I have always known that as a Left Outer join. Personally, I just create the query in the query grid, double click on the join line from the customer table CustomerID to the Order table CustomerID, and select all records from the customer table option.
    Last edited by GolferGuy; 08-25-07 at 23:11.

  5. #5
    Join Date
    Aug 2007
    Posts
    102

    Left Join fixed it!

    Hi GolferGuy,,

    Thanks so much for the hints....I went back to figure out why the 'left join' didn't work.....well I had CustomerID from the Orders table, instead of the Customers table inthe query,,,all the other fields were fine.

    So now it's working .....
    imrosie.

  6. #6
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Good job! It's always nice to here what fixed it, plus when another user reads this, the answer is there!
    Thanks for letting us know.

  7. #7
    Join Date
    Aug 2007
    Posts
    102
    Hi GolferGuy,

    After I got the left join working......I started running through my program from top to bottom. On the main form supported by the 'left join'.....two events are not working now:
    1.) The control for OrderID (Orders) based on an autonumber too (it's part of the right side table of the Left Join), won't give me an Autonumber when I search a customer for the start of a new Order. Before (with Original Query) I would automatically get a new Autonumber (used for OrderID) in that control for the customer name in the combo box.

    2.)I have a command button on the Form called "beginanorder". It's purpose was to retain customer information, while clearing old order info. When a customer was located through combo box, the old Order also populates the controls, so this command button worked like this:
    Code: ( text )
    Private Sub beginanorder_Click()
    'Copies fields from original record to variables
    Field1 = Me.custacct
    Field2 = Me.atitle
    Field3 = Me.fname
    Field4 = Me.lname
    Field5 = Me.TheCompany

    'Begins a new record
    DoCmd.GoToRecord , , acNewRec

    'Plugs in old values into the new record
    Me.custacct.Value = Field1
    Me.atitle.Value = Field2
    Me.fname.Value = Field3
    Me.lname.Value = Field4
    Me.TheCompany.Value = Field5
    End Sub

    The problem is (I believe) that since NOW the Customers table is only available for the main form,,,,,,,,it's the Orders control fields, that now stopped working. The Order's table is outside the left join.....so I've got to figure a new way to display the info on the Main form and the Orders information (Orders table and Orders Detail table) in the subform.....
    If you've got ideas, feel free to throw them at me.
    take care...
    imrosie

Posting Permissions

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