Re: pivot table

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" <AccessAddict@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:8C3D7EC2-3C0C-4E96-99FF-E24F7F4E0CF3@xxxxxxxxxxxxxxxx
> >I apologize for the fact that these are syntax errors...I will get it...
> >
> > It doesn't like this statement is this syntax correct?:
> >
> > strItem=.fields(SUPSUBFL)
> >
> > as I run my cursor over the text SUPSUBFL it shows data for that field
> > yet, I get the error '3265' - Item not found in this collection
> > this error description does shows, though, as I run the cursor over the
> > word
> > "fields"
> >
> > SUPSUBFL shows as:
> > SUPSUBFL="E&J Gallo Winery/Burlwood/44.1" this data is correct.
> >
> >
> > "AccessAddict" wrote:
> >
> >> Thanks Duane...my first instinct was to add the brackets, but since I am
> >> not
> >> as familiar with VB and SQL, I thought you might not be able to do
> >> that...this will be much easier...since this is the way I'm used to...
> >>
> >> "Duane Hookom" wrote:
> >>
> >> > If you use spaces and punctuation, you must wrap your object names in
> >> > [ ]s.
> >> > 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]"
> >> > You had an extra comma to the left of "FROM" which I removed. I haven't
> >> > check the remainder of your code or logic (baby steps).
> >> > --
> >> > Duane Hookom
> >> > MS Access MVP
> >> > --
> >> >
> >> > "AccessAddict" <AccessAddict@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
> >> > message
> >> > news:165A7B45-9602-4506-A4A4-DEC4D8AAD01C@xxxxxxxxxxxxxxxx
> >> > >I just tried to change a table name...of course, it doesn't change in
> >> > >all
> >> > >the
> >> > > places that I have used it. Since I see that this is a big job, I
> >> > > will
> >> > > wait
> >> > > for your response to see if there is an easier way... :-( .
> >> > >
> >> > > "AccessAddict" wrote:
> >> > >
> >> > >> I should know better than that...but since Access lets you...I guess
> >> > >> I
> >> > >> decided, at some point, to wait until something like this
> >> > >> happened...before
> >> > >> giving up the descriptive names.
> >> > >>
> >> > >> Question #1:
> >> > >> If I change the names in my tables, will it change the names in the
> >> > >> queries,
> >> > >> forms, reports, etc.? I am used to using Access 97...I have noticed
> >> > >> that
> >> > >> in
> >> > >> Access 2003...I did change a name and it surprised me and changed
> >> > >> someplace
> >> > >> automatically...but does it change in all places? ...or should I
> >> > >> expect
> >> > >> more
> >> > >> problems?
> >> > >>
> >> > >> Question #2:
> >> > >> Will the "#Name" (which shows in the text boxes on the form)
> >> > >> disappear
> >> > >> when
> >> > >> I get the code (which fills them) correct? ... or is that an
> >> > >> entirely
> >> > >> different problem?
> >> > >>
> >> > >> Comment:
> >> > >> I guess I named this question incorrectly as well...so far not much
> >> > >> of
> >> > >> this
> >> > >> thread has had much to do with Pivot Tables...LOL...although the
> >> > >> final
> >> > >> layout
> >> > >> will be like a Pivot Table...but editable and updatable. Since this
> >> > >> is a
> >> > >> forum to help others, should I try to change the name of this
> >> > >> question...or
> >> > >> is that not do-able?
> >> > >>
> >> > >> Thanks...Donna
> >> > >>
> >> > >>
> >> > >>
> >> > >> "Duane Hookom" wrote:
> >> > >>
> >> > >> > You are being punished for allowing spaces and punctuation in your
> >> > >> > object
> >> > >> > names.
> >> > >> >
> >> > >> > Figure out how to fix this. If you can't find the fix, come back
> >> > >> > for an
> >> > >> > answer.
> >> > >> >
> >> > >> > --
> >> > >> > Duane Hookom
> >> > >> > MS Access MVP
> >> > >> >
> >> > >> >
> >> > >> > "AccessAddict" <AccessAddict@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
> >> > >> > message
> >> > >> > news:D318A05E-9A54-4CEA-94FF-867585A29AD7@xxxxxxxxxxxxxxxx
> >> > >> > > Duane...
> >> > >> > > I created a form with 20 rows and 36 columns - formatted as you
> >> > >> > > suggested
> >> > >> > > except:
> >> > >> > > The name intCustomer is replaced with the name intITEM
> >> > >> > > strCustomer is replaced with the name strITEM
> >> > >> > > intOrder is replaced with the name intPO
> >> > >> > > txtCust is replaced with txtITEM
> >> > >> > > txtOrdDate is replaced with txtPOPrice
> >> > >> > >
> >> > >> > > below is the code from my button...
> >> > >> > > ----------
> >> > >> > > Private Sub Command3191_Click()
> >> > >> > > On Error GoTo Err_Command3191_Click
> >> > >> > >
> >> > >> > >
> >> > >> > > DoCmd.GoToRecord , , acFirst
> >> > >> > >
> >> > >> > > Dim db As DAO.Database
> >> > >> > > Dim rs As DAO.Recordset
> >> > >> > > Dim strSQL As String
> >> > >> > > Dim intITEM As Integer
> >> > >> > > Dim strITEM As String
> >> > >> > > Dim intPO As Integer
> >> > >> > >
> >> > >> > > ' Select the [field],[field] from [table] joined to [table]
> >> > >> > > inner
> >> > >> > > join
> >> > >> > > [table].[key field]
> >> > >> > > ' [table].[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...
> >> > >> > >
> >> > >> > >
> >> > >> > > "Duane Hookom" wrote:
> >> > >> > >
> >> > >> > >> 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
.

Quantcast