Excel for Life: 25 new messages in 16 topics

microsoft.public.access.modulesdaovba
http://groups.google.com/group/microsoft.public.access.modulesdaovba?hl=en

microsoft.public.access.modulesdaovba@googlegroups.com

Today's topics:

==============================================================================
TOPIC: Looping through controls
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/86ccbdf3634a1c3b?hl=en
==============================================================================

== 1 of 1 ==
Date: Fri, Nov 16 2007 10:10 am
From: "Douglas J. Steele"

Try something like:

Dim ctlSubform As Control
Dim ctrLoop As Control

For Each ctlSubform In Me.Controls
If TypeOf ctlSubform Is Subform Then
For Each ctrLoop In ctlSubform.Form.Controls
' ctrLoop.OnUndo = "myProcedure"
Next ctrLoop
End If
Next frmLoop

However, I'm not aware of an Undo event (I only have Access 97 on this
machine, and it definitely doesn't have that event) If there is an Undo
event, the property would likely be called Undo, not OnUndo, and not every
control would have one (for example, since you can't type in a label or
line, there wouldn't be an Undo event for them). What you might want to do
is add a function like the following:

Function HasEvent(ControlObject As Control, EventName As String) As Boolean
On Error Resume Next

Dim strCurrProc As String

strCurrProc = ControlObject.Properties(EventName)
HasEvent = (Err.Number = 0)

End Function

You could then use

Dim ctlSubform As Control
Dim ctrLoop As Control

For Each ctlSubform In Me.Controls
If TypeOf ctlSubform Is Subform Then
For Each ctrLoop In ctlSubform.Form.Controls
If HasEvent(ctlLoop, "Undo") Then
ctrLoop.Undo = "myProcedure"
End If
Next ctrLoop
End If
Next frmLoop

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"NasaDBGuy" <NasaDBGuy@discussions.microsoft.com> wrote in message
news:955CD9DC-291A-4FD7-8C20-DF2E2ABCC689@microsoft.com...
> is there a way I can loop through all the controls in all the subforms in
> an
> Access form? I want to call a function in each control, so I have
> something
> like:
>
> Dim frmLoop As Form
> Dim ctrLoop As Control
>
>
> For Each frmLoop In Forms
> For Each ctrLoop In frmLoop
> Me.frmLoop.Controls.ctrLoop.OnUndo = "myProcedure"
>
> Next ctrLoop
> Next frmLoop
>
> Am I on the right track? I know my syntax is bad. That's why it's not
> working? Thanks for any help

==============================================================================
TOPIC: Combobox record source query results not visible in form
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/b0b748eceff3b668?hl=en
==============================================================================

== 1 of 2 ==
Date: Fri, Nov 16 2007 10:16 am
From: CT3or4

The least complex table in the db Backend is suddenly not "viewable" in
Combobox results of multiple Frontends & forms.
The recordsource queries are always visible in all FE form properties.
"Visible" property is YES throughout every Properties (2 wekks pouring over
code).
No module in BE exists nor FE exists or, should relate to the table or
related controls in the various FEs.

Table comprises: ID(autonumber), Text, the ID again as an appended text
string.

Constantly tweaking the FEs but the disappearance of the query result in the
forms was sudden & universal, unrelated to any changes made in some (because
tweaks were not made in all); each has independent forms/controls affected in
addition to some shared/linked.
I've checked the table in BE, of course. Again, no module at all except
Gobal (IsLoaded); it remains unchanged since BE created 2 years ago. Table
Properties unchanged since creation, as best I can tell.

Worst case: is the a utility that will show when & where changes were made
in code &/or macros? I'll go fish, db-by-db, if needed.
Thanks,
--
CT3or4

== 2 of 2 ==
Date: Fri, Nov 16 2007 11:59 am
From: "George Nicholson"

You don't say what version of Access you are using. I am going to guess that
its 2003 and that the problems started as soon as SP3 was applied.

What you are seeing is *one* of the known bugs caused by SP3. (Sure explains
sudden and universal, huh?)
http://allenbrowne.com/tips.html

Not 100% sure, but I believe the fix is to open the backend and remove any
formatting applied to the field that displays in the combo. You might check
other posts for something a bit more definitive on that score.

--
HTH,
George

"CT3or4" <CT3or4@discussions.microsoft.com> wrote in message
news:1E25600C-E867-4459-A2C3-20ADBF3F8235@microsoft.com...
> The least complex table in the db Backend is suddenly not "viewable" in
> Combobox results of multiple Frontends & forms.
> The recordsource queries are always visible in all FE form properties.
> "Visible" property is YES throughout every Properties (2 wekks pouring
> over
> code).
> No module in BE exists nor FE exists or, should relate to the table or
> related controls in the various FEs.
>
> Table comprises: ID(autonumber), Text, the ID again as an appended text
> string.
>
> Constantly tweaking the FEs but the disappearance of the query result in
> the
> forms was sudden & universal, unrelated to any changes made in some
> (because
> tweaks were not made in all); each has independent forms/controls affected
> in
> addition to some shared/linked.
> I've checked the table in BE, of course. Again, no module at all except
> Gobal (IsLoaded); it remains unchanged since BE created 2 years ago. Table
> Properties unchanged since creation, as best I can tell.
>
> Worst case: is the a utility that will show when & where changes were made
> in code &/or macros? I'll go fish, db-by-db, if needed.
> Thanks,
> --
> CT3or4

==============================================================================
TOPIC: ADO Connection
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/7189f0bec8d7fe75?hl=en
==============================================================================

== 1 of 1 ==
Date: Fri, Nov 16 2007 10:19 am
From: Rio

When establishing an ADO connection when should I use

Set cn = CurrentProject.Connection

cnn.Open "Provider=sqloledb;" & _
"Data Source=yourServerName;" & _
"Initial Catalog=yourDatabaseName;" & _
"Integrated Security=SSPI"

My app is Access (front-end) and SQL Server (back-end)

Thanks!

==============================================================================
TOPIC: Save an Outlook attachment using VBA
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/3731915bb5cd3ba9?hl=en
==============================================================================

== 1 of 1 ==
Date: Fri, Nov 16 2007 10:34 am
From: kos_sd

I would like to automate the saving of a Outlook attachment, to a specific
drive and file name using a VBA coding in either Outlook, Excel or Access. I
can open the email with Access but cannot save or copy the attachment without
doing some manual things. Is there a way I could at least save the
attachment as a file to the drive desired?

==============================================================================
TOPIC: change label backcolor on all forms
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/0ad0a6c33992c174?hl=en
==============================================================================

== 1 of 3 ==
Date: Fri, Nov 16 2007 10:45 am
From: ToniS

I have an .adp application and I would like to add an interface to allow the
user to change the color for all labels on all forms. I am already using the
AllForms object to change the title on each form based on the user selection
and that works fine.
I am not sure how to add the same type of logic for all labels.. My naming
convention for all lables is as follows: they all start with 'lbl'

Any suggestions would be greatly appreciated

Thanks
ToniS

== 2 of 3 ==
Date: Fri, Nov 16 2007 1:22 pm
From: fredg

On Fri, 16 Nov 2007 10:45:02 -0800, ToniS wrote:

> I have an .adp application and I would like to add an interface to allow the
> user to change the color for all labels on all forms. I am already using the
> AllForms object to change the title on each form based on the user selection
> and that works fine.
> I am not sure how to add the same type of logic for all labels.. My naming
> convention for all lables is as follows: they all start with 'lbl'
>
> Any suggestions would be greatly appreciated
>
> Thanks
> ToniS

Open each form (you can use the same procedure you are using to change
the name of the form) in Design View. Cycle through the controls. If
the control is a Label control, change it's backcolor. Then save the
form changes and go on to the next form.

Incorporate the below snippet of code into your current code that
cycles through the form collection to change the form name.

frmName is whatever name you have called this form.

DoCmd.OpenForm frmName, acDesign, , , , acHidden

Dim ctl as Control
For each ctl in Controls
If TypeOf ctl is Label then
ctl.BackColor = vbBlue
End If
Next ctl
DoCmd.Close acForm, frmName, acSaveYes

Continue looping through the forms collection to get the next form.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

== 3 of 3 ==
Date: Fri, Nov 16 2007 2:22 pm
From: ToniS

Thanks I think this is exactly what I am looking for, unfortunately I am not
sure what I am doing wrong.... The titles change correctly but the colors do
not, I did test the IntBackColor by setting
Forms(obj.Name).LblTitle.backcolor = intbackcolor and that did work...

What is the purpose of the DoCmd.OpenForm and Close? I did not need that
for changing the title on each form? FYI: The below code is located in
module1 in a public sub....

Below is what I have.

Dim obj As AccessObject
Dim ctl As Controls
Dim dbs As Object

DoCmd.OpenForm Form_Frm_Maintenance_CurrentShow, acDesign, , , , acHidden

For Each obj In dbs.AllForms
Forms(obj.Name).LblTitle.Caption = strTitle

' For Each ctl In Controls
For Each ctl In dbs.AllForms.Controls
If TypeOf ctl Is Label Then
' ctl.BackColor = vbBlue
' ctl.BackColor = intbackcolor
dbs.AllForms.Controls.ctl.BackColor = intBackcolor
End If
Next ctl
Next obj

DoCmd.Close acForm, Form_Frm_Maintenance_CurrentShow, acSaveYes

"fredg" wrote:

> On Fri, 16 Nov 2007 10:45:02 -0800, ToniS wrote:
>
> > I have an .adp application and I would like to add an interface to allow the
> > user to change the color for all labels on all forms. I am already using the
> > AllForms object to change the title on each form based on the user selection
> > and that works fine.
> > I am not sure how to add the same type of logic for all labels.. My naming
> > convention for all lables is as follows: they all start with 'lbl'
> >
> > Any suggestions would be greatly appreciated
> >
> > Thanks
> > ToniS
>
> Open each form (you can use the same procedure you are using to change
> the name of the form) in Design View. Cycle through the controls. If
> the control is a Label control, change it's backcolor. Then save the
> form changes and go on to the next form.
>
>
> Incorporate the below snippet of code into your current code that
> cycles through the form collection to change the form name.
>
> frmName is whatever name you have called this form.
>
> DoCmd.OpenForm frmName, acDesign, , , , acHidden
>
> Dim ctl as Control
> For each ctl in Controls
> If TypeOf ctl is Label then
> ctl.BackColor = vbBlue
> End If
> Next ctl
> DoCmd.Close acForm, frmName, acSaveYes
>
> Continue looping through the forms collection to get the next form.
>
>
> --
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail
>

==============================================================================
TOPIC: exif data from jpg's
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/4b577c7fec3e2b7e?hl=en
==============================================================================

== 1 of 1 ==
Date: Fri, Nov 16 2007 11:27 am
From: "Albert D. Kallal"

"CowDev" <CowDev@discussions.microsoft.com> wrote in message
news:C0CC8801-F95E-4C5B-9EBB-9C0C98A2FDBB@microsoft.com...

> Thanks for the quick reply Albert. I did come across that sample code in
> my
> searches. However, I am unfamiliar with incorporating vb6 .cls files into
> MS
> Access (2003) and have not found any tutorials for doing this. Can you
> assist.
>
> jim

Sure, I just tried this...and it works like a charm.....

Simply unzip the download.

Now, in the folder, simply open up that file called ExifReader.cls

If you have vb6 installed, it will see it appear as a VB6 class icon, and if
you double click on the file (ExifReader.cls), then the
VB6 development system and editor will launch when you click on it.

If you do NOT have vb6 installed, then when you click on the file
(ExifReader.cls), then you be asked to choose an web service, or the 2nd
option of select the program from a list. (use the 2nd option)

application to open it with

(use notepad) .

When you open the text document, simply place the cursor on the first line,
hold down the shift key, and start hitting the down arrow key.
We need to highlight up to the line:

Option Explicit.

(it is aout 16 lines you have to hight lit)...now, hit the delete key).

If you had vb6 insalled, the first junk would be removed for you..and you
simply cut/paste the code...

So, we delete all stuff up to the line Option Explicit.

Now, go edit->select all.

now edit->copy

We now are now going to paste this code into a ms-accesss CLASS OBJECT
modulete..

Open up the access appcation were you going to use this code.

from the main menu, go

insert->class module

You see:

Option Compare Database
Option Explicit

move your cusor below the above two lines (you might not have the Option
Expclit, but that somtong you should have set in the tools->otipons...we fix
that later).

Now, edit->paste in your code

(if you copied the Option Explit..hten we have it two times...delete one of
them)

now, save (hit the save buttion on teh code editor menu).

THIS NAME we going to give the module will become the name of this class
object code we just pasted.

lets call it

clsExif

After you save it, you should do a debug->compile...make sure all your code
compiles....

Now, in one of your standard code modules, or forms, lets do a test of this
code:

Sub testread()

Dim clsPic As New clsExif

clsPic.picFile = "c:\1.jpg"
Debug.Print clsPic.Tag(ExifImageHeight)
Debug.Print clsPic.Tag(ExifImageWidth)
Debug.Print clsPic.Tag(DateTime)

End Sub

When I run the above, I get:

2304
3072
2007:10:22 19:41:10

I using a 7.2 mp digital camera...

Of course, that means I have a picture called 1.jpg in c:\ (in the root
directory).

Note that I never used this before, but intel-sense WILL show you the "list"
of appropriate volutes in the tag function when you hit the "("...if it does
not , then try pressing ctrl-j...it should pop a list of possible values.
This intel-sense ONLY works if you have no compile errors in your code...

I did the above as I typed these instructions...and it worked the first
time.

It is a nice routine ...and I will save it...as I also have need for a jpeg
picture reading routine....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

==============================================================================
TOPIC: Show me function
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/8c24e13f4016cce5?hl=en
==============================================================================

== 1 of 1 ==
Date: Fri, Nov 16 2007 11:29 am
From: Del

How do I turn on the "Show me" function that Ouactrl.ocx update disabled?
--
Thank you,
Del

==============================================================================
TOPIC: Visible = True and Visible = False
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/6b1da785c92f7cd8?hl=en
==============================================================================

== 1 of 1 ==
Date: Fri, Nov 16 2007 11:36 am
From: dsc2bjn

==============================================================================
TOPIC: recordset - incorrect number of records returning
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/87a043fd38669c0e?hl=en
==============================================================================

== 1 of 2 ==
Date: Fri, Nov 16 2007 11:44 am
From: "George Nicholson"

> but only returns it once.

Are you basing that statement on the value of temprs.Recordcount? Did you
use temprs.MoveLast before testing Recordcount?

Per the Help entry for Recordcount (DAO):

**************
The RecordCount property doesn't indicate how many records are contained in
a dynaset-, snapshot-, or forward-only-type Recordset object until all
records have been accessed. Once the last record has been accessed, the
RecordCount property indicates the total number of undeleted records in the
Recordset or TableDef object. To force the last record to be accessed, use
the MoveLast method on the Recordset object.
**************

--
HTH,
George

"Steven Cheng" <StevenCheng@discussions.microsoft.com> wrote in message
news:08C6823F-A501-4140-96DF-F13DA444EB67@microsoft.com...
>i have a base table (transactioncodes) which has 4 fields (transactionno
> (duplicates allowed, indexed, integer type), description, default code,
> and
> search string).
>
> i have declared two variables, temprs and rs1 which call upon
> transactioncodes twice for different reasons:
> 1. set rs1 = db.openrecordset("Select * from
> Transactioncodes",dbOpensnapshot) used at the beginning of the module and
> left until the module closes.
> 2. set temprs = db.openrecordset("Select * from Transactioncodes WHERE
> transactionno = " & x) which is called upon to look for only those records
> that satisfy the x value passed onto it (integer type). this is created
> in
> the middle of the procedure.
>
> i have tested the sqlstring used for temprs and it appears to be fine.
> however, in certain instances where a ceran transaction code appears
> multiple
> times, temprs should return those records but only returns it once.
>
> is this having to do with the fact that i transactioncodes called as a
> snapshot previously?

== 2 of 2 ==
Date: Fri, Nov 16 2007 1:56 pm
From: Steven Cheng

George, indeed it did. Thanks.

"George Nicholson" wrote:

> > but only returns it once.
>
> Are you basing that statement on the value of temprs.Recordcount? Did you
> use temprs.MoveLast before testing Recordcount?
>
> Per the Help entry for Recordcount (DAO):
>
> **************
> The RecordCount property doesn't indicate how many records are contained in
> a dynaset-, snapshot-, or forward-only-type Recordset object until all
> records have been accessed. Once the last record has been accessed, the
> RecordCount property indicates the total number of undeleted records in the
> Recordset or TableDef object. To force the last record to be accessed, use
> the MoveLast method on the Recordset object.
> **************
>
> --
> HTH,
> George
>
>
> "Steven Cheng" <StevenCheng@discussions.microsoft.com> wrote in message
> news:08C6823F-A501-4140-96DF-F13DA444EB67@microsoft.com...
> >i have a base table (transactioncodes) which has 4 fields (transactionno
> > (duplicates allowed, indexed, integer type), description, default code,
> > and
> > search string).
> >
> > i have declared two variables, temprs and rs1 which call upon
> > transactioncodes twice for different reasons:
> > 1. set rs1 = db.openrecordset("Select * from
> > Transactioncodes",dbOpensnapshot) used at the beginning of the module and
> > left until the module closes.
> > 2. set temprs = db.openrecordset("Select * from Transactioncodes WHERE
> > transactionno = " & x) which is called upon to look for only those records
> > that satisfy the x value passed onto it (integer type). this is created
> > in
> > the middle of the procedure.
> >
> > i have tested the sqlstring used for temprs and it appears to be fine.
> > however, in certain instances where a ceran transaction code appears
> > multiple
> > times, temprs should return those records but only returns it once.
> >
> > is this having to do with the fact that i transactioncodes called as a
> > snapshot previously?
>
>
>

==============================================================================
TOPIC: Visible = True Vs Visble = False
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/ab35e58d40612388?hl=en
==============================================================================

== 1 of 5 ==
Date: Fri, Nov 16 2007 11:54 am
From: dsc2bjn

I have a report which returns a series of records for which I wish to
suppress the duplicate values for a few fields. The "Hide Duplicate"
attribute will only hide the duplicates on a single page of the report. I
which to hide all duplicates for like values throughout the report.

My VB skills are weak (ok practically non-existant); however, I inherited
this DB from someone who's code kind of works, but not in all instances.

Data Structure for the Report is:
FunctionOrder, Function, ActivityOrder, Activity, Risk.

Each Function may contain mulitple Activities.
Each Activity may contain multiple Risk.

I wish to supress the display for the FunctionOrder, Function,
ActivityOrder, and Activity for all but the first record.

Below is the code I inherited, it kind of works. On some pages the
FunctionOrder, Function, ActivtyOrder, and Activity repeat. I am resonably
sure it occurs when a new activity starts on a new page and the risks
associated to the Activity can not fit on the page.

Any suggestions would be greatly appreciated.

Code:
---------------
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
' Declaring Function and pageno variables locally and as static
' So that if another report is running at same time, it will not use them
' as it might had they been declared publicly or with the same
' name as in the other report.

' Note - this report has code which controls manually when and where the
' "F-", the function order and the function print on the page.
' To handle this manually or programmatically, the code gets somewhat
complicated.
' One aspect handled is the suppression of this function information on the
' next page when a function begins on the previous page and continued to the
next
' page.
Static strrptRIAFunction As String
Static strrptRIAActivity As String

Static reccount As Integer
'set line width
Me.DrawWidth = 10
'create gridlines
Me.Line (0, 0)-(0, 15000)
Me.Line (3115, 0)-(3115, 15000)
Me.Line (6780, 0)-(6780, 15000)
Me.Line (8980, 0)-(8980, 15000)
Me.Line (9540, 0)-(9540, 15000)
Me.Line (11830, 0)-(11830, 15000)
Me.Line (14300, 0)-(14300, 15000)
Me.Line (15110, 0)-(15110, 15000)

' Defines the Text Fields used to designate Function and Activity (prefix
for FunctionOrder and ActivityOrder
Me.Text14 = "F-"
Me.Text101 = "A-"

' Set default of visible
Me.Text14.Visible = True
Me.Function.Visible = True
Me.FunctionOrder.Visible = True
Me.Text101.Visible = True
Me.ActivityOrder.Visible = True
Me.Activity.Visible = True

' Set Visibility
If Me.Function.OldValue <> strrptRIAFunction Then
Me.Text14.Visible = True
Me!FunctionOrder.Visible = True
Me!Function.Visible = True
End If

If Me.Function.OldValue = strrptRIAFunction Then
Me.Text14.Visible = False
Me!FunctionOrder.Visible = False
Me!Function.Visible = False
End If

If Me.Activity.OldValue <> strrptRIAActivity Then
Me.Text101.Visible = True
Me!ActivityOrder.Visible = True
Me!Activity.Visible = True
End If

If Me.Activity.OldValue = strrptRIAActivity Then
Me.Text101.Visible = False
Me!ActivityOrder.Visible = False
Me!Activity.Visible = False
End If

strrptRIAFunction = Nz(Me.Function.OldValue)
strrptRIAActivity = Nz(Me.Activity.OldValue)

End Sub

== 2 of 5 ==
Date: Fri, Nov 16 2007 12:09 pm
From: "John Spencer"

Are you printing the five fields in a detail section? If so, you can add
another control to determine if the fields should print.

New text control
Name: LineCounter
Control Source: =1
Running Sum: Overall

Now add code to the Detail Section's format event

If Me.LineCounter = 1 then
Me.FunctionOrder.visible = True
Me.Function.Visible = True
Me.ActivityOrder.Visible = True
Me.Activity.Visible = True
Else
Me.FunctionOrder.visible = False
Me.Function.Visible = False
Me.ActivityOrder.Visible = False
Me.Activity.Visible = False

End IF

That will show the four controls for the first record and hide them for
every other record in the report. If that is not what you want, then post
back with a better description of what you do want.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

"dsc2bjn" <dsc2bjn@discussions.microsoft.com> wrote in message
news:0F0D6B6C-1361-422B-BD31-BC0EC5F46B79@microsoft.com...
>I have a report which returns a series of records for which I wish to
> suppress the duplicate values for a few fields. The "Hide Duplicate"
> attribute will only hide the duplicates on a single page of the report. I
> which to hide all duplicates for like values throughout the report.
>
> My VB skills are weak (ok practically non-existant); however, I inherited
> this DB from someone who's code kind of works, but not in all instances.
>
> Data Structure for the Report is:
> FunctionOrder, Function, ActivityOrder, Activity, Risk.
>
> Each Function may contain mulitple Activities.
> Each Activity may contain multiple Risk.
>
> I wish to supress the display for the FunctionOrder, Function,
> ActivityOrder, and Activity for all but the first record.
>
> Below is the code I inherited, it kind of works. On some pages the
> FunctionOrder, Function, ActivtyOrder, and Activity repeat. I am
> resonably
> sure it occurs when a new activity starts on a new page and the risks
> associated to the Activity can not fit on the page.
>
> Any suggestions would be greatly appreciated.
>
> Code:
> ---------------
> Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
> ' Declaring Function and pageno variables locally and as static
> ' So that if another report is running at same time, it will not use them
> ' as it might had they been declared publicly or with the same
> ' name as in the other report.
>
> ' Note - this report has code which controls manually when and where the
> ' "F-", the function order and the function print on the page.
> ' To handle this manually or programmatically, the code gets somewhat
> complicated.
> ' One aspect handled is the suppression of this function information on
> the
> ' next page when a function begins on the previous page and continued to
> the
> next
> ' page.
> Static strrptRIAFunction As String
> Static strrptRIAActivity As String
>
> Static reccount As Integer
> 'set line width
> Me.DrawWidth = 10
> 'create gridlines
> Me.Line (0, 0)-(0, 15000)
> Me.Line (3115, 0)-(3115, 15000)
> Me.Line (6780, 0)-(6780, 15000)
> Me.Line (8980, 0)-(8980, 15000)
> Me.Line (9540, 0)-(9540, 15000)
> Me.Line (11830, 0)-(11830, 15000)
> Me.Line (14300, 0)-(14300, 15000)
> Me.Line (15110, 0)-(15110, 15000)
>
> ' Defines the Text Fields used to designate Function and Activity (prefix
> for FunctionOrder and ActivityOrder
> Me.Text14 = "F-"
> Me.Text101 = "A-"
>
> ' Set default of visible
> Me.Text14.Visible = True
> Me.Function.Visible = True
> Me.FunctionOrder.Visible = True
> Me.Text101.Visible = True
> Me.ActivityOrder.Visible = True
> Me.Activity.Visible = True
>
> ' Set Visibility
> If Me.Function.OldValue <> strrptRIAFunction Then
> Me.Text14.Visible = True
> Me!FunctionOrder.Visible = True
> Me!Function.Visible = True
> End If
>
> If Me.Function.OldValue = strrptRIAFunction Then
> Me.Text14.Visible = False
> Me!FunctionOrder.Visible = False
> Me!Function.Visible = False
> End If
>
>
> If Me.Activity.OldValue <> strrptRIAActivity Then
> Me.Text101.Visible = True
> Me!ActivityOrder.Visible = True
> Me!Activity.Visible = True
> End If
>
> If Me.Activity.OldValue = strrptRIAActivity Then
> Me.Text101.Visible = False
> Me!ActivityOrder.Visible = False
> Me!Activity.Visible = False
> End If
>
>
> strrptRIAFunction = Nz(Me.Function.OldValue)
> strrptRIAActivity = Nz(Me.Activity.OldValue)
>
> End Sub
>
>
>

== 3 of 5 ==
Date: Fri, Nov 16 2007 12:23 pm
From: "John Spencer"

I realize that I should have said REPLACE all that code in the Detail format
section with ... instead of "Add Code"

By the way, do this all on a COPY of your report.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

"John Spencer" <spencer@chpdm.edu> wrote in message
news:OrSe8xIKIHA.4196@TK2MSFTNGP04.phx.gbl...
> Are you printing the five fields in a detail section? If so, you can add
> another control to determine if the fields should print.
>
> New text control
> Name: LineCounter
> Control Source: =1
> Running Sum: Overall
>
> Now add code to the Detail Section's format event
>
> If Me.LineCounter = 1 then
> Me.FunctionOrder.visible = True
> Me.Function.Visible = True
> Me.ActivityOrder.Visible = True
> Me.Activity.Visible = True
> Else
> Me.FunctionOrder.visible = False
> Me.Function.Visible = False
> Me.ActivityOrder.Visible = False
> Me.Activity.Visible = False
>
> End IF
>
> That will show the four controls for the first record and hide them for
> every other record in the report. If that is not what you want, then post
> back with a better description of what you do want.
> --
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> .
>
> "dsc2bjn" <dsc2bjn@discussions.microsoft.com> wrote in message
> news:0F0D6B6C-1361-422B-BD31-BC0EC5F46B79@microsoft.com...
>>I have a report which returns a series of records for which I wish to
>> suppress the duplicate values for a few fields. The "Hide Duplicate"
>> attribute will only hide the duplicates on a single page of the report.
>> I
>> which to hide all duplicates for like values throughout the report.
>>
>> My VB skills are weak (ok practically non-existant); however, I inherited
>> this DB from someone who's code kind of works, but not in all instances.
>>
>> Data Structure for the Report is:
>> FunctionOrder, Function, ActivityOrder, Activity, Risk.
>>
>> Each Function may contain mulitple Activities.
>> Each Activity may contain multiple Risk.
>>
>> I wish to supress the display for the FunctionOrder, Function,
>> ActivityOrder, and Activity for all but the first record.
>>
>> Below is the code I inherited, it kind of works. On some pages the
>> FunctionOrder, Function, ActivtyOrder, and Activity repeat. I am
>> resonably
>> sure it occurs when a new activity starts on a new page and the risks
>> associated to the Activity can not fit on the page.
>>
>> Any suggestions would be greatly appreciated.
>>
>> Code:
>> ---------------
>> Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
>> ' Declaring Function and pageno variables locally and as static
>> ' So that if another report is running at same time, it will not use them
>> ' as it might had they been declared publicly or with the same
>> ' name as in the other report.
>>
>> ' Note - this report has code which controls manually when and where the
>> ' "F-", the function order and the function print on the page.
>> ' To handle this manually or programmatically, the code gets somewhat
>> complicated.
>> ' One aspect handled is the suppression of this function information on
>> the
>> ' next page when a function begins on the previous page and continued to
>> the
>> next
>> ' page.
>> Static strrptRIAFunction As String
>> Static strrptRIAActivity As String
>>
>> Static reccount As Integer
>> 'set line width
>> Me.DrawWidth = 10
>> 'create gridlines
>> Me.Line (0, 0)-(0, 15000)
>> Me.Line (3115, 0)-(3115, 15000)
>> Me.Line (6780, 0)-(6780, 15000)
>> Me.Line (8980, 0)-(8980, 15000)
>> Me.Line (9540, 0)-(9540, 15000)
>> Me.Line (11830, 0)-(11830, 15000)
>> Me.Line (14300, 0)-(14300, 15000)
>> Me.Line (15110, 0)-(15110, 15000)
>>
>> ' Defines the Text Fields used to designate Function and Activity (prefix
>> for FunctionOrder and ActivityOrder
>> Me.Text14 = "F-"
>> Me.Text101 = "A-"
>>
>> ' Set default of visible
>> Me.Text14.Visible = True
>> Me.Function.Visible = True
>> Me.FunctionOrder.Visible = True
>> Me.Text101.Visible = True
>> Me.ActivityOrder.Visible = True
>> Me.Activity.Visible = True
>>
>> ' Set Visibility
>> If Me.Function.OldValue <> strrptRIAFunction Then
>> Me.Text14.Visible = True
>> Me!FunctionOrder.Visible = True
>> Me!Function.Visible = True
>> End If
>>
>> If Me.Function.OldValue = strrptRIAFunction Then
>> Me.Text14.Visible = False
>> Me!FunctionOrder.Visible = False
>> Me!Function.Visible = False
>> End If
>>
>>
>> If Me.Activity.OldValue <> strrptRIAActivity Then
>> Me.Text101.Visible = True
>> Me!ActivityOrder.Visible = True
>> Me!Activity.Visible = True
>> End If
>>
>> If Me.Activity.OldValue = strrptRIAActivity Then
>> Me.Text101.Visible = False
>> Me!ActivityOrder.Visible = False
>> Me!Activity.Visible = False
>> End If
>>
>>
>> strrptRIAFunction = Nz(Me.Function.OldValue)
>> strrptRIAActivity = Nz(Me.Activity.OldValue)
>>
>> End Sub
>>
>>
>>
>
>

== 4 of 5 ==
Date: Fri, Nov 16 2007 12:47 pm
From: dsc2bjn

Yes I am printing the fivel fields in the detail section. The Detail Section
is also where I call the Event Procedure.

I implemented what you suggested; however, it suppresses the values for all
but the first FunctionOrder, Function, ActivityOrder and Activity.

I modified what you suggested slightly and have ALMOST what I need.

By setting the "Running Sum" to "No". It produces the desired output for
everything I mentioned previously.

I do have two text fields used as a Prefix before the FunctionOrder and
ActivityOrder which I also need to set as Visible = False, when the
FuctionOrder and the ActivityOrder are the same as the previous record(s).

I take it from the code below, this does not work because these are Text
field and not numeric items which can be counted.

New Code:
----------
' Define Labels for FuctionOrder and ActivityOrder prefixes
Me.Text14 = "F-"
Me.Text101 = "A-"

'create gridlines
Me.Line (0, 0)-(0, 15000)
Me.Line (3115, 0)-(3115, 15000)
Me.Line (6780, 0)-(6780, 15000)
Me.Line (8980, 0)-(8980, 15000)
Me.Line (9540, 0)-(9540, 15000)
Me.Line (11830, 0)-(11830, 15000)
Me.Line (14300, 0)-(14300, 15000)
Me.Line (15110, 0)-(15110, 15000)

' Now add code to the Detail Section's format event
If Me.LineCounter = 1 Then
Me.Text14.Visible = True
Me.FunctionOrder.Visible = True
Me.Function.Visible = True
Me.Text101.Visible = True
Me.ActivityOrder.Visible = True
Me.Activity.Visible = True
Else
Me.Text14.Visible = False
Me.FunctionOrder.Visible = False
Me.Function.Visible = False
Me.Text101.Visible = False
Me.ActivityOrder.Visible = False
Me.Activity.Visible = False

End If
End Sub

"John Spencer" wrote:

> Are you printing the five fields in a detail section? If so, you can add
> another control to determine if the fields should print.
>
> New text control
> Name: LineCounter
> Control Source: =1
> Running Sum: Overall
>
> Now add code to the Detail Section's format event
>
> If Me.LineCounter = 1 then
> Me.FunctionOrder.visible = True
> Me.Function.Visible = True
> Me.ActivityOrder.Visible = True
> Me.Activity.Visible = True
> Else
> Me.FunctionOrder.visible = False
> Me.Function.Visible = False
> Me.ActivityOrder.Visible = False
> Me.Activity.Visible = False
>
> End IF
>
> That will show the four controls for the first record and hide them for
> every other record in the report. If that is not what you want, then post
> back with a better description of what you do want.
> --
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
>
> "dsc2bjn" <dsc2bjn@discussions.microsoft.com> wrote in message
> news:0F0D6B6C-1361-422B-BD31-BC0EC5F46B79@microsoft.com...
> >I have a report which returns a series of records for which I wish to
> > suppress the duplicate values for a few fields. The "Hide Duplicate"
> > attribute will only hide the duplicates on a single page of the report. I
> > which to hide all duplicates for like values throughout the report.
> >
> > My VB skills are weak (ok practically non-existant); however, I inherited
> > this DB from someone who's code kind of works, but not in all instances.
> >
> > Data Structure for the Report is:
> > FunctionOrder, Function, ActivityOrder, Activity, Risk.
> >
> > Each Function may contain mulitple Activities.
> > Each Activity may contain multiple Risk.
> >
> > I wish to supress the display for the FunctionOrder, Function,
> > ActivityOrder, and Activity for all but the first record.
> >
> > Below is the code I inherited, it kind of works. On some pages the
> > FunctionOrder, Function, ActivtyOrder, and Activity repeat. I am
> > resonably
> > sure it occurs when a new activity starts on a new page and the risks
> > associated to the Activity can not fit on the page.
> >
> > Any suggestions would be greatly appreciated.
> >
> > Code:
> > ---------------
> > Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
> > ' Declaring Function and pageno variables locally and as static
> > ' So that if another report is running at same time, it will not use them
> > ' as it might had they been declared publicly or with the same
> > ' name as in the other report.
> >
> > ' Note - this report has code which controls manually when and where the
> > ' "F-", the function order and the function print on the page.
> > ' To handle this manually or programmatically, the code gets somewhat
> > complicated.
> > ' One aspect handled is the suppression of this function information on
> > the
> > ' next page when a function begins on the previous page and continued to
> > the
> > next
> > ' page.
> > Static strrptRIAFunction As String
> > Static strrptRIAActivity As String
> >
> > Static reccount As Integer
> > 'set line width
> > Me.DrawWidth = 10
> > 'create gridlines
> > Me.Line (0, 0)-(0, 15000)
> > Me.Line (3115, 0)-(3115, 15000)
> > Me.Line (6780, 0)-(6780, 15000)
> > Me.Line (8980, 0)-(8980, 15000)
> > Me.Line (9540, 0)-(9540, 15000)
> > Me.Line (11830, 0)-(11830, 15000)
> > Me.Line (14300, 0)-(14300, 15000)
> > Me.Line (15110, 0)-(15110, 15000)
> >
> > ' Defines the Text Fields used to designate Function and Activity (prefix
> > for FunctionOrder and ActivityOrder
> > Me.Text14 = "F-"
> > Me.Text101 = "A-"
> >
> > ' Set default of visible
> > Me.Text14.Visible = True
> > Me.Function.Visible = True
> > Me.FunctionOrder.Visible = True
> > Me.Text101.Visible = True
> > Me.ActivityOrder.Visible = True
> > Me.Activity.Visible = True
> >
> > ' Set Visibility
> > If Me.Function.OldValue <> strrptRIAFunction Then
> > Me.Text14.Visible = True
> > Me!FunctionOrder.Visible = True
> > Me!Function.Visible = True
> > End If
> >
> > If Me.Function.OldValue = strrptRIAFunction Then
> > Me.Text14.Visible = False
> > Me!FunctionOrder.Visible = False
> > Me!Function.Visible = False
> > End If
> >
> >
> > If Me.Activity.OldValue <> strrptRIAActivity Then
> > Me.Text101.Visible = True
> > Me!ActivityOrder.Visible = True
> > Me!Activity.Visible = True
> > End If
> >
> > If Me.Activity.OldValue = strrptRIAActivity Then
> > Me.Text101.Visible = False
> > Me!ActivityOrder.Visible = False
> > Me!Activity.Visible = False
> > End If
> >
> >
> > strrptRIAFunction = Nz(Me.Function.OldValue)
> > strrptRIAActivity = Nz(Me.Activity.OldValue)
> >
> > End Sub
> >
> >
> >
>
>
>

== 5 of 5 ==
Date: Fri, Nov 16 2007 12:57 pm
From: dsc2bjn

I knew to replace and not add, but thanks.

Given what I said in the other reply regarding the "Running Sum" is set to
"No".

I did just realize that the text for the Function and Activity do not repeat
on each line, but the FunctionOrder and ActivityOrder do.

Example of what I want the data to look like:

F-1 F1Text A-1 A1Text Risk
Risk
Risk
A-2 A2Text Risk
F-2 F2Text A-1 A1Text Risk
A-2 A2Text Risk
Risk
Risk
F-3 F3Text A-1 A1Text Risk

"John Spencer" wrote:

> I realize that I should have said REPLACE all that code in the Detail format
> section with ... instead of "Add Code"
>
> By the way, do this all on a COPY of your report.
>
>
>
> --
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
>
> "John Spencer" <spencer@chpdm.edu> wrote in message
> news:OrSe8xIKIHA.4196@TK2MSFTNGP04.phx.gbl...
> > Are you printing the five fields in a detail section? If so, you can add
> > another control to determine if the fields should print.
> >
> > New text control
> > Name: LineCounter
> > Control Source: =1
> > Running Sum: Overall
> >
> > Now add code to the Detail Section's format event
> >
> > If Me.LineCounter = 1 then
> > Me.FunctionOrder.visible = True
> > Me.Function.Visible = True
> > Me.ActivityOrder.Visible = True
> > Me.Activity.Visible = True
> > Else
> > Me.FunctionOrder.visible = False
> > Me.Function.Visible = False
> > Me.ActivityOrder.Visible = False
> > Me.Activity.Visible = False
> >
> > End IF
> >
> > That will show the four controls for the first record and hide them for
> > every other record in the report. If that is not what you want, then post
> > back with a better description of what you do want.
> > --
> > John Spencer
> > Access MVP 2002-2005, 2007
> > Center for Health Program Development and Management
> > University of Maryland Baltimore County
> > .
> >
> > "dsc2bjn" <dsc2bjn@discussions.microsoft.com> wrote in message
> > news:0F0D6B6C-1361-422B-BD31-BC0EC5F46B79@microsoft.com...
> >>I have a report which returns a series of records for which I wish to
> >> suppress the duplicate values for a few fields. The "Hide Duplicate"
> >> attribute will only hide the duplicates on a single page of the report.
> >> I
> >> which to hide all duplicates for like values throughout the report.
> >>
> >> My VB skills are weak (ok practically non-existant); however, I inherited
> >> this DB from someone who's code kind of works, but not in all instances.
> >>
> >> Data Structure for the Report is:
> >> FunctionOrder, Function, ActivityOrder, Activity, Risk.
> >>
> >> Each Function may contain mulitple Activities.
> >> Each Activity may contain multiple Risk.
> >>
> >> I wish to supress the display for the FunctionOrder, Function,
> >> ActivityOrder, and Activity for all but the first record.
> >>
> >> Below is the code I inherited, it kind of works. On some pages the
> >> FunctionOrder, Function, ActivtyOrder, and Activity repeat. I am
> >> resonably
> >> sure it occurs when a new activity starts on a new page and the risks
> >> associated to the Activity can not fit on the page.
> >>
> >> Any suggestions would be greatly appreciated.
> >>
> >> Code:
> >> ---------------
> >> Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
> >> ' Declaring Function and pageno variables locally and as static
> >> ' So that if another report is running at same time, it will not use them
> >> ' as it might had they been declared publicly or with the same
> >> ' name as in the other report.
> >>
> >> ' Note - this report has code which controls manually when and where the
> >> ' "F-", the function order and the function print on the page.
> >> ' To handle this manually or programmatically, the code gets somewhat
> >> complicated.
> >> ' One aspect handled is the suppression of this function information on
> >> the
> >> ' next page when a function begins on the previous page and continued to
> >> the
> >> next
> >> ' page.
> >> Static strrptRIAFunction As String
> >> Static strrptRIAActivity As String
> >>
> >> Static reccount As Integer
> >> 'set line width
> >> Me.DrawWidth = 10
> >> 'create gridlines
> >> Me.Line (0, 0)-(0, 15000)
> >> Me.Line (3115, 0)-(3115, 15000)
> >> Me.Line (6780, 0)-(6780, 15000)
> >> Me.Line (8980, 0)-(8980, 15000)
> >> Me.Line (9540, 0)-(9540, 15000)
> >> Me.Line (11830, 0)-(11830, 15000)
> >> Me.Line (14300, 0)-(14300, 15000)
> >> Me.Line (15110, 0)-(15110, 15000)
> >>
> >> ' Defines the Text Fields used to designate Function and Activity (prefix
> >> for FunctionOrder and ActivityOrder
> >> Me.Text14 = "F-"
> >> Me.Text101 = "A-"
> >>
> >> ' Set default of visible
> >> Me.Text14.Visible = True
> >> Me.Function.Visible = True
> >> Me.FunctionOrder.Visible = True
> >> Me.Text101.Visible = True
> >> Me.ActivityOrder.Visible = True
> >> Me.Activity.Visible = True
> >>
> >> ' Set Visibility
> >> If Me.Function.OldValue <> strrptRIAFunction Then
> >> Me.Text14.Visible = True
> >> Me!FunctionOrder.Visible = True
> >> Me!Function.Visible = True
> >> End If
> >>
> >> If Me.Function.OldValue = strrptRIAFunction Then
> >> Me.Text14.Visible = False
> >> Me!FunctionOrder.Visible = False
> >> Me!Function.Visible = False
> >> End If
> >>
> >>
> >> If Me.Activity.OldValue <> strrptRIAActivity Then
> >> Me.Text101.Visible = True
> >> Me!ActivityOrder.Visible = True
> >> Me!Activity.Visible = True
> >> End If
> >>
> >> If Me.Activity.OldValue = strrptRIAActivity Then
> >> Me.Text101.Visible = False
> >> Me!ActivityOrder.Visible = False
> >> Me!Activity.Visible = False
> >> End If
> >>
> >>
> >> strrptRIAFunction = Nz(Me.Function.OldValue)
> >> strrptRIAActivity = Nz(Me.Activity.OldValue)
> >>
> >> End Sub
> >>
> >>
> >>
> >
> >
>
>
>

==============================================================================
TOPIC: Help topic not available in Access 2000
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/4b70feb57b484882?hl=en
==============================================================================

== 1 of 1 ==
Date: Fri, Nov 16 2007 12:00 pm
From: Del

I have come across several Help topics that are not available. In MS VB Help
I bring up RunSQL Method. When I click on the Drop Table link it tells me
that the Help topic is not available. I follow the instructions for
installing Help & VBA Help and it is still not available.

I have the same version of MS Office at home and these topics are available
there. What can I do to get them here at work?

--
Thank you,
Del

==============================================================================
TOPIC: Getting Procedure Name
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/037f7a39f60699dc?hl=en
==============================================================================

== 1 of 2 ==
Date: Fri, Nov 16 2007 1:31 pm
From: "James A. Fortune"

John Spencer wrote:
> You do realize that you just posted copyrighted material.
>
> '====================================================
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> '====================================================

See:

U.S. Copyright Office - Fair Use:

http://www.copyright.gov/fls/fl102.html

I would argue that the effect of the use here would be to increase the
potential market for the book rather than decrease it. Then again,
after seeing the amount of code posted, purportedly to get the name of
the procedure, it might have a negative affect :-).

James A. Fortune
MPAPoster@FortuneJames.com

== 2 of 2 ==
Date: Fri, Nov 16 2007 2:19 pm
From: Arch

On Fri, 16 Nov 2007 16:31:55 -0500, "James A. Fortune"
<MPAPoster@FortuneJames.com> wrote:

>John Spencer wrote:
>> You do realize that you just posted copyrighted material.
>>
>> '====================================================
>> John Spencer
>> Access MVP 2002-2005, 2007
>> Center for Health Program Development and Management
>> University of Maryland Baltimore County
>> '====================================================
>
>See:
>
>U.S. Copyright Office - Fair Use:
>
>http://www.copyright.gov/fls/fl102.html
>
>I would argue that the effect of the use here would be to increase the
>potential market for the book rather than decrease it. Then again,
>after seeing the amount of code posted, purportedly to get the name of
>the procedure, it might have a negative affect :-).
>
>James A. Fortune
>MPAPoster@FortuneJames.com

I suspect that if any of those guys (Litwin, Getz, or Gilbert) were
approached, they would readily grant permission to post that function.
I can't agree, however, that this falls under "Fair Use" guidelines. I
think it is rather blatant copyright violation.

My 2 cents worth,
Arch

==============================================================================
TOPIC: recordset?
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/0632845a2ccc9f82?hl=en
==============================================================================

== 1 of 1 ==
Date: Fri, Nov 16 2007 1:48 pm
From: Roger Converse

Not sure, but I think this should work:

sqlStr = "SELECT * FROM dbo_LaserMachineMaintLog WHERE [LaserMachine] = " &
Me.cboLaserMachine & " AND [Hours] = '" & Me.cboHours & "';"

Set rs = db.OpenRecordset(sqlStr, dbOpenDynaset, dbSeeChanges)

"Russ Hromyko" wrote:

> Can anyone tell me why this is happening, when I use an actual number, 47731
> for the [Hours] everything works, but when I use Me.cboHours I get an error
> message? What am I missing and how do I make Me.cboHours work? When I hit
> debug on the error message it points to the recordset (Set rs = ). Below
> the dotted line is not part of the code, it's the code I want to work.
>
>
> Private Sub cboHours_AfterUpdate()
> Dim sqlStr As String
> Dim db As DAO.Database
> Dim rs As DAO.Recordset
>
> Set db = CurrentDb
>
> sqlStr = "SELECT * FROM dbo_LaserMachineMaintLog WHERE [Laser Machine] =
> " & _
> "'" & Me.cboLaserMachine & "' " & "AND [Hours] = 47731"
>
> Set rs = db.OpenRecordset(sqlStr, dbOpenDynaset, dbSeeChanges)
>
> '---------------------------------------------------------------------------------------------------------------------------------------------
>
>
> sqlStr = "SELECT * FROM dbo_LaserMachineMaintLog WHERE [Laser Machine] = " &
> _
> "'" & Me.cboLaserMachine & "' " & "AND [Hours] = " & Me.cboHours
>
> Set rs = db.OpenRecordset(sqlStr, dbOpenDynaset, dbSeeChanges)
>
>
>

==============================================================================
TOPIC: Exporting Query to Excel - Multiple Files
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/4bcf072eba3c3922?hl=en
==============================================================================

== 1 of 1 ==
Date: Fri, Nov 16 2007 2:05 pm
From: Roger Converse

Here is something that I created that could work, if you don't mind making
temptables.

I create a temptable with all of our buyers and then in the export use a
similar recordset and loop to export and it works like a charm. You could
also add a delete
temp table to the loop after the export in order to save space. My export
is under a different sub, but if you were constantly exporting to the same
filepath or wanted to add an input box for the file path you could include it
all under one sub.

This is one of my first offers to assist, so if you don't mind, please
respond back if it was helpful or I should keep my ideas to myself.

Thanks!
Roger

Private Sub btnCreateICRIF_Files_Click()
Dim strSql As String, Booisit As Boolean, dbs As Database, rst As Recordset,
strBuyer As String

'Turn Warnings off

DoCmd.SetWarnings False
DoCmd.Hourglass (True)

Me.txtInvFillPlsWait.Visible = True
DoCmd.RepaintObject

'Remove old tables ICRIFPt1 & ICRIFPt2

Call Subs.TableisThere(Booisit, "ICRIFPt1")

If Booisit = True Then DoCmd.DeleteObject acTable, "ICRIFPt1"

Call Subs.TableisThere(Booisit, "ICRIFPt2")

If Booisit = True Then DoCmd.DeleteObject acTable, "ICRIFPt2"

'Create the ICRIF Part 1 table

strSql = "SELECT tblICRIF.Whse, tblICRIF.Product, tblICRIF.Description,
tblICRIF.Buyer, tblICRIF.[Vendor #], tblICRIF.[Vendor Name]," _
& " tblICRIF.[Replenishment Source], tblICRIF.Class, tblICRIF.[$ Ordered],
tblICRIF.[$ Shipped], tblICRIF.[$ Fill Pct]," _
& " tblICRIF.[Qty Ordered], tblICRIF.[Qty Shipped], tblICRIF.[Fill Pct],
tblICRIF.[# Lines], tblICRIF.[# Complete Lines]," _
& " tblICRIF.[Line Fill Pct] INTO ICRIFPt1 FROM tblICRIF WHERE
(((tblICRIF.Whse)<='TAMP')) ORDER BY tblICRIF.Whse;"

DoCmd.RunSQL (strSql)

Set dbs = CurrentDb()

strSql = "SELECT ICRIFPt1.Buyer FROM ICRIFPt1 GROUP BY ICRIFPt1.Buyer;"

Set rst = dbs.OpenRecordset(strSql)

Do Until rst.EOF = True

strBuyer = rst!Buyer

'Create the temp tables for export by buyer sorted in descending Fill
percent

strSql = "SELECT ICRIFPt1.Whse, ICRIFPt1.Product, ICRIFPt1.Description,
ICRIFPt1.Buyer, ICRIFPt1.[Vendor #]," _
& " ICRIFPt1.[Vendor Name], ICRIFPt1.[Replenishment Source],
ICRIFPt1.Class, ICRIFPt1.[$ Ordered], ICRIFPt1.[$ Shipped]," _
& " ICRIFPt1.[$ Fill Pct], ICRIFPt1.[Qty Ordered], ICRIFPt1.[Qty
Shipped], ICRIFPt1.[Fill Pct], ICRIFPt1.[# Lines]," _
& " ICRIFPt1.[# Complete Lines], ICRIFPt1.[Line Fill Pct] INTO temp" &
strBuyer & " FROM ICRIFPt1" _
& " WHERE (((ICRIFPt1.Buyer) = '" & strBuyer & "')) ORDER BY
ICRIFPt1.[Line Fill Pct];"

DoCmd.RunSQL (strSql)

rst.MoveNext

Loop

Me.txtInvFillPlsWait.Visible = False

'Turn Warnings on

DoCmd.SetWarnings True
DoCmd.Hourglass (False)

MsgBox "The ICRIF table has been created and is ready to export.", vbOKOnly,
msglbl

End Sub

"andrewbecks" wrote:

> Hello. I am looking for some help in exporting a query to Excel.
>
> Currently, I have a query that has four columns:
>
> -District_Manager
> -Store_Number
> -Fiscal_Week
> -Sales_Dollars
>
> There are approx. 200 different District Managers, and each District
> Manager has about 10 lines of data. What I would love to do is have a
> seperate excel file generated for each District Manager, that includes
> only his or her information. I'd like the Excel files to have the name
> of the District Manager.
>
> So, for District Manager Joe Example, an excel file named [Joe
> Example.xls] would be created that includes the above query but only
> includes row where the value for District Manager is Joe Example.
>
> Any assistance with this would be GREATLY appreciate.
>
> Andrew
>

==============================================================================
TOPIC: Is there an utility.wlib_AutoDial replacement?
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/988f324f3ac928d8?hl=en
==============================================================================

== 1 of 1 ==
Date: Fri, Nov 16 2007 2:46 pm
From: "David W. Fenton"

=?Utf-8?B?QWw=?= <Al@discussions.microsoft.com> wrote in
news:76E51032-61AD-4142-A344-7AA5EC5A2F35@microsoft.com:

> Is there a replacement for this so we don't have to make VBA
> Object library references to Utility.MDA?
>
> I have an application that crashes more often than not because of
> this Utility.

Without the reference, call:

Application.Run("Utility.wlib_AutoDial")

I just tested it in A2K3, with no reference. Obviously, you'd pass
it appropriate arguments.

--
David W. Fenton

http://www.dfenton.com/

usenet at dfenton dot com

http://www.dfenton.com/DFA/

==============================================================================
TOPIC: Importing text files into length specify parameters
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/b9e78415dc6e9557?hl=en
==============================================================================

== 1 of 1 ==
Date: Fri, Nov 16 2007 2:53 pm
From: N.Ordiers

Thanks, but the file is outside of MS Access. I need to copy expecific number
of characters from each lines into defined fields in a MS Access table. I
have try using a schema.in but iti does not work for me. If you have any
idea of how I can accomplish the task, I will welcome it

"Marshall Barton" wrote:

> N.Ordiers wrote:
> >I have forgotten how to import a string and tell how many character to
> >insert from the begging of the line into a specify column.
> >
> >I think the code looks like this "INSERT INTO TableName (Field1(char 5),
> >Field 2(char 10))
> >"FROM TextFile"
>
>
> Check the Mid function in Help to see if that's what you
> want.
>
> --
> Marsh
> MVP [MS Access]
>

==============================================================================

You received this message because you are subscribed to the Google Groups "microsoft.public.access.modulesdaovba"
group.

To post to this group, visit http://groups.google.com/group/microsoft.public.access.modulesdaovba?hl=en

To unsubscribe from this group, send email to microsoft.public.access.modulesdaovba-unsubscribe@googlegroups.com

To change the way you get mail from this group, visit:
http://groups.google.com/group/microsoft.public.access.modulesdaovba/subscribe?hl=en

To report abuse, send email explaining the problem to abuse@googlegroups.com

==============================================================================
Google Groups: http://groups.google.com?hl=en

Excel for Life

Jumat, 16 November 2007

25 new messages in 16 topics - digest

Tidak ada komentar:

Arsip Blog

Blogeroll

Mengenai Saya