Excel for Life: 25 new messages in 13 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: How to stop the procedure instead of continuing sending a mail after
none of criteria match
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/c095b2ddb863b664?hl=en
==============================================================================

== 1 of 2 ==
Date: Thurs, Nov 15 2007 7:58 am
From: Gerry

Hello

Descprition of situation:

As you can see here we have a loop (While not ... Wend) which contains
2 'case conditions'.
What is my problem. At the end of this piece of code there is more
code that prepares a mail containing all constatations.
My problem:
In case none of the 'case criteria' match I want to end the procedure
preliminary so the mail doesn't have to be sent.
Can anybody tell me how I have to proceed? Thanks a lot!

nomb = Rs1.RecordCount
Rs1.MoveFirst
While Not (Rs1.EOF)

resultstr = ""
Posnr = Rs1.Fields("Position")
Instrnr = Rs1.Fields("INSTRUMENT")
Select Case Abs(Rs1.Fields("Position"))

Case 5 To 10
resultstr = Instrnr & ":" & Posnr & "EUR"
MsgBox ("Nobody ")

Case 11 To 20
resultstr = resultstr & Instrnr & ":" & Posnr & "EUR"
MsgBox ("Go for It")

End Select
Rs1.MoveNext
Wend
MsgBox ("All Positions checked!")

.... ?? ..... If the criteria don't match, the mail that is generated
hereafter doesn't have to be sent.

== 2 of 2 ==
Date: Thurs, Nov 15 2007 8:27 am
From: "Stuart McCall"

"Gerry" <geert.vanransbeeck@dexia.be> wrote in message
news:e0ecd190-934b-458b-aa5e-59fcff92530b@y5g2000hsf.googlegroups.com...
> Hello
>
> Descprition of situation:
>
> As you can see here we have a loop (While not ... Wend) which contains
> 2 'case conditions'.
> What is my problem. At the end of this piece of code there is more
> code that prepares a mail containing all constatations.
> My problem:
> In case none of the 'case criteria' match I want to end the procedure
> preliminary so the mail doesn't have to be sent.
> Can anybody tell me how I have to proceed? Thanks a lot!
>
> <check of files in a Recordset>
>
> nomb = Rs1.RecordCount
> Rs1.MoveFirst
> While Not (Rs1.EOF)
>
> resultstr = ""
> Posnr = Rs1.Fields("Position")
> Instrnr = Rs1.Fields("INSTRUMENT")
> Select Case Abs(Rs1.Fields("Position"))
>
> Case 5 To 10
> resultstr = Instrnr & ":" & Posnr & "EUR"
> MsgBox ("Nobody ")
>
> Case 11 To 20
> resultstr = resultstr & Instrnr & ":" & Posnr & "EUR"
> MsgBox ("Go for It")
>
> End Select
> Rs1.MoveNext
> Wend
> MsgBox ("All Positions checked!")
>
> .... ?? ..... If the criteria don't match, the mail that is generated
> hereafter doesn't have to be sent.
>
> <code which creates a general mail containing constatations even if
> there is nothing relevant to mention>

Insert the line:

If resultStr <> "" Then Exit Sub

after your MsgBox line.

==============================================================================
TOPIC: Button in form header
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/f72483eb1388258f?hl=en
==============================================================================

== 1 of 2 ==
Date: Thurs, Nov 15 2007 8:00 am
From: mcescher

On Nov 14, 6:12 pm, JamesDeckert
<JamesDeck...@discussions.microsoft.com> wrote:
> I think you want me.txtUID.Value
>
> James
>
>
>
> "mcescher" wrote:
> > Help, I must be missing something basic (pardon the pun)
>
> > Windows XP Pro, Access 2002
>
> > Created a new form, with some unbound text boxes in the form header,
> > and a couple of buttons.
>
> > cmdClose just closes the form with DoCmd.Close
>
> > When I click the other button I want to check the values of the text
> > boxes. They don't show up with the dropdown when I type "me." But the
> > controls from the detail section do show up in the dropdown list.
>
> > When I try to compile, it throws an error on "If Len(Me.txtUID & "") =
> > 0 Then"
> > "Compile error: Method or data member now found"
>
> > Both the box and the button are in the header, and I have records that
> > show up in the detail section, which is set to show "Continuous Forms"
>
> > I've tried:
> > txtUID
> > Me.txtUID
> > Forms!frmcamISeriesMaint!txtUID
>
> > All with bad results. What am I doing wrong? I just want to test for
> > valid data in the form, and feed the values to another function that
> > I've already written.
>
> > Thanks so much for your time,
> > Chris M.- Hide quoted text -
>
> - Show quoted text -

Nope, still throws the same error.

I'm currently trying to show records in the detail section
(Continuous) with just one set of controls at the top, am I going to
have to use a subform to do this?

Thanks for taking a look.

Chris M.

== 2 of 2 ==
Date: Thurs, Nov 15 2007 9:15 am
From: mcescher

On Nov 14, 6:12 pm, JamesDeckert
<JamesDeck...@discussions.microsoft.com> wrote:
> I think you want me.txtUID.Value
>
> James

Well, I created a new form, and it all worked fine, so I must have
botched something on that form. I'll just copy the controls over, and
it will all be fine.

Thanks James for your help,
Chris M.

==============================================================================
TOPIC: Field properties
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/76e39d7d763aec7a?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Nov 15 2007 8:03 am
From: "Alex Dybenko"

Thanks for update John!

Never tried to do so via DDL, will try!

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

"John Spencer" <spencer@chpdm.edu> wrote in message
news:ebEje94JIHA.5928@TK2MSFTNGP05.phx.gbl...
> You can use DDL (SQL) to change field size.
>
> -- Code snippet --
> Dim dbAny as DAO.Database
> Dim strSQL as String
> strSQL = "ALTER TABLE Data ALTER COLUMN Instruction TEXT(250) "
> Set Dbany = CurrentDb()
> DbAny.Execute strSQL, dbFailonError
>
>
> --
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> .
>
> "Alex Dybenko" <alexdyb@PLEASE.cemi.NO.rssi.SPAM.ru> wrote in message
> news:epgnmx4JIHA.1020@TK2MSFTNGP05.phx.gbl...
>> Hi,
>> most of the field properties you can change, but not size. For size you
>> have to create a new field with required size (there is a sample in
>> online help), copy data from old field to new using update query, delete
>> old field and then rename new to old name
>>
>> --
>> Best regards,
>> ___________
>> Alex Dybenko (MVP)
>> http://accessblog.net
>> http://www.PointLtd.com
>>
>> "JT" <JT@discussions.microsoft.com> wrote in message
>> news:00423B3B-AAD7-4E7F-ABC4-66F13D560E44@microsoft.com...
>>> Is it possible to change the properties of a field in a table
>>> programmatically? For instance, I want to update the "Instruction"
>>> field in
>>> the "Data" table by changing the size from 50 to 250.
>>>
>>> I have a macro that will cycle through a number of folders and I want to
>>> add
>>> some code to change the field size for a database in each folder.
>>>
>>> Any help getting started would be appreciated. Thanks for the help.
>>> --
>>> JT
>>
>
>

==============================================================================
TOPIC: Generate Email
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/8d353c49a11d29ca?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Nov 15 2007 8:07 am
From: "Alex Dybenko"

Hi,
say you have a textbox txtTo, then code behind button's click event on there
same form will be:

docmd.sendobject acSendTable,"MyTable",,me.txtTo

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

"James Mullan" <JamesMullan@discussions.microsoft.com> wrote in message
news:117C5328-3AE1-43B3-987A-4B6F306CBC5C@microsoft.com...
> Thanks Alex, I've got the basics of this sorted. Do you know how I could
> call
> the the contents of a field in the current record and place them in the
> 'To'
> field of the message?
>
> Thanks
>
> James
>
> "Alex Dybenko" wrote:
>
>> Hi,
>> have a look at docmd.sendobject
>>
>> --
>> Best regards,
>> ___________
>> Alex Dybenko (MVP)
>> http://accessblog.net
>> http://www.PointLtd.com
>>
>> "James Mullan" <JamesMullan@discussions.microsoft.com> wrote in message
>> news:A8A531D5-41B3-4ECC-BED1-5847FDCADE77@microsoft.com...
>> > Hi
>> >
>> > Can anyone advise how I can generate an email from access? I want the
>> > user
>> > to be able to click a button which will open an email template in
>> > outlook
>> > and
>> > populate with data from specified tables and columns.
>> >
>> > Thanks
>> >
>> > James
>>
>>

==============================================================================
TOPIC: conting by letter rather than number
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/d5ff87184daf9c06?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Nov 15 2007 8:23 am
From: ArielZusya

Hi Dave,

So... don't know how I missed this but for whatever reason I missed your
reply. sorry bout that. OK... so... I'm back now and I'll stop relying on
the "notify me of replies" and just be vigilant about checking back manually.

Rules... I agree with you completely about the logic but unfortunately the
folks I'm writing this for are less than logical. Funny thing is this is for
tracking exhibits in court. The way courts do this is for exhibits
introduced by either the plaintiff in civil matters or the prosecution in
criminal matters the courts will use 1, 2, 3, etc. but for defense they use
A, B, C ... Z, AA, BB, CC ... ZZ, AAA, BBB, CCC ... ZZZ, AAAA, BBBB, etc.
Unfortunately, while I'd like to say that no case is so complex that it will
have more than a certain number of exhibits, I can't with certainty so best
would be the ability for the system to keep going infinitely. That said, if
that's insane I'm happy to stop at 6 digits deep ZZZZZZ which would break the
150 mark for exhibits (I hope we don't see cases with more than that for
defense exhibits! The reason the prosecution/plaintiffs get the numbering is
because traditionally the prosecution has the majority exhibits. At any rate
a trial with ZZZZZZ exhibits would be a sleepy trial. *GRIN* Sorry...
couldn't resist.)

I'm pretty much self taught with vba and access and I've wanted to start
playing with loops (I'm assuming that's where we're headed) so I'm really
excited (and nervous). On the other hand if looping isn't necessary, I'll be
content with whatever I learn. Thanks again for all your help.

Ariel

"Klatuu" wrote:

> I can show you how to write the code, but I need to know some rules.
> How many characters can be in the string?
> What is the order. For example, I would expect it would go from
> A - Z
> AA - AZ
> AB - ABZ
>
> In your original example, you show going form BB to CC, but that doesn't
> seem logical. However, it is your app and your rules, so I need to know how
> the order actually is.
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "ArielZusya" wrote:
>
> > OK... so... that's so cool... quick question though... How do I get past 90?
> > 91 seems to go to [ rather than to AA? I imagine that's because asc must be
> > returning the key mapped to that value or something like that, right? I
> > suppose I would have to have it do some math or something to get it to
> > concatonate the chr of two asc returns or something. I noticed that asc("AA")
> > returns 65 so I'm not clear on how to make this all work. Do I need to loop
> > this? Wouldn't it have been great if the two groups both used something
> > easier like GroupA1, GroupA2, GroupA3, etc. and GroupB1, GroupB2, GroupB3
> > etc. That I could do with no assistance. At least I'm learning something
> > new though. Any thoughts on what I should do next would be greatly
> > appreciated! Thanks for all your help.
> >
> > "ArielZusya" wrote:
> >
> > > I'm working on a dataset which numbers each entry by number (1, 2, 3, etc.)
> > > for one group and by letter for another group (A, B, C, ... AA, BB, CC, ...
> > > AAA, BBB, CCC, etc.). I'd like to have the system figure out the last record
> > > entered and detect the letters and know what should come next. That way if
> > > the 28th record is entered (BB) it knows that the 29th record should be CC.
> > > Is there an easy way to accomplish this using VBA? I could simply create a
> > > table with the first 130 entries (A - ZZZZZ) but I'd rather have the system
> > > work a bit more independantly (so in the event that there is 131 records I
> > > don't run into trouble). Thanks!

==============================================================================
TOPIC: Do I need a Nested Loop?
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/70866bd7537ade3c?hl=en
==============================================================================

== 1 of 8 ==
Date: Thurs, Nov 15 2007 8:23 am
From: "Douglas J. Steele"

You need to keep track of what the previous value was for phsnum, and reset
linecount each time it changes:

Dim Prevphsnum As ??? ' I don't know what the data type should be

Do Until cycles = count

With rst
If !phsnum <> Prevphsnum Then
linecount = 1
End If
.Edit
![linnum] = linecount
.Update
rst.MoveNext
linecount = linecount + 1
cycles = cycles + 1
Prevphsnum = !phsnum
End With

Loop

Why have you set linecount to a string, though, if you're doing arithmetic
with it?

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

"nomadk" <nomadk@discussions.microsoft.com> wrote in message
news:95A25BB3-A992-4B71-BB17-7B3165EE0DFE@microsoft.com...
> Hi Everyone,
>
> Access 2003, XP Pro
>
> I use this function to assign a value to [linnum].
>
> "Option Compare Database
>
>
> Public Function AssignNumbersBudgetLines()
>
> ' Assign linnum to tblBudgetLines
> Dim count, cycles, linecount As String
>
> cycles = 0 'how many loops
> count = 0 ' number of records
> linecount = "01" ' increment for line items
>
> Dim dbs As Database, rst As Recordset
> Set dbs = CurrentDb
> strSql = "select tblBudgetLines.* from tblBudgetLines order by
> phsnum,Match"
> Set rst = dbs.OpenRecordset(strSql)
> Set rst = dbs.OpenRecordset("tblBudgetLines", dbOpenTable)
>
>
> rst.MoveLast
> rst.MoveFirst
> count = rst.RecordCount
>
>
> Do Until cycles = count
>
> With rst
> .Edit
> ![linnum] = linecount
> .Update
> rst.MoveNext
> linecount = linecount + 1
> cycles = cycles + 1
> End With
>
> Loop
>
> rst.Close
>
> End Function"
>
> How can I modify this so that I restart numbering each time the function
> encounters a new [phsnum]?
>
> For Example:
>
> phsnum Match linnum
> 01 1000 1
> 01 2000 2
> 01 3000 3
> 02 1000 1
> 02 1500 2
> 03 5000 1
>
>
> TIA

== 2 of 8 ==
Date: Thurs, Nov 15 2007 8:31 am
From: "Stuart McCall"

Insert the line:

savPhsnum = !phsnum

before the .Edit line. Then, just before the End With, insert:

If savPhsnum <> !phsnum Then cycles = 0

== 3 of 8 ==
Date: Thurs, Nov 15 2007 10:17 am
From: nomadk

"Douglas J. Steele" wrote:

> You need to keep track of what the previous value was for phsnum, and reset
> linecount each time it changes:
>
> Dim Prevphsnum As ??? ' I don't know what the data type should be
>
> Do Until cycles = count
>
> With rst
> If !phsnum <> Prevphsnum Then
> linecount = 1
> End If
> .Edit
> ![linnum] = linecount
> .Update
> rst.MoveNext
> linecount = linecount + 1
> cycles = cycles + 1
> Prevphsnum = !phsnum
> End With
>
> Loop
>
>
> Why have you set linecount to a string, though, if you're doing arithmetic
> with it?
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "nomadk" <nomadk@discussions.microsoft.com> wrote in message
> news:95A25BB3-A992-4B71-BB17-7B3165EE0DFE@microsoft.com...
> > Hi Everyone,
> >
> > Access 2003, XP Pro
> >
> > I use this function to assign a value to [linnum].
> >
> > "Option Compare Database
> >
> >
> > Public Function AssignNumbersBudgetLines()
> >
> > ' Assign linnum to tblBudgetLines
> > Dim count, cycles, linecount As String
> >
> > cycles = 0 'how many loops
> > count = 0 ' number of records
> > linecount = "01" ' increment for line items
> >
> > Dim dbs As Database, rst As Recordset
> > Set dbs = CurrentDb
> > strSql = "select tblBudgetLines.* from tblBudgetLines order by
> > phsnum,Match"
> > Set rst = dbs.OpenRecordset(strSql)
> > Set rst = dbs.OpenRecordset("tblBudgetLines", dbOpenTable)
> >
> >
> > rst.MoveLast
> > rst.MoveFirst
> > count = rst.RecordCount
> >
> >
> > Do Until cycles = count
> >
> > With rst
> > .Edit
> > ![linnum] = linecount
> > .Update
> > rst.MoveNext
> > linecount = linecount + 1
> > cycles = cycles + 1
> > End With
> >
> > Loop
> >
> > rst.Close
> >
> > End Function"
> >
> > How can I modify this so that I restart numbering each time the function
> > encounters a new [phsnum]?
> >
> > For Example:
> >
> > phsnum Match linnum
> > 01 1000 1
> > 01 2000 2
> > 01 3000 3
> > 02 1000 1
> > 02 1500 2
> > 03 5000 1
> >
> >
> > TIA
>
>
>
Thanks for your help, Doug.

Here's my revised code:

"Option Compare Database

Public Function AssignNumbersBudgetLines()

' Assign linnum to tblBudgetLines
Dim count, cycles As String
Dim lincount As Long

cycles = 0 'how many loops
count = 0 ' number of records
linecount = "01" ' increment for line items

Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
strSql = "select tblBudgetLines.* from tblBudgetLines order by phsnum,Match"
Set rst = dbs.OpenRecordset(strSql)
Set rst = dbs.OpenRecordset("tblBudgetLines", dbOpenTable)

rst.MoveLast
rst.MoveFirst
count = rst.RecordCount
Dim Prevphsnum As Long

Do Until cycles = count

With rst
If !phsnum <> Prevphsnum Then
linecount = 1
End If
.Edit
![linnum] = linecount
.Update
rst.MoveNext
linecount = linecount + 1
cycles = cycles + 1
Prevphsnum = !phsnum

End With

Loop

rst.Close

End Function"

When I execute it I get error '3021' No Current Record.

Any suggestions?

== 4 of 8 ==
Date: Thurs, Nov 15 2007 10:32 am
From: "Douglas J. Steele"

Looking at your code, it would appear that the only reason you've got Cycles
and Count in there is to control your loop. It's far safer to use the EOF
property of the recordset. You're also opening rst twice, and since you're
strictly using the table in the second case, your data will be unsorted.

Try:

Public Function AssignNumbersBudgetLines()

' Assign linnum to tblBudgetLines
Dim lincount As Long

Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
strSql = "select tblBudgetLines.* from tblBudgetLines order by phsnum,Match"
Set rst = dbs.OpenRecordset(strSql)

rst.MoveLast
rst.MoveFirst
Dim Prevphsnum As Long

Do Until rst.EOF

With rst
If !phsnum <> Prevphsnum Then
linecount = 1
End If
.Edit
![linnum] = linecount
.Update
rst.MoveNext
linecount = linecount + 1
Prevphsnum = !phsnum
End With

Loop

rst.Close

End Function

Your sample code declares count and cycles as String variables, yet you're
doing arithmetic with them. You declare linecount to be Long, but you
initialize it to a string ("01").

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

"nomadk" <nomadk@discussions.microsoft.com> wrote in message
news:CF3497E9-CDA8-49F4-8608-536E958DA649@microsoft.com...
> Thanks for your help, Doug.
>
>
> When I execute it I get error '3021' No Current Record.
>
> Any suggestions?

== 5 of 8 ==
Date: Thurs, Nov 15 2007 11:17 am
From: nomadk

Thanks again for your help, Doug. I should have prefaced my question by
saying that I'm teaching myself VBA, one module at a time.

I see what you mean about the sort order. The table is already sorted by
phsnum and Match anyway.

Using your revised code I still get the No Current Record error. The updated
table shows this:

phsnum Match linnum
0 1100
0 2250 1
1 1100 2
1 1140 3
1 1230 4
3 1290 5
4 2280 6

== 6 of 8 ==
Date: Thurs, Nov 15 2007 11:45 am
From: "John Spencer"

Public Function AssignNumbersBudgetLines()

' Assign linnum to tblBudgetLines
Dim lincount As Long

Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
strSql = "select tblBudgetLines.* from tblBudgetLines order by phsnum,Match"
Set rst = dbs.OpenRecordset(strSql)

rst.MoveLast
rst.MoveFirst
Dim Prevphsnum As Long

Do Until rst.EOF

With rst
If !phsnum <> Prevphsnum Then
linecount = 1
End If
.Edit
![linnum] = linecount
.Update
linecount = linecount + 1
Prevphsnum = !phsnum
rst.MoveNext 'Note the move of this line to the end the loop
End With

Loop

rst.Close

End Function

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

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:ulcD0X7JIHA.2268@TK2MSFTNGP02.phx.gbl...
> Looking at your code, it would appear that the only reason you've got
> Cycles and Count in there is to control your loop. It's far safer to use
> the EOF property of the recordset. You're also opening rst twice, and
> since you're strictly using the table in the second case, your data will
> be unsorted.
>
> Try:
>
> Public Function AssignNumbersBudgetLines()
>
> ' Assign linnum to tblBudgetLines
> Dim lincount As Long
>
> Dim dbs As Database, rst As Recordset
> Set dbs = CurrentDb
> strSql = "select tblBudgetLines.* from tblBudgetLines order by
> phsnum,Match"
> Set rst = dbs.OpenRecordset(strSql)
>
> rst.MoveLast
> rst.MoveFirst
> Dim Prevphsnum As Long
>
> Do Until rst.EOF
>
> With rst
> If !phsnum <> Prevphsnum Then
> linecount = 1
> End If
> .Edit
> ![linnum] = linecount
> .Update
> rst.MoveNext
> linecount = linecount + 1
> Prevphsnum = !phsnum
> End With
>
> Loop
>
> rst.Close
>
> End Function
>
> Your sample code declares count and cycles as String variables, yet you're
> doing arithmetic with them. You declare linecount to be Long, but you
> initialize it to a string ("01").
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "nomadk" <nomadk@discussions.microsoft.com> wrote in message
> news:CF3497E9-CDA8-49F4-8608-536E958DA649@microsoft.com...
>> Thanks for your help, Doug.
>>
>>
>> When I execute it I get error '3021' No Current Record.
>>
>> Any suggestions?
>
>

== 7 of 8 ==
Date: Thurs, Nov 15 2007 11:53 am
From: "Douglas J. Steele"

"nomadk" <nomadk@discussions.microsoft.com> wrote in message
news:8C84E028-E5AB-461C-90C5-652E01258DA8@microsoft.com...
> Thanks again for your help, Doug. I should have prefaced my question by
> saying that I'm teaching myself VBA, one module at a time.
>
> I see what you mean about the sort order. The table is already sorted by
> phsnum and Match anyway.

No, it's not. You can never make any assumption about the order of records
in tables. Tables are "sacks of data": Access puts the data wherever it
feels like. The only way to ensure the order is to use a query with a
particular ORDER BY clause.

> Using your revised code I still get the No Current Record error. The
> updated
> table shows this:
>
> phsnum Match linnum
> 0 1100
> 0 2250 1
> 1 1100 2
> 1 1140 3
> 1 1230 4
> 3 1290 5
> 4 2280 6

John corrected an error I made in the code I gave you earlier. Put the
MoveNext at the end of the section:

With rst
If !phsnum <> Prevphsnum Then
linecount = 1
End If
.Edit
![linnum] = linecount
.Update
linecount = linecount + 1
Prevphsnum = !phsnum
.MoveNext
End With

The way it was before, at the last record, the code was moving to the end of
the recordset and then trying to access the value of phsnum in that
non-existant record. Sorry about that!

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

== 8 of 8 ==
Date: Thurs, Nov 15 2007 11:58 am
From: nomadk

Thanks, John, for the suggestion.

Using your function I get the following:

phsnum Match linnum
0 1100
0 2250 1
1 1100 1
1 1140 2
1 1230 3
3 1290 1
4 2280 1

So restarting at phsnum is working but it still skips the first record.

"John Spencer" wrote:

> Public Function AssignNumbersBudgetLines()
>
> ' Assign linnum to tblBudgetLines
> Dim lincount As Long
>
> Dim dbs As Database, rst As Recordset
> Set dbs = CurrentDb
> strSql = "select tblBudgetLines.* from tblBudgetLines order by phsnum,Match"
> Set rst = dbs.OpenRecordset(strSql)
>
> rst.MoveLast
> rst.MoveFirst
> Dim Prevphsnum As Long
>
> Do Until rst.EOF
>
> With rst
> If !phsnum <> Prevphsnum Then
> linecount = 1
> End If
> .Edit
> ![linnum] = linecount
> .Update
> linecount = linecount + 1
> Prevphsnum = !phsnum
> rst.MoveNext 'Note the move of this line to the end the loop
> End With
>
> Loop
>
> rst.Close
>
> End Function
>
>
> --
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> ..
>
> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
> news:ulcD0X7JIHA.2268@TK2MSFTNGP02.phx.gbl...
> > Looking at your code, it would appear that the only reason you've got
> > Cycles and Count in there is to control your loop. It's far safer to use
> > the EOF property of the recordset. You're also opening rst twice, and
> > since you're strictly using the table in the second case, your data will
> > be unsorted.
> >
> > Try:
> >
> > Public Function AssignNumbersBudgetLines()
> >
> > ' Assign linnum to tblBudgetLines
> > Dim lincount As Long
> >
> > Dim dbs As Database, rst As Recordset
> > Set dbs = CurrentDb
> > strSql = "select tblBudgetLines.* from tblBudgetLines order by
> > phsnum,Match"
> > Set rst = dbs.OpenRecordset(strSql)
> >
> > rst.MoveLast
> > rst.MoveFirst
> > Dim Prevphsnum As Long
> >
> > Do Until rst.EOF
> >
> > With rst
> > If !phsnum <> Prevphsnum Then
> > linecount = 1
> > End If
> > .Edit
> > ![linnum] = linecount
> > .Update
> > rst.MoveNext
> > linecount = linecount + 1
> > Prevphsnum = !phsnum
> > End With
> >
> > Loop
> >
> > rst.Close
> >
> > End Function
> >
> > Your sample code declares count and cycles as String variables, yet you're
> > doing arithmetic with them. You declare linecount to be Long, but you
> > initialize it to a string ("01").
> >
> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele
> > (no e-mails, please!)
> >
> >
> > "nomadk" <nomadk@discussions.microsoft.com> wrote in message
> > news:CF3497E9-CDA8-49F4-8608-536E958DA649@microsoft.com...
> >> Thanks for your help, Doug.
> >>
> >>
> >> When I execute it I get error '3021' No Current Record.
> >>
> >> Any suggestions?
> >
> >
>
>
>

==============================================================================
TOPIC: Check for a table
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/e380c37a3dce7d16?hl=en
==============================================================================

== 1 of 4 ==
Date: Thurs, Nov 15 2007 9:48 am
From: JT

Can you programatically check a database to see if a specific table exists in
your database?

I have a number of cost centers and there are several different versions
existing, depending when they came on-line.

What I would like to do is write (or borrow) some code that will check a
database for a specific table.

If it doesn't exist, I would like to:
1) Add the table
2) Add 2 fields for Group and Branch
3) Populate both fields with 5.0 (I assume this is an update query)

Any help getting started would be appreciated. Thanks
--
JT

== 2 of 4 ==
Date: Thurs, Nov 15 2007 10:52 am
From: "Jeff Boyce"

I may be reading too much into your description...

It sounds like you have a separate database for each "cost center". If so,
this is exactly the design you'd need to use ... for a spreadsheet-based
solution! In Access, you won't get the best use of the
relationally-oriented features and functions if you feed it "sheet" data.

Just in case you do have separate dbs, consider creating a single db, and
using one additional field in any relevant table(s), that field holding the
CostCenterID.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"JT" <JT@discussions.microsoft.com> wrote in message
news:2B0D0CC8-A5E3-4236-AF95-684E34E5A51B@microsoft.com...
> Can you programatically check a database to see if a specific table exists
> in
> your database?
>
> I have a number of cost centers and there are several different versions
> existing, depending when they came on-line.
>
> What I would like to do is write (or borrow) some code that will check a
> database for a specific table.
>
> If it doesn't exist, I would like to:
> 1) Add the table
> 2) Add 2 fields for Group and Branch
> 3) Populate both fields with 5.0 (I assume this is an update query)
>
> Any help getting started would be appreciated. Thanks
> --
> JT

== 3 of 4 ==
Date: Thurs, Nov 15 2007 10:54 am
From: "Dirk Goldgar"

In news:2B0D0CC8-A5E3-4236-AF95-684E34E5A51B@microsoft.com,
JT <JT@discussions.microsoft.com> wrote:
> Can you programatically check a database to see if a specific table
> exists in your database?
>
> I have a number of cost centers and there are several different
> versions existing, depending when they came on-line.
>
> What I would like to do is write (or borrow) some code that will
> check a database for a specific table.
>
> If it doesn't exist, I would like to:
> 1) Add the table
> 2) Add 2 fields for Group and Branch
> 3) Populate both fields with 5.0 (I assume this is an update query)
>
> Any help getting started would be appreciated. Thanks

Check if table exists:

Function fncTableExists(TableName As String) As Boolean
On Error Resume Next
fncTableExists = _
(TableName = CurrentDb.TableDefs(TableName).Name)
End Function

Add table and create fields (an example of one way):

CurrentDb.Execute _
"CREATE TABLE MyTable (Group DOUBLE, Branch DOUBLE)", _
dbFailOnError

Add a record with values of 5.0, 5.0:

CurrentDb.Execute _
"INSERT INTO MyTable ([Group], Branch) VALUES (5.0, 5.0)", _
dbFailOnError

Note that the Group field must be enclosed in brackets because it's a
reserved word in SQL. You would do better to use a different name for
this field.

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

(please reply to the newsgroup)

== 4 of 4 ==
Date: Thurs, Nov 15 2007 11:53 am
From: JT

Thanks. I'll give this a try.....
--
JT

"Dirk Goldgar" wrote:

> In news:2B0D0CC8-A5E3-4236-AF95-684E34E5A51B@microsoft.com,
> JT <JT@discussions.microsoft.com> wrote:
> > Can you programatically check a database to see if a specific table
> > exists in your database?
> >
> > I have a number of cost centers and there are several different
> > versions existing, depending when they came on-line.
> >
> > What I would like to do is write (or borrow) some code that will
> > check a database for a specific table.
> >
> > If it doesn't exist, I would like to:
> > 1) Add the table
> > 2) Add 2 fields for Group and Branch
> > 3) Populate both fields with 5.0 (I assume this is an update query)
> >
> > Any help getting started would be appreciated. Thanks
>
> Check if table exists:
>
> Function fncTableExists(TableName As String) As Boolean
> On Error Resume Next
> fncTableExists = _
> (TableName = CurrentDb.TableDefs(TableName).Name)
> End Function
>
> Add table and create fields (an example of one way):
>
> CurrentDb.Execute _
> "CREATE TABLE MyTable (Group DOUBLE, Branch DOUBLE)", _
> dbFailOnError
>
> Add a record with values of 5.0, 5.0:
>
> CurrentDb.Execute _
> "INSERT INTO MyTable ([Group], Branch) VALUES (5.0, 5.0)", _
> dbFailOnError
>
> Note that the Group field must be enclosed in brackets because it's a
> reserved word in SQL. You would do better to use a different name for
> this field.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
>

==============================================================================
TOPIC: SQL Back End - Access Front End
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/bf68c046fb543559?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Nov 15 2007 9:52 am
From: TheNovice

All,

I have a need/Challenge to move all of our Access Databases and convert them
to SQL but keeping Access as the front end for all of the users.

Can anyone guide me to the right direction on how to accomplish this?

Thanks in advance,
--
-The Novice
Learn Today, Teach Tomorrow

Great Success is ones ability to ask for Help.

==============================================================================
TOPIC: Audit Log
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/0c40ff55056ae894?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Nov 15 2007 10:21 am
From: raja07

Thanks Neville, i will take a look and let you know. i have been trying to do
this for sometime now. i have two tables that holds the before update record
and one that holds the after update record. now i need to loop through and
fields and find the two that do not match. actually now that i'm typing this
it seems to me i've taken the long way to get the audit log done. i will let
you know if i am sucessful. thanks again

--
raj

"NevilleT" wrote:

> Hi Raj
> I created an audit some time ago. First I created two audit files. One is
> for changed memo fields and the other for everything else. If you need to
> cater for memo fields, you will need a memo field to store the before and
> after. This means a very large file very quickly. I would consider two
> files.
>
> What you need to do is loop through the controls on the form and find which
> one is changed. You can then write some information to the history file..
> This is a cut and paste from my code so there are some other functions called
> but you can probably get the drift. I pass a pile of information to the
> function and you may want to handle it differently.
>
> Post a message if you want more info.
>
> Public Function funLogTrans(frm As Form, _
> intKey As Integer, _
> strFormName As String, _
> strKeyName As String, _
> Optional strOptional As String) _
> As Boolean
> ' Frm is the form passing the information
> ' intKey is the value of the PK
> ' strFormName is the name of the form being
> modified including full path for subforms
> ' strKeyName is the name of the Primary Key
> field in the table e.g. "tblPeople.PersonNo"
> ' strOptional1 is the cancatenated descriptive
> string.
> Dim dbs As DAO.Database
> Dim ctlCtrl As Control
> Dim MyMsg As String
> Dim strHist As String
> Dim lngOldValue As Long
> Dim lngNewValue As Long
>
> ' Loop through controls to find ones that changed
> For Each ctlCtrl In frm.Controls
> If (funActiveCtrl(ctlCtrl)) Then ' Check
> it is an updateable control
> If IsNoOldValue(ctlCtrl) = True Then ' Is
> the oldvalue valid for this control
> If ctlCtrl.Enabled = True Then ' Is
> the control enabled.
> If ((ctlCtrl.Value <> ctlCtrl.OldValue) _
> Or (IsNull(ctlCtrl) And Not IsNull(ctlCtrl.OldValue)) _
> Or (Not IsNull(ctlCtrl) And IsNull(ctlCtrl.OldValue)))
> Then
> lngNewValue = Len(IIf(IsNull(ctlCtrl), 0, ctlCtrl))
> lngOldValue = Len(IIf(IsNull(ctlCtrl.OldValue), 0,
> ctlCtrl.OldValue))
> If lngOldValue > 255 Or lngNewValue > 255 Then
> ' If a memo, write to that table
> strHist = "tblHistMemo"
> ' Memo table
> Else
> strHist = "tblHist"
> ' Non memo table
> End If
>
> ' This function creates new history records
> Call funAddHist(strHist, _
> intKey, _
> strFormName, _
> strKeyName, _
> ctlCtrl, _
> strOptional)
>
> ' strHist = Select which table to
> enter data into
> ' MyKey is the value of the PK
> ' strFormName is the name of the
> form being modified including full path for subforms
> ' strKeyName is the name of the
> Primary Key field in the table e.g. "tblPeople.PersonNo"
> ' ctlCtrl is the control that changed
> ' strOptional1 is the cancatenated
> descriptive string
> End If
> End If
> End If
> End If
> Next ctlCtrl
>
> End Function
>
>
> "raja07" wrote:
>
> > i am trying to create an audit report of only changed fields and i've looked
> > at the code provided here http://support.microsoft.com/default.aspx/kb/197592
> > but instead of returning just the changes i am getting all the fields on the
> > form those whose values have changed and those that didn't change. can
> > someone please help me to grab only those values that have been edited? i
> > have spent all day on this and i could really use some help. thanks
> > --
> > raj

==============================================================================
TOPIC: PDF Loop
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/cfa74507feea73f5?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Nov 15 2007 10:51 am
From: "dhoover via AccessMonster.com"

I have a form with a dropdown box (cboFindCustomer). When a button is
clicked on the form, a report (Customer instructions) is opened and filtered
so that only information for the customer selected in cbofindcustomer appears.

Once the report is opened, i need to be able to print it to a pdf file. I
have adobe instalelled so I already have the print driver. I do not have the
option to switch to another pdf file creator, not can I use leeben's program.
Can someone please help me with the code to print the opened file to a pdf.
thanks!

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

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

== 1 of 1 ==
Date: Thurs, Nov 15 2007 10:54 am
From: Marshall Barton

y770 wrote:

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

You can't! And if you could, it would be a very bad idea
for many reasons.

For a form, you need to put the data in a table and bind the
form to the table. Even then, you may not get a decent
looking display.

For reports, precreate a sufficient number of invisible
controls on an unbound report. Then use the report's Open
event to initialize the array index:
arrayindex = 0

The Detail section's Format event can be used to process
each row in the array one at a time making the appropriate
controls visible/invisible and whatever else you want to do.
The last lines in the event procedure would be like:
Me.NextRecord = arrayindex < UBound(array)
arrayindex = arrayindex +1
to tell the report you are done with an array row and
whether there are more to process or not.

You can do quite a bit with an unbound report, but there are
several features that are useless.

--
Marsh
MVP [MS Access]

==============================================================================
TOPIC: Problem with Filters
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/80452e6befc675a5?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Nov 15 2007 11:11 am
From: bhammer

Solved!

With Access 2000 or later, the Replace function is available (or you
can get sample code to make your own global function from others in
this usergroup). Use it to search your copied Filter string for any
instances of 'Lookup_cbo' (for example) and replace that string bit
with 'tbl'.

For example, if your combobox is named 'cboCompany' and displays the
Company name in the combo (with the CompanyID in the hidden column)
then any filter done by the user that includes that field will have
the string 'Lookup_'cboCompany.Company', and poor old confused Access
throws-up the Enter Parameter dialog asking you to enter a value for
'Lookup_cboCompany.Company', due the fact that there is no table or
query named 'cboCompany'. Solve this by editing the Filter string
using the Replace function.

Change:
'Lookup_cbo.Company.Company', to:
'tblCompany.Company'

That way, Access can find the value. This only works, of course, if
you have the underlying query in the form you filtered from, include
the table 'tblCompany''.

See Access Help for tips on using the Replace function.

==============================================================================
TOPIC: WHERE clause
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/353169f7b14f9b13?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Nov 15 2007 11:51 am
From: JT

I'm trying to use a sql statement to select the Dept and Branch from a table
for a specific customer number and where the groups does not start with 000.
I have tried writing the statement as follows:

vSQL = "SELECT [DEPT],[BR] from [Data] WHERE [CUST_NO] = '" & CSTMR & "'AND
MID([GROUP],1,3)<>""000"")"

However, it is still pulling in all of the groups even those that start with
000.

Can someone tell me what is wrong with this statement. Thank You very much!

--
JT

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

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, 15 November 2007

25 new messages in 13 topics - digest

Tidak ada komentar:

Arsip Blog

Blogeroll

Mengenai Saya