Excel for Life: 21 new messages in 15 topics

microsoft.public.access.modulesdaovba
http://groups.google.com/group/microsoft.public.access.modulesdaovba?hl=en

microsoft.public.access.modulesdaovba@googlegroups.com

Today's topics:

==============================================================================
TOPIC: Getting Procedure Name
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/037f7a39f60699dc?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Nov 15 2007 5:55 pm
From: "Neil"

Thanks. I'll have a look at that.

"bobh" <vulcaned@yahoo.com> wrote in message
news:0b087292-9188-4294-9ca2-ee141220fa45@n20g2000hsh.googlegroups.com...
> On Nov 15, 10:20 am, "Neil" <nos...@nospam.net> wrote:
>> Can I get the name of a procedure from within the procedure? In my error
>> handler, I write the error to an error table. I'd like to write the name
>> of
>> the procedure that's writing the error. But, rather than customizing each
>> error handler with the procedure name, it would be nice to be able to
>> call a
>> system variable or function that gives me the procedure name and module
>> name. Is that possible?
>
> Hi, there is this module I use - originaly from Access97 Developers
> handbook. I have modified it a bit but it still works and I use it in
> all my XP apps today.
> bobh.
>
> Option Compare Database
> Option Explicit
>
> ' From Access 97 Developer's Handbook by Litwin, Getz, and Gilbert.
> (Sybex)
> ' Copyright 1997. All Rights Reserved.
>
> Private Declare Function adh_apiIsClipboardFormatAvailable Lib
> "user32" Alias "IsClipboardFormatAvailable" (ByVal uFormat As Integer)
> As Integer
> Private Declare Function adh_apiOpenClipboard Lib "user32" Alias
> "OpenClipboard" (ByVal hWnd As Long) As Integer
> Private Declare Function adh_apiGetClipboardData Lib "user32" Alias
> "GetClipboardData" (ByVal uFormat As Integer) As Long
> Private Declare Function adh_apiGlobalSize Lib "kernel32" Alias
> "GlobalSize" (ByVal hMem As Long) As Integer
> Private Declare Function adh_apiGlobalLock Lib "kernel32" Alias
> "GlobalLock" (ByVal hMem As Long) As Long
> Private Declare Sub adh_apiMoveMemory Lib "kernel32" Alias
> "RtlMoveMemory" (ByVal strDest As Any, ByVal lpSource As Any, ByVal
> Length As Long)
> Private Declare Function adh_apiGlobalUnlock Lib "kernel32" Alias
> "GlobalUnlock" (ByVal hMem As Long) As Integer
> Private Declare Function adh_apiCloseClipboard Lib "user32" Alias
> "CloseClipboard" () As Integer
> Private Declare Function adh_apiGlobalAlloc Lib "kernel32" Alias
> "GlobalAlloc" (ByVal uFlags As Integer, ByVal dwBytes As Long) As Long
> Private Declare Function adh_apiEmptyClipboard Lib "user32" Alias
> "EmptyClipboard" () As Integer
> Private Declare Function adh_apiSetClipboardData Lib "user32" Alias
> "SetClipboardData" (ByVal uFormat As Integer, ByVal hData As Long) As
> Long
> Private Declare Function adh_apiGlobalFree Lib "kernel32" Alias
> "GlobalFree" (ByVal hMem As Long) As Long
>
> Private Const GMEM_MOVABLE = &H2&
> Private Const GMEM_DDESHARE = &H2000&
> Private Const CF_TEXT = 1
>
> 'Error return codes from Clipboard2Text
> Public Const adhCLIPBOARDFORMATNOTAVAILABLE = 1
> Public Const adhCANNOTOPENCLIPBOARD = 2
> Public Const adhCANNOTGETCLIPBOARDDATA = 3
> Public Const adhCANNOTGLOBALLOCK = 4
> Public Const adhCANNOTCLOSECLIPBOARD = 5
> Public Const adhCANNOTGLOBALALLOC = 6
> Public Const adhCANNOTEMPTYCLIPBOARD = 7
> Public Const adhCANNOTSETCLIPBOARDDATA = 8
> Public Const adhCANNOTGLOBALFREE = 9
>
> Public Sub ErrorHandler()
> 'This routine will automatically add error handling to the routine
> selected if executed.
> 'To get it to work properly (as currently set up) have the name of the
> routine selected
> 'when you right-mouse click and run this from the shortcut menu.
> '
> 'This routine has been modified by Ron Allard and Bob Hynes to improve
> the error message display.
> '
> 'Adding a command to the toolbar shortcut popup menu while in vba page
> design.
> 'Click on the View menu bar and choose Toolbars>Customize. In the
> Toolbars tab of
> 'Customize dialog, display the Shortcut Menus by checking the box next
> to it (it's the last in the list).
> 'After you see the "Shortcut Menus" displayed, move to the Commands
> tab in the Customize dialog.
> 'In the File category, click on "Custom" and drag it over the
> "Shortcut Menus" bar.
> 'Hover over the Module menu and when it drops down, go into "Module
> Uncompiled"
> 'Now you can drop the Custom item wherever you want it to be located.
> 'Right click the Custom item you just dropped and name it (right click
> to pull up its properties).
> 'Then click on the "properties" option at the bottom of the dialog
> box. On the next dialog all you need
> 'to do is type in the "On Action" data. The name of the function you
> want it to run " =ErrorHandler() "
> '
> Dim strRoutineName As String, strRoutineType As String
> Dim str3Letters As String, strTitle As String, str As String
>
> SendKeys "(^c)" ' capture name of
> routine
> DoEvents ' allow clipboard
> to note the new text
> 'MsgBox Clipboard.GetText()
> strRoutineName = ClipboardGetText("")
> SendKeys "(^{LEFT})(^{LEFT})"
> SendKeys "+{RIGHT}+{RIGHT}+{RIGHT}"
> SendKeys "(^c)" ' capture 3
> letters of word
> DoEvents ' allow
> clipboard to note the new text
> str3Letters = ClipboardGetText("")
> Select Case str3Letters
> Case "Sub"
> strRoutineType = "Sub"
> Case "Fun"
> strRoutineType = "Function"
> Case "Get", "Let", "Set"
> strRoutineType = "Property"
> Case Else
> strRoutineType = "What is this?"
> End Select
>
> strTitle = strRoutineType & " - " & strRoutineName & " in "
> str = "{END}~" & "On Error GoTo Err_" & strRoutineName _
> & "~Exit_" & strRoutineName & ":~{TAB} Exit " & strRoutineType
> _
> & "~Err_" & strRoutineName & ":~MsgBox Err.Number " & "& "" -
> "" &" & " Err.Description" & ",," & """" & strTitle & """" & " & " &
> "Me.Name" _
> & "~Resume Exit_" & strRoutineName & "~"
> SendKeys str
> str = "{RIGHT}+{DOWN}(^x){up 4}(^v){UP 3}(^v){UP}~{TAB}"
> SendKeys str
>
> End Sub
>
> Public Function ClipboardGetText(ByVal strFailureString As String) As
> String
> ' Purpose: Gets some text from the Windows clipboard
> ' Params:
> ' strFailureString: the string to return if the function fails to
> get a string from the Clipboard
> ' Returns: either the string in the Clipboard or the default failure
> string (strFailureString)
> ' Note: This routine calls the Access 97 Developer Handbook routine
> adhClipboardGetText().
> ' adhClipboardGetText returns a variant, and what is needed is a
> string. This routine ensures
> ' the return of a string.
> On Error Resume Next
> Dim varReturnValue As Variant
> varReturnValue = adhClipboardGetText()
> ClipboardGetText = CStr(varReturnValue)
> If err.Number <> 0 Then ClipboardGetText = strFailureString
>
> End Function
>
> Function adhClipboardSetText(strText As String) As Variant
> ' Puts some text on the Windows clipboard
> ' From Access 97 Developer's Handbook by Litwin, Getz, and Gilbert.
> (Sybex)
> ' Copyright 1997. All Rights Reserved.
> ' In:
> ' The text to place on the clipboard
> ' Out:
> ' If IsError returns true, then the value
> ' is an error number. If IsError is false
> ' the value is meaningless.
>
> Dim varRet As Variant
> Dim fSetClipboardData As Boolean
> Dim hMemory As Long
> Dim lpMemory As Long
> Dim lngSize As Long
>
> varRet = False
> fSetClipboardData = False
>
> ' Get the length, including one extra for a CHR$(0) at the end.
> lngSize = Len(strText) + 1
> hMemory = adh_apiGlobalAlloc(GMEM_MOVABLE, lngSize)
> If Not CBool(hMemory) Then
> varRet = CVErr(adhCANNOTGLOBALALLOC)
> GoTo adhClipboardSetTextDone
> End If
> ' Lock the object into memory
> lpMemory = adh_apiGlobalLock(hMemory)
> If Not CBool(lpMemory) Then
> varRet = CVErr(adhCANNOTGLOBALLOCK)
> GoTo adhClipboardSetTextGlobalFree
> End If
> ' Move the string into the memory we locked
> Call adh_apiMoveMemory(lpMemory, strText, lngSize)
> ' Don't send clipboard locked memory.
> Call adh_apiGlobalUnlock(hMemory)
> ' Open the clipboard
> If Not CBool(adh_apiOpenClipboard(0&)) Then
> varRet = CVErr(adhCANNOTOPENCLIPBOARD)
> GoTo adhClipboardSetTextGlobalFree
> End If
> ' Remove the current contents of the clipboard
> If Not CBool(adh_apiEmptyClipboard()) Then
> varRet = CVErr(adhCANNOTEMPTYCLIPBOARD)
> GoTo adhClipboardSetTextCloseClipboard
> End If
> ' Add our string to the clipboard as text
> If Not CBool(adh_apiSetClipboardData(CF_TEXT, hMemory)) Then
> varRet = CVErr(adhCANNOTSETCLIPBOARDDATA)
> GoTo adhClipboardSetTextCloseClipboard
> Else
> fSetClipboardData = True
> End If
>
> adhClipboardSetTextCloseClipboard:
> ' Close the clipboard
> If Not CBool(adh_apiCloseClipboard()) Then
> varRet = CVErr(adhCANNOTCLOSECLIPBOARD)
> End If
>
> adhClipboardSetTextGlobalFree:
> If Not fSetClipboardData Then
> 'If we have set the clipboard data, we no longer own
> ' the object--Windows does, so don't free it.
> If CBool(adh_apiGlobalFree(hMemory)) Then
> varRet = CVErr(adhCANNOTGLOBALFREE)
> End If
> End If
>
> adhClipboardSetTextDone:
> adhClipboardSetText = varRet
>
> End Function
>
> Public Sub adhTestClipboard()
> ' Tests putting some text on the clipboard then reading it off again
> ' From Access 97 Developer's Handbook by Litwin, Getz, and Gilbert.
> (Sybex)
> ' Copyright 1997. All Rights Reserved.
> ' Example:
> ' Call adhTestClipboard
>
> Dim varRet As Variant
>
> varRet = adhClipboardSetText("This is a test")
> If IsError(varRet) Then
> Call adhReportClipboardError(CInt(varRet))
> Else
> varRet = adhClipboardGetText()
> If IsError(varRet) Then
> Call adhReportClipboardError(CInt(varRet))
> Else
> MsgBox varRet
> End If
> End If
>
> End Sub
>
> Public Sub adhReportClipboardError(ByVal intError As Integer)
> ' Reports an error received from the clipboard
> ' From Access 97 Developer's Handbook by Litwin, Getz, and Gilbert.
> (Sybex)
> ' Copyright 1997. All Rights Reserved.
> ' Example:
> ' Call adhReportClipboardError(CInt(varRet))
>
> Select Case CInt(intError)
> Case adhCLIPBOARDFORMATNOTAVAILABLE
> MsgBox "Clipboard format not available"
> Case adhCANNOTOPENCLIPBOARD
> MsgBox "Cannot open clipboard"
> Case adhCANNOTGETCLIPBOARDDATA
> MsgBox "Cannot get clipboard data"
> Case adhCANNOTGLOBALLOCK
> MsgBox "Cannot global lock data"
> Case adhCANNOTCLOSECLIPBOARD
> MsgBox "Cannot close clipboard"
> Case adhCANNOTGLOBALALLOC
> MsgBox "Cannot global alloc"
> Case adhCANNOTEMPTYCLIPBOARD
> MsgBox "Cannot empty clipboard"
> Case adhCANNOTSETCLIPBOARDDATA
> MsgBox "Cannot set clipboard data"
> Case adhCANNOTGLOBALFREE
> MsgBox "Cannot global free"
> Case Else
> MsgBox "From module function - adhReportClipboardError",
> vbExclamation, "I'm stuck!"
> End Select
>
> End Sub
>
> Public Function adhClipboardGetText() As Variant
> ' Gets some text on the Windows clipboard
> ' From Access 97 Developer's Handbook by Litwin, Getz, and Gilbert.
> (Sybex)
> ' Copyright 1997. All Rights Reserved.
> ' Out:
> ' The text on the clipboard.
> ' If IsError returns true, then the value
> ' is an error number
>
> Dim hMemory As Long
> Dim lpMemory As Long
> Dim strText As String
> Dim lngSize As Long
> Dim varRet As Variant
>
> varRet = ""
>
> ' Is there text on the clipboard? If not, error out.
> If Not CBool(adh_apiIsClipboardFormatAvailable(CF_TEXT)) Then
> varRet = CVErr(adhCLIPBOARDFORMATNOTAVAILABLE)
> GoTo adhClipboardGetTextDone
> End If
>
> ' Open the clipboard
> If Not CBool(adh_apiOpenClipboard(0&)) Then
> varRet = CVErr(adhCANNOTOPENCLIPBOARD)
> GoTo adhClipboardGetTextDone
> End If
>
> ' Get the handle to the clipboard data
> hMemory = adh_apiGetClipboardData(CF_TEXT)
> If Not CBool(hMemory) Then
> varRet = CVErr(adhCANNOTGETCLIPBOARDDATA)
> GoTo adhClipboardGetTextCloseClipboard
> End If
>
> ' Find out how big it is and allocate enough space
> ' in a string
> lngSize = adh_apiGlobalSize(hMemory)
> strText = Space$(lngSize)
>
> ' Lock the handle so we can use it
> lpMemory = adh_apiGlobalLock(hMemory)
> If Not CBool(lpMemory) Then
> varRet = CVErr(adhCANNOTGLOBALLOCK)
> GoTo adhClipboardGetTextCloseClipboard
> End If
>
> ' Move the information from the clipboard memory
> ' into our string
> Call adh_apiMoveMemory(strText, lpMemory, lngSize)
>
> ' Truncate it at the first Null character because
> ' the value reported by lngSize is erroneously large
> strText = left$(strText, InStr(1, strText, Chr$(0)) - 1)
>
> ' Free the lock
> Call adh_apiGlobalUnlock(hMemory)
>
> adhClipboardGetTextCloseClipboard:
> ' Close the clipboard
> If Not CBool(adh_apiCloseClipboard()) Then
> varRet = CVErr(adhCANNOTCLOSECLIPBOARD)
> End If
>
> adhClipboardGetTextDone:
> If Not IsError(varRet) Then
> adhClipboardGetText = strText
> Else
> adhClipboardGetText = varRet
> End If
>
> End Function

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

== 1 of 1 ==
Date: Thurs, Nov 15 2007 6:03 pm
From: "pietlinden@hotmail.com"

On Nov 13, 6:26 am, "Ken Snell $MVP$"
<kthsneisll...@ncoomcastt.renaetl> wrote:
> Yoiu're on the right track, but your code approach has some coding errors...
> additionally, using the "evaluate parameters" approach assumes that you're
> going to open a recordset based on that query after you resolve the
> parameters, which you're not doing. I don't see any parameters in your
> desired SQL statement, either, so I suggest a completely different approach
> where you just build the SQL statement and then execute it:
>
> 'Start of code
> Public Sub TestQuery(ByVal strFieldName As String)
> Dim dbs As DAO.Database
> Dim strSQL As String, strTemp As String
>
> Set dbs = CurrentDb
>
> ' Create the desired SQL statement, starting from
> ' the stored query's SQL statement
> strSQL = dbs.QueryDefs("qapp Anemia").SQL
> strSQL = Replace(strSQL, "Anemia", strFieldName)
> dbs.Execute strSQL, dbFailOnError
> dbs.Close
> Set dbs = Nothing
> Exit Sub
> End Sub
> 'End of code
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
> <pietlin...@hotmail.com> wrote in message
>
> news:1194911270.831346.281200@v2g2000hsf.googlegroups.com...
>
>
>
> >I read this article... apparently I didn't understand it as well as I
> > thought I did...
> >http://www.mvps.org/access/queries/qry0013.htm
>
> > What I'm doing... (in case it helps).
>
> > I have a seriously denormalized table, that looks like this:
>
> > CREATE TABLE Induction(
> > RecordID Autonumber,
> > ANEMIA Number,
> > NEUTROPENIA Number,
> > ....
> > )
>
> > All the symptoms are in all caps (in case it matters). So I can
> > identify all those with a function.
>
> > because I have to normalize this mess, I'm inserting records into a
> > normalized table.
>
> > (PatientID, Symptom, Grade, CycleNumber, Phase)
>
> > so I created a single append query and then used Replace to modify the
> > SQL.
>
> > Here's the routine to do the replace...
> > Public Sub TestQuery(ByVal strFieldName As String)
> > Dim strSQL As String
> > Dim strNewSQL As String
> > Dim qdf As DAO.QueryDef
> > Dim qdfNew As DAO.QueryDef
> > Dim prm As DAO.Parameter
>
> > Set qdf = DBEngine(0)(0).QueryDefs("qapp Anemia")
> > strSQL = qdf.SQL
>
> > strNewSQL = Replace(qdf.SQL, "Anemia", strFieldName)
> > Debug.Print "Old SQL:"
> > Debug.Print qdf.SQL
> > Debug.Print
> > Debug.Print "New SQL"
> > Debug.Print strNewSQL
> > 'CurrentDb.Execute strNewSQL, dbFailOnError
> > Set qdfNew = New QueryDef
> > qdfNew.SQL = strNewSQL
>
> > For Each prm In qdfNew.Parameters
> > prm.Value = Eval(prm.Name)
> > Next prm
>
> > qdf.Execute
> > Debug.Print qdf.RecordsAffected
> > End Sub
>
> > here's a sample output of the "TestQuery" function:
> > testquery "Allopecia"
> > Old SQL:
> > INSERT INTO HasToxicity ( [Record#], [Patient Initials], [Pt
> > Identifier], [Cycle#], Symptom, Grade )
> > SELECT [Toxicity Induction].[Record#], [Toxicity Induction].[Patient
> > Initials], [Toxicity Induction].[Pt Identifier], [Toxicity Induction].
> > [Cycle#], "Anemia" AS Symptom, [Toxicity Induction].ANEMIA
> > FROM [Toxicity Induction]
> > WHERE ((([Toxicity Induction].ANEMIA) Is Not Null));
>
> > '---routine returns this....
> > New SQL
> > INSERT INTO HasToxicity ( [Record#], [Patient Initials], [Pt
> > Identifier], [Cycle#], Symptom, Grade )
> > SELECT [Toxicity Induction].[Record#], [Toxicity Induction].[Patient
> > Initials], [Toxicity Induction].[Pt Identifier], [Toxicity Induction].
> > [Cycle#], "Allopecia" AS Symptom, [Toxicity Induction].Allopecia
> > FROM [Toxicity Induction]
> > WHERE ((([Toxicity Induction].Allopecia) Is Not Null));
>
> > when I try to run the query, I get "Too few parameters, expected
> > n" (see the article).
>
> > Even with the parameters supposedly getting evaluated, it still
> > returns the same error. What am I doing wrong?
>
> > It probably sounds like a lot of work to fix a few queries, but I have
> > lots of databases that are not normalized, so I'm trying to create a
> > routine that will automate most of the dirty work for me. so that's
> > why I'm doing all this in code...
>
> > Any ideas what I'm doing wrong? (Maybe I just need to take a walk or
> > something... )
>
> > Thanks for the help,
>
> > Pieter- Hide quoted text -
>
> - Show quoted text -

OIC!!! Eye gnu about that... but I was such a dumb beast that I
didn't see it...

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

== 1 of 2 ==
Date: Thurs, Nov 15 2007 6:14 pm
From: "Russ Hromyko"

Can anyone tell me why this is happening, when I use an actual number, 47731
for the [Hours] everything works, but when I use Me.cboHours I get an error
message? What am I missing and how do I make Me.cboHours work? When I hit
debug on the error message it points to the recordset (Set rs = ). Below
the dotted line is not part of the code, it's the code I want to work.

Private Sub cboHours_AfterUpdate()
Dim sqlStr As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb

sqlStr = "SELECT * FROM dbo_LaserMachineMaintLog WHERE [Laser Machine] =
" & _
"'" & Me.cboLaserMachine & "' " & "AND [Hours] = 47731"

Set rs = db.OpenRecordset(sqlStr, dbOpenDynaset, dbSeeChanges)

'---------------------------------------------------------------------------------------------------------------------------------------------

sqlStr = "SELECT * FROM dbo_LaserMachineMaintLog WHERE [Laser Machine] = " &
_
"'" & Me.cboLaserMachine & "' " & "AND [Hours] = " & Me.cboHours

Set rs = db.OpenRecordset(sqlStr, dbOpenDynaset, dbSeeChanges)

== 2 of 2 ==
Date: Thurs, Nov 15 2007 6:23 pm
From: "Dirk Goldgar"

In news:K17%i.14782$h61.10123@trndny02,
Russ Hromyko <rhromyko@verizon.net> wrote:
> Can anyone tell me why this is happening, when I use an actual
> number, 47731 for the [Hours] everything works, but when I use
> Me.cboHours I get an error message? What am I missing and how do I
> make Me.cboHours work? When I hit debug on the error message it
> points to the recordset (Set rs = ). Below the dotted line is not
> part of the code, it's the code I want to work.
>
> Private Sub cboHours_AfterUpdate()
> Dim sqlStr As String
> Dim db As DAO.Database
> Dim rs As DAO.Recordset
>
> Set db = CurrentDb
>
> sqlStr = "SELECT * FROM dbo_LaserMachineMaintLog WHERE [Laser
> Machine] = " & _
> "'" & Me.cboLaserMachine & "' " & "AND [Hours] = 47731"
>
> Set rs = db.OpenRecordset(sqlStr, dbOpenDynaset, dbSeeChanges)
>
> '---------------------------------------------------------------------------------------------------------------------------------------------
>
>
> sqlStr = "SELECT * FROM dbo_LaserMachineMaintLog WHERE [Laser
> Machine] = " & _
> "'" & Me.cboLaserMachine & "' " & "AND [Hours] = " &
> Me.cboHours
> Set rs = db.OpenRecordset(sqlStr, dbOpenDynaset, dbSeeChanges)

What error message are you getting, and what are the values of cboHours
and sqlStr when you get it?

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

(please reply to the newsgroup)

==============================================================================
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 6:16 pm
From: "Dirk Goldgar"

In news:1D1355AA-6C3C-44B2-B794-E3A3EABBBC88@microsoft.com,
JT <JT@discussions.microsoft.com> wrote:
> 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!

I see a couple of things wrong there: (1) you need a space before the
keyword "AND", and (2) you have an unmatched closing parenthesis. I'm
not convinced you're ever actually executing this statement, because it
ought to give you a syntax error, not just return all groups. Check to

Try this and see if it works:

vSQL = _
"SELECT [DEPT],[BR] from [Data] " & _
"WHERE [CUST_NO] = '" & CSTMR & _
"' AND [GROUP] Not Like '000*'"

If that doesn't work, check to make sure that's actually the statement
being executed.

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

(please reply to the newsgroup)

==============================================================================
TOPIC: Relationship help
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/b299b2eab38b9b1c?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Nov 15 2007 6:28 pm
From: hogan

I have an inventory database with a excel spreadsheet linked to. The
spreadsheet has the actual inventory items. I have a "packages" table showing
who, when, where and the linked table is the what. The primary key for the
Packages table is PackageID. There is also a packageid in the linked table. I
have a form for the packages and a subform for the inventory items. How can I
show the inventory items in the subform that have the same packageid as the
packages form. Can this be done with code.

==============================================================================
TOPIC: ADO
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/5bf1dcd64c6cb7b9?hl=en
==============================================================================

== 1 of 2 ==
Date: Thurs, Nov 15 2007 6:42 pm
From: Weste

We have an application with Access 2003 for the front-end and SQL Server 2005
for the back-end. Is performance decreased when using
CurrentProject.Connection to connect to the database vs suppling the
connection string for the connection object? The application is not an ADP -
it is an mdb. The SQL Server tables are linked in the front-end mdb. When
we query the tables using the CurrentProject.Connection, are all the records
in the tables returned even if there is filtering done using the WHERE clause
since the tables are linked via ODBC? Thanks for your help.

== 2 of 2 ==
Date: Thurs, Nov 15 2007 8:35 pm
From: Tom van Stiphout

On Thu, 15 Nov 2007 18:42:02 -0800, Weste
<Weste@discussions.microsoft.com> wrote:

The connection string has something to do with performance, but
typically not very much.

On your second question I'm really in the dark as to what you're
talking about. With attached tables ALL queries are executed locally.
However, Access is smart enough when the opportunity arises to only
download entire indexes to the local machine rather than entire
tables.
SQL Passthrough Queries don't suffer from this performance
degradation.

-Tom.

>We have an application with Access 2003 for the front-end and SQL Server 2005
>for the back-end. Is performance decreased when using
>CurrentProject.Connection to connect to the database vs suppling the
>connection string for the connection object? The application is not an ADP -
>it is an mdb. The SQL Server tables are linked in the front-end mdb. When
>we query the tables using the CurrentProject.Connection, are all the records
>in the tables returned even if there is filtering done using the WHERE clause
>since the tables are linked via ODBC? Thanks for your help.

==============================================================================
TOPIC: Message Box
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/37d28d4a8a1fe703?hl=en
==============================================================================

== 1 of 2 ==
Date: Thurs, Nov 15 2007 7:03 pm
From: MarkS

Hi,
I have a group of querys which run one after the other and some VB code to
get around a timeout issue. As it takes awhile to run I would like to put a
message box on the screen to say where it is up to.

Thanks

== 2 of 2 ==
Date: Thurs, Nov 15 2007 7:22 pm
From: "Dirk Goldgar"

In news:B9FAC4A3-3B63-4A9E-9F28-EB9448FA05C8@microsoft.com,
MarkS <MarkS@discussions.microsoft.com> wrote:
> Hi,
> I have a group of querys which run one after the other and some VB
> code to get around a timeout issue. As it takes awhile to run I would
> like to put a message box on the screen to say where it is up to.

I'd use a form for this. Have a label or text box on the form that will
display your progress message. Open the form as you begin your process,
update the message (label caption or text box value) on the form as you
go along, and close the form when you're done.

One thing you may need to do, depending on what your code is doing, is
execute the DoEvents statement after you update the message on the form.
You may not have to, but sometimes tight loops can prevent the form from
being redrawn unless you put in a DoEvents.

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

(please reply to the newsgroup)

==============================================================================
TOPIC: Exporting Query to Excel - Multiple Files
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/4bcf072eba3c3922?hl=en
==============================================================================

== 1 of 2 ==
Date: Thurs, Nov 15 2007 7:58 pm
From: andrewbecks

On Nov 15, 5:30 pm, "Ken Snell $MVP$"
<kthsneisll...@ncoomcastt.renaetl> wrote:
> Here's some generic code that will get you started:
>
> Generic code to create a temporary query, get list of
> filtering values, and then loop through the list to filter
> various data and export each filtered query to separate
> EXCEL files
> ----------------------------------------------------------
>
> 'Start of code
> Dim qdf As DAO.QueryDef
> Dim dbs As DAO.Database
> Dim rstMgr As DAO.Recordset
> Dim strSQL As String, strTemp As String, strMgr As String
>
> Const strQName As String = "zExportQuery"
>
> Set dbs = CurrentDb
>
> ' Create temporary query that will be used for exporting data;
> ' give it a dummy SQL statement initially
> strTemp = dbs.TableDefs(0).Name
> strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
> Set qdf = dbs.CreateQueryDef(strQName, strSQL)
> qdf.Close
> strTemp = strQName
>
> ' *** code to set strSQL needs to be changed to conform to your
> ' *** database design -- ManagerID, EmployeesTable need to
> ' *** be changed to your table and field names
> ' Get list of manager IDs -- note: replace my generic table and field
> names
> ' with the real names of the employees table and the manager ID field
> strSQL = "SELECT DISTINCT ManagerID FROM EmployeesTable;"
> Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
>
> ' Now loop through list of manager IDs and create a query for each ID
> ' so that the data can be exported -- the code assumes that the actual
> names
> ' of the managers are in a lookup table -- again, replace generic names
> with
> ' real names of tables and fields
> If rstMgr.EOF = False And rstMgr.BOF = False Then
> rstMgr.MoveFirst
> Do While rstMgr.EOF = False
> ' *** code to set strMgr needs to be changed to conform to your
> ' *** database design -- ManagerNameField, ManagersTable,
> ' *** ManagerID need to be changed to your table and field names
> ' *** be changed to your table and field names
> strMgr = DLookup("ManagerNameField", "ManagersTable", _
> "ManagerID = " & rstMgr!ManagerID.Value)
> ' *** code to set strSQL needs to be changed to conform to your
> ' *** database design -- ManagerID, EmployeesTable need to
> ' *** be changed to your table and field names
> strSQL = "SELECT * FROM EmployeesTable WHERE " & _
> "ManagerID = " & rstMgr!ManagerID.Value & ";"
> Set qdf = dbs.QueryDefs(strTemp)
> qdf.Name = "q_" & strMgr
> strTemp = qdf.Name
> qdf.SQL = strSQL
> qdf.Close
> Set qdf = Nothing
> ' Replace C:\FolderName\ with actual path
> DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
> strTemp, "C:\FolderName\" & strMgr & Format(Now(), _
> "ddMMMyyy_hhnn") & ".xls"
> rstMgr.MoveNext
> Loop
> End If
>
> rstMgr.Close
> Set rstMgr = Nothing
>
> dbs.QueryDefs.Delete strTemp
> dbs.Close
> Set dbs = Nothing
> 'End of code
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
> "andrewbecks" <andrewbe...@gmail.com> wrote in message
>
> news:e695d86d-fa07-496d-adba-7a7bbf418b8e@o6g2000hsd.googlegroups.com...
>
>
>
> > Hello. I am looking for some help in exporting a query to Excel.
>
> > Currently, I have a query that has four columns:
>
> > -District_Manager
> > -Store_Number
> > -Fiscal_Week
> > -Sales_Dollars
>
> > There are approx. 200 different District Managers, and each District
> > Manager has about 10 lines of data. What I would love to do is have a
> > seperate excel file generated for each District Manager, that includes
> > only his or her information. I'd like the Excel files to have the name
> > of the District Manager.
>
> > So, for District Manager Joe Example, an excel file named [Joe
> > Example.xls] would be created that includes the above query but only
> > includes row where the value for District Manager is Joe Example.
>
> > Any assistance with this would be GREATLY appreciate.
>
> > Andrew- Hide quoted text -
>
> - Show quoted text -

Hi. Thanks so much for your help with this code. I've spent a couple
of hours playing around with it and I keep getting stuck around this
section:

strMgr = DLookup("ManagerNameField", "ManagersTable", _
"ManagerID = " & rstMgr!ManagerID.Value)

Anyway, I was wondering if you could provide any additional
assistance. In the code you provided, it appears as if it wants the
Manager name to be sitting in a seperate table. What if, I simply want
it to find the manager name from the existing table.

Let's say that there is only one table, called sample_table. And in
sample_table, there are three fields:
maanger_name, store_number, and sales_amount

Now, let's say that there are 20 different managers whose names appear
within the manager_name field through out the table and that each
manager (represented by manager_name), had 15-20 records. Is it
possible to filter for each manager_name and export all records to a
spreadhseet, and then move onto the next manager and do the same
thing. Each time, I'd like the XLS file to be named manager_name value
+ .xls.

Thanks again for your help.

== 2 of 2 ==
Date: Thurs, Nov 15 2007 8:57 pm
From: "Ken Snell $MVP$"

In that case, the code would be changed slightly so that you do not need to
use the DLookup function. That step was there because the original question
by a poster, for which I'd written this code, included the desire to use the
full name of the manager in the filename, but the data being filtered
contained a field for the manager ID value, so it was necessary to look up
the full name for the export.

Post the code that you have "created" from the code example, and I will
assist in making the additional modifications to it.

Ken Snell
<MS ACCESS MVP>

"andrewbecks" <andrewbecks@gmail.com> wrote in message
news:591c312f-d2c1-486c-bafb-dbc249a37a7c@l1g2000hsa.googlegroups.com...
>
> Hi. Thanks so much for your help with this code. I've spent a couple
> of hours playing around with it and I keep getting stuck around this
> section:
>
> strMgr = DLookup("ManagerNameField", "ManagersTable", _
> "ManagerID = " & rstMgr!ManagerID.Value)
>
> Anyway, I was wondering if you could provide any additional
> assistance. In the code you provided, it appears as if it wants the
> Manager name to be sitting in a seperate table. What if, I simply want
> it to find the manager name from the existing table.
>
> Let's say that there is only one table, called sample_table. And in
> sample_table, there are three fields:
> maanger_name, store_number, and sales_amount
>
> Now, let's say that there are 20 different managers whose names appear
> within the manager_name field through out the table and that each
> manager (represented by manager_name), had 15-20 records. Is it
> possible to filter for each manager_name and export all records to a
> spreadhseet, and then move onto the next manager and do the same
> thing. Each time, I'd like the XLS file to be named manager_name value
> + .xls.
>
> Thanks again for your help.
>

==============================================================================
TOPIC: Compact & repair cuts connection to networked mdb
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/6e4ecf532a8cff9d?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Nov 15 2007 8:44 pm
From: Tom van Stiphout

On Thu, 15 Nov 2007 15:01:01 -0800, DMainland
<DMainland@discussions.microsoft.com> wrote:

You have a single MDB with the tables and forms etc.?
That's a not-recommended configuration, and you will suffer side
effects, like the one you are reporting.
Split your database in front-end and back-end.

-Tom.

>Whenever I do a compact and repair of an Access2K mdb I share over a peer to
>peer network, one or two PCS lose the connection to the file. The only way I
>seem to be able to re-establish the connection is to go to the PC the file is
>on and move it to a new folder and create new shortcuts on all the other
>networked PCs. What is going on? Any help would be greatly appreciated.

==============================================================================
TOPIC: VBA future in MS ACCESS (or VSTA)
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/027c7233fa631b2e?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Nov 15 2007 9:23 pm
From: Eric Decker

"Eric Decker" wrote:

> Has anyone heard if VSTA will ever be supported for MS ACCESS? I've heard
> rumors that VBA will not be supported for 64 bit and the VSTA will. Will MS
> Access someday support VSTA then? Thanks

Yes, VSTA is Visual Studio Tools for Applications and 64 bit is Windows 64
bit OS. I ask the question in an effort to understand if ever MS Access will
be able to run .Net natively and not as an office addin. I would love to
leverage .Net business objects or be able to use MS Access as the front end
to an N Tiered (not classical 2 tier client/server) architecture. I know I
can do this with some effort through a .Net Plug-in but it is not very
cleanly.

==============================================================================
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 9:23 pm
From: "Tony Toews [MVP]"

"dhoover via AccessMonster.com" <u26355@uwe> wrote:

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

Adobe likely have some API code you can use but you'll need to poke
about on their website. Look for Visual Basic (not .NET) as that will
usually work inside Access.

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: passing control names as parameters
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/19160a0c734f2eef?hl=en
==============================================================================

== 1 of 3 ==
Date: Thurs, Nov 15 2007 9:51 pm
From: waynemb

I'm trying to pass different controls on an open form to an Access2003
function:

function (MyControlName as ???)

Forms!MyForm!MyControlName = "whatever"

end function

Thanks

== 2 of 3 ==
Date: Thurs, Nov 15 2007 10:18 pm
From: "Allen Browne"

You can pass a control name as a string if you wish:
Function AssignToControl(strFormName As String, strControlName As
String, varValue As Variant)
Forms(strFormName).Controls(strControlName) = varValue
End Function
Then use it like this:
Call AssignToControl("Form1", "Text0", 99)

That won't work with subforms, since the subform is not open in its own
right (i.e. it is not in the Forms collection.) Consequently, it might be
better to pass a reference to the control itself instead of using the form
and control names:
Function AssignToControl(ctl As Control, varValue As Variant)
ctl = varValue
End Function
And use it like this:
Call AssignToControl(Me.Text0, 99)

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

"waynemb" <waynemb@discussions.microsoft.com> wrote in message
news:52CAB466-C7EB-4C36-AB21-A44460EADEE7@microsoft.com...
> I'm trying to pass different controls on an open form to an Access2003
> function:
>
> function (MyControlName as ???)
>
> Forms!MyForm!MyControlName = "whatever"
>
> end function
>
> Thanks

== 3 of 3 ==
Date: Thurs, Nov 15 2007 11:26 pm
From: waynemb

Thanks a million, that works perfectly.

"Allen Browne" wrote:

> You can pass a control name as a string if you wish:
> Function AssignToControl(strFormName As String, strControlName As
> String, varValue As Variant)
> Forms(strFormName).Controls(strControlName) = varValue
> End Function
> Then use it like this:
> Call AssignToControl("Form1", "Text0", 99)
>
> That won't work with subforms, since the subform is not open in its own
> right (i.e. it is not in the Forms collection.) Consequently, it might be
> better to pass a reference to the control itself instead of using the form
> and control names:
> Function AssignToControl(ctl As Control, varValue As Variant)
> ctl = varValue
> End Function
> And use it like this:
> Call AssignToControl(Me.Text0, 99)
>
> --
> 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.
>
> "waynemb" <waynemb@discussions.microsoft.com> wrote in message
> news:52CAB466-C7EB-4C36-AB21-A44460EADEE7@microsoft.com...
> > I'm trying to pass different controls on an open form to an Access2003
> > function:
> >
> > function (MyControlName as ???)
> >
> > Forms!MyForm!MyControlName = "whatever"
> >
> > end function
> >
> > Thanks
>
>

==============================================================================
TOPIC: Is there an utility.wlib_AutoDial replacement?
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/988f324f3ac928d8?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Nov 15 2007 10:53 pm
From: "Stuart McCall"

"Tony Toews [MVP]" <ttoews@telusplanet.net> wrote in message
news:lrspj35j1knrcm6dga30uld5707ovpjn0d@4ax.com...
> "Stuart McCall" <smccall@myunrealbox.com> wrote:
>
> Thanks for that. Once a year or so we see a similar request and I've
> always wanted to have another solution. However I've been to lazy to
> do any searching.
>
> Tony

No problem. Back when I was experimenting with various dialling methods, I
pasted this code into a module and ran it. It worked "right out of the box".
I never ended up using it though, as my client was satisfied with the way
tapiRequestMakeCall behaved.

==============================================================================
TOPIC: no option selected
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/7b63dc9497a0b74c?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Nov 15 2007 11:12 pm
From: "tina"

open the form in Design view, click on the option group "frame" to select
it. in the Properties box, go to the Default line. type a number 1 in the
line, and press the Down arrow on the keyboard once. press the Up arrow
once; the number 1 should be highlighted - DON'T touch it with the mouse.
press the Delete key on the keyboard to delete the 1 value on the Default
property line. press the Down arrow on the keyboard once. save the form and
close it.

hth

"MKM" <MKM@discussions.microsoft.com> wrote in message
news:ED263981-B6BD-47F9-AF89-83B9A6FE7682@microsoft.com...
> I should have mentioned...I already tried setting the Default Value of the
> frame to Null (literally typed in) and I also tried deleting the contents
of
> the Default property field in the property box. I also tried setting the
> default value to "". When the form opens it always has option 1 selected.
> Maybe setting it in the OnOpen event?
>
> "tina" wrote:
>
> > in A2003 and prior, you would remove the Default value in the option
control
> > (the "frame").
> >
> > hth
> >
> >
> > "MKM" <MKM@discussions.microsoft.com> wrote in message
> > news:098A0A17-B622-4076-BA5B-2B18C19E5C15@microsoft.com...
> > > I have two option btns in an option group. When the form opens I want
> > > neither selected. What property controls that? is it a frame
property or
> > a
> > > property of each btn? I'm using Access 2007, but it must be the same
for
> > > earlier versions...maybe!
> >
> >
> >

==============================================================================
TOPIC: releasing mailing data cource
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/80e18b565603c4b1?hl=en
==============================================================================

== 1 of 1 ==
Date: Thurs, Nov 15 2007 11:52 pm
From: judith

I copy a table from my current database over to an independant database
(mailingBaseDB) and then mailmerge this data. When I have executed the
mailmerge I close the merge document but it leaves the database
(mailingBaseDB). I am using Office 2000

' Set the mail merge data source
MergeFileName.MailMerge.OpenDataSource _
Name:=docPath & "\MailingBaseDB.mdb", _
Connection:="TABLE mailMergeBaseGeneral", _
SQLStatement:="SELECT * FROM [mailMergeBaseGeneral]"

' Execute the mail merge.
Set closeFileName = MergeFileName
MergeFileName.MailMerge.Execute
closeFileName.Close wdDoNotSaveChanges

I tried closing the database with code but that didnt seem to work

' Close mailingDB
Set mergeInfo = OpenDatabase(docPath & "\MailingBaseDB.mdb")
mergeInfo.Close

Any suggestions please

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

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

Jumat, 16 November 2007

21 new messages in 15 topics - digest

Tidak ada komentar:

Arsip Blog

Blogeroll

Mengenai Saya