Excel for Life: 25 new messages in 12 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: Switchboard code
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/f45ca34ca93506bb?hl=en
==============================================================================

== 1 of 6 ==
Date: Thurs, Nov 8 2007 11:27 am
From: Gelpaks

Thanks so much for your reply :)
First I defined the command to open the query as: "Const conCmdOpenQuery =
10" which I added to the list of other constants listed. Then I added:
' Open a Query
Case conCmdOpenQuery
DoCmd.OpenQuery rs![Argument]

to the rs! SELECT CASE and changed the #'s in the switchboard table
accordingly. In the switchboard code, conErrDoCmdCancelled was defined:
"Const conErrDoCmdCancelled = 2501"

I just can't figure out why it doesn't "resume next" with the input queries,
but it will with reports, eventhough they too ask for input b/c they are
based on those very same queries! Is there another IF statement I can add
(maybe within the err handling IF stmt) that will avoid this err msg? Or
will that just give me more trouble than it's worth??

I hope I have explained this well enough. If not, just ask and I will try
to clarify. Thanks!

"fredg" wrote:

> On Thu, 8 Nov 2007 09:57:03 -0800, Gelpaks wrote:
>
> > I added code to the switchboard so I could open queries, and that works
> > great. Got the info from
> > http://www.accessmvp.com/JConrad/accessjunkie/switchboardfaq.html#query
> >
> > My problem is that if I cancel the input query, I get the msg: "There was
> > an error executing the command." In the switchboard code:
> >
> > HandleButtonClick_Err:
> > ' If the action was cancelled by the user for
> > ' some reason, don't display an error message.
> > ' Instead, resume on the next line.
> > If (Err = conErrDoCmdCancelled) Then
> > Resume Next
> > Else
> > MsgBox "There was an error executing the command.", vbCritical
> > Resume HandleButtonClick_Exit
> > End If
> >
> > How can I fix this so I don't get that err msg?
>
> One of the difficulties of using the built-in Access switchboard is
> that it is user unfriendly and not very functional.
>
> You need to trap the error 2501 in the sub routine's error handler.
>
> If Err = 2501 then
> Resume Exit Sub
> Else
> ....... Whatever other error handling you need
> Resume somewhere?
> End If
>
>
> I don't know where or how you added the Open Query to the code, so you
> may have to add an additional error handler and use that to trap the
> error.
>
> On Error GoTo Err_Handler2
> DoCmd.OpenQuery "QueryName"
>
> Exit_Sub:
> Exit Sub
> Err_Handler2:
> If Err = 2501 then
> Else
> MsgBox "Error #: " & Err.Number & " " & Err.Description
> End If
> Resume Exit_Sub
>
> Or you might be able to simply add the
> If Err = 2501 then
> line to the existing error handler.
>
> A better solution would be to scrap the switchboard and create your
> own, using an unbound form with command buttons. If you use the
> Command button wizard to add the buttons, Access will even write most
> of the code for you. You will have more control over the switchboard
> appearance, are not limited to 8 buttons, and maintenance will be
> simpler.
>
>
> --
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail
>

== 2 of 6 ==
Date: Thurs, Nov 8 2007 11:49 am
From: "Jeff Conrad [MSFT]"

Change this line:
If (Err = conErrDoCmdCancelled) Then

to:
If (Err = conErrDoCmdCancelled) Or Err.Number = 3270 Then

That should fix it.

--
Jeff Conrad - Access Junkie - MVP Alumni
SDET - XAS Services - Microsoft Corporation

Co-author - Microsoft Office Access 2007 Inside Out
Presenter - Microsoft Access 2007 Essentials
http://www.accessmvp.com/JConrad/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------

"Gelpaks" wrote in message:
news:C3B09D97-ECD1-4460-B1AA-18AAA92F4BE5@microsoft.com...

> Thanks so much for your reply :)
> First I defined the command to open the query as: "Const conCmdOpenQuery
> =
> 10" which I added to the list of other constants listed. Then I added:
> ' Open a Query
> Case conCmdOpenQuery
> DoCmd.OpenQuery rs![Argument]
>
> to the rs! SELECT CASE and changed the #'s in the switchboard table
> accordingly. In the switchboard code, conErrDoCmdCancelled was defined:
> "Const conErrDoCmdCancelled = 2501"
>
> I just can't figure out why it doesn't "resume next" with the input
> queries,
> but it will with reports, eventhough they too ask for input b/c they are
> based on those very same queries! Is there another IF statement I can add
> (maybe within the err handling IF stmt) that will avoid this err msg? Or
> will that just give me more trouble than it's worth??
>
> I hope I have explained this well enough. If not, just ask and I will try
> to clarify. Thanks!

== 3 of 6 ==
Date: Thurs, Nov 8 2007 12:03 pm
From: Gelpaks

You are a genius! :) Thanks so much that worked great. How did you know
which err code to put in?

"Jeff Conrad [MSFT]" wrote:

> Change this line:
> If (Err = conErrDoCmdCancelled) Then
>
> to:
> If (Err = conErrDoCmdCancelled) Or Err.Number = 3270 Then
>
> That should fix it.
>
> --
> Jeff Conrad - Access Junkie - MVP Alumni
> SDET - XAS Services - Microsoft Corporation
>
> Co-author - Microsoft Office Access 2007 Inside Out
> Presenter - Microsoft Access 2007 Essentials
> http://www.accessmvp.com/JConrad/accessjunkie.html
> Access 2007 Info: http://www.AccessJunkie.com
>
> ----------
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.mspx
> ----------
>
> "Gelpaks" wrote in message:
> news:C3B09D97-ECD1-4460-B1AA-18AAA92F4BE5@microsoft.com...
>
> > Thanks so much for your reply :)
> > First I defined the command to open the query as: "Const conCmdOpenQuery
> > =
> > 10" which I added to the list of other constants listed. Then I added:
> > ' Open a Query
> > Case conCmdOpenQuery
> > DoCmd.OpenQuery rs![Argument]
> >
> > to the rs! SELECT CASE and changed the #'s in the switchboard table
> > accordingly. In the switchboard code, conErrDoCmdCancelled was defined:
> > "Const conErrDoCmdCancelled = 2501"
> >
> > I just can't figure out why it doesn't "resume next" with the input
> > queries,
> > but it will with reports, eventhough they too ask for input b/c they are
> > based on those very same queries! Is there another IF statement I can add
> > (maybe within the err handling IF stmt) that will avoid this err msg? Or
> > will that just give me more trouble than it's worth??
> >
> > I hope I have explained this well enough. If not, just ask and I will try
> > to clarify. Thanks!
>
>
>

== 4 of 6 ==
Date: Thurs, Nov 8 2007 12:40 pm
From: "Jeff Conrad [MSFT]"

Genius?
No, but thanks for the compliment.
:-)

I just added this:
Msgbox Err.Number

before..
MsgBox "There was an error executing the command.", vbCritical

The error number then showed up in the Message Box.

--
Jeff Conrad - Access Junkie - MVP Alumni
SDET - XAS Services - Microsoft Corporation

Co-author - Microsoft Office Access 2007 Inside Out
Presenter - Microsoft Access 2007 Essentials
http://www.accessmvp.com/JConrad/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com

"Gelpaks" wrote in message:
news:590658F8-E65B-4DDD-B7F2-E1794F9ED12B@microsoft.com...

> You are a genius! :) Thanks so much that worked great. How did you know
> which err code to put in?

== 5 of 6 ==
Date: Thurs, Nov 8 2007 1:54 pm
From: Gelpaks

Excellent :) Thanks again for all your help!

"Jeff Conrad [MSFT]" wrote:

> Genius?
> No, but thanks for the compliment.
> :-)
>
> I just added this:
> Msgbox Err.Number
>
> before..
> MsgBox "There was an error executing the command.", vbCritical
>
> The error number then showed up in the Message Box.
>
> --
> Jeff Conrad - Access Junkie - MVP Alumni
> SDET - XAS Services - Microsoft Corporation
>
> Co-author - Microsoft Office Access 2007 Inside Out
> Presenter - Microsoft Access 2007 Essentials
> http://www.accessmvp.com/JConrad/accessjunkie.html
> Access 2007 Info: http://www.AccessJunkie.com
>
> ----------
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.mspx
> ----------
>
> "Gelpaks" wrote in message:
> news:590658F8-E65B-4DDD-B7F2-E1794F9ED12B@microsoft.com...
>
> > You are a genius! :) Thanks so much that worked great. How did you know
> > which err code to put in?
>
>
>

== 6 of 6 ==
Date: Thurs, Nov 8 2007 2:12 pm
From: "Jeff Conrad [MSFT]"

No problem, good luck with your project.

--
Jeff Conrad - Access Junkie - MVP Alumni
SDET - XAS Services - Microsoft Corporation

Co-author - Microsoft Office Access 2007 Inside Out
Presenter - Microsoft Access 2007 Essentials
http://www.accessmvp.com/JConrad/accessjunkie.html
Access 2007 Info: http://www.AccessJunkie.com

"Gelpaks" wrote in message:
news:7697B4AE-8181-4502-B3B5-7CFC3EF1739D@microsoft.com...

> Excellent :) Thanks again for all your help!

==============================================================================
TOPIC: drop table, import delimited text
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/83135dd9409f67e5?hl=en
==============================================================================

== 1 of 4 ==
Date: Thurs, Nov 8 2007 12:23 pm
From: mark

Hello.

I'm very familiar with VBA programming in Excel, but not in Access.

I need to:

1) Drop a given table, call it "tblData"
2) Import a delimited text file to recreate the table, call it "tblData.txt"
to table "tblData"

Can anyone help me with knowing how to do that in VBA in Access?

Alternatively, I could get the Access app to just connect to our Oracle
database, but I didn't find a path to do that in Access.

In Excel, it works from
Data-Import External Data - Import Data . From there, you choose 'New
Source' the first time, one of the option is Oracle, and it connects
nicely... even offers to put the data in your Pivot.

The object of this exercise is to run some data out of some Oracle tables,
into an Excel Pivot.

But, in some runs, the data will exceed Excel 2003's sheet row limit, and
we're not ready to have everybody use Excel 2007.

Thanks.

== 2 of 4 ==
Date: Thurs, Nov 8 2007 1:18 pm
From: mark

okay, I'm part way there... I searched and copied off of someone else... have
the drop part going fine.

now on to the others.

"mark" wrote:

> Hello.
>

== 3 of 4 ==
Date: Thurs, Nov 8 2007 1:43 pm
From: John Nurick

I don't recall a 64-character limit on Excel paths in Access 97 (there
was for dBASE paths). You can work round by using the equivalent short
path, most easily obtainable with this little bit of code posted
recently by Pieter Wijnen:

Private Declare Function APIGetShortPath Lib "kernel32" _
Alias "GetShortPathNameA" _
(ByVal lpszLongPath As String, _
ByVal lpszShortPath As String, _
ByVal cchBuffer As Long) As Long

Public Function GetShortPath(ByVal LongName As String) As String
Dim ShortName As String * 256

APIGetShortPath LongName & VBA.vbNullChar, ShortName,
VBA.Len(ShortName)
GetShortPath = VBA.Left(ShortName, VBA.InStr(ShortName,
VBA.vbNullChar) - 1)
End Function

Normally it's bad practice to drop a table and create a replacement as
part of ordinary operations. Instead, use a delete query to empty the
table and then import the new data to the same table.

On Thu, 8 Nov 2007 12:23:01 -0800, mark
<mark@discussions.microsoft.com> wrote:

>Hello.
>
>I'm very familiar with VBA programming in Excel, but not in Access.
>
>I need to:
>
>1) Drop a given table, call it "tblData"
>2) Import a delimited text file to recreate the table, call it "tblData.txt"
>to table "tblData"
>
>
>Can anyone help me with knowing how to do that in VBA in Access?
>
>Alternatively, I could get the Access app to just connect to our Oracle
>database, but I didn't find a path to do that in Access.
>
>In Excel, it works from
>Data-Import External Data - Import Data . From there, you choose 'New
>Source' the first time, one of the option is Oracle, and it connects
>nicely... even offers to put the data in your Pivot.
>
>The object of this exercise is to run some data out of some Oracle tables,
>into an Excel Pivot.
>
>But, in some runs, the data will exceed Excel 2003's sheet row limit, and
>we're not ready to have everybody use Excel 2007.
>
>Thanks.
>
--
John Nurick - Access MVP

== 4 of 4 ==
Date: Thurs, Nov 8 2007 2:11 pm
From: mark

okay, I'm using Access 2000, so I think you're telling me about a problem I
might have had, with the path info.

> Normally it's bad practice to drop a table and create a replacement as
> part of ordinary operations. Instead, use a delete query to empty the
> table and then import the new data to the same table.

Okay, thanks for mentioning that. I guess it makes sense, but like I said,
although I have a bunch of experience automating Excel, Access is new to me.

I'm having some data import errors, but I'll get around them. Excel handles
them fine. It's just type conversion stuff.

I'll look into how to do a delte query, and then work on the code to import
the new data, soon.

All I've ever done with Access code was a line or two stuff here or there,
and usually called that from an Access instance I initiated in Excel. A time
or two, I've called someone else's code that they already wrote in Access.

But that's it.

Can you offer me an explanation as to why Access won't let you record VBA
code, yet knows all about how to convert a 'Macro' to a 'Module' ? (If you
just say, "Microsoft wrote it that way.", okay, but I'm wondering if there's
a background logical reason?

Obviously, recorded code is junk as far as end product applications, but if
you're trying to do something you never did before, it can often give you a
clue on how one might approach that task.

Thanks for the help.

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

== 1 of 1 ==
Date: Thurs, Nov 8 2007 1:28 pm
From: J. Freed

That did the trick. Thanks!

"J. Freed" wrote:

> I've got a sub-form inside a form for which I want to change the recordset
> from within VBA. (The sub-form has no recordset for itself and I wish to use
> the same sub-form for several different queries, depending on which button is
> pressed).
>
> The line I currently am trying is
>
> Me!pos_in.Recordset = "pos_same" where pos_in is the subform and "pos_name"
> is the query I wish to use. The system rejects this line, but it likes
>
> Me!pos_in.SourceObject = "pos_in_fimat"
>
> which apparently changes the sub-form, not the recordset. How can I change
> the recordset associated with that sub-form? TIA.....

==============================================================================
TOPIC: Strange behaviour
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/fb504c18f8392244?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Nov 8 2007 1:59 pm
From: Humphrey

I've just migrated to 2007 and code that used to work is giving errors. The
code is generic,
Dim myItem As Outlook.MailItem
Dim myRecipient As Outlook.Recipient

Set myItem = Application.CreateItem(olMailItem)

but the software is now telling me can't find CreateItem. The exact error
is "Method or data memer not found". I assume its due to the overzealous
security in 2007, but come someone help me fix this??

==============================================================================
TOPIC: Disabling notifications
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/236be2edd30c2cc5?hl=en
==============================================================================

== 1 of 2 ==
Date: Thurs, Nov 8 2007 2:03 pm
From: "Conan Kelly"

Hello all,

help files say that "DoCmd.SetWarnings" will enable/disable "System
Messages".

What are all the messages that are considered "System Messages"?

In Access>Tools>Options...>Edit/Find tab>Confirm group, there are 3 items:
Record Changes, Document deletions, & Action queries.

Are all 3 of these considered System Messages? Are there any more?

Can I programatically set each one of these 3 individually? If so, what is
the code, please?

Thanks for any help anyone can provide,

Conan Kelly

== 2 of 2 ==
Date: Thurs, Nov 8 2007 5:10 pm
From: "Allen Browne"

It might be helpful to know what you are seeking to do.

In general, you want to leave SetWarnings turned on. Use the Exeucte method
to run your action queries (instead of RunSQL or OpenQuery.) More info:
Action queries: suppressing dialogs, while knowing results
at:

http://allenbrowne.com/ser-60.html

You also want to leave the confirmation options checked for most scenarios.
Otherwise objects are deleted from the Database window without confirmation.
Rather than turn the option off, it's much better to use Shift+Delete to
delete objects without confirmation.

Similarly, deleting records without confirmation is generally not a good
idea. A side-effect of doing that is that your form's BeforeDelConfirm and
AfterDelConfirm don't fire, so any checking or custom messages are also
suppressed if you do that.

If you do want to set the options programmatically, this list gives you the
option names to use:

http://msdn2.microsoft.com/en-us/library/Aa140014(office.10).aspx

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Conan Kelly" <CTBarbarinNOSPAM@msnNOSPAM.comNOSPAM> wrote in message
news:4JLYi.7203$if6.5978@bgtnsc05-news.ops.worldnet.att.net...
> Hello all,
>
> help files say that "DoCmd.SetWarnings" will enable/disable "System
> Messages".
>
> What are all the messages that are considered "System Messages"?
>
> In Access>Tools>Options...>Edit/Find tab>Confirm group, there are 3 items:
> Record Changes, Document deletions, & Action queries.
>
> Are all 3 of these considered System Messages? Are there any more?
>
> Can I programatically set each one of these 3 individually? If so, what
> is the code, please?
>
> Thanks for any help anyone can provide,
>
> Conan Kelly

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

== 1 of 1 ==
Date: Thurs, Nov 8 2007 2:29 pm
From: Robert_L_Ross

I can't figure out how to do this!

Here's my situation:
I have a form (form1) that displays records (from table1) associated with a
specific record on a different table (table2). In the header of form1 I have
fields where the user can 'build' a new record to add to table1.

I want to verify before adding the new record that it doesn't conflict with
an existing record on table1 (basically, that the start/end dates don't
overlap a record in the table1).

I have a query (query1) in Access that I can open to see if the existing new
record (from form1) has dates that overlap an existing record of the same
type in table1.

I want to have the button from form1 that you click to add the record first
open query1 and see if it returned any results (found an overlapping record).
If it does, I will show a warning and stop the process. If no overlapping
records are found, I'll kick off an append query to add the records, then
refresh my form to show the new record.

I can't for the life of me get the query to open as a recordset. I must
have gone through a dozen examples on this part of the group and none work.

Any ideas?

==============================================================================
TOPIC: Compressing/Zipping Files from Access
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/f5683b004bd0d744?hl=en
==============================================================================

== 1 of 3 ==
Date: Thurs, Nov 8 2007 2:32 pm
From: "Douglas J. Steele"

If I recall that code correctly, I don't think it's possible to use it
within Access. I'm pretty sure VBA doesn't expose the IStorage type, while
VB does.

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

"Alan" <Alan@discussions.microsoft.com> wrote in message
news:704984AD-3C2F-4441-99A9-52A5143E97FC@microsoft.com...
> Im sorry ... I may be somewhat thick here, however I am looking for a way
> to
> compress csv files after I have imported them to access... I have found
> and
> read the topics here and downloaded emandos code as suggested previously
> from
> http://www.mvps.org/emorcillo/download/vb6/xpzip.zip
>
> However this does not seem to work for me. I have imported the Class
> Module
> and tried to call the functions there in with No Joy ... Infact I get the
> error Sub or Function Not Defined when I call this from the immediate
> window
> ... Im not sure whether I am missing a library file or something else ...
> (it
> calls an IStorage type)
>
> Can anyone give me some pointers as the code would appear to do exactly
> what
> I require...
>
> Sorry if this is a stupid question, however I am becoming somewhat
> frustrated
> --
> Many Thanks
>
> Alan

== 2 of 3 ==
Date: Thurs, Nov 8 2007 3:03 pm
From: Alan

Cheers Douglas

Disappointing though as it looks to [provide exactly the functionality I am
looking for without introducing any DDLs

--
Many Thanks Once Again

Regards

Alan

"Douglas J. Steele" wrote:

> If I recall that code correctly, I don't think it's possible to use it
> within Access. I'm pretty sure VBA doesn't expose the IStorage type, while
> VB does.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Alan" <Alan@discussions.microsoft.com> wrote in message
> news:704984AD-3C2F-4441-99A9-52A5143E97FC@microsoft.com...
> > Im sorry ... I may be somewhat thick here, however I am looking for a way
> > to
> > compress csv files after I have imported them to access... I have found
> > and
> > read the topics here and downloaded emandos code as suggested previously
> > from
> > http://www.mvps.org/emorcillo/download/vb6/xpzip.zip
> >
> > However this does not seem to work for me. I have imported the Class
> > Module
> > and tried to call the functions there in with No Joy ... Infact I get the
> > error Sub or Function Not Defined when I call this from the immediate
> > window
> > ... Im not sure whether I am missing a library file or something else ...
> > (it
> > calls an IStorage type)
> >
> > Can anyone give me some pointers as the code would appear to do exactly
> > what
> > I require...
> >
> > Sorry if this is a stupid question, however I am becoming somewhat
> > frustrated
> > --
> > Many Thanks
> >
> > Alan
>
>
>

== 3 of 3 ==
Date: Thurs, Nov 8 2007 3:05 pm
From: "Stuart McCall"

You could give the free Zlib library a try:

http://www.zlib.net/

Known to work in vb/vba environments.

==============================================================================
TOPIC: create function problem
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/e070dedb6988e50f?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Nov 8 2007 4:52 pm
From: Andy

It works perfectly.
Thanks soooooooooo much, Tina.

"tina" wrote:

> okay, you want a custom function that returns a calculated value to each
> record in the query's recordset. forget opening recordsets and using
> querydefs in the function - you already have the data available within the
> query. just pass the data to the public function using arguments, calculate
> the value, and return it to the query, as
>
> Public Function dailysalary(ByVal sglStart As Single, _
> ByVal sglEnd As Single, ByVal sglSalary As Single) As Single
>
> If sglStart > 8 And sglStart < 13 And _
> sglEnd > 13 And sglEnd < 19 Then
> dailysalary = (13 - sglStart + sglEnd - 14) * sglSalary / 8
> ElseIf sglStart > 8 And sglEnd < 14 Then
> dailysalary = (sglEnd - sglStart) * sglSalary / 8
> ElseIf sglStart > 13 And sglEnd < 19 Then
> dailysalary = (sglEnd - sglStart) * sglSalary / 8
> ElseIf sglStart > 8 And sglStart < 13 And _
> sglEnd > 13 And sglEnd > 19 And sglEnd <= 24 Then
> dailysalary = (13 - sglStart + 18 - 14) * sglSalary / 8 +
> (sglEnd - 18) * sglSalary / 8 * 1.5
> ElseIf sglStart > 8 And sglStart < 13 And _
> sglEnd > 13 And sglEnd > 19 And sglEnd > 24 Then
> dailysalary = (13 - sglStart + 18 - 14) * sglSalary / 8 + (24 -
> 18) * sglSalary / 8 * 1.5 + (sglEnd - 24) * sglSalary / 8 * 2
> ElseIf sglStart > 13 And sglEnd > 18 And sglEnd <= 24 Then
> dailysalary = (18 - sglStart) * sglSalary / 8 + (sglEnd - 18) *
> sglSalary / 8 * 1.5
> ElseIf sglStart > 13 And sglEnd > 18 And sglEnd > 24 Then
> dailysalary = (18 - sglStart) * sglSalary / 8 + (24 - 18) *
> sglSalary / 8 * 1.5 +
> (sglEnd - 24) * sglSalary / 8 * 2
> Else
> dailysalary = 0
> End If
>
> End Function
>
> make each equation (dailysalary = "a math expression") run on one line,
> regardless of linewrap here.
>
> add the calculated field to the query's Design grid, as
>
> [daily total]: dailysalary([START TIME], [END TIME], [SALARY])
>
> hth
>
>
> "Andy" <Andy@discussions.microsoft.com> wrote in message
> news:6AEFCAF8-4D49-41FC-8CDF-B9C977712795@microsoft.com...
> > Sorry for the confusion! Tina,
> > Actually I would like the calculated filed [daily total] show on the
> query.
> >
> > For example :
> > [daily total] : dailysalary()
> >
> > It does not work as per the error message#3027,
> > I believe that I have something wrong, could you please correct me?
> >
> > Thanks again!
> >
> > "tina" wrote:
> >
> > > well, you could open a second recordset on the one table, and update
> that;
> > > it would probably involve doing a Find action to get to the appropriate
> > > record in the second recordset, within each loop of the first recordset.
> > >
> > > but, generally speaking, you shouldn't store calculated data in a table
> at
> > > all. it violates normalization rules, and always creates the potential
> for
> > > inconsistent - and therefore inaccurate - data. i'd strongly recommend
> > > against it, in most cases.
> > >
> > > hth
> > >
> > >
> > > "Andy" <Andy@discussions.microsoft.com> wrote in message
> > > news:D74BE274-F764-4A0A-9872-F1A5E72C11D0@microsoft.com...
> > > >
> > > > Have added the new line within the loop as below :
> > > > Do
> > > > .....
> > > > .....
> > > > rst![daily total] = mysalary 'add new line here
> > > >
> > > > rst.MoveNext
> > > > Loop Until rst.EOF
> > > > .........
> > > > .........
> > > >
> > > > But find the error message :
> > > > "error#3027, the query can not be updated"
> > > >
> > > > Have mentioned that the query is based on 2 tables, so I can not
> update
> > > the
> > > > table by this query, could you please advise and teach me what I can
> do?
> > > or
> > > > suggest any best solution?
> > > >
> > > > Thanks so much!
> > > >
> > > > "tina" wrote:
> > > >
> > > > > > I would like the value of dailysalary is going to equal the value
> of
> > > > > > mysalary *from EACH record in the recordset* show in the query
> "salary
> > > > > total"
> > > > >
> > > > > *how are you going to use each value* as you retrieve it from each
> > > record?
> > > > > if you're going to do something with it, then you must do it within
> the
> > > > > loop, NOT after the loop is completed.
> > > > >
> > > > > hth
> > > > >
> > > > >
> > > > > "Andy" <Andy@discussions.microsoft.com> wrote in message
> > > > > news:4D24F624-928F-4EF5-B75F-3FCD908C6CC4@microsoft.com...
> > > > > > Thanks, Tina.
> > > > > >
> > > > > > Please ingore the "[work details]" (I have removed) and exactly
> what
> > > you
> > > > > > said the value of dailysalary is always going to equal the value
> of
> > > > > mysalary
> > > > > > *from the last record in the recordset*.
> > > > > >
> > > > > > I would like the value of dailysalary is going to equal the value
> of
> > > > > > mysalary *from EACH record in the recordset* show in the query
> "salary
> > > > > total"
> > > > > > but I do not know what I can do?? Very appreciate for your expert
> > > > > advise!!!
> > > > > >
> > > > > > Thanks again!
> > > > > >
> > > > > > "tina" wrote:
> > > > > >
> > > > > > > well, "it does not work" doesn't give us much to go on. lacking
> any
> > > > > details
> > > > > > > of the problem you're seeing, my only comment is that you're
> looping
> > > > > through
> > > > > > > one or more records in the recordset and setting the value of
> > > mysalary
> > > > > on
> > > > > > > each pass - but not putting that value anywhere. so the value of
> > > > > dailysalary
> > > > > > > is always going to equal the value of mysalary *from the last
> record
> > > in
> > > > > the
> > > > > > > recordset*.
> > > > > > >
> > > > > > > beyond that, i'm lost on where you're getting rst![work
> > > details]![START
> > > > > > > TIME]. presumably [START TIME] is a field in query "salary
> total",
> > > which
> > > > > is
> > > > > > > the query your recordset is based on. but what is [work details]
> > > > > referring
> > > > > > > to?
> > > > > > >
> > > > > > > hth
> > > > > > >
> > > > > > >
> > > > > > > "Andy" <Andy@discussions.microsoft.com> wrote in message
> > > > > > > news:F5A0B87A-083B-46EF-B45C-96C71D2E8E1C@microsoft.com...
> > > > > > > > Hi there,
> > > > > > > >
> > > > > > > > I have a query "salary total"(2 tables) , I would like to
> create a
> > > > > > > function
> > > > > > > > to calculate the salary but it does not work, code as follows.
> > > > > Appreciate
> > > > > > > for
> > > > > > > > your expert help!
> > > > > > > >
> > > > > > > > Function dailysalary()
> > > > > > > >
> > > > > > > > Dim mysalary As Single
> > > > > > > > Dim db As DAO.Database
> > > > > > > > Dim rst As DAO.Recordset
> > > > > > > > Dim qdf As DAO.QueryDef
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > Set db = CurrentDb
> > > > > > > > Set qdf = db.QueryDefs("salary total")
> > > > > > > > Set rst = qdf.OpenRecordset
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > Do
> > > > > > > >
> > > > > > > > If rst![work details]![START TIME] > 8 And rst![work
> > > details]![START
> > > > > TIME]
> > > > > > > <
> > > > > > > > 13 And rst![work details]![END TIME] > 13 And rst![work
> > > details]![END
> > > > > > > TIME] <
> > > > > > > > 19 Then
> > > > > > > > mysalary = (13 - rst![work details]![START TIME] + rst![work
> > > > > details]![END
> > > > > > > > TIME] - 14) * rst![work details]![SALARY] / 8
> > > > > > > >
> > > > > > > > ElseIf rst![work details]![START TIME] > 8 And rst![work
> > > details]![END
> > > > > > > TIME]
> > > > > > > > < 14 Then
> > > > > > > > mysalary = (rst![work details]![END TIME] - rst![work
> > > details]![START
> > > > > > > TIME])
> > > > > > > > * rst![work details]![SALARY] / 8
> > > > > > > >
> > > > > > > > ElseIf rst![work details]![START TIME] > 13 And rst![work
> > > > > details]![END
> > > > > > > > TIME] < 19 Then
> > > > > > > > mysalary = (rst![work details]![END TIME] - rst![work
> > > details]![START
> > > > > > > TIME])
> > > > > > > > * rst![work details]![SALARY] / 8
> > > > > > > >
> > > > > > > > ElseIf rst![work details]![START TIME] > 8 And rst![work
> > > > > details]![START
> > > > > > > > TIME] < 13 And rst![work details]![END TIME] > 13 And
> rst![work
> > > > > > > details]![END
> > > > > > > > TIME] > 19 And rst![work details]![END TIME] <= 24 Then
> > > > > > > > mysalary = (13 - rst![work details]![START TIME] + 18 - 14) *
> > > > > rst![work
> > > > > > > > details]![SALARY] / 8 + (rst![work details]![END TIME] - 18) *
> > > > > rst![work
> > > > > > > > details]![SALARY] / 8 * 1.5
> > > > > > > >
> > > > > > > > ElseIf rst![work details]![START TIME] > 8 And rst![work
> > > > > details]![START
> > > > > > > > TIME] < 13 And rst![work details]![END TIME] > 13 And
> rst![work
> > > > > > > details]![END
> > > > > > > > TIME] > 19 And rst![work details]![END TIME] > 24 Then
> > > > > > > > mysalary = (13 - rst![work details]![START TIME] + 18 - 14) *
> > > > > rst![work
> > > > > > > > details]![SALARY] / 8 + (24 - 18) * rst![work
> details]![SALARY] /
> > > 8 *
> > > > > 1.5
> > > > > > > +
> > > > > > > > (rst![work details]![END TIME] - 24) * rst![work
> details]![SALARY]
> > > / 8
> > > > > * 2
> > > > > > > >
> > > > > > > > ElseIf rst![work details]![START TIME] > 13 And rst![work
> > > > > details]![END
> > > > > > > > TIME] > 18 And rst![work details]![END TIME] <= 24 Then
> > > > > > > > mysalary = (18 - rst![work details]![START TIME]) * rst![work
> > > > > > > > details]![SALARY] / 8 + (rst![work details]![END TIME] - 18) *
> > > > > rst![work
> > > > > > > > details]![SALARY] / 8 * 1.5
> > > > > > > >
> > > > > > > > ElseIf rst![work details]![START TIME] > 13 And rst![work
> > > > > details]![END
> > > > > > > > TIME] > 18 And rst![work details]![END TIME] > 24 Then
> > > > > > > > mysalary = (18 - rst![work details]![START TIME]) * rst![work
> > > > > > > > details]![SALARY] / 8 + (24 - 18) * rst![work
> details]![SALARY] /
> > > 8 *
> > > > > 1.5
> > > > > > > +
> > > > > > > > (rst![work details]![END TIME] - 24) * rst![work
> details]![SALARY]
> > > / 8
> > > > > * 2
> > > > > > > >
> > > > > > > > Else
> > > > > > > > mysalary = 0
> > > > > > > > End If
> > > > > > > >
> > > > > > > > rst.MoveNext
> > > > > > > > Loop Until rst.EOF
> > > > > > > >
> > > > > > > > dailysalary = mysalary
> > > > > > > >
> > > > > > > > End Function
> > > > > > > >
> > > > > > >
> > > > > > >

==============================================================================
TOPIC: Default value on a Subform
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/0d4f96140df790f1?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Nov 8 2007 5:26 pm
From: NevilleT

Have been fiddling with this for about a week. I have a main form which is a
datasheet. There is a subform which is a continuous form. Main form
displays budget line by line and subform displays expenditure items for that
account code.

I am trying to put a default value for the account code in the new record on
the subform. If I set the default to the main form (datasheet) it always
picks up the first account code value. I even tried doing it with code.

Me.txtAccountCode.DefaultValue =
[Forms]![frmExpenditure]![subGeneric].[Form]![frmExpenditureBudgetSub].[Form]![cmbAccountCode]

Just to explain, I am using Allen Browne's suggestion of having a generic
subform managed through a tab control hence the
[Forms]![frmExpenditure]![subGeneric].

I thought I could try and capture the value of the parent child link to the
subform but have not been able to come up with a way to find that using VBA.
Can anyone make a suggestion as to how to ensure the default value for the
subform does not always correspond to the first record on the datasheet?

==============================================================================
TOPIC: Make a table with VBA (Access 2003)
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/a68b557c4992fc02?hl=en
==============================================================================

== 1 of 3 ==
Date: Thurs, Nov 8 2007 5:26 pm
From: "tg112001 via AccessMonster.com"

Hello,

I have a form with a combo box (named Combo1) and a command button. The
purpose of the form is to make a new table based on the combo box value after
clicking the command button. Can someone help me with the proper syntax?

The recordset should look like:

SELECT * FROM tbl1 WHERE field1 = Combo1.value

But I don't know how to use that recordset to make a new table. Can someone
help me with the correct VBA syntax to make this happen?

Thanks!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200711/1

== 2 of 3 ==
Date: Thurs, Nov 8 2007 5:49 pm
From: NevilleT

Hi
Use something like this

Dim dbs as database
Dim tdfNew as tabledef
Dim strSQL as String
Dim lngFirst as Long
Dim lngSecond as Long

lngFirst = rst!FirstValue
lngSecond = rst!SecondValue

Set dbs = CurrentDb
Set tdfNew = dbs.CreateTableDef("tblNew") ' Set the table name

With tdfNew
.Fields.Append .CreateField("Field1", dbLong) ' Create the
field
.Fields.Append .CreateField("Field2", dbLong) ' Create
the field
dbs.TableDefs.Append tdfNew ' Append
the table

Set idxNew = .CreateIndex("NumIndex")
idxNew.Fields.Append idxNew.CreateField("Field1")
idxNew.Primary = True
.Indexes.Append idxNew
End With

strSQL = "INSERT into tblNew (Field1, Field2) VALUES( " & lngFirst &
", " & lngSecond & ";"

Set qdf = dbs.CreateQueryDef("", strSQL) ' Create
new QueryDef.
qdf.Execute ' Run
the insert query

"tg112001 via AccessMonster.com" wrote:

> Hello,
>
> I have a form with a combo box (named Combo1) and a command button. The
> purpose of the form is to make a new table based on the combo box value after
> clicking the command button. Can someone help me with the proper syntax?
>
> The recordset should look like:
>
> SELECT * FROM tbl1 WHERE field1 = Combo1.value
>
> But I don't know how to use that recordset to make a new table. Can someone
> help me with the correct VBA syntax to make this happen?
>
> Thanks!
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200711/1
>
>

== 3 of 3 ==
Date: Thurs, Nov 8 2007 7:09 pm
From: "Dirk Goldgar"

In news:7aee9c48822e6@uwe,
tg112001 via AccessMonster.com <u34664@uwe> wrote:
> Hello,
>
> I have a form with a combo box (named Combo1) and a command button.
> The purpose of the form is to make a new table based on the combo box
> value after clicking the command button. Can someone help me with
> the proper syntax?
>
> The recordset should look like:
>
> SELECT * FROM tbl1 WHERE field1 = Combo1.value
>
> But I don't know how to use that recordset to make a new table. Can
> someone help me with the correct VBA syntax to make this happen?

Here's a pretty simple way:

CurrentDb.Execute _
"SELECT * INTO NewTableName FROM tbl1 " & _
"WHERE field1 = " & Combo1.value,
dbFailOnError

If field1 is a text field, use

"WHERE field1 = '" & Combo1.value & "'",

(assuming the combo's value won't contain the single-quote character
(') ).

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

(please reply to the newsgroup)

==============================================================================
TOPIC: Giant Toolbar In A2007
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/9054bc3608ec25fb?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Nov 8 2007 7:00 pm
From: BrerGoose

My application, written in A2000, when run on A2007 has a major visual
deficiency. The custom toolbar, which in A2000 is only about a quarter inch
tall, is suddenly 2 inches tall in A2007. How can this be fixed ?

See screenshot....
http://ourworld.compuserve.com/homepages/webspace/test/GiantToolbar.jpg

Thank you.
--
BrerGoose
Moose-Tech Software:

http://ourworld.compuserve.com/homepages/webspace/MTS/Home.htm

==============================================================================
TOPIC: Why my code take time 10 Seconds
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/798c11ebb2454bf2?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Nov 8 2007 8:43 pm
From: Nova

I need to insert data to table intermod and write code in cal button click
event. Unfortunately It takes time more than 20 seconds. I try many times
and belives that "Insert into...." in M2FSql1 take almost of time.
How to write code to reduce time.

Dim M2FSql1 as String

N=35
For M = 0 To N - 1
For X = M + 2 To N
For Q = 1 To 3

F1=M*N+5
F2=M+1-N*2
OrderN = 2 * Q + 1
cal1 = (Q + 1) * F1 - Q * F2
cal2 = (Q + 1) * F2 - Q * F1

M2FSql1 = "INSERT INTO Intermod (OrderN,IntmodFreq, Freq1, Freq2) "
M2FSql1 = M2FSql1 & "Values(" & OrderN & "," & cal1 & "," & F1 & "," & F2 &
" );"
DoCmd.RunSQL M2FSql1
Next Q
Next X
Next M

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

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

Kamis, 08 November 2007

25 new messages in 12 topics - digest

Tidak ada komentar:

Arsip Blog

Blogeroll

Mengenai Saya