Complex query for sub form

[Chris E.]

I am trying to "stuff" a text box on a form with the
results of a query. The basic need is to display the last
price that a client paid for a given product. I'm working
in an Order Detail subform. I have been trying to use the
Control Source for the text box I call LastPrice. I have
placed a SELECT statement in the field. The LastPrice
text box displays #name no matter what the select
statement is.

I've tried various versions of the following:

SELECT TOP 1 [OrderDetails].[UnitPrice] FROM
[OrderDetails] INNER JOIN [OrderDetails] ON [Orders].
[OrderID] = [OrderDetails].[OrderID] WHERE [Orders].
[CustomerID] = "the current order record's CustomerID"
AND [OrderDetails].[ProductID] = "the current detail
record ProductID" ORDER BY DESCENDING [Orders].[OrderDate]

Can I add AS LastPrice to the above to load the text box
or will the select do it because of the TOP 1.

What is the prefered syntax for loading a select with
specific data from a currently opened form? Should I use
Me!.CustomerID or is that only for VB? What about
selection criteria which needs data from the form
(CustomerID) and the sub form (ProductID) when ProductID
can have several rows of data.

What happens if there are no records that match the
criteria? What will LastPrice contain?

Thanks for any assistance.

Chris

 

[Dina]

I haven't read any further in your question than that your txtbox displays
'name#' no matter what you do. I had the same problem and all it was was
that I was missing msadodc.ocx in my system32 folder.
if thats not it you may be missing components for ur database.

good luck

 

[Treebeard]

I am trying to "stuff" a text box on a form with the
results of a query. The basic need is to display the last
price that a client paid for a given product. I'm working
in an Order Detail subform. I have been trying to use the
Control Source for the text box I call LastPrice. I have
placed a SELECT statement in the field. The LastPrice
text box displays #name no matter what the select
statement is.

I've tried various versions of the following:

SELECT TOP 1 [OrderDetails].[UnitPrice] FROM
[OrderDetails] INNER JOIN [OrderDetails] ON [Orders].
[OrderID] = [OrderDetails].[OrderID] WHERE [Orders].
[CustomerID] = "the current order record's CustomerID"
AND [OrderDetails].[ProductID] = "the current detail
record ProductID" ORDER BY DESCENDING [Orders].[OrderDate]

Can I add AS LastPrice to the above to load the text box
or will the select do it because of the TOP 1.

What is the prefered syntax for loading a select with
specific data from a currently opened form? Should I use
Me!.CustomerID or is that only for VB? What about
selection criteria which needs data from the form
(CustomerID) and the sub form (ProductID) when ProductID
can have several rows of data.

What happens if there are no records that match the
criteria? What will LastPrice contain?

Thanks for any assistance.

Chris

Chris,

I don't know any way to do want you want without some VBA code in your form.
Here's my suggestion:

As the record source for the LastPrice textbox put "=GetLastPrice()"

Create a query joining together the two tables , you don't need the "TOP"
statement. You probably have this query laying around already, we'll call it
"qryOrdersJoin" it should look something like this:

SELECT * FROM Orders, OrderDetails where [Orders].[OrderID] =
[OrderDetails].[OrderID] ;

Put the following function in the code of your subform. Unless the
[LastPrice] text box is on the master form, i'm assuming it's on the
subform.

good Luck,

Jack
**************************************************
Public Function GetLastPrice() As Currency

Dim TheLastPrice As Currency, strSQL As String
Dim dbs As ADODB.Connection, rst As ADODB.Recordset
Dim TheCustomerID As Long, TheProductID As Long

TheProductID = Me.ProductID
TheCustomerID = [Forms]![OrderForm].CustomerID


strSQL = "SELECT Max([qryOrdersJoin].[OrderDate]) AS [LastDate],
First([qryOrdersJoin].[UnitPrice]) As [LastUnitPrice]
FROM [qryOrdersJoin]
WHERE [CustomerID] = " & TheCustomerID &
" AND [ProductID] = " & TheProductID

TheLastPrice = 0 ' if there is none , it will return 0

' Return reference to current database.
Set dbs = CurrentProject.Connection ' open the database
Set rst = New Recordset ' open a recordset

' open the query
rst.Open strSQL, dbs, adOpenStatic, adLockReadOnly

If rst.RecordCount > 0 Then
TheLastPrice = rst!LastUnitPrice
End If

rst.Close
dbs.Close
Set dbs = Nothing
Set rst = Nothing
GetLastPrice= TheLastPrice
End Function

 

[Chris E]

-----Original Message-----

I am trying to "stuff" a text box on a form with the
results of a query. The basic need is to display the last
price that a client paid for a given product. I'm working
in an Order Detail subform. I have been trying to use the
Control Source for the text box I call LastPrice. I have
placed a SELECT statement in the field. The LastPrice
text box displays #name no matter what the select
statement is.

I've tried various versions of the following:

SELECT TOP 1 [OrderDetails].[UnitPrice] FROM
[OrderDetails] INNER JOIN [OrderDetails] ON [Orders].
[OrderID] = [OrderDetails].[OrderID] WHERE [Orders].
[CustomerID] = "the current order record's CustomerID"
AND [OrderDetails].[ProductID] = "the current detail
record ProductID" ORDER BY DESCENDING [Orders]. [OrderDate]

Can I add AS LastPrice to the above to load the text box
or will the select do it because of the TOP 1.

What is the prefered syntax for loading a select with
specific data from a currently opened form? Should I use
Me!.CustomerID or is that only for VB? What about
selection criteria which needs data from the form
(CustomerID) and the sub form (ProductID) when ProductID
can have several rows of data.

What happens if there are no records that match the
criteria? What will LastPrice contain?

Thanks for any assistance.

Chris

Chris,

I don't know any way to do want you want without some VBA code in your form.
Here's my suggestion:

As the record source for the LastPrice textbox put "=GetLastPrice()"

Create a query joining together the two tables , you don't need the "TOP"
statement. You probably have this query laying around already, we'll call it
"qryOrdersJoin" it should look something like this:

SELECT * FROM Orders, OrderDetails where [Orders]. [OrderID] =
[OrderDetails].[OrderID] ;

Put the following function in the code of your subform. Unless the
[LastPrice] text box is on the master form, i'm assuming it's on the
subform.

good Luck,

Jack
**************************************************
Public Function GetLastPrice() As Currency

Dim TheLastPrice As Currency, strSQL As String
Dim dbs As ADODB.Connection, rst As ADODB.Recordset
Dim TheCustomerID As Long, TheProductID As Long

TheProductID = Me.ProductID
TheCustomerID = [Forms]![OrderForm].CustomerID


strSQL = "SELECT Max([qryOrdersJoin].[OrderDate]) AS [LastDate],
First([qryOrdersJoin].[UnitPrice]) As [LastUnitPrice]
FROM [qryOrdersJoin]
WHERE [CustomerID] = " & TheCustomerID &
" AND [ProductID] = " & TheProductID

TheLastPrice = 0 ' if there is none , it will return 0

' Return reference to current database.
Set dbs = CurrentProject.Connection ' open the database
Set rst = New Recordset ' open a recordset

' open the query
rst.Open strSQL, dbs, adOpenStatic, adLockReadOnly

If rst.RecordCount > 0 Then
TheLastPrice = rst!LastUnitPrice
End If

rst.Close
dbs.Close
Set dbs = Nothing
Set rst = Nothing
GetLastPrice= TheLastPrice
End Function

Jack,

Thanks for the reply. I am using an Access 2000 structure
which I do not believe supports ADODB. Can you modify the
above to work in Access 2000? I am also having some
problems locating the right reference books or the right
sections for passing data between forms, reports,
preloading forms and reports with data.

Would you or anyone be interested in answering these
questions for $$. I don't have the time to screw around
with my client and spend hours looking through reference
books.

I have written all the tables, forms and reports.
Structures are all fine. It is in doing things like
loading a subform with the correct data rows. Extracting
the row reference from a subform (which row is selected)
then using that to determine the correct id which needs
to be passed into a command button to display additional
data. The additional data popup works fine, I just can't
seem to pass the correct ID into it.

I have a few scenarios like this that I need some hand
holding with syntax and some coaching.

Please respond to my email if you are interested in a few
hours of help later today and maybe some time tomorrow as
well.

Chris