.[first-key field of a combo-field-key field]
strSQL = "SELECT SUPSUBFL, Post Off Price, FROM N ITEMS PRICING TABLE
INNER
JOIN N Post Off Table ON N items Pricing Table.Item # = N POST OFF
TABLE.Item
# ORDER BY SUPSUBFL, Post Off Price"
' SUPSUBFL
' POST OFF PRICE
' N ITEMS PRICING TABLE
' N POST OFF TABLE
' N ITEMS PRICING TABLE.Item #
' N POST OFF TABLE.Item #
' sort by SUPSUBFL, POST OFF PRICE
' The syntax won't let me past this point...
' I get the error:
'Run-Time error '3075'
'Syntax error (missing operator) in query expression 'Post Off Price'
'Plus, the text boxes that I created on the form with the names show #Name
in all of
'them.
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst
With rs
intITEM = 0
Do Until .EOF Or intITEM > 4152
strITEM = .Fields(SUPSUBFL)
intITEM = intITEM + 1
Me("txtITEM" & intITEM) = strITEM
intPO = 0
'This code groups by SUPSUBFL
Do Until strITEM <> .Fields(SUPSUBFL) Or intPO > 11952
intPO = intPO + 1
Me("txtPOPrice" & intITEM & "_" & intPO) = .Fields("Post
Off
Price")
.MoveNext
Loop
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing
Exit_Command3191_Click:
Exit Sub
Err_Command3191_Click:
MsgBox Err.Description
Resume Exit_Command3191_Click
End Sub
---------------
Thanks again Duane for your time...
:
This might be a bit complex but your question is complex. I created a
form
in the Northwind database with 18 text boxes in a grid of 6 columns and 3
rows. The first column text boxes have the names of:
txtCust1 - txtCust3
The 2 - 6 text boxes of the first row have names like:
txtOrdDate1_1 - txtOrdDate1_5
The 2 - 6 text boxes of the second row have names like:
txtOrdDate2_1 - txtOrdDate2_5
The 2 - 6 text boxes of the third row have names like:
txtOrdDate3_1 - txtOrdDate3_5
I added a command button on the form that opens a recordset and places
customers and order dates into the text boxes. Code would need to be
written
that would loop through the controls on the form and either update or
append
records back to the table(s).
Private Sub cmdPullOrderDates_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim intCustomer As Integer
Dim strCustomer As String
Dim intOrder As Integer
strSQL = "SELECT CompanyName, OrderDate " & _
"FROM Customers INNER JOIN " & _
"Orders ON Customers.CustomerID = Orders.CustomerID " & _
"ORDER BY CompanyName, OrderDate"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst
With rs
intCustomer = 0
Do Until .EOF Or intCustomer > 2
strCustomer = .Fields("CompanyName")
intCustomer = intCustomer + 1
Me("txtCust" & intCustomer) = strCustomer
intOrder = 0
Do Until strCustomer <> .Fields("CompanyName") Or intOrder >
4
intOrder = intOrder + 1
Me("txtOrdDate" & intCustomer & "_" & intOrder) =
..Fields("OrderDate")
.MoveNext
Loop
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing
End Sub
--
Duane Hookom
MS Access MVP
Duane...
As far as I know, I have no limit on the number of columns created in
the
crosstab.
although, I do know the approximate limit to its use in this
application...of the twelve months the average additional odd dates
would
be
three additional per month...give or take a few. Some months will have
more
than three others none. The way their calendar works 7/1/2005 is
really a
June date and 7/31/2005 is really an August date. As it stands now
10/30/2005 & 10/31/2005 are the only two for November and 11/26/2005 is
the
only date for December. These dates are dynamic in that all dates
after
the
current date are subject to the addition of another date before the
year
ends.
I am not familiar with "unbound" forms... I have always used forms
bound
to
queries or tables. I just tried to create an unbound form with text
boxes
as
you said...(of course, my text boxes are referencing tables EX: =[GROUP
Crosstab MAKE TABLE]![Item #]). However, the expressions come back
with
#Name?... Should I be entering the code into the SQL view? Or am I
using
the wrong syntax? Could you give me an example of what you are talking
about?
I appreciate your help and just because I'm addicted doesn't mean that
I
know everything...LOL I'm addicted to the learning of it...I'm a
"can't
stop
until I find a solution" type of addict..."where there's a will there's
a
way"
Thanks for your time Duane...
:
Do you have any limits on the number of columns created in your
crosstab?
I guess I would create an unbound form that code fills from a
recordset.
When the user is done updating the unbound grid of text boxes, more
code
would loop through the controls and either update or append values to
your
original table.
--
Duane Hookom
MS Access MVP
message
I apologize for the name of the query (as it started out as a Pivot
Table
then I tried the crosstab)
[PivotTable Update] is a select query combining the [Post-Off Table]
to
the
[Items Table] using the item # as key (Items table)
and [item #] combined with the [start date] as key [Post Off Table].
There
are several Post Off's for each item #.
The crosstab query gives me the structure that I need for user
input,
but
does not let me type into the cells.
-------------
This is the SQL:
TRANSFORM First([PivotTable Update].[Post Off Price]) AS
[FirstOfPost
Off
Price]
SELECT [PivotTable Update].SUPSUBFL, [PivotTable Update].[Item #],
[PivotTable Update].[Item Description], First([PivotTable
Update].[Post
Off
Price]) AS [Total Of Post Off Price]
FROM [PivotTable Update]
GROUP BY [PivotTable Update].SUPSUBFL, [PivotTable Update].[Item #],
[PivotTable Update].[Item Description]
PIVOT Format([Post Off Start Date],"Short Date");
---------------------
SUPSUBFL = combination of three fields for grouping purposes (for
updating
groups of similar data - with similar structures)
But..
