Excel for Life: 17 new messages in 8 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: Where to DIM
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/9f3a707c1ad90ee1?hl=en
==============================================================================

== 1 of 1 ==
Date: Sun, Nov 11 2007 5:13 pm
From: "Rick Brandt"

Dave180 wrote:
> Is it good practice to DIM all variables at the top of a large module
> or better to Dim a variable that may be required within a Sub /
> Function, or indeed within a conditional clause, within that module
> so that it is only DIMed when it is guaranteed to be required?

Unlike some other languages, all VBA variables in a procedure are "dimmed"
regardless of where you do so. That being the case I prefer to do all of that
at the top of the procedure so they are all in one place.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

==============================================================================
TOPIC: combo box data entry error
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/2a3f58cb100a5bee?hl=en
==============================================================================

== 1 of 4 ==
Date: Sun, Nov 11 2007 5:13 pm
From: "pubdude2003 via AccessMonster.com"

Key Down

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

== 2 of 4 ==
Date: Sun, Nov 11 2007 5:25 pm
From: "ruralguy via AccessMonster.com"

What other key did you trap and what action was taken? What key are you
trying to pass to the next form?

pubdude2003 wrote:
>yep, I just trapped another key and it works fine... just something about the
>Alt key trap... weird eh? whoops, gave away my Canadian status there....
>
>Is there a way around this?

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

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

== 3 of 4 ==
Date: Sun, Nov 11 2007 5:34 pm
From: "pubdude2003 via AccessMonster.com"

thanks for looking at my post ruralguy... I think I will just open this form
with a trapped Ctrl key instead... seems to work fine

thanks for letting me know about the Alt key

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

== 4 of 4 ==
Date: Sun, Nov 11 2007 5:51 pm
From: "ruralguy via AccessMonster.com"

AFAIK the Ctrl key should act that same as the Alt key. Any chance you could
send me a sample of your db that demonstrates this issue? I get the feeling
something else is going on here. My addy is Rural Guy at Wild Blue dot Net
if you can see your way clear to let me have a sample. Remove any sensitive
data of course. Thanks.

pubdude2003 wrote:
>thanks for looking at my post ruralguy... I think I will just open this form
>with a trapped Ctrl key instead... seems to work fine
>
>thanks for letting me know about the Alt key

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

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

==============================================================================
TOPIC: Code does not 'see' the required related record.
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/5bfc2cc84ab94cc1?hl=en
==============================================================================

== 1 of 4 ==
Date: Sun, Nov 11 2007 5:23 pm
From: "Dirk Goldgar"

In news:F7364574-52EB-4B40-8AA1-E627BD03CB8E@microsoft.com,
ThomasAJ <ThomasAJ@discussions.microsoft.com> wrote:
> Code too lengthy to show.
>
> You asked "why add a record to a table by manipulating a form?"
>
> The user 'drops' an Outlook email into a form. ("Form_Drop").
> Form_Drop opens the target form ("Form_Target"),
> pre-fills some info into it from the email
> The user adds some info themselves into Form_Target
> Clicks a button on Form_Target to say they are done with this email
> Form_Drop resumes executing some code (this is where the error occurs)
> The Form_Drop grabs another email etc.

Okay, that makes sense. But without seeing the code that tries (and
fails) to add a related record to CUSTPRODUCT, it's hard to say what's
wrong. I've seen this sort of problem when the code opens a separate
database connection from the one that Access is using. You aren't using
the DAO OpenDatabase method, are you?

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

(please reply to the newsgroup)

== 2 of 4 ==
Date: Sun, Nov 11 2007 6:03 pm
From: ThomasAJ

Thanks Dirk
You said "You aren't using the DAO OpenDatabase method, are you?"

NO. It's just the simple rsCUSTPRODUCT.OpenRecordSet("CUSTPRODUCT") in
Form_Drop.

There is a complication that might be affecting it:
Table CUSTPRODUCT is the RecordSource for a Subform (in a TAB) WITHIN
Form_Target. This is NOT shown during this 'Email Drop' action.

This is the only thing I can think of that is 'unusual'.
--
Regards
Tom

"Dirk Goldgar" wrote:

> In news:F7364574-52EB-4B40-8AA1-E627BD03CB8E@microsoft.com,
> ThomasAJ <ThomasAJ@discussions.microsoft.com> wrote:
> > Code too lengthy to show.
> >
> > You asked "why add a record to a table by manipulating a form?"
> >
> > The user 'drops' an Outlook email into a form. ("Form_Drop").
> > Form_Drop opens the target form ("Form_Target"),
> > pre-fills some info into it from the email
> > The user adds some info themselves into Form_Target
> > Clicks a button on Form_Target to say they are done with this email
> > Form_Drop resumes executing some code (this is where the error occurs)
> > The Form_Drop grabs another email etc.
>
> Okay, that makes sense. But without seeing the code that tries (and
> fails) to add a related record to CUSTPRODUCT, it's hard to say what's
> wrong. I've seen this sort of problem when the code opens a separate
> database connection from the one that Access is using. You aren't using
> the DAO OpenDatabase method, are you?
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
>

== 3 of 4 ==
Date: Sun, Nov 11 2007 6:10 pm
From: "Dirk Goldgar"

In news:7C938045-5295-43E1-BEE5-41C1DF1ACC54@microsoft.com,
ThomasAJ <ThomasAJ@discussions.microsoft.com> wrote:
> Thanks Dirk
> You said "You aren't using the DAO OpenDatabase method, are you?"
>
> NO. It's just the simple rsCUSTPRODUCT.OpenRecordSet("CUSTPRODUCT") in
> Form_Drop.

What would rsCUSTPRODUCT be, then? I'd expect a recordset object from
the prefix, but this doesn't seem like the sort of situation where you'd
be opening a recordset from another recordset, and the syntax isn't
right for that, anyway.

Helping you with this would be a lot easier if you would post the
relevant code.

> There is a complication that might be affecting it:
> Table CUSTPRODUCT is the RecordSource for a Subform (in a TAB) WITHIN
> Form_Target. This is NOT shown during this 'Email Drop' action.
>
> This is the only thing I can think of that is 'unusual'.

That doesn't sound like it would have any effect on the matter.

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

(please reply to the newsgroup)

== 4 of 4 ==
Date: Sun, Nov 11 2007 6:52 pm
From: ThomasAJ

Sorry the open recordset should have been:
Set rsCUSTPRODUCT = myDB.OpenRecordSet("CUSTPRODUCT").

You said "opening a recordset from another recordset" but I'm not sure why
think that. Anyway Set rsCUSTPRODUCT is being performed within Form_Drop
which is the 'controlling' code.

In summary 'data movement wise'

Table CUSTPRODUCT is dependent on table CUST.
Form_Target updates table CUST via its RecordSource property.
(Table CUSTPRODUCT is the Record Source for a subform in Form_Target)
Form_Drop wants to update table CUSTPRODUCT via DAO code. (fails)

I'll post the code soon.
--
Regards
Tom

"Dirk Goldgar" wrote:

> In news:7C938045-5295-43E1-BEE5-41C1DF1ACC54@microsoft.com,
> ThomasAJ <ThomasAJ@discussions.microsoft.com> wrote:
> > Thanks Dirk
> > You said "You aren't using the DAO OpenDatabase method, are you?"
> >
> > NO. It's just the simple rsCUSTPRODUCT.OpenRecordSet("CUSTPRODUCT") in
> > Form_Drop.
>
> What would rsCUSTPRODUCT be, then? I'd expect a recordset object from
> the prefix, but this doesn't seem like the sort of situation where you'd
> be opening a recordset from another recordset, and the syntax isn't
> right for that, anyway.
>
> Helping you with this would be a lot easier if you would post the
> relevant code.
>
> > There is a complication that might be affecting it:
> > Table CUSTPRODUCT is the RecordSource for a Subform (in a TAB) WITHIN
> > Form_Target. This is NOT shown during this 'Email Drop' action.
> >
> > This is the only thing I can think of that is 'unusual'.
>
> That doesn't sound like it would have any effect on the matter.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
>

==============================================================================
TOPIC: dynamic linking/referencing Office libraries
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/0335c5e3d25ecf0e?hl=en
==============================================================================

== 1 of 1 ==
Date: Sun, Nov 11 2007 5:42 pm
From: "Tony Toews [MVP]"

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

>I suspcted as much, but needed the intellisense/deugging option

Oh, I quite agree. That web page should show you how to switch
between using the intellisense for development and debugging and to
using late binding once that code is working.

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: Array
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/9f6b37ad261d6c5c?hl=en
==============================================================================

== 1 of 2 ==
Date: Sun, Nov 11 2007 5:59 pm
From: Mike

I'm trying to pass the REG_Z_COUNTER in an Array Can some shed some light on
this for me
Thanks Mike
SQL1 = "SELECT TENDSALE.REG_Z_COUNTER " _
& "FROM TENDSALE " _
& "GROUP BY TENDSALE.REG_Z_COUNTER;"

== 2 of 2 ==
Date: Sun, Nov 11 2007 11:20 pm
From: John Nurick

Hi Mike,

I'm not sure what you mean by 'pass'. If you're trying to bind the
column TENDSALE.REG_Z_COUNTER to an array, you can't do that sort of
thing in VBA.

Things you can do, depending on what you're trying to achieve:

1) Open a recordset, e.g.
Dim R As DAO.Recordset
Set R = CurrentDB.OpenRecordset( _
"SELECT REG_Z_COUNTER FROM TENDSALE ORDER BY XXX;")
and then pass the recordset.

2) Open a recordset, iterate through it appending each value to an
array, e.g.
Dim A As String()
Dim R As DAO.Recordset
Dim j As Long

Set R = CurrentDB.OpenRecordset( _
"SELECT REG_Z_COUNTER FROM TENDSALE ORDER BY XXX;")
R.MoveLast
R.MoveFirst
Redim A(R.RecCount - 1)
For j = 0 to UBound(A)
A(j) = R.Fields(0).Value
R.MoveNext
Next
R.Close

and pass the array; later you can reverse the process and write values
back from the array into the table.

3) As for (2), but concatenate the values into a delimited string.

On Sun, 11 Nov 2007 17:59:00 -0800, Mike
<Mike@discussions.microsoft.com> wrote:

>I'm trying to pass the REG_Z_COUNTER in an Array Can some shed some light on
>this for me
>Thanks Mike
>SQL1 = "SELECT TENDSALE.REG_Z_COUNTER " _
> & "FROM TENDSALE " _
> & "GROUP BY TENDSALE.REG_Z_COUNTER;"
--
John Nurick - Access MVP

==============================================================================
TOPIC: What Code Do I Write ?
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/758e465168699795?hl=en
==============================================================================

== 1 of 3 ==
Date: Sun, Nov 11 2007 8:31 pm
From: Jone

== 2 of 3 ==
Date: Sun, Nov 11 2007 8:35 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

== 3 of 3 ==
Date: Sun, Nov 11 2007 9:07 pm
From: "Allen Browne"

If Dir("C:\Windows\Sample.txt") <> vbNullString Then

--
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.

"Jone" <Jone@discussions.microsoft.com> wrote in message
news:2616FFC4-4240-4862-84B9-0620A650EBF7@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

==============================================================================
TOPIC: how to write code table1 into table2
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/e2c644a92fd14e7b?hl=en
==============================================================================

== 1 of 1 ==
Date: Sun, Nov 11 2007 8:35 pm
From: hisham

dear member,
I want to write result comparison table1 between table2 and result must
write in table2.
1-table1 has item1 and qty1
2-table2 has item2, quatity2 and result

"when item1 = item2 then qty1 = quatity2 and result must write in column
result in table2."
any body can help me

==============================================================================
TOPIC: how to transform text to code
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/97c816fe38af505e?hl=en
==============================================================================

== 1 of 1 ==
Date: Sun, Nov 11 2007 11:26 pm
From: "HENDRY_del_FUEGO via AccessMonster.com"

Hi all,

I am struggling with the following:
(i posted this question also in the query module, sorry if this is not
correct..)

I have made a table where a define Case / Description Case / SQL-code
The sql-code is the correct code (with [table]!... etc.) which need to be
checked if a record belongs to case 1 , case 2 , etc.

I want the code for these codes all togheter in a table to make it easier for
my successor to maintain it.

Now the problem:
In a query I make the following check:
Case 6: IIf([Overview cases]![SQL-code],"OK","NOK")

This does not work because access sees the test as TEXT, how can I make it
clear to access that this [Overview cases]![SQL-code] is sql code?

thanks in advance

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

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

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

17 new messages in 8 topics - digest

Tidak ada komentar:

Arsip Blog

Blogeroll

Mengenai Saya