"Me.OrderBy" Not working?

[tonygg]

Hi.

I was hoping to get some help with the strange behaviour I am getting. I have created a continuous form based on a view (actually a nested view showing the orders and the products being ordered and the person ordering them - please see bottom of post).

The data set is orders. I pull back the full set of orders and give the user a series of buttons to filter the order by different dates. I then want the filtered form to be ordered by the products rather than the orders. This would show all the orders for each product in a list (filtered by the date the user selects).



I have the filtering working fine. However I just cant get the ordering to change. To be honest when I execute the code it looks like it just comes back with some totally random ordering!

The code I am using on the form is:


FilterText = "[Order Sequence Number] >= " & FirstOrderOnDate

Me.Filter = FilterText
Me.FilterOn = True

Me.OrderBy = "[sProductDescription]"
Me.OrderByOn = True

I was hoping someone could point me in the right direction to get the filtering to work?

Not sure if it is relevant but my queries are:

1. THE FORM IS CREATED FROM:

SELECT Person.Name, Order.Status AS OrderStatus, Order.[Order Number], TONY_ML_OrderedProducts.sProductDescription, Order.[Date Received], Order.nTotalItemsShipped, [Order].[nTotalItemsOrdered]-([Order].[nTotalItemsShipped]+[Order].[nTotalItemsCancelled]+[Order].[nTotalItemsBackOrdered]) AS nItemsOutstanding, Order.bOrderIsDeleted, Order.Status, Order.[Date Ordered], Order.[Date Order Finished], Order.nPaymentStatus, TONY_ML_OrderedProducts.ProductReference, Order.InvoiceContactID, TONY_ML_OrderedProducts.QuantityOrdered, Order.nCustomerID, Order.[Total Cost], [Order].dHandlingCost+[Order].[Shipping Total Cost] AS [Ship Hand], [Order].[Tax Total Cost 1]+[Order].[Tax Total Cost 2] AS Tax, Order.bOngoingOrder, TONY_ML_OrderedProducts.Price, Order.[Order Sequence Number]
FROM Person INNER JOIN ([Order] INNER JOIN TONY_ML_OrderedProducts ON Order.[Order Sequence Number] = TONY_ML_OrderedProducts.[Order Sequence Number]) ON Person.ContactID = Order.InvoiceContactID
WHERE (((Order.nPaymentStatus)=10) AND ((Person.ContactID)=[Order].[InvoiceContactID] Or (Person.ContactID)=[Order].[DeliverContactID]))
ORDER BY TONY_ML_OrderedProducts.sProductDescription;

2. THE QUERY ABOVE USES ANOTHER QUERY called "TONY_ML_OrderedProducts" :

SELECT Order.[Order Number], Order.[Date Received], Order.[Order Sequence Number], OrderDetail.sProductDescription, OrderDetail.ProductReference, OrderDetail.QuantityOrdered, Order.nCustomerID, Order.[Total Cost], [Order].dHandlingCost+[Order].[Shipping Total Cost] AS [Ship Hand], [Order].[Tax Total Cost 1]+[Order].[Tax Total Cost 2] AS Tax, OrderDetail.bIsComponent, OrderDetail.Price
FROM [Order] INNER JOIN OrderDetail ON Order.[Order Sequence Number] = OrderDetail.OrderSequenceNumber
WHERE (((OrderDetail.ProductReference) Not Like ':::::') AND ((OrderDetail.QuantityOrdered)>0) AND ((OrderDetail.Price)>0)) OR (((OrderDetail.bComponentsAsSepOrderLines)=True))
ORDER BY OrderDetail.sProductDescription;

Many thanks

Tony

[John_G]

Try doing a requery: ( Me.Requery ) after setting the OrderBy and Filter properties.

[tonygg]

Hi John

I am doing a Me.Requery (sorry I should have said). Also I tried a separate ordering only button.

I wondered whether it was the nested query so I created a single query between ORDERS and ORDER DETAILS (1 to Many). This seemed to filter and sort fine.

I was wondering if I joined the tables in one single query it might work as well. Trouble is I am not sure how to create the query.

I have 3 tables: Person, Order and OrderDetail.

The join between Order and OrderDetail is: FROM [Order] INNER JOIN OrderDetail ON Order.[Order Sequence Number] = OrderDetail.OrderSequenceNumber

The join between Person and Order is: FROM Person INNER JOIN [Order] ON Person.ContactID = [Order].InvoiceContactID

I was wondering if you knew how to combine these 2 joins into a single SQL statement?

I tried: FROM Person INNER JOIN ([Order] INNER JOIN OrderDetail ON Order.[Order Sequence Number] = OrderDetail.[Order Sequence Number]) ON Person.ContactID = Order.InvoiceContactID but that doesn't work.

Many thanks

Tony

[tonygg]

I have realised I made a mistake with the name of Order Sequence Number. It works fine now with the new single query across the three tables.

Thanks

Tony