Excel for Life: 20 new messages in 11 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: Undefine Function "FunctionName" in expression
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/9324a3418a405bf3?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 7 2007 2:10 pm
From: Marshall Barton

nonlinear wrote:
>I would like to inform you that this question is not for novices...
>I have an Access query that uses a VBA public user-defined function (reside
>in an module) to calculate one query's field. When I directly run the query
>everything is ok. BUT..when the query is run from outside the ACCESS (for
>example through VB6 ADO code to result a recordset: rs.open "query") then the
>jet engine issues an error message: "Undefined Function 'FunctionName' in
>expression". It seeems that jet engine doesn't execute the function if the
>query is run outside the Access. I have searched the web and I found some
>information but we will analyze them after your comments.

Jet and Access are separate entitie, so you do not have an
Access query, you have a Jet query.

When you use Jet outside of Access, then Access and your VBA
module are not available.

--
Marsh
MVP [MS Access]

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

== 1 of 3 ==
Date: Wed, Nov 7 2007 2:52 pm
From: "Douglas J. Steele"

Try:

Me!pos_in.Form.Recordset = "pos_same"

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

"J. Freed" <JFreed@discussions.microsoft.com> wrote in message
news:8AB48CEF-E17C-4F85-9A31-E60CBCFB157A@microsoft.com...
> 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.....

== 2 of 3 ==
Date: Wed, Nov 7 2007 3:17 pm
From: John W. Vinson

On Wed, 7 Nov 2007 13:53:00 -0800, J. Freed <JFreed@discussions.microsoft.com>
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.....

I think you want

Me!pos_in.Form.Recordsource = "pos_name"

The Source Object is the name of a Form used within the subform control; that
Form has a Recordsource (not Recordset!) property which is the name of a
table, of a query, or is a SQL string.

John W. Vinson [MVP]

== 3 of 3 ==
Date: Wed, Nov 7 2007 4:08 pm
From: Marshall Barton

J. Freed wrote:

Me!pos_in.Form.RecordSource = "pos_same"

--
Marsh
MVP [MS Access]

==============================================================================
TOPIC: Error checking code
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/7e01220e6d832ae9?hl=en
==============================================================================

== 1 of 4 ==
Date: Wed, Nov 7 2007 3:14 pm
From: GLT

Hi,

I have the following code which checks for nulls and if the field(s) are
null, then it will turn the boarder of the box (which all the fields are
listed in) and the field label red.

When the field has been updated with a value, then the colours will change
from red back to green.

This works fine, except it will not change the last "red" field back to green.

For example, when I test it, if there are three fields that turn red, then
after I update the three fields one by one, they all turn green except the
last field.

If anyone could advise what is wrong with this logic I would be most greatful:

Dim errTag As Variant

errTag = 0

If IsNull(Me![cmb_BoxStat]) Or IsNull(Me![cmb_RecBy]) Or IsNull(Me![Quantum
No]) Or (IsNull(Me![cmb_RecBy])) Or IsNull(Me![Recall Date]) Or
IsNull(Me![cmb_Urg]) Then

If IsNull(Me![cmb_BoxStat]) Then
Me![lbl_BoxStat].ForeColor = 255
Me![Box23].SpecialEffect = 0
Me![Box23].BorderColor = 255
errTag = 1

Else

Me![lbl_BoxStat].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

If IsNull(Me![cmb_RecBy]) Then
Me![lbl_RecBy].ForeColor = 255
Me![Box23].SpecialEffect = 0
Me![Box23].BorderColor = 255
errTag = 2

Else

Me![lbl_RecBy].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

If IsNull(Me![Quantum No]) Then
Me![lbl_QtnNo].ForeColor = 255
Me![Box23].SpecialEffect = 0
errTag = 3

Else

Me![lbl_QtnNo].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

If IsNull(Me![cmb_RecBy]) Then
Me![lbl_RecBy].ForeColor = 255
Me![Box23].SpecialEffect = 0
errTag = 4

Else

Me![lbl_RecBy].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

If IsNull(Me![Recall Date]) Then
Me![lbl_RecDte].ForeColor = 255
Me![Box23].SpecialEffect = 0
errTag = 5

Else

Me![lbl_RecDte].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

If IsNull(Me![cmb_Urg]) Then
Me![lbl_Urg].ForeColor = 255
Me![Box23].SpecialEffect = 0
errTag = 6

Else

Me![lbl_Urg].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

End If

End If

If (Me![cmb_BoxStat] = "WAITING - ON ORDER") And (errTag = 0) Then

MsgBox "WOULD YOU LIKE TO SEND AN EMAIL?", vbOKCancel, "EMAIL NOTIFICATION"

SendMessage True

End If

End Sub

== 2 of 4 ==
Date: Wed, Nov 7 2007 3:49 pm
From: JamesDeckert

Where is this code placed?
I think you'd need to call this routine from the On_Update event of every
field.
Does it work the same way if you fill the fields in starting with the last
field and progressing up? In other words, is it the last field in time which
isn't working or the last field on the form? If the last field in time, you
have a timing problem (which event is calling the code). If the last field on
the form, step through the code in debug to watch it execute.

James

"GLT" wrote:

> Hi,
>
> I have the following code which checks for nulls and if the field(s) are
> null, then it will turn the boarder of the box (which all the fields are
> listed in) and the field label red.
>
> When the field has been updated with a value, then the colours will change
> from red back to green.
>
> This works fine, except it will not change the last "red" field back to green.
>
> For example, when I test it, if there are three fields that turn red, then
> after I update the three fields one by one, they all turn green except the
> last field.
>
> If anyone could advise what is wrong with this logic I would be most greatful:
>
> Dim errTag As Variant
>
> errTag = 0
>
> If IsNull(Me![cmb_BoxStat]) Or IsNull(Me![cmb_RecBy]) Or IsNull(Me![Quantum
> No]) Or (IsNull(Me![cmb_RecBy])) Or IsNull(Me![Recall Date]) Or
> IsNull(Me![cmb_Urg]) Then
>
> If IsNull(Me![cmb_BoxStat]) Then
> Me![lbl_BoxStat].ForeColor = 255
> Me![Box23].SpecialEffect = 0
> Me![Box23].BorderColor = 255
> errTag = 1
>
> Else
>
> Me![lbl_BoxStat].ForeColor = 8421376
> Me![Box23].SpecialEffect = 3
> Me![Box23].BorderColor = 0
>
> End If
>
> If IsNull(Me![cmb_RecBy]) Then
> Me![lbl_RecBy].ForeColor = 255
> Me![Box23].SpecialEffect = 0
> Me![Box23].BorderColor = 255
> errTag = 2
>
> Else
>
> Me![lbl_RecBy].ForeColor = 8421376
> Me![Box23].SpecialEffect = 3
> Me![Box23].BorderColor = 0
>
> End If
>
> If IsNull(Me![Quantum No]) Then
> Me![lbl_QtnNo].ForeColor = 255
> Me![Box23].SpecialEffect = 0
> errTag = 3
>
> Else
>
> Me![lbl_QtnNo].ForeColor = 8421376
> Me![Box23].SpecialEffect = 3
> Me![Box23].BorderColor = 0
>
> End If
>
> If IsNull(Me![cmb_RecBy]) Then
> Me![lbl_RecBy].ForeColor = 255
> Me![Box23].SpecialEffect = 0
> errTag = 4
>
> Else
>
> Me![lbl_RecBy].ForeColor = 8421376
> Me![Box23].SpecialEffect = 3
> Me![Box23].BorderColor = 0
>
> End If
>
> If IsNull(Me![Recall Date]) Then
> Me![lbl_RecDte].ForeColor = 255
> Me![Box23].SpecialEffect = 0
> errTag = 5
>
> Else
>
> Me![lbl_RecDte].ForeColor = 8421376
> Me![Box23].SpecialEffect = 3
> Me![Box23].BorderColor = 0
>
> End If
>
> If IsNull(Me![cmb_Urg]) Then
> Me![lbl_Urg].ForeColor = 255
> Me![Box23].SpecialEffect = 0
> errTag = 6
>
> Else
>
> Me![lbl_Urg].ForeColor = 8421376
> Me![Box23].SpecialEffect = 3
> Me![Box23].BorderColor = 0
>
> End If
>
> End If
>
> If (Me![cmb_BoxStat] = "WAITING - ON ORDER") And (errTag = 0) Then
>
> MsgBox "WOULD YOU LIKE TO SEND AN EMAIL?", vbOKCancel, "EMAIL NOTIFICATION"
>
> SendMessage True
>
> End If
>
> End Sub

== 3 of 4 ==
Date: Wed, Nov 7 2007 4:06 pm
From: GLT

Hi James,

I have an 'Update Details' button that this code is attached to.

So everytime I press the update details button, it does this checking to
ensure fields are not null before sending an email (which uses those fields).

I just tested it again and it does not matter which order the field values
are entered - it is whatever (the last field value entered which had turned
red) will not turn green.

It like its exiting the code too early ...

"JamesDeckert" wrote:

> Where is this code placed?
> I think you'd need to call this routine from the On_Update event of every
> field.
> Does it work the same way if you fill the fields in starting with the last
> field and progressing up? In other words, is it the last field in time which
> isn't working or the last field on the form? If the last field in time, you
> have a timing problem (which event is calling the code). If the last field on
> the form, step through the code in debug to watch it execute.
>
> James
>
> "GLT" wrote:
>
> > Hi,
> >
> > I have the following code which checks for nulls and if the field(s) are
> > null, then it will turn the boarder of the box (which all the fields are
> > listed in) and the field label red.
> >
> > When the field has been updated with a value, then the colours will change
> > from red back to green.
> >
> > This works fine, except it will not change the last "red" field back to green.
> >
> > For example, when I test it, if there are three fields that turn red, then
> > after I update the three fields one by one, they all turn green except the
> > last field.
> >
> > If anyone could advise what is wrong with this logic I would be most greatful:
> >
> > Dim errTag As Variant
> >
> > errTag = 0
> >
> > If IsNull(Me![cmb_BoxStat]) Or IsNull(Me![cmb_RecBy]) Or IsNull(Me![Quantum
> > No]) Or (IsNull(Me![cmb_RecBy])) Or IsNull(Me![Recall Date]) Or
> > IsNull(Me![cmb_Urg]) Then
> >
> > If IsNull(Me![cmb_BoxStat]) Then
> > Me![lbl_BoxStat].ForeColor = 255
> > Me![Box23].SpecialEffect = 0
> > Me![Box23].BorderColor = 255
> > errTag = 1
> >
> > Else
> >
> > Me![lbl_BoxStat].ForeColor = 8421376
> > Me![Box23].SpecialEffect = 3
> > Me![Box23].BorderColor = 0
> >
> > End If
> >
> > If IsNull(Me![cmb_RecBy]) Then
> > Me![lbl_RecBy].ForeColor = 255
> > Me![Box23].SpecialEffect = 0
> > Me![Box23].BorderColor = 255
> > errTag = 2
> >
> > Else
> >
> > Me![lbl_RecBy].ForeColor = 8421376
> > Me![Box23].SpecialEffect = 3
> > Me![Box23].BorderColor = 0
> >
> > End If
> >
> > If IsNull(Me![Quantum No]) Then
> > Me![lbl_QtnNo].ForeColor = 255
> > Me![Box23].SpecialEffect = 0
> > errTag = 3
> >
> > Else
> >
> > Me![lbl_QtnNo].ForeColor = 8421376
> > Me![Box23].SpecialEffect = 3
> > Me![Box23].BorderColor = 0
> >
> > End If
> >
> > If IsNull(Me![cmb_RecBy]) Then
> > Me![lbl_RecBy].ForeColor = 255
> > Me![Box23].SpecialEffect = 0
> > errTag = 4
> >
> > Else
> >
> > Me![lbl_RecBy].ForeColor = 8421376
> > Me![Box23].SpecialEffect = 3
> > Me![Box23].BorderColor = 0
> >
> > End If
> >
> > If IsNull(Me![Recall Date]) Then
> > Me![lbl_RecDte].ForeColor = 255
> > Me![Box23].SpecialEffect = 0
> > errTag = 5
> >
> > Else
> >
> > Me![lbl_RecDte].ForeColor = 8421376
> > Me![Box23].SpecialEffect = 3
> > Me![Box23].BorderColor = 0
> >
> > End If
> >
> > If IsNull(Me![cmb_Urg]) Then
> > Me![lbl_Urg].ForeColor = 255
> > Me![Box23].SpecialEffect = 0
> > errTag = 6
> >
> > Else
> >
> > Me![lbl_Urg].ForeColor = 8421376
> > Me![Box23].SpecialEffect = 3
> > Me![Box23].BorderColor = 0
> >
> > End If
> >
> > End If
> >
> > If (Me![cmb_BoxStat] = "WAITING - ON ORDER") And (errTag = 0) Then
> >
> > MsgBox "WOULD YOU LIKE TO SEND AN EMAIL?", vbOKCancel, "EMAIL NOTIFICATION"
> >
> > SendMessage True
> >
> > End If
> >
> > End Sub

== 4 of 4 ==
Date: Wed, Nov 7 2007 5:10 pm
From: GLT

Ok I fixed it now - there was nothing at the end of the code to reset all
colors if the all tests succeeded. I put the following code between the else
and endif statements and it works fine:

Else

Me![lbl_BoxStat].ForeColor = 8421376
Me![lbl_QtnNo].ForeColor = 8421376
Me![lbl_RecBy].ForeColor = 8421376
Me![lbl_RecDte].ForeColor = 8421376
Me![lbl_Urg].ForeColor = 8421376
Me![Box23].SpecialEffect = 3
Me![Box23].BorderColor = 0

EndIf

"GLT" wrote:

> Hi James,
>
> I have an 'Update Details' button that this code is attached to.
>
> So everytime I press the update details button, it does this checking to
> ensure fields are not null before sending an email (which uses those fields).
>
> I just tested it again and it does not matter which order the field values
> are entered - it is whatever (the last field value entered which had turned
> red) will not turn green.
>
> It like its exiting the code too early ...
>
>
>
>
>
>
> "JamesDeckert" wrote:
>
> > Where is this code placed?
> > I think you'd need to call this routine from the On_Update event of every
> > field.
> > Does it work the same way if you fill the fields in starting with the last
> > field and progressing up? In other words, is it the last field in time which
> > isn't working or the last field on the form? If the last field in time, you
> > have a timing problem (which event is calling the code). If the last field on
> > the form, step through the code in debug to watch it execute.
> >
> > James
> >
> > "GLT" wrote:
> >
> > > Hi,
> > >
> > > I have the following code which checks for nulls and if the field(s) are
> > > null, then it will turn the boarder of the box (which all the fields are
> > > listed in) and the field label red.
> > >
> > > When the field has been updated with a value, then the colours will change
> > > from red back to green.
> > >
> > > This works fine, except it will not change the last "red" field back to green.
> > >
> > > For example, when I test it, if there are three fields that turn red, then
> > > after I update the three fields one by one, they all turn green except the
> > > last field.
> > >
> > > If anyone could advise what is wrong with this logic I would be most greatful:
> > >
> > > Dim errTag As Variant
> > >
> > > errTag = 0
> > >
> > > If IsNull(Me![cmb_BoxStat]) Or IsNull(Me![cmb_RecBy]) Or IsNull(Me![Quantum
> > > No]) Or (IsNull(Me![cmb_RecBy])) Or IsNull(Me![Recall Date]) Or
> > > IsNull(Me![cmb_Urg]) Then
> > >
> > > If IsNull(Me![cmb_BoxStat]) Then
> > > Me![lbl_BoxStat].ForeColor = 255
> > > Me![Box23].SpecialEffect = 0
> > > Me![Box23].BorderColor = 255
> > > errTag = 1
> > >
> > > Else
> > >
> > > Me![lbl_BoxStat].ForeColor = 8421376
> > > Me![Box23].SpecialEffect = 3
> > > Me![Box23].BorderColor = 0
> > >
> > > End If
> > >
> > > If IsNull(Me![cmb_RecBy]) Then
> > > Me![lbl_RecBy].ForeColor = 255
> > > Me![Box23].SpecialEffect = 0
> > > Me![Box23].BorderColor = 255
> > > errTag = 2
> > >
> > > Else
> > >
> > > Me![lbl_RecBy].ForeColor = 8421376
> > > Me![Box23].SpecialEffect = 3
> > > Me![Box23].BorderColor = 0
> > >
> > > End If
> > >
> > > If IsNull(Me![Quantum No]) Then
> > > Me![lbl_QtnNo].ForeColor = 255
> > > Me![Box23].SpecialEffect = 0
> > > errTag = 3
> > >
> > > Else
> > >
> > > Me![lbl_QtnNo].ForeColor = 8421376
> > > Me![Box23].SpecialEffect = 3
> > > Me![Box23].BorderColor = 0
> > >
> > > End If
> > >
> > > If IsNull(Me![cmb_RecBy]) Then
> > > Me![lbl_RecBy].ForeColor = 255
> > > Me![Box23].SpecialEffect = 0
> > > errTag = 4
> > >
> > > Else
> > >
> > > Me![lbl_RecBy].ForeColor = 8421376
> > > Me![Box23].SpecialEffect = 3
> > > Me![Box23].BorderColor = 0
> > >
> > > End If
> > >
> > > If IsNull(Me![Recall Date]) Then
> > > Me![lbl_RecDte].ForeColor = 255
> > > Me![Box23].SpecialEffect = 0
> > > errTag = 5
> > >
> > > Else
> > >
> > > Me![lbl_RecDte].ForeColor = 8421376
> > > Me![Box23].SpecialEffect = 3
> > > Me![Box23].BorderColor = 0
> > >
> > > End If
> > >
> > > If IsNull(Me![cmb_Urg]) Then
> > > Me![lbl_Urg].ForeColor = 255
> > > Me![Box23].SpecialEffect = 0
> > > errTag = 6
> > >
> > > Else
> > >
> > > Me![lbl_Urg].ForeColor = 8421376
> > > Me![Box23].SpecialEffect = 3
> > > Me![Box23].BorderColor = 0
> > >
> > > End If
> > >
> > > End If
> > >
> > > If (Me![cmb_BoxStat] = "WAITING - ON ORDER") And (errTag = 0) Then
> > >
> > > MsgBox "WOULD YOU LIKE TO SEND AN EMAIL?", vbOKCancel, "EMAIL NOTIFICATION"
> > >
> > > SendMessage True
> > >
> > > End If
> > >
> > > End Sub

==============================================================================
TOPIC: Access control to Access
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/6bf14c87578c29e9?hl=en
==============================================================================

== 1 of 3 ==
Date: Wed, Nov 7 2007 3:24 pm
From: MarkS

Hi,
Having never done this before I need a lot of help. I need to control the
access level for multiple users. Most will have read\write rights to one or
two tables and read rights to all the other tables.
Any help would be great

== 2 of 3 ==
Date: Wed, Nov 7 2007 6:03 pm
From: Daniel

This is called User Level security (tools---security---User-Level Security
Wizard). Look it up in the help or google it for more info. you can control
what each user has access to (forms, reports, tables,....), create group,
user account....
--
Hope this helps,

Daniel P

"MarkS" wrote:

> Hi,
> Having never done this before I need a lot of help. I need to control the
> access level for multiple users. Most will have read\write rights to one or
> two tables and read rights to all the other tables.
> Any help would be great

== 3 of 3 ==
Date: Wed, Nov 7 2007 10:23 pm
From: John W. Vinson

On Wed, 7 Nov 2007 15:24:00 -0800, MarkS <MarkS@discussions.microsoft.com>
wrote:

>Hi,
>Having never done this before I need a lot of help. I need to control the
>access level for multiple users. Most will have read\write rights to one or
>two tables and read rights to all the other tables.
>Any help would be great

Download the Microsoft Access 2000 Security FAQ:

http://support.microsoft.com/kb/207793/en-us

Print it out. Read it, carefully.

Get a good night's sleep.

Read it again, even MORE carefully. Secure your database step by step,
following the instructions and checking them off as you do (I've secured many
databases and *still* do so just in case). It's easy to screw up security!

If you have A2007, note that Microsoft took this security feature *out* of the
program. Grrrrr.....

John W. Vinson [MVP]

==============================================================================
TOPIC: Multiple Queries
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/10656f0ace54e128?hl=en
==============================================================================

== 1 of 2 ==
Date: Wed, Nov 7 2007 3:29 pm
From: MarkS

Hi,
I need to delete all the data in a update table, run several quries to load
the data into the update table and then append the data in the update table
to the final table. I have several queries which do this and one query that
runs then all in the correct sequence. Is there a better way to do this
because if one of the queries fails the update to the final table still
happens but the data is incomplete

Thanks

== 2 of 2 ==
Date: Wed, Nov 7 2007 10:24 pm
From: John W. Vinson

On Wed, 7 Nov 2007 15:29:00 -0800, MarkS <MarkS@discussions.microsoft.com>
wrote:

>Hi,
>I need to delete all the data in a update table, run several quries to load
>the data into the update table and then append the data in the update table
>to the final table. I have several queries which do this and one query that
>runs then all in the correct sequence. Is there a better way to do this
>because if one of the queries fails the update to the final table still
>happens but the data is incomplete
>
>Thanks

I'd suggest running all the queries in sequence from VBA code, wrapped in a
Transaction. See the online help for Transactions, it's pretty good - if that
doesn't make it clear post back.

John W. Vinson [MVP]

==============================================================================
TOPIC: Problems with using Microsoft Document Imaging View to print image
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/37864a2a9e80707c?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 7 2007 3:39 pm
From: Juan Carlos

Hi,

I am using Access 2003 VBA to create queues of various kinds of documents
mined from a series of data artifacts to print. Some are Access reports, some
are Word Docs, some are TIF and MDI files which we normally view in Microsoft
Document Imagine Viewer.

It all works great using the MODI.Document object, but there is one problem.
When a MODI Document object is created, the visual component is launched. But
when I execute the modi.document.close method, it doesn't go away. I am not
using the activeX viewer, so I do not have access to the .FileName or other
properties.

Is there some way I can tell these instances of MODIV to shut down?

Thanks,
Juan Carlos

==============================================================================
TOPIC: Issue editing a recordset
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/1b1e66513834853e?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 7 2007 3:44 pm
From: "jonathansnyder via AccessMonster.com"

Thank You John,

You code worked, much obliged. I knew the issue was with the record set, but
could not figure it out.

Jonathan

John Spencer wrote:
>Private Sub Combo48_AfterUpdate()
> ' Find the record that matches the control.
> Dim rs As Object
>
> If Me.Dirty then Me.Dirty = False 'Force the record to be saved
> Set rs = Me.RecordsetClone '<<<<<<
> rs.FindFirst "[Tagger_ID] = '" & Me![Combo48] & "'"
>
> If rs.NoMatch = False then
> Me.Bookmark = rs.Bookmark
> Eval (Tagger_ID) ' <<<< What is this line supposed to do
> Else
> Msgbox TaggerID & " is not in the current record set"
> End If
>
> rs.Close
> 'DoCmd.FindRecord Tagger_ID, , acEntire, , acSearchAll, , acCurrent
>End Sub
>
>Private Sub Form_BeforeUpdate(Cancel As Integer)
>Dim X As String
>X = fOSUserName()
>'nr is a global variable declared in module nr - sets nr equal to true if
>its
>a new record
>
>If nr = True Then
>Exit Sub
>End If
>
>result = MsgBox("You are about to update the current record! Do you really
>want to?", vbYesNo + vbCritical + vbDefaultButton2, "WARNING")
>If result = vbNo Then
>Me.Undo
>Exit Sub
>End If
>
>Me.Update_Date = Date
>Me.Update_Name = X
>
>End Sub
>
>> Bump.
>>
>[quoted text clipped - 9 lines]
>>>>Thanks
>>>>Jonathan

--
Message posted via http://www.accessmonster.com

==============================================================================
TOPIC: running app -> make the Access menus hidden
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/a3c8ba8766d4c19f?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 7 2007 4:39 pm
From: "P.Schuman"

We have an application that runs Access 2007.
Is there a way to "hide" all the normal Access menus, ribbons, etc,
so only the application screens are displayed ??

I tried just renaming the ACCDB -> ACCDR and running with the "runtime",
but got some weird crash when it ran on a system with the real Access 2007.

Everyone has Access 2007,
I just want to have less clutter on the screen for them
when running the application.

==============================================================================
TOPIC: move excel workbook
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/77da7fbba47ad6ed?hl=en
==============================================================================

== 1 of 2 ==
Date: Wed, Nov 7 2007 4:46 pm
From: jnewl

how do i move an excel workbook from one folder to another?

thanks for your help

== 2 of 2 ==
Date: Wed, Nov 7 2007 7:22 pm
From: "Stuart McCall"

"jnewl" <jnewl@discussions.microsoft.com> wrote in message
news:B64B594F-BCC1-45BF-8405-17BB50871FF5@microsoft.com...
> how do i move an excel workbook from one folder to another?
>
> thanks for your help

FileCopy "C:\OldFolder\MyWorkbook.xls", "C:\NewFolder\MyWorkbook.xls"
Kill "C:\OldFolder\MyWorkbook.xls"

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

== 1 of 1 ==
Date: Wed, Nov 7 2007 10:00 pm
From: "tina"

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: Email Report as an Attachment
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/0b093cb6c36e99d5?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Nov 8 2007 12:16 am
From: "Adam"

Hi,

I have Adobe Profession v 7.

I would like to be able to convert a selected messages in Outlook and save
the message as a PDF in a predetermined folder. One of the things is that I
would like to attach the msg file and the attachements of the msg in the
output adobe PDF.

I am expecting that this is going to be no mean task.

Any help would be appreciated.

Thanks

Adam

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

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

20 new messages in 11 topics - digest

Tidak ada komentar:

Arsip Blog

Blogeroll

Mengenai Saya