Excel for Life: 25 new messages in 15 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: Stop Macro in VBA
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/6de566de020a1e44?hl=en
==============================================================================

== 1 of 1 ==
Date: Mon, Nov 12 2007 10:14 am
From: djf

Thanks for your input. I did something different that works but I think it is
the same concept as the one you described. Before running the macro, I check
for a condition, if it's true I run my script if not, I run a different macro.

"John Spencer" wrote:

> SInce you are running the function from a macro, I would suggest that you
> change the function to return true or false
>
> Function CheckField() as Boolean
> Dim tfContinue as Boolean
> tfContinue = True
> If IsNull(Forms![frmProjectNew]![Project]) Then
> Forms![frmProjectNew]![Project].BackColor = 3937500
> tfContinue = False
> End If
>
> If IsNull(Forms![frmProjectNew]![Center]) Then
> Forms![frmProjectNew]![Center].BackColor = 3937500
> tfContinue = False
> End If
>
> If tfContinue = false then
> MsgBox "The fields in red are required fields", _
> vbOKOnly, "Required Field"
> End if
>
> CheckField=tfContinue
> End Function
>
> Then add a condition to your macro
> Condition: CheckField() = False
> Action: Stop Macro
>
> If Checkfield returns False then the action will run Stopping the macro if
> Checkfield returns True then the Action (Stop Macro) will not run and the
> next line in the macro will run.
> --
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
>
> "djf" <djf@discussions.microsoft.com> wrote in message
> news:75005D6C-7385-4D4F-8490-F07FE89AEF1D@microsoft.com...
> >I run this module from a macro. It checks an input form for blank records.
> > The code works exactly how I want it to work, but I need to stop the macro
> > at
> > the end of this code. How can I do it?
> >
> > Function CheckField()
> >
> >
> > If IsNull(Forms![frmProjectNew]![Project]) Then
> >
> > Forms![frmProjectNew]![Project].BackColor = 3937500
> >
> > End If
> >
> > If IsNull(Forms![frmProjectNew]![Center]) Then
> >
> > Forms![frmProjectNew]![Center].BackColor = 3937500
> >
> > End If
> >
> > MsgBox "The fields in red are required fields", _
> > vbOKOnly, "Required Field"
> >
> >
> > End Function
> >
> >
>
>
>

==============================================================================
TOPIC: Using Combo Box to Populate a Form in Access
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/319dd98c1c211ec0?hl=en
==============================================================================

== 1 of 2 ==
Date: Mon, Nov 12 2007 10:15 am
From: JoeMammy

I am attempting to Populate a Form (Project Log Q Summary), based off a query
(Project Log Q Summary). The Query is based off a table (Project Log). I
would like to have the user select a combo box (cboAuditorName), choose the
correct ID, and have every record associated with that ID populate the form.
The Query can have many records with the same ID. The ID is based off a
table (Employee Table), which is connected to the Project Log table. Any
suggestions are appreciated.

Thanks,
--
Joe Mammy

== 2 of 2 ==
Date: Mon, Nov 12 2007 1:13 pm
From: Marshall Barton

JoeMammy wrote:

>I am attempting to Populate a Form (Project Log Q Summary), based off a query
>(Project Log Q Summary). The Query is based off a table (Project Log). I
>would like to have the user select a combo box (cboAuditorName), choose the
>correct ID, and have every record associated with that ID populate the form.
>The Query can have many records with the same ID. The ID is based off a
>table (Employee Table), which is connected to the Project Log table. Any
>suggestions are appreciated.

I think you can use the combo box's AfterUpdate event to set
the form's Filter property. The code would probably look
like:

Me.Filter = "ID=" & Me.cboAuditorName
Me.FilterOn = True

--
Marsh
MVP [MS Access]

==============================================================================
TOPIC: Populate dropdown box with information from other fields
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/02634369e516d5cd?hl=en
==============================================================================

== 1 of 2 ==
Date: Mon, Nov 12 2007 10:30 am
From: "Douglas J. Steele"

Try

strList = """" & Me.Writer1 & """;""" & Me.Writer2 & """;""" & Me.Writer3 &
""""

That's four double quotes in a row at the beginning and end, and three
double quotes on either side of the semi-colons.

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

"BZeyger" <BZeyger@discussions.microsoft.com> wrote in message
news:D484156B-225D-4525-845D-0C1A99ED3381@microsoft.com...
>I have changed the typo to semicolons and the issue is still happening. It
>is
> putting the first and last name on two seperate line.
>
> "Douglas J. Steele" wrote:
>
>> Slight typo. It should be semi-colons in both places:
>>
>> strList = Me.Writer1 & ";" & Me.Writer2 & ";" & Me.Writer3
>>
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "John Spencer" <spencer@chpdm.edu> wrote in message
>> news:O7bLFxUJIHA.748@TK2MSFTNGP04.phx.gbl...
>> > You could use the Current Event to set the comboboxes value list.
>> >
>> > Set the Row Source Type of your combobox:
>> > Row Source Type: Value List
>> >
>> > Private Sub Form_Current()
>> > Dim strList as string
>> > strList = Me.Writer1 & ";" & Me.Writer2 & ":" & Me.Writer3
>> > Me.YourCombobox.RowSource = strList
>> > End Sub
>> > --
>> > John Spencer
>> > Access MVP 2002-2005, 2007
>> > Center for Health Program Development and Management
>> > University of Maryland Baltimore County
>> > .
>> >
>> > "BZeyger" <BZeyger@discussions.microsoft.com> wrote in message
>> > news:15DACAE6-C691-40E8-9F0A-0DB2FF6AE148@microsoft.com...
>> >>I currently have a Access VBA database projetc. I have a form which
>> >>uses
>> >> information from a current record. There are fields that populate
>> >> perfectly.
>> >> I would like to add a dropdown box that will display the information
>> >> from
>> >> the
>> >> various fields.
>> >>
>> >> Example:
>> >>
>> >> The form conatins 3 non-visible fields : Writer1, Writer2, Writer3
>> >>
>> >> I would like the drop-down to display Writer1, Writer2, and Writer3 of
>> >> the
>> >> current record.
>> >>
>> >> Is there a way to have it displayed directly from the object's
>> >> property
>> >> window (Row Source) ?
>> >>
>> >
>> >
>>
>>
>>

== 2 of 2 ==
Date: Mon, Nov 12 2007 12:12 pm
From: "John Spencer"

Perhaps I don't understand what you are attempting to do.

If you are trying to get the three parts of ONE Name as choices in a
combobox then you probably need to use a query as the source for the
combobox where you have concatenated (added together) three fields
containing the name parts. You can combine the three fields in the
underlying query.

Field: FirstName & " " & MiddleName & " " & LastName

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

"BZeyger" <BZeyger@discussions.microsoft.com> wrote in message
news:5DF1EC96-BC0F-4E0C-A027-A2224FE9E207@microsoft.com...
> This code works however it does not show the desired results. Writer1,
> Writer2, and Writer3 fields contain comma's in them. For example: Smith,
> John
>
> The code provided does put the desired information into the drop-down box
> but it does not place the full name on the same line.
> What happens is: Smith
> John
> Doe
> Jane
>
> I would like it to show as : John, Smith
> Jane Doe
>
>
>
> "John Spencer" wrote:
>
>> You could use the Current Event to set the comboboxes value list.
>>
>> Set the Row Source Type of your combobox:
>> Row Source Type: Value List
>>
>> Private Sub Form_Current()
>> Dim strList as string
>> strList = Me.Writer1 & ";" & Me.Writer2 & ":" & Me.Writer3
>> Me.YourCombobox.RowSource = strList
>> End Sub
>> --
>> John Spencer
>> Access MVP 2002-2005, 2007
>> Center for Health Program Development and Management
>> University of Maryland Baltimore County
>> ..
>>
>> "BZeyger" <BZeyger@discussions.microsoft.com> wrote in message
>> news:15DACAE6-C691-40E8-9F0A-0DB2FF6AE148@microsoft.com...
>> >I currently have a Access VBA database projetc. I have a form which uses
>> > information from a current record. There are fields that populate
>> > perfectly.
>> > I would like to add a dropdown box that will display the information
>> > from
>> > the
>> > various fields.
>> >
>> > Example:
>> >
>> > The form conatins 3 non-visible fields : Writer1, Writer2, Writer3
>> >
>> > I would like the drop-down to display Writer1, Writer2, and Writer3 of
>> > the
>> > current record.
>> >
>> > Is there a way to have it displayed directly from the object's property
>> > window (Row Source) ?
>> >
>>
>>
>>

==============================================================================
TOPIC: MSAccess Query as recordset
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/13065fc90d37f3bf?hl=en
==============================================================================

== 1 of 1 ==
Date: Mon, Nov 12 2007 10:52 am
From: "Dirk Goldgar"

In news:C8726C99-3939-437C-B572-B8BF32F57659@microsoft.com,
Robert_L_Ross <RobertLRoss@discussions.microsoft.com> wrote:
> Quick follow up to my last post...adding the form's fields as
> parameters in the query doesn't fix the problem.

Did you try it like this?

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim OVERLAP_RECORDSET As DAO.Recordset
Dim NumRecords As Long
Dim SomeNumber As Long

Set db = CurrentDb
Set qdf = db.QueryDefs("AddLenderAgreements_FindOverlap")

With qdf
For Each prm In .Parameters
prm.Value = Eval(prm.Name)
Next prm
Set OVERLAP_RECORDSET = .OpenRecordset
End With

With OVERLAP_RECORDSET
If .EOF Then ' it's empty
NumRecords = 0
Else
' ... etc.
End If
.Close
End With

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

==============================================================================
TOPIC: DAO 3.6 Object Library moved down the list
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/a7355fa5aa8b92a6?hl=en
==============================================================================

== 1 of 3 ==
Date: Mon, Nov 12 2007 11:24 am
From: Trillium97

Hi all you smart people.
I have an Access 2003 database. We have a number of users that have Access
installed and we have a handful of users that are running this database using
the run-time deployment. This has been working fine since mid-summer. Last
week one of the run-time users was using the database for a while, then all
of the sudden it went belly up, and nobody could get in. The error was "type
mismatch" and they couldn't get out of it - ended up restoring the previous
night's backup. Then last week it happened again, by the same user. I did
some testing and found that the reference to DAO 3.6 Object Library was no
longer in the place it was originally - instead of at position 3 in the
reference list, it was at the bottom - still checked however. All I did was
move it back into place, re-compile, and we're running again.

I saw on another post to preface my recordset statements with DAO, and I
will do that, but I also would like to know why the reference got moved and
if there's something I should do about that.

Thanks in advance.

== 2 of 3 ==
Date: Mon, Nov 12 2007 11:56 am
From: "Douglas J. Steele"

I've not heard of the references reordering themselves. I'd check whether
that user was trying to fix things him/herself.

However, from what you've described, I'd hazard a guess that you haven't
split the application into a front-end (containing the queries, forms,
reports, macros and modules), linked to a back-end (containing the tables
and relations). Only the back-end should be on the server: each user should
have his/her own copy of the front-end, ideally on his/her hard drive. With
a set up like that, at least you wouldn't have to worry about one user
impacting the others (and you likely wouldn't have had to restore from
backup)

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

"Trillium97" <Trillium97@discussions.microsoft.com> wrote in message
news:CFC5ED1F-D913-47FE-81FC-E1C899038163@microsoft.com...
> Hi all you smart people.
> I have an Access 2003 database. We have a number of users that have
> Access
> installed and we have a handful of users that are running this database
> using
> the run-time deployment. This has been working fine since mid-summer.
> Last
> week one of the run-time users was using the database for a while, then
> all
> of the sudden it went belly up, and nobody could get in. The error was
> "type
> mismatch" and they couldn't get out of it - ended up restoring the
> previous
> night's backup. Then last week it happened again, by the same user. I
> did
> some testing and found that the reference to DAO 3.6 Object Library was no
> longer in the place it was originally - instead of at position 3 in the
> reference list, it was at the bottom - still checked however. All I did
> was
> move it back into place, re-compile, and we're running again.
>
> I saw on another post to preface my recordset statements with DAO, and I
> will do that, but I also would like to know why the reference got moved
> and
> if there's something I should do about that.
>
> Thanks in advance.

== 3 of 3 ==
Date: Mon, Nov 12 2007 12:32 pm
From: "Tony Toews [MVP]"

Trillium97 <Trillium97@discussions.microsoft.com> wrote:

>I have an Access 2003 database. We have a number of users that have Access
>installed and we have a handful of users that are running this database using
>the run-time deployment. This has been working fine since mid-summer. Last
>week one of the run-time users was using the database for a while, then all
>of the sudden it went belly up, and nobody could get in. The error was "type
>mismatch" and they couldn't get out of it - ended up restoring the previous
>night's backup. Then last week it happened again, by the same user. I did
>some testing and found that the reference to DAO 3.6 Object Library was no
>longer in the place it was originally - instead of at position 3 in the
>reference list, it was at the bottom - still checked however. All I did was
>move it back into place, re-compile, and we're running again.
>
>I saw on another post to preface my recordset statements with DAO, and I
>will do that, but I also would like to know why the reference got moved and
>if there's something I should do about that.

Are you using ADO? If not then remove the ADO reference completely
and that should take care of any ordering problems in the reference
list.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at

http://www.granite.ab.ca/accsmstr.htm

Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

==============================================================================
TOPIC: Emails sent through Exchange/Outlook slowed and require click
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/9cd5fbd3b4907b64?hl=en
==============================================================================

== 1 of 2 ==
Date: Mon, Nov 12 2007 11:40 am
From: Trillium97

Hi
I have an Access 2003 database that we use once a year to send individual
emails to all our volunteers (appx 1500) so they can verify their information
and hours they've volunteered. The access database uses the user's outlook.
This works fine when I test it from home because I'm not on Exchange at home,
but when we run it from the office, and through Exchange, Exchange (via
Outlook) adds a seven-second wait and the user has to click for each email
sent. This is on top of the Outlook warning about automated email, giving
her 10 minutes she can send per set. I can live with that Outlook warning
(although it would be wonderful to rid us of that as well), but the 7 second
wait and click per email is unacceptable. I have searched a lot and can't
find the answer - I'm probably using the wrong key words. Finally have to
post a question. Thanks in advance for any help anyone can provide.

== 2 of 2 ==
Date: Mon, Nov 12 2007 12:31 pm
From: "Tony Toews [MVP]"

Trillium97 <Trillium97@discussions.microsoft.com> wrote:

>Hi
>I have an Access 2003 database that we use once a year to send individual
>emails to all our volunteers (appx 1500) so they can verify their information
>and hours they've volunteered. The access database uses the user's outlook.
>This works fine when I test it from home because I'm not on Exchange at home,
>but when we run it from the office, and through Exchange, Exchange (via
>Outlook) adds a seven-second wait and the user has to click for each email
>sent. This is on top of the Outlook warning about automated email, giving
>her 10 minutes she can send per set.

See the Outlook specific links at the Access Email FAQ at my website.
http://www.granite.ab.ca/access/email/outlook.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at

http://www.granite.ab.ca/accsmstr.htm

Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

==============================================================================
TOPIC: Access 2007: Form timer events not firing and vba slowdowns
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/0a9362668b1242b9?hl=en
==============================================================================

== 1 of 1 ==
Date: Mon, Nov 12 2007 12:05 pm
From: "Tech_vs_Life"

I have an Access 2007 form timer event that (seemingly at random times)
fails to fire. I also have Access VBA code that slows down, sometimes
taking 4 or 5 minutes to do what it otherwise does in 5 seconds. As I
increase either the cpu speed or the number of cpus (e.g., from core to
quad), the problem gets much worse.

This is a problem both under 32-bit Vista and under 64-bit Windows Server
2008 RC0. It's been going on for a long time, and affects multiple
computers.

Any suggestions? I can't pinpoint any other software that could be causing
the problem. Right now, I'm left with the theory that it has something to
do with using SATA drives in a RAID configuration, so I'm testing changing
that. Are there some settings I should be changing to test this, or some
line of code to add? Thanks.

(apologies for failing to include these newsgroups on the last post.)

==============================================================================
TOPIC: Run-time access
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/a1ed64b038865c70?hl=en
==============================================================================

== 1 of 1 ==
Date: Mon, Nov 12 2007 12:14 pm
From: "John Spencer"

The Mac can run it if you use Bootcamp software and install windows and MS
Access or if you use Parallels software and install Windows and MS Access,
but no the Mac cannot run Access natively.

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

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:uZSYUOVJIHA.1204@TK2MSFTNGP03.phx.gbl...
> Perhaps if you're running a Windows emulator on the mac. Otherwise, no.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "dougsnyder" <dougsnyder@discussions.microsoft.com> wrote in message
> news:576B1C86-E225-4B68-9610-5C26A8512DC2@microsoft.com...
>>I have created a program and with the office developers extensions
>>packaged
>> the access run-time version. Will a mac user be able to use my program?
>
>

==============================================================================
TOPIC: Help
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/a2505d07a7ac8969?hl=en
==============================================================================

== 1 of 3 ==
Date: Mon, Nov 12 2007 12:18 pm
From: Jone

What code do I write when I want my main menu form to check for a file before
it opens if it cannot find the file it should not open ? for Ex. check if I
have the file C:\Windows\Sample.txt if it cannot find if it should not open

I got form somone this ***If Dir("C:\Windows\Sample.txt") <> vbNullString
Then** BUT IT DOSE NOT WORK!

== 2 of 3 ==
Date: Mon, Nov 12 2007 12:28 pm
From: "Douglas J. Steele"

What does that code do if it doesn't work?

Here's a slight variation that I prefer:

If Len(Dir("C:\Windows\Sample.txt")) > 0 Then
' the file exists
Else
' the file does not exist
End If

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

"Jone" <Jone@discussions.microsoft.com> wrote in message
news:1D111BA4-C992-4FBF-9E8F-0036FBAF5377@microsoft.com...
> What code do I write when I want my main menu form to check for a file
> before
> it opens if it cannot find the file it should not open ? for Ex. check if
> I
> have the file C:\Windows\Sample.txt if it cannot find if it should not
> open
>
> I got form somone this ***If Dir("C:\Windows\Sample.txt") <> vbNullString
> Then** BUT IT DOSE NOT WORK!
>

== 3 of 3 ==
Date: Mon, Nov 12 2007 3:18 pm
From: Jone

It worked thank you !

==============================================================================
TOPIC: Array to SQL question
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/0f3f3291c313d503?hl=en
==============================================================================

== 1 of 4 ==
Date: Mon, Nov 12 2007 12:50 pm
From: Angie

I have 41 tables. I would like to append the data in them to one table. I
don't work with arrays much, but would like to get around writing tons of
queries. Can someone take a look at this and tell me what I am doing wrong?

Public Sub FillArrayandAppend()
covs(0) = "COV000 MTD Application Summaryb"
covs(1) = "COV001 MTD Application Summaryb"
covs(2) = "COV002 MTD Application Summaryb"
covs(3) = "COV003 MTD Application Summaryb"
covs(4) = "COV004 MTD Application Summaryb"
covs(5) = "COV005 MTD Application Summaryb"
covs(6) = "COV006 MTD Application Summaryb"
covs(7) = "COV099 MTD Application Summaryb"
covs(8) = "COV100 MTD Application Summary"
covs(9) = "COV101 MTD Application Summary"
covs(10) = "COV104 MTD Application Summary"
covs(11) = "COV106 MTD Application Summary"
covs(12) = "COV201 MTD Application Summary"
covs(13) = "COV202 MTD Application Summary"
covs(14) = "COV211 MTD Application Summary"
covs(15) = "COV213 MTD Application Summary"
covs(16) = "COV301 MTD Application Summary"
covs(17) = "COV302 MTD Application Summary"
covs(18) = "COV303 MTD Application Summary"
covs(19) = "COV304 MTD Application Summary"
covs(20) = "COV600 MTD Application Summary"
covs(21) = "COV610 MTD Application Summary"
covs(22) = "COV611 MTD Application Summary"
covs(23) = "COV613 MTD Application Summary"
covs(24) = "COV614 MTD Application Summary"
covs(25) = "COV615 MTD Application Summary"
covs(26) = "COV617 MTD Application Summary"
covs(27) = "COV700 MTD Application Summary"
covs(28) = "COV701 MTD Application Summary"
covs(29) = "COV702 MTD Application Summary"
covs(30) = "COV703 MTD Application Summary"
covs(31) = "COV708 MTD Application Summary"
covs(32) = "COV709 MTD Application Summary"
covs(33) = "COV710 MTD Application Summary"
covs(34) = "COV798 MTD Application Summary"
covs(35) = "COV799 MTD Application Summary"
covs(36) = "COV801 MTD Application Summary"
covs(37) = "COV803 MTD Application Summary"
covs(38) = "COV804 MTD Application Summary"
covs(39) = "COV805 MTD Application Summary"
covs(40) = "COV990 MTD Application Summary"
covs(41) = "COV992 MTD Application Summary"

For x = 0 To 41
Dim db As DAO.Database
Dim rst As DAO.Recordset
strtable = covs(x)
Set db = CurrentDb
sqlstatement = "Select * From [" & strtable & "]"
Set rst = db.OpenRecordset(sqlstatement)

ssql = "INSERT INTO MonthlyDetail ( [Item Code], [Item Description],
Units, Price, Extension, AppCode, DateOfFile ) SELECT rst.F1, rst.F2, rst.F3,
rst.F4, rst.F5, Left(rst,6) AS AppCode, [Date of File] AS DateOfFile FROM rst
WHERE rst.F5 Is Not Null;"
DoCmd.RunSQL ssql
Set rst = Nothing
Set db = Nothing

End Sub

== 2 of 4 ==
Date: Mon, Nov 12 2007 1:03 pm
From: "Douglas J. Steele"

There's no need for the recordset:

Dim db As DAO.Database

Set db = CurrentDb

For x = 0 To 41
strtable = covs(x)
ssql = "INSERT INTO MonthlyDetail ( [Item Code], [Item Description], " &
_
"Units, Price, Extension, AppCode, DateOfFile ) " & _
"SELECT F1, F2, F3, F4, F5, '" & _
Left(strTable,6) & "' AS AppCode, " & _
"[Date of File] AS DateOfFile FROM " & strtable & _
" WHERE rst.F5 Is Not Null;"
db.Execute ssql, dbFailOnError
Next x

I'm not sure what [Date of File] is supposed to be.

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

"Angie" <Angie@discussions.microsoft.com> wrote in message
news:68ECE87E-F6AF-4DE4-8BBE-54F330E40E54@microsoft.com...
>I have 41 tables. I would like to append the data in them to one table. I
> don't work with arrays much, but would like to get around writing tons of
> queries. Can someone take a look at this and tell me what I am doing
> wrong?
>
> Public Sub FillArrayandAppend()
> covs(0) = "COV000 MTD Application Summaryb"
> covs(1) = "COV001 MTD Application Summaryb"
> covs(2) = "COV002 MTD Application Summaryb"
> covs(3) = "COV003 MTD Application Summaryb"
> covs(4) = "COV004 MTD Application Summaryb"
> covs(5) = "COV005 MTD Application Summaryb"
> covs(6) = "COV006 MTD Application Summaryb"
> covs(7) = "COV099 MTD Application Summaryb"
> covs(8) = "COV100 MTD Application Summary"
> covs(9) = "COV101 MTD Application Summary"
> covs(10) = "COV104 MTD Application Summary"
> covs(11) = "COV106 MTD Application Summary"
> covs(12) = "COV201 MTD Application Summary"
> covs(13) = "COV202 MTD Application Summary"
> covs(14) = "COV211 MTD Application Summary"
> covs(15) = "COV213 MTD Application Summary"
> covs(16) = "COV301 MTD Application Summary"
> covs(17) = "COV302 MTD Application Summary"
> covs(18) = "COV303 MTD Application Summary"
> covs(19) = "COV304 MTD Application Summary"
> covs(20) = "COV600 MTD Application Summary"
> covs(21) = "COV610 MTD Application Summary"
> covs(22) = "COV611 MTD Application Summary"
> covs(23) = "COV613 MTD Application Summary"
> covs(24) = "COV614 MTD Application Summary"
> covs(25) = "COV615 MTD Application Summary"
> covs(26) = "COV617 MTD Application Summary"
> covs(27) = "COV700 MTD Application Summary"
> covs(28) = "COV701 MTD Application Summary"
> covs(29) = "COV702 MTD Application Summary"
> covs(30) = "COV703 MTD Application Summary"
> covs(31) = "COV708 MTD Application Summary"
> covs(32) = "COV709 MTD Application Summary"
> covs(33) = "COV710 MTD Application Summary"
> covs(34) = "COV798 MTD Application Summary"
> covs(35) = "COV799 MTD Application Summary"
> covs(36) = "COV801 MTD Application Summary"
> covs(37) = "COV803 MTD Application Summary"
> covs(38) = "COV804 MTD Application Summary"
> covs(39) = "COV805 MTD Application Summary"
> covs(40) = "COV990 MTD Application Summary"
> covs(41) = "COV992 MTD Application Summary"
>
> For x = 0 To 41
> Dim db As DAO.Database
> Dim rst As DAO.Recordset
> strtable = covs(x)
> Set db = CurrentDb
> sqlstatement = "Select * From [" & strtable & "]"
> Set rst = db.OpenRecordset(sqlstatement)
>
>
> ssql = "INSERT INTO MonthlyDetail ( [Item Code], [Item Description],
> Units, Price, Extension, AppCode, DateOfFile ) SELECT rst.F1, rst.F2,
> rst.F3,
> rst.F4, rst.F5, Left(rst,6) AS AppCode, [Date of File] AS DateOfFile FROM
> rst
> WHERE rst.F5 Is Not Null;"
> DoCmd.RunSQL ssql
> Set rst = Nothing
> Set db = Nothing
>
> Next
>
> End Sub

== 3 of 4 ==
Date: Mon, Nov 12 2007 1:15 pm
From: Angie

Thank you so much! I get a 3131 error though "Syntax error in FROM clause"

"Douglas J. Steele" wrote:

> There's no need for the recordset:
>
> Dim db As DAO.Database
>
> Set db = CurrentDb
>
> For x = 0 To 41
> strtable = covs(x)
> ssql = "INSERT INTO MonthlyDetail ( [Item Code], [Item Description], " &
> _
> "Units, Price, Extension, AppCode, DateOfFile ) " & _
> "SELECT F1, F2, F3, F4, F5, '" & _
> Left(strTable,6) & "' AS AppCode, " & _
> "[Date of File] AS DateOfFile FROM " & strtable & _
> " WHERE rst.F5 Is Not Null;"
> db.Execute ssql, dbFailOnError
> Next x
>
> I'm not sure what [Date of File] is supposed to be.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Angie" <Angie@discussions.microsoft.com> wrote in message
> news:68ECE87E-F6AF-4DE4-8BBE-54F330E40E54@microsoft.com...
> >I have 41 tables. I would like to append the data in them to one table. I
> > don't work with arrays much, but would like to get around writing tons of
> > queries. Can someone take a look at this and tell me what I am doing
> > wrong?
> >
> > Public Sub FillArrayandAppend()
> > covs(0) = "COV000 MTD Application Summaryb"
> > covs(1) = "COV001 MTD Application Summaryb"
> > covs(2) = "COV002 MTD Application Summaryb"
> > covs(3) = "COV003 MTD Application Summaryb"
> > covs(4) = "COV004 MTD Application Summaryb"
> > covs(5) = "COV005 MTD Application Summaryb"
> > covs(6) = "COV006 MTD Application Summaryb"
> > covs(7) = "COV099 MTD Application Summaryb"
> > covs(8) = "COV100 MTD Application Summary"
> > covs(9) = "COV101 MTD Application Summary"
> > covs(10) = "COV104 MTD Application Summary"
> > covs(11) = "COV106 MTD Application Summary"
> > covs(12) = "COV201 MTD Application Summary"
> > covs(13) = "COV202 MTD Application Summary"
> > covs(14) = "COV211 MTD Application Summary"
> > covs(15) = "COV213 MTD Application Summary"
> > covs(16) = "COV301 MTD Application Summary"
> > covs(17) = "COV302 MTD Application Summary"
> > covs(18) = "COV303 MTD Application Summary"
> > covs(19) = "COV304 MTD Application Summary"
> > covs(20) = "COV600 MTD Application Summary"
> > covs(21) = "COV610 MTD Application Summary"
> > covs(22) = "COV611 MTD Application Summary"
> > covs(23) = "COV613 MTD Application Summary"
> > covs(24) = "COV614 MTD Application Summary"
> > covs(25) = "COV615 MTD Application Summary"
> > covs(26) = "COV617 MTD Application Summary"
> > covs(27) = "COV700 MTD Application Summary"
> > covs(28) = "COV701 MTD Application Summary"
> > covs(29) = "COV702 MTD Application Summary"
> > covs(30) = "COV703 MTD Application Summary"
> > covs(31) = "COV708 MTD Application Summary"
> > covs(32) = "COV709 MTD Application Summary"
> > covs(33) = "COV710 MTD Application Summary"
> > covs(34) = "COV798 MTD Application Summary"
> > covs(35) = "COV799 MTD Application Summary"
> > covs(36) = "COV801 MTD Application Summary"
> > covs(37) = "COV803 MTD Application Summary"
> > covs(38) = "COV804 MTD Application Summary"
> > covs(39) = "COV805 MTD Application Summary"
> > covs(40) = "COV990 MTD Application Summary"
> > covs(41) = "COV992 MTD Application Summary"
> >
> > For x = 0 To 41
> > Dim db As DAO.Database
> > Dim rst As DAO.Recordset
> > strtable = covs(x)
> > Set db = CurrentDb
> > sqlstatement = "Select * From [" & strtable & "]"
> > Set rst = db.OpenRecordset(sqlstatement)
> >
> >
> > ssql = "INSERT INTO MonthlyDetail ( [Item Code], [Item Description],
> > Units, Price, Extension, AppCode, DateOfFile ) SELECT rst.F1, rst.F2,
> > rst.F3,
> > rst.F4, rst.F5, Left(rst,6) AS AppCode, [Date of File] AS DateOfFile FROM
> > rst
> > WHERE rst.F5 Is Not Null;"
> > DoCmd.RunSQL ssql
> > Set rst = Nothing
> > Set db = Nothing
> >
> > Next
> >
> > End Sub
>
>
>

== 4 of 4 ==
Date: Mon, Nov 12 2007 3:46 pm
From: John Spencer

Douglas Steele missed that your table names contains spaces and
therefore requires square brackets to be placed around the name of the
table(s).

ssql = "INSERT INTO MonthlyDetail " & _
( [Item Code], [Item Description], " & _
"Units, Price, Extension, AppCode, DateOfFile ) " & _
"SELECT F1, F2, F3, F4, F5, '" & _
Left(strTable,6) & "' AS AppCode, " & _
"[Date of File] AS DateOfFile FROM [" & strtable & _
"] WHERE rst.F5 Is Not Null;"

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

Angie wrote:
> Thank you so much! I get a 3131 error though "Syntax error in FROM clause"
>
> "Douglas J. Steele" wrote:
>
>> There's no need for the recordset:
>>
>> Dim db As DAO.Database
>>
>> Set db = CurrentDb
>>
>> For x = 0 To 41
>> strtable = covs(x)
>> ssql = "INSERT INTO MonthlyDetail ( [Item Code], [Item Description], " &
>> _
>> "Units, Price, Extension, AppCode, DateOfFile ) " & _
>> "SELECT F1, F2, F3, F4, F5, '" & _
>> Left(strTable,6) & "' AS AppCode, " & _
>> "[Date of File] AS DateOfFile FROM " & strtable & _
>> " WHERE rst.F5 Is Not Null;"
>> db.Execute ssql, dbFailOnError
>> Next x
>>
>> I'm not sure what [Date of File] is supposed to be.
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "Angie" <Angie@discussions.microsoft.com> wrote in message
>> news:68ECE87E-F6AF-4DE4-8BBE-54F330E40E54@microsoft.com...
>>> I have 41 tables. I would like to append the data in them to one table. I
>>> don't work with arrays much, but would like to get around writing tons of
>>> queries. Can someone take a look at this and tell me what I am doing
>>> wrong?
>>>
>>> Public Sub FillArrayandAppend()
>>> covs(0) = "COV000 MTD Application Summaryb"
>>> covs(1) = "COV001 MTD Application Summaryb"
>>> covs(2) = "COV002 MTD Application Summaryb"
>>> covs(3) = "COV003 MTD Application Summaryb"
>>> covs(4) = "COV004 MTD Application Summaryb"
>>> covs(5) = "COV005 MTD Application Summaryb"
>>> covs(6) = "COV006 MTD Application Summaryb"
>>> covs(7) = "COV099 MTD Application Summaryb"
>>> covs(8) = "COV100 MTD Application Summary"
>>> covs(9) = "COV101 MTD Application Summary"
>>> covs(10) = "COV104 MTD Application Summary"
>>> covs(11) = "COV106 MTD Application Summary"
>>> covs(12) = "COV201 MTD Application Summary"
>>> covs(13) = "COV202 MTD Application Summary"
>>> covs(14) = "COV211 MTD Application Summary"
>>> covs(15) = "COV213 MTD Application Summary"
>>> covs(16) = "COV301 MTD Application Summary"
>>> covs(17) = "COV302 MTD Application Summary"
>>> covs(18) = "COV303 MTD Application Summary"
>>> covs(19) = "COV304 MTD Application Summary"
>>> covs(20) = "COV600 MTD Application Summary"
>>> covs(21) = "COV610 MTD Application Summary"
>>> covs(22) = "COV611 MTD Application Summary"
>>> covs(23) = "COV613 MTD Application Summary"
>>> covs(24) = "COV614 MTD Application Summary"
>>> covs(25) = "COV615 MTD Application Summary"
>>> covs(26) = "COV617 MTD Application Summary"
>>> covs(27) = "COV700 MTD Application Summary"
>>> covs(28) = "COV701 MTD Application Summary"
>>> covs(29) = "COV702 MTD Application Summary"
>>> covs(30) = "COV703 MTD Application Summary"
>>> covs(31) = "COV708 MTD Application Summary"
>>> covs(32) = "COV709 MTD Application Summary"
>>> covs(33) = "COV710 MTD Application Summary"
>>> covs(34) = "COV798 MTD Application Summary"
>>> covs(35) = "COV799 MTD Application Summary"
>>> covs(36) = "COV801 MTD Application Summary"
>>> covs(37) = "COV803 MTD Application Summary"
>>> covs(38) = "COV804 MTD Application Summary"
>>> covs(39) = "COV805 MTD Application Summary"
>>> covs(40) = "COV990 MTD Application Summary"
>>> covs(41) = "COV992 MTD Application Summary"
>>>
>>> For x = 0 To 41
>>> Dim db As DAO.Database
>>> Dim rst As DAO.Recordset
>>> strtable = covs(x)
>>> Set db = CurrentDb
>>> sqlstatement = "Select * From [" & strtable & "]"
>>> Set rst = db.OpenRecordset(sqlstatement)
>>>
>>>
>>> ssql = "INSERT INTO MonthlyDetail ( [Item Code], [Item Description],
>>> Units, Price, Extension, AppCode, DateOfFile ) SELECT rst.F1, rst.F2,
>>> rst.F3,
>>> rst.F4, rst.F5, Left(rst,6) AS AppCode, [Date of File] AS DateOfFile FROM
>>> rst
>>> WHERE rst.F5 Is Not Null;"
>>> DoCmd.RunSQL ssql
>>> Set rst = Nothing
>>> Set db = Nothing
>>>
>>> Next
>>>
>>> End Sub
>>
>>

==============================================================================
TOPIC: sql question
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/c3d3f54c3d6dff99?hl=en
==============================================================================

== 1 of 1 ==
Date: Mon, Nov 12 2007 2:57 pm
From: JT

The dates in a text field are all 6 characters long,e.g., 060107, 1019/07.
060505, etc. Some dates also appear as 000OCT, 000AUG, 000SEP, etc.

I'm trying use sql code to extract records from a database. The code is as
follows:

vSQL = "SELECT [CUST],[SOURCE],[DEPT] from [Current] WHERE [CUST] = '" &
CSTMR & "'AND ([DATE] <= '" & DateVar & "' AND mid([DATE],1,3) <> '" &
DateVar1 & "')"

CSTMR = "9999"
DateVar = "010107"
DateVar1 = "000"

I'm trying to extract the records that are older than 01/01/07 but am not
having much luck. I thought it would work if the DATE field was all 6
characters long but I'm not finding that to be true.

In Excel "080705" is less than "080707" but I can't extract these same
records with the sql statement. Any help would be appreciated. Thanks......

--
JT

==============================================================================
TOPIC: Background Processing
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/8090e9f12254eff6?hl=en
==============================================================================

== 1 of 1 ==
Date: Mon, Nov 12 2007 3:45 pm
From: y770

Thank You for the help.
I found how to save the password into the linked tables, so I do not need to
refresh links after all.
Sorry.

y770.

==============================================================================
TOPIC: Too few parameters, expected n when executing SQL from VBA
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/7d467667fa4fcfa1?hl=en
==============================================================================

== 1 of 1 ==
Date: Mon, Nov 12 2007 3:47 pm
From: "pietlinden@hotmail.com"

I read this article... apparently I didn't understand it as well as I
thought I did...
http://www.mvps.org/access/queries/qry0013.htm

What I'm doing... (in case it helps).

I have a seriously denormalized table, that looks like this:

CREATE TABLE Induction(
RecordID Autonumber,
ANEMIA Number,
NEUTROPENIA Number,
....
)

All the symptoms are in all caps (in case it matters). So I can
identify all those with a function.

because I have to normalize this mess, I'm inserting records into a
normalized table.

(PatientID, Symptom, Grade, CycleNumber, Phase)

so I created a single append query and then used Replace to modify the
SQL.

Here's the routine to do the replace...
Public Sub TestQuery(ByVal strFieldName As String)
Dim strSQL As String
Dim strNewSQL As String
Dim qdf As DAO.QueryDef
Dim qdfNew As DAO.QueryDef
Dim prm As DAO.Parameter

Set qdf = DBEngine(0)(0).QueryDefs("qapp Anemia")
strSQL = qdf.SQL

strNewSQL = Replace(qdf.SQL, "Anemia", strFieldName)
Debug.Print "Old SQL:"
Debug.Print qdf.SQL
Debug.Print
Debug.Print "New SQL"
Debug.Print strNewSQL
'CurrentDb.Execute strNewSQL, dbFailOnError
Set qdfNew = New QueryDef
qdfNew.SQL = strNewSQL

For Each prm In qdfNew.Parameters
prm.Value = Eval(prm.Name)
Next prm

qdf.Execute
Debug.Print qdf.RecordsAffected
End Sub

here's a sample output of the "TestQuery" function:
testquery "Allopecia"
Old SQL:
INSERT INTO HasToxicity ( [Record#], [Patient Initials], [Pt
Identifier], [Cycle#], Symptom, Grade )
SELECT [Toxicity Induction].[Record#], [Toxicity Induction].[Patient
Initials], [Toxicity Induction].[Pt Identifier], [Toxicity Induction].
[Cycle#], "Anemia" AS Symptom, [Toxicity Induction].ANEMIA
FROM [Toxicity Induction]
WHERE ((([Toxicity Induction].ANEMIA) Is Not Null));

'---routine returns this....
New SQL
INSERT INTO HasToxicity ( [Record#], [Patient Initials], [Pt
Identifier], [Cycle#], Symptom, Grade )
SELECT [Toxicity Induction].[Record#], [Toxicity Induction].[Patient
Initials], [Toxicity Induction].[Pt Identifier], [Toxicity Induction].
[Cycle#], "Allopecia" AS Symptom, [Toxicity Induction].Allopecia
FROM [Toxicity Induction]
WHERE ((([Toxicity Induction].Allopecia) Is Not Null));

when I try to run the query, I get "Too few parameters, expected
n" (see the article).

Even with the parameters supposedly getting evaluated, it still
returns the same error. What am I doing wrong?

It probably sounds like a lot of work to fix a few queries, but I have
lots of databases that are not normalized, so I'm trying to create a
routine that will automate most of the dirty work for me. so that's
why I'm doing all this in code...

Any ideas what I'm doing wrong? (Maybe I just need to take a walk or
something... )

Thanks for the help,

Pieter

==============================================================================
TOPIC: How to Build Form/Report Dynamically
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/2053542f5f047877?hl=en
==============================================================================

== 1 of 1 ==
Date: Mon, Nov 12 2007 3:57 pm
From: y770

I am trying to make BOM report with recursive function that returns array of
elements of the BOM.
Each element in a record corresponding to a few controls in a form/report.
This way I can create a form/report that displays my BOM in Form/Report
Design view.
Here comes my problem: I need to run this as onOpen/onPage procedure of the
Form/Report. At the same time as I am switching from Design View to Normal
View onOpen/onPage event kicks in again, messing things up. How do I add
controls without switching to Design View? I have seen a lot of advices to
use preset control and switch their visibility. I am not sure how to
implement this method as I have no idea how many records my BOM may return.
It maybe 1 or 2 or 50 or more.
I would hate to create 100 controls to display 1 or 2 of them only.
Is there a solution for me?

y700

==============================================================================
TOPIC: Get a file from the web
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/5a3eaa9e7f308da6?hl=en
==============================================================================

== 1 of 1 ==
Date: Mon, Nov 12 2007 5:01 pm
From: Humphrey

I have a CSV file that is updated overnight and stored on our website. Since
I cannot link to this file in Access I'd like to copy it locally using VBA
and then link to the local copy. This is so I can remain up to date without
having to manually move the file.
Can someone point me in the right direction? I've tried FileSystemObject
and ADODB but cannot get them to work. Searching the web shows the
.CopyRecord method for ADODB but this give me an error when I try it in my
2007 DB.

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

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

Senin, 12 November 2007

25 new messages in 15 topics - digest

Tidak ada komentar:

Arsip Blog

Blogeroll

Mengenai Saya