pivot table

Duane...

I have cleaned up the code a little (removed old useless comments & some
".moveNext's") and am examining my "if" statements...

Question: If I don't want to move to the next record (as in the middle of
the if statement) what would I put there to make the code continue to the
next line?

Please Note: In the code, I switched the "if" back to the (less than
#6/4/2005# to try to accomplish this), hoping it would go to the next line of
code...and got an error saying that I had a "loop" without a "Do". The "Do"
is there. ???? I temporarily put the ".moveNext" back until I figure it
out. Oh, I forgot to tell you that it now picks-up the missing date and does
not put the incorrect amount in the 1/2/2005 column. But it is still not
recognizing the "if" statement and says it "can't find txtPO_7/31/2005
referred to in your expression" and I now know why it is putting the value of
the nextrecord in a date field that is not valid for "this" record...it is
the moveNext in my if statement (so I guess the "If" is working
afterall...but not all the way).
More similar code below (tired of it yet? I am. LOL):
Private Sub UpdateGroup_Click()
On Error GoTo Err_UpdateGroup_Click

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String
Dim intITEM As Integer
Dim hldITEM As String
Dim intGroup As Integer
Dim hldGroup As String
Dim hldPOST As String



strSQL = "SELECT [SUPSUBFL],[Post Off Start Date],[Post Off Price],[N POST
OFF TABLE].[Item #] FROM [N ITEM PRICING TABLE]INNER JOIN[N Post Off
Table]ON[N item Pricing Table].[Item #]=[N POST OFF TABLE].[Item #] ORDER BY
[SUPSUBFL],[N Item Pricing Table].[Item #],[Post Off Start Date]"
'strSQL = "SELECT [SUPSUBFL],[Post Off Start Date],[Post Off
Price],[Grouping Post Off Query].[Item #] from [Grouping Post Off Query]
ORDER BY [SUPSUBFL],[Post Off Start Date]"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

rs.MoveFirst
With rs

intGroup = 0
intITEM = 0

hldGroup = .Fields("SUPSUBFL")
hldITEM = .Fields("[Item #]")

Do Until hldGroup .Fields("SUPSUBFL") Or .EOF

Me("txtGroup") = hldGroup
intGroup = intGroup + 1
intITEM = intITEM + 1

Do Until .EOF Or hldGroup .Fields("SUPSUBFL")

hldITEM = .Fields("[Item #]")
Me("txtITEM" & intITEM) = hldITEM

Do Until hldITEM .Fields("Item #") Or .EOF

If (.Fields("[Post Off Start Date]") #6/4/2005#)
Then hldPOST = .Fields("[Post Off Start Date]") Else .MoveNext

hldPOST = Str$(.Fields("[Post Off Start Date]"))

Me("txtPO" & intITEM & "_" & hldPOST) =
..Fields("[Post Off Price]")

.MoveNext
Loop
Loop
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing

Exit_UpdateGroup_Click:
Exit Sub

"AccessAddict" wrote:

LOL...I understand, so you see my dilema...I understand, as well, if you want
to bow out...this is a lot to ask of anyone, except myself.

I have managed to move along a little further since that last stream...and I
did get the date in the string...and the data moves across from the item #
through the dates and fills in the form and (almost) in the correct
places...I'm still working on it. The first one has the wrong amount in the
right place, then it doesn't put an amount in the 1/30/2005 spot that it
should (although, at one point it did - I lost it), then I changed the last
column to accept a date that was out of range - because it doesn't seem to
recognize my "if" statements and says it can't find that date - and it puts
the correct amount in for that date. I changed the name of that last
textbox..to move it along and see what would happen. I will eventually
change it back and have a second button with the code for the second half of
the year (unless you can help me get more text boxes on one form-LOL).

It doesn't seem to be handling the first record, because it is putting the
wrong amount in the first spot (1/2/2005). I don't seem to have control of
the data. I have sorted the tables the way I want and used them in the SQL
statement then I tried to join the tables in a query and use that in the
SQL...to no avail. I will probably go back and forth with this concept until
I get it right - I did have it a little closer in this area at one time
before I got the string date value working.

I will list my code and you can see how I handled the date value string - I
probably have some logic problems as well:

Private Sub UpdateGroup_Click()
On Error GoTo Err_UpdateGroup_Click

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String
Dim intITEM As Integer
Dim hldITEM As String
Dim intGroup As Integer
Dim hldGroup As String
Dim hldPOST As String
'Dim S As String
'Dim P As String


'strSQL = "SELECT [SUPSUBFL],[Post Off Start Date],[Post Off Price],[N POST
OFF TABLE].[Item #] FROM [N ITEM PRICING TABLE]INNER JOIN[N Post Off
Table]ON[N item Pricing Table].[Item #]=[N POST OFF TABLE].[Item #] ORDER BY
[SUPSUBFL],[Post Off Start Date]"
strSQL = "SELECT [SUPSUBFL],[Post Off Start Date],[Post Off Price],[Grouping
Post Off Query].[Item #] from [Grouping Post Off Query] ORDER BY
[SUPSUBFL],[Post Off Start Date]"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

'rs.MoveFirst
With rs

intGroup = 0
intITEM = 0

hldGroup = .Fields("SUPSUBFL")
hldITEM = .Fields("[Item #]")


'Do Until .EOF Or hldGroup .Fields("SUPSUBFL")
Do Until hldITEM .Fields("[Item #]") Or .EOF
Me("txtGroup") = hldGroup

intGroup = intGroup + 1
intITEM = intITEM + 1
.MoveNext

' Do Until .EOF Or hldITEM .Fields("[Item #]") Or intITEM 19
' Do Until .EOF Or intITEM 19
Do Until .EOF Or hldGroup .Fields("SUPSUBFL")
hldITEM = .Fields("[Item #]")

Me("txtITEM" & intITEM) = hldITEM

.MoveNext

'Do Until hldGroup .Fields("SUPSUBFL") Or .EOF
Do Until hldITEM .Fields("Item #") Or .EOF
If (.Fields("[Post Off Start Date]") #6/4/2005#) Then
.MoveNext Else hldPOST = .Fields("[Post Off Start Date]")

If (hldPOST = " ") Then .MoveNext

' S = Str$(.Fields("[Post Off Start Date]"))
' P = Str$(.Fields("[Post Off Price]"))

hldPOST = Str$(.Fields("[Post Off Start Date]"))
Me("txtPO" & intITEM & "_" & hldPOST) = .Fields("[Post
Off Price]")


.MoveNext
Loop
Loop
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing



Exit_UpdateGroup_Click:
Exit Sub

Err_UpdateGroup_Click:
MsgBox Err.Description
Resume Exit_UpdateGroup_Click
End Sub



"Duane Hookom" wrote:

This is more than I bargained for. My text boxes didn't have a date in the
control names. They used an integer value that was incremented in the
looping code.

--
Duane Hookom
MS Access MVP


"AccessAddict" wrote in message
...
Duane...
I am back. Of course, needing help. I apologize for the length of this
stream.

Here's where I am. I only want one group (SUPSUBFL) at a time, to show on
this form.

Therefore, I have adjusted the code for that purpose. It (sort of) worked
but because of the incrementation of the integer field used to fill in the
body of the form, it just strung them across the form (one after the
other)
and not in the specific columns that I wanted.
I want the value of (Post Off Price) to populate the body according to the
(Post Off Start Date).
I adjusted the code to use the field (Post Off Start Date) in the text
boxes
and named each text box accordingly "txtPO1_1/2/2005", "txtPO2_1/2/2005,
etc.


The group header works as does the txtItems fields down the first column.
When I get this code working, I will use an adjusted copy of it to clear
the
form for a new group.
Also, the form only lets me put enough controls on it for half of the year
so I added the following if statements (expecting to code another button
the
same for the second half of the year):

If (.Fields("[Post Off Start Date]") #6/5/2005#) Then hldPOST =
.Fields("[Post Off Start Date]") Else .MoveNext
If (hldPOST = 0) Then .MoveNext
Me("txtPO" & intITEM & "_" & hldPOST) = .Fields("[Post Off Price]")

The body of the form does not work. The error I get is "cannot find the
field txtPO_12:00:00 AM referred to in your expression" . The
txtPO_(field)
should be the date - not the time. How do I code the form to only include
mmddyyyy? Or do you have another idea?

Below is the actual code:

Private Sub UpdateGroup_Click()
On Error GoTo Err_UpdateGroup_Click

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String
Dim intITEM As Integer
Dim hldITEM As String
Dim intGroup As String
Dim hldGroup As String
Dim hldPOST As Date


strSQL = "SELECT [SUPSUBFL],[Post Off Start Date],[Post Off Price],[N POST
OFF TABLE].[Item #] FROM [N ITEM PRICING TABLE]INNER JOIN[N Post Off
Table]ON[N item Pricing Table].[Item #]=[N POST OFF TABLE].[Item #] ORDER
BY
[SUPSUBFL],[Post Off Start Date]"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

rs.MoveFirst
With rs

intGroup = 0
intITEM = 0

hldGroup = .Fields("SUPSUBFL")
hldITEM = .Fields("[Item #]")


Do Until .EOF Or hldGroup .Fields("SUPSUBFL")

Me("txtGroup") = hldGroup

intGroup = intGroup + 1

.MoveNext

Do Until .EOF Or intITEM 19

intITEM = intITEM + 1
hldITEM = .Fields("[Item #]")

Me("txtITEM" & intITEM) = hldITEM

.MoveNext

Do Until hldGroup .Fields("SUPSUBFL") Or .EOF

If (.Fields("[Post Off Start Date]") #6/5/2005#) Then
hldPOST = .Fields("[Post Off Start Date]") Else .MoveNext
If (hldPOST = 0) Then .MoveNext
Me("txtPO" & intITEM & "_" & hldPOST) = .Fields("[Post
Off Price]")

.MoveNext
Loop
Loop
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing


Exit_UpdateGroup_Click:
Exit Sub

Err_UpdateGroup_Click:
MsgBox Err.Description
Resume Exit_UpdateGroup_Click

End Sub

"AccessAddict" wrote:

Thank you...sorry for the oversight...I guess I thought you put the
quotation
marks there to tell me to insert the name of my table. I also noticed
them
in the strSQL - but kept them for some reason. I am not familiar with
string
logic...therefore, yes, I am learning.

Thanks.

I am not out of the woods yet...but as you may have guessed from an
earlier
thread...I have some correcting to do. As it turns out, first I used
labels
instead of text boxes, then, I didn't put the names in the tab other
under
name...I put it under the data tab in the control source box. I had a
bad
day Friday...locked my keys in the car twice...so it does not surprise me
that I did this Friday as well. I am still correcting the text
boxes...but
wanted to let you know that I appreciate all that you have helped me
with.

I'll be back...I'm sure...but not today...

Have a good one!

"Duane Hookom" wrote:

My code from earlier reply:
strCustomer = .Fields("CompanyName")
Your code:
strItem=.fields(SUPSUBFL)

Note the difference?

--
Duane Hookom
MS Access MVP
--

"AccessAddict" wrote in
message
...
I apologize for the fact that these are syntax errors...I will get
it...

It doesn't like this statement is this syntax correct?:

Okay...I got it working...code below:

Now I have to figure out how to use the combo box that I have on another
form (it chooses the group for this form) to produce the specified group.
Any ideas?
I know that I can run a macro (from Access) to retrieve this form...but I
have only created combo boxes on forms not in VBA. Probably the same only
different - right?
....or, sorry, just thinking "outloud"...LOL
possibly put a combo box on this form...let me try that...sheesh...be back.

Private Sub UpdateGroup_Click()
On Error GoTo Err_UpdateGroup_Click

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String
Dim intITEM As Integer
Dim hldITEM As String
Dim intGroup As Integer
Dim hldGroup As String
Dim hldPOST As String



strSQL = "SELECT [SUPSUBFL],[Post Off Start Date],[Post Off Price],[N POST
OFF TABLE].[Item #] FROM [N ITEM PRICING TABLE]INNER JOIN[N Post Off
Table]ON[N item Pricing Table].[Item #]=[N POST OFF TABLE].[Item #] ORDER BY
[SUPSUBFL],[N Item Pricing Table].[Item #],[Post Off Start Date]"
'strSQL = "SELECT [SUPSUBFL],[Post Off Start Date],[Post Off
Price],[Grouping Post Off Query].[Item #] from [Grouping Post Off Query]
ORDER BY [SUPSUBFL],[Post Off Start Date]"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

rs.MoveFirst
With rs

intGroup = 0
intITEM = 0

hldGroup = .Fields("SUPSUBFL")
hldITEM = .Fields("[Item #]")

Do Until hldGroup .Fields("SUPSUBFL") Or .EOF

Me("txtGroup") = hldGroup
intGroup = intGroup + 1
intITEM = intITEM + 1

Do Until .EOF Or hldGroup .Fields("SUPSUBFL")

hldITEM = .Fields("[Item #]")
Me("txtITEM" & intITEM) = hldITEM

Do Until hldITEM .Fields("Item #") Or .EOF

If (.Fields("[Post Off Start Date]") #6/5/2005#)
Then

hldPOST = .Fields("[Post Off Start Date]")

hldPOST = Str$(.Fields("[Post Off Start Date]"))

Me("txtPO" & intITEM & "_" & hldPOST) =
..Fields("[Post Off Price]")
intITEM = intITEM + 1
'.MoveNext
'Else
'.MoveNext
End If
.MoveNext
Loop
Loop
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing



Exit_UpdateGroup_Click:
Exit Sub

Err_UpdateGroup_Click:
MsgBox Err.Description
Resume Exit_UpdateGroup_Click
End Sub


"AccessAddict" wrote:

Duane...

I have cleaned up the code a little (removed old useless comments & some
".moveNext's") and am examining my "if" statements...

Question: If I don't want to move to the next record (as in the middle of
the if statement) what would I put there to make the code continue to the
next line?

Please Note: In the code, I switched the "if" back to the (less than
#6/4/2005# to try to accomplish this), hoping it would go to the next line of
code...and got an error saying that I had a "loop" without a "Do". The "Do"
is there. ???? I temporarily put the ".moveNext" back until I figure it
out. Oh, I forgot to tell you that it now picks-up the missing date and does
not put the incorrect amount in the 1/2/2005 column. But it is still not
recognizing the "if" statement and says it "can't find txtPO_7/31/2005
referred to in your expression" and I now know why it is putting the value of
the nextrecord in a date field that is not valid for "this" record...it is
the moveNext in my if statement (so I guess the "If" is working
afterall...but not all the way).
More similar code below (tired of it yet? I am. LOL):
Private Sub UpdateGroup_Click()
On Error GoTo Err_UpdateGroup_Click

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String
Dim intITEM As Integer
Dim hldITEM As String
Dim intGroup As Integer
Dim hldGroup As String
Dim hldPOST As String



strSQL = "SELECT [SUPSUBFL],[Post Off Start Date],[Post Off Price],[N POST
OFF TABLE].[Item #] FROM [N ITEM PRICING TABLE]INNER JOIN[N Post Off
Table]ON[N item Pricing Table].[Item #]=[N POST OFF TABLE].[Item #] ORDER BY
[SUPSUBFL],[N Item Pricing Table].[Item #],[Post Off Start Date]"
'strSQL = "SELECT [SUPSUBFL],[Post Off Start Date],[Post Off
Price],[Grouping Post Off Query].[Item #] from [Grouping Post Off Query]
ORDER BY [SUPSUBFL],[Post Off Start Date]"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

rs.MoveFirst
With rs

intGroup = 0
intITEM = 0

hldGroup = .Fields("SUPSUBFL")
hldITEM = .Fields("[Item #]")

Do Until hldGroup .Fields("SUPSUBFL") Or .EOF

Me("txtGroup") = hldGroup
intGroup = intGroup + 1
intITEM = intITEM + 1

Do Until .EOF Or hldGroup .Fields("SUPSUBFL")

hldITEM = .Fields("[Item #]")
Me("txtITEM" & intITEM) = hldITEM

Do Until hldITEM .Fields("Item #") Or .EOF

If (.Fields("[Post Off Start Date]") #6/4/2005#)
Then hldPOST = .Fields("[Post Off Start Date]") Else .MoveNext

hldPOST = Str$(.Fields("[Post Off Start Date]"))

Me("txtPO" & intITEM & "_" & hldPOST) =
.Fields("[Post Off Price]")

.MoveNext
Loop
Loop
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing

Exit_UpdateGroup_Click:
Exit Sub

"AccessAddict" wrote:

LOL...I understand, so you see my dilema...I understand, as well, if you want
to bow out...this is a lot to ask of anyone, except myself.

I have managed to move along a little further since that last stream...and I
did get the date in the string...and the data moves across from the item #
through the dates and fills in the form and (almost) in the correct
places...I'm still working on it. The first one has the wrong amount in the
right place, then it doesn't put an amount in the 1/30/2005 spot that it
should (although, at one point it did - I lost it), then I changed the last
column to accept a date that was out of range - because it doesn't seem to
recognize my "if" statements and says it can't find that date - and it puts
the correct amount in for that date. I changed the name of that last
textbox..to move it along and see what would happen. I will eventually
change it back and have a second button with the code for the second half of
the year (unless you can help me get more text boxes on one form-LOL).

It doesn't seem to be handling the first record, because it is putting the
wrong amount in the first spot (1/2/2005). I don't seem to have control of
the data. I have sorted the tables the way I want and used them in the SQL
statement then I tried to join the tables in a query and use that in the
SQL...to no avail. I will probably go back and forth with this concept until
I get it right - I did have it a little closer in this area at one time
before I got the string date value working.

I will list my code and you can see how I handled the date value string - I
probably have some logic problems as well:

Private Sub UpdateGroup_Click()
On Error GoTo Err_UpdateGroup_Click

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String
Dim intITEM As Integer
Dim hldITEM As String
Dim intGroup As Integer
Dim hldGroup As String
Dim hldPOST As String
'Dim S As String
'Dim P As String


'strSQL = "SELECT [SUPSUBFL],[Post Off Start Date],[Post Off Price],[N POST
OFF TABLE].[Item #] FROM [N ITEM PRICING TABLE]INNER JOIN[N Post Off
Table]ON[N item Pricing Table].[Item #]=[N POST OFF TABLE].[Item #] ORDER BY
[SUPSUBFL],[Post Off Start Date]"
strSQL = "SELECT [SUPSUBFL],[Post Off Start Date],[Post Off Price],[Grouping
Post Off Query].[Item #] from [Grouping Post Off Query] ORDER BY
[SUPSUBFL],[Post Off Start Date]"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

'rs.MoveFirst
With rs

intGroup = 0
intITEM = 0

hldGroup = .Fields("SUPSUBFL")
hldITEM = .Fields("[Item #]")


'Do Until .EOF Or hldGroup .Fields("SUPSUBFL")
Do Until hldITEM .Fields("[Item #]") Or .EOF
Me("txtGroup") = hldGroup

intGroup = intGroup + 1
intITEM = intITEM + 1
.MoveNext

' Do Until .EOF Or hldITEM .Fields("[Item #]") Or intITEM 19
' Do Until .EOF Or intITEM 19
Do Until .EOF Or hldGroup .Fields("SUPSUBFL")
hldITEM = .Fields("[Item #]")

Me("txtITEM" & intITEM) = hldITEM

.MoveNext

'Do Until hldGroup .Fields("SUPSUBFL") Or .EOF
Do Until hldITEM .Fields("Item #") Or .EOF
If (.Fields("[Post Off Start Date]") #6/4/2005#) Then
.MoveNext Else hldPOST = .Fields("[Post Off Start Date]")

If (hldPOST = " ") Then .MoveNext

' S = Str$(.Fields("[Post Off Start Date]"))
' P = Str$(.Fields("[Post Off Price]"))

hldPOST = Str$(.Fields("[Post Off Start Date]"))
Me("txtPO" & intITEM & "_" & hldPOST) = .Fields("[Post
Off Price]")


.MoveNext
Loop
Loop
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing



Exit_UpdateGroup_Click:
Exit Sub

Err_UpdateGroup_Click:
MsgBox Err.Description
Resume Exit_UpdateGroup_Click
End Sub



"Duane Hookom" wrote:

This is more than I bargained for. My text boxes didn't have a date in the
control names. They used an integer value that was incremented in the
looping code.

--
Duane Hookom
MS Access MVP


"AccessAddict" wrote in message
...
Duane...
I am back. Of course, needing help. I apologize for the length of this
stream.

Here's where I am. I only want one group (SUPSUBFL) at a time, to show on
this form.

Therefore, I have adjusted the code for that purpose. It (sort of) worked
but because of the incrementation of the integer field used to fill in the
body of the form, it just strung them across the form (one after the
other)
and not in the specific columns that I wanted.
I want the value of (Post Off Price) to populate the body according to the
(Post Off Start Date).
I adjusted the code to use the field (Post Off Start Date) in the text
boxes
and named each text box accordingly "txtPO1_1/2/2005", "txtPO2_1/2/2005,
etc.


The group header works as does the txtItems fields down the first column.
When I get this code working, I will use an adjusted copy of it to clear
the
form for a new group.
Also, the form only lets me put enough controls on it for half of the year
so I added the following if statements (expecting to code another button
the
same for the second half of the year):

If (.Fields("[Post Off Start Date]") #6/5/2005#) Then hldPOST =
.Fields("[Post Off Start Date]") Else .MoveNext
If (hldPOST = 0) Then .MoveNext
Me("txtPO" & intITEM & "_" & hldPOST) = .Fields("[Post Off Price]")

The body of the form does not work. The error I get is "cannot find the
field txtPO_12:00:00 AM referred to in your expression" . The
txtPO_(field)
should be the date - not the time. How do I code the form to only include
mmddyyyy? Or do you have another idea?

Below is the actual code:

Private Sub UpdateGroup_Click()
On Error GoTo Err_UpdateGroup_Click

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String
Dim intITEM As Integer
Dim hldITEM As String
Dim intGroup As String
Dim hldGroup As String
Dim hldPOST As Date


strSQL = "SELECT [SUPSUBFL],[Post Off Start Date],[Post Off Price],[N POST
OFF TABLE].[Item #] FROM [N ITEM PRICING TABLE]INNER JOIN[N Post Off
Table]ON[N item Pricing Table].[Item #]=[N POST OFF TABLE].[Item #] ORDER
BY
[SUPSUBFL],[Post Off Start Date]"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

rs.MoveFirst
With rs

intGroup = 0
intITEM = 0

hldGroup = .Fields("SUPSUBFL")
hldITEM = .Fields("[Item #]")


Do Until .EOF Or hldGroup .Fields("SUPSUBFL")

Me("txtGroup") = hldGroup