Update Query

[tessaco via AccessMonster.com]

I seem to be having a problem with an update query I am trying to run. I have
two tables, "Territories" and "Unique Territory" There are 60 entries in the
Territories table and I would like the Unique territory table to display one
territory at a time in order to run subsequent reports.

Here is my code:

Set rsterritory = CurrentDb.OpenRecordset("select distinct Territory from
[Territories] order by Territory")
Do While Not rsterritory.EOF

'update current territory table to the current record
CurrentDb.Execute ("Update [Unique Territory] set [Territory] = '" &
rsterritory!territory & "'")

Next

r = r + 1
rs.MoveNext
Loop
Set rs = Nothing


I'm not sure what I am doing incorrectly, but I would appreciate any help!

Thanks!!

 

[Duane Hookom]

I can't understand what you mean by "I would like the Unique territory table
to display one territory at a time". There is nothing in your code that
"displays" anything. It looks like you are looping through some records and
updating all the records in another table, never stopping to do anything else.

 

[tessaco via AccessMonster.com]

Sorry for the confusion, I just posted the part of my code that I seemed to
be experiencing problems with.

This is the code in its entirety:

Public Sub RunComp()
Dim rs As Recordset
Dim XlApp As Excel.Application
Dim Xlbook As Excel.Workbook
'Dim xlSheet As Excel.Worksheet

'open territory record set
Dim rsterritory As Recordset

Set rsterritory = CurrentDb.OpenRecordset("select distinct Territory from
[Territories] order by Territory")
Do While Not rsterritory.EOF

'update current territory table to the current record
CurrentDb.Execute ("Update [Unique Territory] set [Territory] = '" &
rsterritory!territory & "'")

expfilename = "C:\Documents and Settings\int\Desktop\Statement Test\Reports\"
& rsterritory!territory & " " & "Statement.xls"

Set XlApp = CreateObject("Excel.Application")
Set Xlbook = XlApp.Workbooks.Open("C:\Documents and Settings\int\Desktop\
Statement Test\Statement.xls")
' Set xlSheet = Xlbook.Worksheets("data")


'First Report
startrow = 2
r = startrow
Set rs = CurrentDb.OpenRecordset("SELECT * FROM [Data]")
Do While Not rs.EOF
For x = 0 To rs.Fields.Count - 1
Xlbook.Worksheets("data").Cells(r, x + 1).Value = rs.Fields(x).
Value

Next

r = r + 1
rs.MoveNext
Loop
Set rs = Nothing

Xlbook.SaveAs (expfilename)
Xlbook.Saved = True

Xlbook.Close
Set Xlbook = Nothing

XlApp.Quit
Set XlApp = Nothing

Loop

End Sub



Thanks!

I can't understand what you mean by "I would like the Unique territory table
to display one territory at a time". There is nothing in your code that
"displays" anything. It looks like you are looping through some records and
updating all the records in another table, never stopping to do anything else.

I seem to be having a problem with an update query I am trying to run. I have
two tables, "Territories" and "Unique Territory" There are 60 entries in the

[quoted text clipped - 21 lines]

Thanks!!

 

[MGFoster]

I seem to be having a problem with an update query I am trying to run. I have
two tables, "Territories" and "Unique Territory" There are 60 entries in the
Territories table and I would like the Unique territory table to display one
territory at a time in order to run subsequent reports.

Here is my code:

Set rsterritory = CurrentDb.OpenRecordset("select distinct Territory from
[Territories] order by Territory")
Do While Not rsterritory.EOF

'update current territory table to the current record
CurrentDb.Execute ("Update [Unique Territory] set [Territory] = '" &
rsterritory!territory & "'")

Next

r = r + 1
rs.MoveNext
Loop
Set rs = Nothing


I'm not sure what I am doing incorrectly, but I would appreciate any help!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You're setting all the Unique Territory values to each Territories row's
(record's) value. IOW, it doesn't make any sense!

It is better to just use a DISTINCT or GROUP BY query to see the unique
territories rather than putting them in a separate table. Relational
DBs (what Access' JET [Joint Engine Technology] is) are meant to avoid
redundant data - putting the unique Territories in the "Unique" table is
redundant. When you change or delete a Territory in the Territories
table you'll have to change/delete it in the Unique table!

Having said that - here's 2 possible solutions to your problem:

If you want to put all the unique territories into the "Unique
Territory" table you'd do it like this:

1. Use an append query:

INSERT INTO [Unique Territory] (Territory)
SELECT DISTINCT Territory FROM Territories

2. Use a VBA loop - this is best if you want to avoid duplication
errors (this assumes that [Unique Territory]'s column Territory is a
unique index or the primary key):

some sub routine

on error goto err_

dim db as dao.database, rsT as dao.recordset, rsU as dao.recordset

set db = currentdb
set rsT = db.openrecordset("SELECT DISTINCT Territory " & _
"FROM Territories", dbOpenForwardOnly)
set rsU = db.openrecordset("[Unique Territory]")

do while not rsT.EOF
rsU.AddNew
rsU!Territory = rsT!Territory
rsU.Update
rsT.MoveNext
loop

exit_:
set rsU = nothing
set rsT = nothing
set db = nothing
exit sub

err_:
' catch duplicates error - ignore it
If err = 3022 then resume next
msgbox "Error: " & err.description, vbexclamation
resume exit_

A simple VIEW (a SELECT query in Access) would be best to see the
Territory names:

SELECT DISTINCT Territory FROM Territories ORDER BY Territory

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSoq3Q4echKqOuFEgEQKPnQCgzH8U0yZ/pynEnoJ2vkBdcZ5aBpkAnjtR
DpNhwvPGUtcw/bv2N6zNTtN3
=hdYW
-----END PGP SIGNATURE-----

 

[Duane Hookom]

I don't recall reading what is happening in your code or what the problem is.
there is nothing later in your code that I can see referenece the [Unique
Territory] table.

You must provide all significant information since we can't see your
application.

--
Duane Hookom
Microsoft Access MVP

Sorry for the confusion, I just posted the part of my code that I seemed to
be experiencing problems with.

This is the code in its entirety:

Public Sub RunComp()
Dim rs As Recordset
Dim XlApp As Excel.Application
Dim Xlbook As Excel.Workbook
'Dim xlSheet As Excel.Worksheet

'open territory record set
Dim rsterritory As Recordset

Set rsterritory = CurrentDb.OpenRecordset("select distinct Territory from
[Territories] order by Territory")
Do While Not rsterritory.EOF

'update current territory table to the current record
CurrentDb.Execute ("Update [Unique Territory] set [Territory] = '" &
rsterritory!territory & "'")

expfilename = "C:\Documents and Settings\int\Desktop\Statement Test\Reports\"
& rsterritory!territory & " " & "Statement.xls"

Set XlApp = CreateObject("Excel.Application")
Set Xlbook = XlApp.Workbooks.Open("C:\Documents and Settings\int\Desktop\
Statement Test\Statement.xls")
' Set xlSheet = Xlbook.Worksheets("data")


'First Report
startrow = 2
r = startrow
Set rs = CurrentDb.OpenRecordset("SELECT * FROM [Data]")
Do While Not rs.EOF
For x = 0 To rs.Fields.Count - 1
Xlbook.Worksheets("data").Cells(r, x + 1).Value = rs.Fields(x).
Value

Next

r = r + 1
rs.MoveNext
Loop
Set rs = Nothing

Xlbook.SaveAs (expfilename)
Xlbook.Saved = True

Xlbook.Close
Set Xlbook = Nothing

XlApp.Quit
Set XlApp = Nothing

Loop

End Sub



Thanks!

I can't understand what you mean by "I would like the Unique territory table
to display one territory at a time". There is nothing in your code that
"displays" anything. It looks like you are looping through some records and
updating all the records in another table, never stopping to do anything else.

I seem to be having a problem with an update query I am trying to run. I have
two tables, "Territories" and "Unique Territory" There are 60 entries in the

[quoted text clipped - 21 lines]

Thanks!!