FoxPro eMail Newsletter
-----Original Message-----
From: Capital PC User Group Board of Directors Communications
[mailto:CPCUGBOD-L@CPCUG.ORG] On Behalf Of Peg Ireland
Sent: Monday, May 04, 1998 1:35 PM
To: CPCUGBOD-L@CPCUG.ORG
Subject: Correction to Minutes & tonight's Agenda
I have added Lynne Sturtz to the list of attendees in the Corrected
Minutes for the April 6, 1998 Meeting.
------------------------
CPCUG BOD Meeting May 4, 1998 Proposed Agenda
1. Agenda Approval
2. Approval of the Minutes of the prior Meeting (April 6, 1998)
3. Officer's Report (brief reports 3 minutes or less)
4. Installation of Treasurer
5. Facilities Report
6. Unfinished Business
7. New Business
8. Good and Welfare (Matters Arising)
9. Adjournment
From: NANCY M LANDREVILLE [nancyml@juno.com]
Sent: Thursday, April 16, 1998 3:46 AM
To: Rick@CICorp.com
Subject: Re: FW: Latest Status on 51 Monroe ST PE2
I spoke with Harold Motin of CPCUG (Training Coordinator). Training for
and at CPCUG has been "put on hold" until perhaps June. This means all
the scheduled classes that we see in the Monitor are either cancelled or
the instructor may opt to hold the class elsewhere. Most are cancelled.
Lillian is working on a new site for SIG mtgs., etc. I will keep you
updated if I hear anything sooner than you do.
Thank you for forwarding this message to me.
Nancy L>
_____________________________________________________________________
You don't need to buy Internet FoxPro to use free Internet e-mail.
Get completely free e-mail from Juno at http://www.juno.com
Or call Juno at (800) 654-JUNO [654-5866]
From: Barbara Conn [bconn@cpcug.org]
Sent: Tuesday, April 07, 1998 10:44 AM
To: McGinn, Mike
Cc: Rick Shaddock
Subject: RE: APC & Cipher Logics at CPCUG
On Tue, 7 Apr 1998, McGinn, Mike wrote:
> > Barbara:
> >
> > Your previous message included the FoxPro Sig. Is it also at NIH? In
> > Lipsett, Masur, the cafeteria?
> >
> >
> > Monday, April 13, 1998, CPCUG Activities
> > ==========================================
> >
> > 7:00 pm HOW TO BEAT POWER PROBLEMS; APC
> > 8:00 pm SECUREWIN, Security for Your Computers; Cipher Logics
> > 9:15 pm "Late Night"
> > 9:15 pm FoxPro SIG
> > General Meeting NIH FREE
Yes, FoxPro Sig meets in the cafeteria after the General Meeting.
Occasionally its meetings have been canceled--it's probably a good
idea to check with its SIG chair Rick Shaddock if you are planning on
attending your first meeting of FoxPro SIG. His phone number is
703-486-2222 (d) and e-mail is rick@cicorp.com.
Barbara Conn * bconn@cpcug.org * http://www.cpcug.org/user/comm/
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Director of Corporate Communications * Capital PC User Group
From: NANCY M LANDREVILLE [nancyml@juno.com]
Sent: Monday, April 06, 1998 10:25 PM
To: Rick@CICorp.com
Subject: Re: FW: Corrected March Minutes
I could not make this meeting after all tonight. I do have contact with
members of the Board of Directors however, so I can relay any info. you
may have. I'll let you know how the new location is.
Nancy L.
_____________________________________________________________________
You don't need to buy Internet FoxPro to use free Internet e-mail.
Get completely free e-mail from Juno at http://www.juno.com
Or call Juno at (800) 654-JUNO [654-5866]
From: NANCY M LANDREVILLE [nancyml@juno.com]
Sent: Monday, April 06, 1998 10:20 PM
To: Rick@CICorp.com
Subject: Re: FW: ZD Support Service Conference-Expo Apr.19-24
Where is this located? I try to attend these conferences to keep up with
the technology. Can you send me a ticket if its within the metro area?
Nancy L.
_____________________________________________________________________
You don't need to buy Internet FoxPro to use free Internet e-mail.
Get completely free e-mail from Juno at http://www.juno.com
Or call Juno at (800) 654-JUNO [654-5866]
From: NANCY M LANDREVILLE [nancyml@juno.com]
Sent: Thursday, March 19, 1998 7:45 PM
To: RShaddock@telegroup.com
Subject: FoxPro SIG and Wire
I will represent us via "being there for the FoxPro SIG on April 6 at
9:15pm. Is there something you need me to cover for you? Or...bring to
the SIG....???
Are you still holding your Sunday meetings? 2:00pm Crystal???
Nancy L.
_____________________________________________________________________
From: Yeargin. Michael, , AFAA/DOVS [yeargin@af.pentagon.mil]
Sent: Wednesday, March 18, 1998 8:10 AM
To: 'Rick@cicorp.com'
Subject: No Headers In Multiple Reports on 2nd Pages
Rick,
I'm a CPCUG member. I wonder if you can help me with a mild
problem. I have a main report with multiple sub-reports. My problem is
that I cannot get the page headers to print on continuous pages when the
information expands to a second page. When I put the headers in the
Report Header it's OK on the 1st page and not there on page 2. When I
put the headers in the page header they do not show up at all. What am I
doing wrong.
Michael Yeargin
w- 703-696-8161
h- 301-218-8247
From: gmclennan [gmclennan@smmg.com]
Sent: Wednesday, March 18, 1998 8:50 AM
To: Rick@CICorp.com
Subject: MAAUG
It looks like MAAUG is having second thoughts about linking up with
another user group. They have decided to stay independent.
--
George L. McLennan
Strategic Marketing & Management Group
gmclennan@smmg.com
703-354-9839 (voice)
703-354-1694 (fax)
Thanks.
RickFrom: Les Squires [lsquires@wj.WJI.COM]
Sent: Monday, March 16, 1998 9:38 AM
To: Rick@CICorp.com
Subject: AC398: FoxPro Email Newsletter
---------------------------------------------------------------
Member: Rick Shaddock, Crystal City Maryland USA
Subs ON/off: ACCE clip FOXP sqlw it vdb WJI
Jobs ON/off: acce actx c++ clip cobol cfusion cryst dbase delph
dmodel docmgt domino dware edp fox glob it java
notes novell nt oracle pb perl sqa sqls sqlw syba
unix vb vdb vconf warp webd webs y2k
---------------------------------------------------------------
Hi again, Rick!
Have you helped spread the word about FoxPro JobsExpress?
Don't wait til the well is dry . . .
Les
-----------------------------------------------------------------------
-----------------------------------------------------------------------
FoxPro Email Newsletter March, 1998
A Service of the World Wide Developer Network Vol. 6 No. 2
http://www.wji.com/FoxPro/homepage.html ftp.wji.com/FoxPro
-----------------------------------------------------------------------
1. Editor's Note
2. Formatting Number Fields When Using DoCmd.TransferText
3. Maximizing the Amount of Controls on Reports and Forms
4. The FoxPro Web
5. Checking Usage on Back-end Database
6. Allowing Single Subform to Replace Multiple Layered Subforms
7. Attaching Tables on The Fly
8. FoxPro to Visual Basic Object Converter ( Version 8.0 )
9. Sharing Code Dynamically Among FoxPro 97 Apps
10. Using GetSet Functions in Queries
12. Establishing a 'ToDo' Function for Client Viewing
13. Querying Multiple Items in a List Box
14. From the Publisher
-----------------------------------------------------------------------
1. Editor's Note
-------------------------------------------------------------------
ReplyTo: Phil Denoncourt III, Senior Editor
Hello everyone!
The smells of spring are in the air. Time to emerge from our
keyboards and experience life again. Before you abandon your
winter's work, don't forget to submit the tips and tricks you
discovered!
We've had a lot of great code submitted over the last month. We
love it! But we need more. Does anyone have more tips and tricks
about replication, security, ODBC direct or other topics that make
FoxPro developers wince?
For those of us who are always looking for another opportunity, make
sure you've visited www.wji.com and signed up for the JobsExpress
feature. Tons of opportunities have been advertised through here.
Thanks for keeping the good content coming.
2. Formatting Number Fields When Using DoCmd.TransferText
-----------------------------------------------------------------------
ReplyTo: Shamil M. Salakhetdinov
URL: http://www.wji.com/FoxPro/w0753.html
Public Function smsTransferText(ByVal vstrTblName As String, _
ByVal vstrTransferToFullPath As String, _
Optional ByRef rvarDbs As Variant = Nothing, _
Optional ByVal vblnUseODBC As Boolean = False) As Boolean
'*+
' Public domain. Written by Shamil Salakhetdinov. DARTS Ltd.,
' St.Petersburg, Russia
' e-mail: shamil@marta.darts.spb.ru
'
' Abstract:
'
' It seems that MS FoxPro 97 uses current locale settings to round
' Double, Single and Currency numbers when data are exported by
' DoCmd.TransferText ...
'
' This function wraps up DoCmd.TransferText. It formats Double,
' Single and Currency numbers using field's DecimalPlaces property
' creating temporary format and export queries. Then it uses export
' query in DoCmd.TransferText to export data into target delimited
' text file.
'
' This function also shows different (advanced) code techniques used
' to process DAO objects, ODBC datasources and optional arguments.
'
' Note: Single, Double and Currency numbers exported by this
' function are enclosed in Text Qualifier chars (usually double
' quotes - Asc("""") = 34) - this isn't a problem for MS FoxPro 97
' to export them back correctly.
'
' Arguments:
'
' vstrTblName - table name to export
' vstrTransferToFullPath - file path to export
' rvarDbs - (optional) database object reference of opened
' database, MS FoxPro .mdb file path or ODBC connect string
' vblnUseODBC - use linked ODBC datasource if True. Should be
' always true if rvarDbs is ODBC connect string
'
' Returns:
'
' True - if export is OK
' False and error message box if export failed
'
' Examples:
'
' smsTransferText "Table1", "c:\daisy\temp\Table1_Acc97.txt"
'
' smsTransferText "Table1", "c:\daisy\temp\Table1_Acc97.txt",Codedb()
'
' smsTransferText "Table1", "c:\daisy\temp\Table1_Acc97.txt", _
' CodeDB().name
'
' strODBC = "ODBC;DSN=DAISY;DATABASE=DAISY"
' smsTransferText "Table1", "c:\daisy\temp\Table1_SQL.txt", _
' strODBC, True
'
' strODBC = "Excel 5.0;HDR=YES;IMEX=2;" & _
' "DATABASE=C:\daisy\Temp\Table1.xls;" & _
' "TABLE=Table1$"
'
' smsTransferText "Table1", _
' "c:\daisy\temp\Table1_XLS.txt", strODBC, True
'
' strODBC = "dBase 5.0;HDR=NO;IMEX=2;" & _
' "DATABASE=C:\daisy\Temp;TABLE=Table1#dbf"
'
' smsTransferText "Table1", "c:\daisy\temp\Table1_dBase.txt", _
' strODBC, True
'
' strODBC = "FoxPro 2.0;HDR=NO;IMEX=2;" & _
' "DATABASE=C:\daisy\Temp;TABLE=Table1f#dbf"
'
' smsTransferText "Table1", "c:\daisy\temp\Table1_Fox.txt", strODBC, True
'
' strODBC = "HTML Import;HDR=NO;IMEX=2;" & _
' "DATABASE=C:\daisy\Temp\Table1.html;TABLE=Table1"
'
' smsTransferText "Table1", "c:\daisy\temp\Table1_HTML.txt", _
' strODBC, True
'*-
On Error GoTo smsTransferText_Err
Dim dbs As Database
Dim dbsCode As Database
Dim tdf As TableDef
Dim qdf As QueryDef
Dim fld As Field
Dim strTblName As String
Dim strFormat As String
Dim intDecPlaces As Integer
Dim strSqlFormat As String
Dim strSqlExport As String
Dim strFormatQryName As String
Dim strExportQryName As String
Dim strInDbs As String
Dim strLinkedTblName As String
If TypeName(rvarDbs) = "Nothing" Then
Set dbs = CodeDb()
strInDbs = ";Database=" & dbs.Name
ElseIf TypeName(rvarDbs) = "Database" Then
Set dbs = rvarDbs
strInDbs = ";Database=" & dbs.Name
Else
If vblnUseODBC Then
'Use temporary linked table instead of directly opened
'ODBC source
Set dbs = DBEngine(0).OpenDatabase("ExportDBS", _
dbDriverNoPrompt, True, rvarDbs)
Set dbs = Nothing
strInDbs = rvarDbs
Else
Set dbs = DBEngine(0).OpenDatabase(rvarDbs, , True)
strInDbs = ";Database=" & dbs.Name
End If
End If
Set dbsCode = CodeDb()
strLinkedTblName = ""
If vblnUseODBC Then
strLinkedTblName = "zttbl" & vstrTblName & "_ToExport"
On Error Resume Next
dbsCode.TableDefs.Delete strLinkedTblName
On Error GoTo smsTransferText_Err
Set tdf = dbsCode.CreateTableDef(strLinkedTblName)
tdf.Connect = rvarDbs
tdf.SourceTableName = vstrTblName
dbsCode.TableDefs.Append tdf
Else
Set tdf = dbs.TableDefs(vstrTblName)
End If
strSqlFormat = " select "
strSqlExport = " select "
For Each fld In tdf.Fields
strFormat = ""
Select Case fld.Type
Case dbDouble, dbSingle, dbCurrency:
On Error Resume Next
intDecPlaces = fld.Properties("DecimalPlaces")
If Err <> 0 Then
strFormat = "General Number"
Else
Select Case fld.Properties("DecimalPlaces")
Case 255: ' Auto
Case Else: strFormat = "0." & _
String(CLng(fld.Properties("DecimalPlaces")),"0")
End Select
End If
On Error GoTo smsTransferText_Err
Case dbGUID:
strFormat = "GUID"
Case Else
End Select
If strFormat = "" Then
strSqlFormat = strSqlFormat & "[" & fld.Name & "],"
strSqlExport = strSqlExport & "[" & fld.Name & "],"
ElseIf strFormat = "GUID" Then
strSqlFormat = strSqlFormat & """{guid "" & _
CStr([" & fld.Name & "]) & ""}""" & _
" as [" & fld.Name & "_Formatted],"
strSqlExport = strSqlExport & "[" & fld.Name & _
"_Formatted] as [" & fld.Name & "],"
Else
strSqlFormat = strSqlFormat & "Format([" & _
fld.Name & "],""" & strFormat & """) _
as [" & fld.Name & "_Formatted],"
strSqlExport = strSqlExport & "[" & fld.Name & _
"_Formatted] as [" & fld.Name & "],"
End If
Next
'Use temporary linked table instead of 'in' clause
'strSqlFormat = Left(strSqlFormat, Len(strSqlFormat) - 1) & _
' " from [" & vstrTblName & "] in '' [" & strInDbs & "]"
If vblnUseODBC Then
strSqlFormat = Left(strSqlFormat, Len(strSqlFormat) - 1) & _
" from [" & strLinkedTblName & "]"
Else
strSqlFormat = Left(strSqlFormat, Len(strSqlFormat) - 1) & _
" from [" & vstrTblName & "]"
End If
strSqlExport = Left(strSqlExport, Len(strSqlExport) - 1) & _
" from [ztqry" & vstrTblName & "_Formatted]"
On Error Resume Next
strFormatQryName = "ztqry" & vstrTblName & "_Formatted"
dbsCode.QueryDefs.Delete strFormatQryName
Set qdf = dbsCode.CreateQueryDef(strFormatQryName, strSqlFormat)
strExportQryName = "ztqry" & vstrTblName & "_ToExport"
dbsCode.QueryDefs.Delete strExportQryName
Set qdf = dbsCode.CreateQueryDef(strExportQryName, strSqlExport)
On Error GoTo smsTransferText_Err
DoCmd.TransferText acExportDelim, , strExportQryName,
vstrTransferToFullPath, True
On Error Resume Next
dbsCode.QueryDefs.Delete strFormatQryName
dbsCode.QueryDefs.Delete strExportQryName
dbsCode.TableDefs.Delete strLinkedTblName
On Error GoTo smsTransferText_Err
smsTransferText = True
smsTransferText_Exit:
Set fld = Nothing
Set tdf = Nothing
Set qdf = Nothing
Set dbs = Nothing
Set dbsCode = Nothing
Exit Function
smsTransferText_Err:
MsgBox "smsTransferText: " & Err & _
" - " & Err.Description, vbOKOnly
smsTransferText = False
Resume smsTransferText_Exit
End Function
3. Maximizing the Amount of Controls on Reports and Forms
-----------------------------------------------------------------------
ReplyTo: Michael M. Myers
URL: http://www.wji.com/FoxPro/w0710.html
A report or form is limited to a maximum amount of 754 controls. My
report had 220 visible controls. However, I had created the report
by cutting and copying columns of textboxes to a temporary report,
using Speed Ferret to rename and update various textbox properties,
and copying the changes back into my report. In the process I had
cut and copied numerous groups of textboxes in and out of the
report.
Apparently, when you cut an control from a report, it does not
reduce the count of controls. So even though the actual count of
current controls in the controls collection was 220, the hidden
count was much greater -- I had exceeded the 754 control limit!
To resolve the problem, I copied the controls and code to a new and
empty report with a different name, deleted the report,
repaired/compacted the database, and renamed the temporary report to
the original report name. The 754 control limit also applies to
forms.
4. The FoxPro Web
-----------------------------------------------------------------------
ReplyTo: Pat Tormey
URL: http://www.wji.com/FoxPro/w0751.html
URL: http://home.att.net/~dashish" //-The FoxPro Web
The FoxPro Web is a great web site that I often recommend to FoxPro
developers.
After a long history of responding to questions on FoxPro
newsgroups, Dev Ashish, of AT&T's Applied Technology Labs in New
Jersey, has put together a website that contains an FAQ for FoxPro.
The site is broken into sections dealing with bugs, tables, queries,
forms, reports, macros, modules, strings, APIs, date/time routines,
and a page of essential downloads.
5. Checking Usage on Back-end Database
-----------------------------------------------------------------------
ReplyTo: Stefan Gotojuch
URL: http://www.wji.com/FoxPro/w0747.html
FoxPro Versions: All
As administrator of multi-user database applications, you need a way
to check if all users are logged out of shared data. Only then can
you exclusively lock data and perform backups or correct an errors
in the system.
I assume that all administrators warn all users about the fact that
they should log-off (for instance by hidden startup form method
described widely), but still it's necessary to check the usage.
It's possible to check usage using the .LDB file, which contains
locking information for the corresponding .MDB file and is always
placed in same folder with .MDB.
In FoxPro 2.0:
The .LDB file is created when .MDB is first opened and remains even
if the .MDB is closed. If you delete .LDB, it will be recreated by
the engine while FoxProing .MDB the next time. If the front-end
application contains attached (linked) tables from a back-end shared
database, it's possible to delete .LDB. It will be recreated only
when somebody needs data from the .MDB (in Edit or in Read-only
mode). Trying to delete .LDB when .MDB is in use generates error.
In FoxPro 97:
The .LDB file is created when .MDB is opened, but it's removed when
.MDB is closed. In the case of the front-end application with linked
tables, it's possible to delete .LDB file if .MDB is not in use, but
the engine recreates it automatically.
The simple procedure below demonstrates how to get codes which can
be interpreted afterwards as:
x2=-1 .LDB successfully deleted, therefore .MDB not in use
x2=53 .LDB doesn't exist, therefore .MDB not in use
x2=75 sharing violation, therefore .MDB still in use
' Procedure works with both FoxPro 2.0 and FoxPro 97
' --------------------------------------------------
Function x2 (mpath As String) As Integer 'mpath=path to .MDB file
x2 = -1 'set default value of x2
On Error GoTo err_x2
Dim lpath As String, x As String 'lpath=path to .LDB file associated
' with mpath .MDB
lpath = Left(mpath, InStr(1, mpath, ".")) + "ldb"
x = Dir(lpath) 'Test for valid file, if not,
' then err=53
Kill lpath 'Test for FoxPro, if not,
' then err=75
g_o: On Error GoTo 0
Exit Function
err_x2: x2 = Err 'set value of x2 as Err so you
' will know what happened
Resume g_o
End Function
I know that this method is not very elegant, but works properly. If
you need more elegant method to check if .MDB is in use, then look
at the WhosOn() Database by Mark Nally mailto:mnally@hartec.com.au
published on ATTAC's web page:
http://ourworld.compuserve.com/homepages/attac-cg/"
6. Allowing Single Subform to Replace Multiple Layered Subforms
-----------------------------------------------------------------------
ReplyTo: Robert S. Ruder
URL: http://www.wji.com/FoxPro/w0696.html
I developed a dynamic interface that relied on an underlying table
to display a subform.
As a consultant, my partner keeps a custom built contact manager of
information about clients and the activities that surround them
including e-mails, tasks, faxes, computer equipment, etc. When he
saw my dynamic form, he asked whether it would be possible to have a
dynamic subform which would display record content by type, but in a
consistent format so that the same subform could be used no matter
what summary records were to be displayed. The reasoning was that
this would allow for a single subform to replace the multiple
layered subforms or multiple form interface he was currently using.
I realized that the process was even more simple than my system
tables approach. Not only that, but I could offer a reference which
would allow the user to double click on a record and jump directly
to a detail form which would represent a more complete data record.
What follows is a description of how that process can be implemented
with just one line of line of code per option that you want
displayed.
Step 1: Develop the Subform Concept.
The subform is going to represent a synopsis of the data record that
you want to have linked to a more complete detail form. The key to
this synopsis is that it should contain the same number of displayed
fields for all the underlying tables that will be used.
For example, I might decide that all the tables I deal with can
display for me the following information: Date, Description, Value.
I will create a subform using the following SQL Statement as its
RecordSource:
"Select '' as Date, '' as Description, '' as Value from Table1".
This will collect blank record values from Table1 but will give me
the field names that I want to use to create the subform.
Set the properties of the subform to display the subform as a
datasheet.
Step 2: Define the complete SQL Statement.
The key to this idea is that though the subform may only display 3
fields its RecordSource may contain additional useful pieces of
information, including a Primary Key value and the name of a subform
in which the full detail record can be displayed. Based on this, you
simply need to plan the basic outline of the SQL statement that will
satisfy the needs of the subform.
For my example above, I would create a template SQL Statement like
the following:
"Select as Date, as Description, as _
Value, as PK, '' as DisplayForm From
"
For each table that I want to display summary records for, all I
have to do is insert the appropriate field names and table name into
my template.
Step 3: Create Buttons to modify the RecordSource Property of the
subform.
For each possible table that you want to display, you only need to
take your template SQL statement and assign the statement to the
RecordSource property of the subform control. A sample Button might
look like this:
Sub Btn1_OnClick ( )
Me![SubfrmCtrl].Forms.RecordSource = "Select [LDate] as Date, _
[Desc] as Description, [Cost] as Value, [ID] as PK, 'ProdFrm' _
as DisplayForm From Products"
End Sub
For each table that you want displayed in the subform, create a
button and copy/modify.
7. Attaching Tables on The Fly
-----------------------------------------------------------------------
ReplyTo: Pat Tormey
URL: http://www.wji.com/FoxPro/w0697.html
FoxPro Version: 8.0
Keywords: ATTACH, UNC
ftp://ftp.wji.com/FoxPro/fsiattac.zip
Download fsiATTAC.ZIP (12,892)
Editor's Note. Steal This Code!!
Need to programmatically attach and remove tables? Or to attach
tables using the Universal Naming Convention (UNC)?
This routine connects a table to a file server without mapping the
drive in FoxPro 2.0. It can be Upsized to any flavor of FoxPro. Or
you can use it to attach locally.
I use it to "roll through" a Table of Table names and attach them on
the fly. Grab the associated file fsiAttach.mdb from the ZIP file
available on this page to see it work.
Function ConnectTable (sMdb, sTableName, sAsName) As Integer
' Call using ConnectTable("\\redserver\red-c\data\data.mdb",
'"tblEmployees","tblEmployees")
Dim ws As WorkSpace, db As Database
Dim tDef As TableDef
Dim i As Integer
Dim ReturnValue As Integer
ReturnValue = False
On Error Resume Next
If isFile(sMdb) Then ' the MDB file is here
Set ws = DBengine.WorkSpaces(0) ' Get default workspace.
Set db = ws.Databases(0) ' Get current database.
Set tDef = db.CreateTableDef(sAsName)
tDef.Connect = ";DATABASE=" & sMdb
tDef.SourceTableName = sTableName
db.TableDefs.Append tDef ' Attach table.
'check to see that it is here.
ReturnValue = IsTable(sAsName)
Else
MsgBox "File " & sMdb & " not found", 64, "Application Note"
ReturnValue = False
End If
ws.Close
ConnectTable = ReturnValue
End Function
8. FoxPro to Visual Basic Object Converter ( Version 8.0 )
-----------------------------------------------------------------------
ReplyTo: Lennox F. Chambers
URL: http://www.wji.com/FoxPro/w7481.html
Versions: 7.0, 8.0
Keywords: CONVERTER FoxPro VB
ftp: ftp.wji.com/FoxPro/objconve.zip //-OBJCONVE.ZIP (138,673)
ftp: ftp.wji.com/FoxPro/conform.zip" //-CONFORM.ZIP (239,019)
Object Converter is a Microsoft FoxPro 97/8 Add-In utility that
rapidly converts FoxPro Forms and Code, Controls (including a number
of ActiveX Custom Controls like the Microsoft Windows Common
Controls and the Common Dialog Control), Macros, Code and Class
Modules to Visual Basic Forms and Projects.
Object Converter saves a great deal of time when porting selected
Forms, Macros and Code Modules, or entire FoxPro applications to
Visual Basic.
There is no need to copy and paste code from FoxPro to VB and re-
design Forms from scratch. Converted Forms and Code will run
immediately in either the Visual Basic version 5.0 or 4.0 32-bit
environments.
9. Sharing Code Dynamically Among FoxPro 97 Apps
-----------------------------------------------------------------------
ReplyTo: Pat Tormey
URL: http://www.wji.com/FoxPro/w9443.html
Want to share code dynamically among FoxPro 97 Applications?
It's really easy. All you have to do is set a reference to an
existing MDB, MDA or MDE file! Yeah, really that's all there is to
it. (It's amazing what you can learn from the help file!)
Just remember that when you set a reference, FoxPro uses the first
referenced item it finds, if two functions or subs have the same
name. You can explicitly call a function using the MyMdb.MyFunction.
If you want to learn how to do it the right way through VB 5.0
ActiveX DLLs, drop in at http://www.foursquare.com/dll_demo for my
presentation to the Boston FoxPro User Group in February, 1998.
10. Using GetSet Functions in Queries
-----------------------------------------------------------------------
ReplyTo: Pat Tormey
URL: http://www.wji.com/FoxPro/w9442.html
Editor's Note. Steal this code!!
I had a problem where I had to run a series of reports and forms
with a predefined BusinessUnit. I didn't want it to change till I
said so.
The solution was to define a function that allows you to 'Set or
Get' the value you need and include that function as criteria in all
youR queries that are used for reports or forms. (Use the like
operator to get the a wildcard match.)
It's easy in FoxPro 97, and you can hack out something like it in
FoxPro 2.0 if you change the optional parameter to test IfNull()
Function BusinessUnit(Optional Bu) As String
'Note this is a STATIC so it will remember its value between runs
Static sBu As String
On Error Resume Next
'If you don't assign a new Business Unit you'll get the last one
If Not IsMissing(Bu) Then
sBu = Bu
End If
If IfNull(sBu) Then
'This returns a wild card so that all Business Units will match
sBu = "*"
End If
BusinessUnit = sBu 'return the current value.
End Function
12. Establishing a 'ToDo' Function for Client Viewing
-----------------------------------------------------------------------
ReplyTo: Stefan Daxenbichler
URL: http://www.wji.com/FoxPro/w9435.html
If you are a chaotic programmer like me, you will probably know this
situation:
While working on a larger FoxPro project there are situations where
some of the code you are writing can't be completed because it
relies on things yet to be implemented. (Or you are to lazy at the
time). Then you forget this code because it's not essential, or
only called in special situations.
When the customer tries to use this 'feature' that you forgot to
finish, one of the following things will happen (add your favorite
to this list):
o nothing at all
o the program crashes, or
o nothing at all except many beeps and weird messages from test
code you wrote.
To overcome this, I wrote a VERY simple, but helpful function called
'The ToDo function':
Function ToDo(Optional Description As String)
MsgBox "Not implemented.@@" & Description, vbInformation
End Function
You can use it in Code or as event handler on forms. This has two
advantages:
o Your Customers get a proper message
o You can search your whole for "ToDo" to find unfinished parts
13. Querying Multiple Items in a List Box
-----------------------------------------------------------------------
ReplyTo: Farid Ahmad
URL: http://www.wji.com/FoxPro/w9381.html
Version: 97
The 'In' clause is used in a SQL query to include records that have
a field value matching a list, e.g. suppose you have a Customers
data base with a field called City, then the following SQL segment
will pull records from three cities:
SELECT * from Customers WHERE city IN ("rome, paris, tokyo").
The trick is to construct a query with the IN clause based on
multiple selections in a list box. By setting the multiple-select
property of list boxes on the property sheet, it is possible to let
the user select multiple items, but getting these selections into a
query needs more work. Suppose you are working with the above
customers database, and you have a form called Input. On this form,
the user can select any number of cities from a list box, and then
press a command button to preview or print the resulting report.
To achieve this, first create a list box called Cities, and set its
Allow Multiple Select property. Now create a text box called
CitiesText anywhere on the form and make it invisible. Then include
the following code in the ON_UPDATE event of the Cities list box:
Private Sub Cities_AfterUpdate()
Dim varitem As Variant
Dim strList As String
strList = ""
With Me!Cities
For Each varitem In .ItemsSelected
strList = strList & str(.Column(0, varitem)) & ","
Next varitem
If Len(strList) > 0 Then
strList = Left$(strList, Len(strList) - 1)
End If
Me!CitiesText = strList
End With
End Sub
This will construct a comma delimited string from the items selected
in the Cities listbox, and place it in the CitiesText text box.
You can now construct a query based on the above string by using the
following code:
FUNCTION cityquery
str = "(" & [Forms]![input].[CitiesText] & ")"
cityquery = "SELECT * from Customers WHERE Customers.City In " & str
END FUNCTION
Finally make the cityquery( ) function the data source of the your
report. One way to do this is to insert the following into the
ON_OPEN event of the report:
Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = cityquery()
End Sub
14. From the Publisher
-------------------------------------------------------------------
The FoxPro Email Newsletter is created and distributed compliments
of the World Wide Developer Network (WWDN) of Boston, Massachusetts,
USA. Come, get involved! This is the place where developers talk
to developers as peers.
Publisher:
Les Squires
Word Jenny, Inc.
400 West Cummings Park, Woburn (Boston), Massachusetts USA
781-935-1520
Editors/Content Specialists
Phil Denoncourt III
Pat Tormey
Brendan Donovan
Website: http://www.wji.com/FoxPro/homepage.html
Address Changes:
Send an email USING YOUR OLD ADDRESS.
Indicate New Address.
Adding Subscribers to Mailing List:
Send Email to FoxPro@WJI.Com.
Include String FoxPro-YES in Subject: or Body of Message.
Deleting Subscribers:
Send Email to FoxPro@WJI.Com.
Include String FoxPro-NO in Subject: or Body of Message.
Retrieving Back Issues and General Downloads:
FTP to ftp.wji.com.
Login as FTP.
Use your ID as your password.
cd FoxPro
Copy back issues and downloads as they become available.
(c) 1998 World Wide Developer Network (WWDN)
From: Charles Racoosin [racooscm@erols.com]
Sent: Saturday, March 07, 1998 8:46 PM
To: rick@cicorp.com
Subject: From: TJ Racoosin - What day does SIG meet?
When is the next meeting for the SIG
9 Mar at 9:15 ?
Thanks!
TJ
From: Capital PC User Group Board of Directors Communications [CPCUGBOD-L@CPCUG.ORG] on behalf of Clifford Porter [chp2@EROLS.COM]
Sent: Sunday, March 01, 1998 8:29 PM
To: CPCUGBOD-L@CPCUG.ORG
Subject: Feb. 1998 Board Phone List
CAPITAL PC USER GROUP BOARD OF DIRECTORS
February 28th, 1998
Telephone List of Officers and SIG Contacts
* = not for public use
D = Weekdays
? = not confirmed
E/W = Evenings and Weekends
JANE BENSON
RESTON SIG
D (703)715-1032
E/W (703)715-1032
Fax (703)715-8455
jbenson@shirenet.com
MARY BROSNAN
ELECTRONIC PUBLISHING SIG
D /E/W (703)207-0477 or 800-609-2214
Fax (703)207-0479
cameronprs@aol.com
JACK CARLSON
SENIOR SIG
D (301)286-7140
E/W (301)474-7091
ROBERT CLEMENZI
PARADOX SIG
D (703)827-2365
E (703)791-5747
clemenzi@cpcug.org
BARBARA CONN
CORPORATE COMMUNICATIONS DIRECTOR
MONITOR EDITOR/PUBLISHER
D (202)508-1494
E/W (202)452-7484
Fax (202)508-3698
bconn@cpcug.org
BRUCE COTTOM
PROJECT MANAGEMENT SIG
E/W (301)869-5690
bcottom@cpcug.org
BILL DEROCHE
MEDICAL COMPUTING DIRECTOR
D (202)283-5237
E/W (301)530-9699
wderoche@cpcug.org
CHIP DODGE
BUILD OR BUY; BUYING GROUP DIRECTOR
D* (703)767-3619
E/W (703)425-7038
cdodgemd@aol.com
STAN DOORE
PAST PRESIDENT
D (301)572-4939
E/W (301)572-4939
Fax (301)572-9165
stan.doore@mix.cpcug.org
ANN DORSEY
LIBRARY SERVICES DIRECTOR
D (301)320-7984
E/W (301)320-7984
abdorsey@cpcug.org
DAVID DRAZIN
AUTOCAD SIG
D* (202)529-7474
E/W (301)279-7593
Fax (202)529-0332
GEORGE ELY
PUBLIC RELATIONS DIRECTOR
D (301)762-3002
E/W (301)762-3002
gely@cpcug.org
HERB FREDRICKSEN
MARYLAND VICE PRESIDENT; GEOWORKS SIG
D (301)840-2056
E/W (301)840-2056
Fax (301)840-2056 (call first)
herb.fredricksen@mix.cpcug.org
LUIS GARCIA
VIRGINIA VICE PRESIDENT
D (301)457-1451
E/W (703)425-6902
Fax (301)457-3033
lgarcia@census.gov (work)
GABE GOLDBERG
INTERNET SIG
(703)941-1657
Fax (703)941-1791
gabe@acm.org
CHARLES HALLAHAN
STATISTICS SIG
D* (202)694-5051
E/W (703)532-2930
Fax (202)694-5718
hallahan@econ.ag.gov
WALTER HOUSER
SCIENCE FAIR COORDINATOR; HTML SIG
D (202)273-8012
E/W (301)299-0593
Fax (202)273-8159
houser@cpcug.org (At work)
CAROL HYATT
K-12 EDUCATION DIRECTOR
D (301)948-3748
E/W (301)948-3748
chyatt@mindspring.com
PEGGY IRELAND
PROGRAM DIRECTOR
D (301)423-1618
E/W (301)423-1618
Fax (301)423-3488
ireland@cpcug.org
PEGGY KAUDERS
pkauders@erols.com
JERRY LAWSON
TRAINING DIRECTOR
E/W (703)541-0928
virtual@mindspring.com
LES LE VINE
WORDPERFECT SIG / BEGINNERS SIG
D (301)652-2532
E/W (301)652-2532
les.le.vine@mix.cpcug.org
BILL LUTZ
BALTIMORE SIG
D (410)256-9403
E/W (410)256-9403
wllutz@aol.com
PETER MAHER
SALES FORCE AUTOMATION SIG
D/E/W (703)834-3706
Fax (703)834-3707
1stplace@crosslink.net
RICHARD MALEY
DELPHI SIG
D (301)840-1554
E/W (301)840-1554
75127.17@compuserve.com
LARRY MCGOLDRICK
INTERNET SERVICES DIRECTOR
larrym@cpcug.org
KEVIN MCCRORY
OS/2 SIG
kmccrory@mnsinc.com
PATRICK MCVEIGH
TREASURER; WINDOWS SIG
E/W (301)963-7304
pmcveigh@cpcug.org
LILLIAN MILLINER
PRESIDENT
D (301)933-1717
E/W (301)963-9053
Fax (301)963-9386
milliner@cpcug.org
HENRY NOBLE
CORPORATE INFORMATION SYSTEMS DIRECTOR
D (301)963-3737
E/W (301)963-3737
noble@cpcug.org
ALTA OBEN
MetroMUG SIG
D* (703)613-7581
E/W (703)938-5831
Fax (703)938-5831 (call first)
alta@cpcug.org
CLIFFORD PORTER (MRS.)
INTERIM SECRETARY
D/E/W (301)762-9480
chp2@erols.com
RICHARD PRICE
OS/2 SIG
D/E/W (202)462-3047
richard_price@compuserve.com
BILL REDISCH
FRAMEWORK SIG
D (301)286-6203
E/W (301)279-7929
bill.redisch@gsfc.nasa.gov
NORMAN RISCH
PEACHTREE SIG
D (301)681-8088
E/W (301)754-0735
norman.risch@mix.cpcug.org
ROY ROSFELD
LEISURE WORLD SIG
D (301)598-2825
E/W (301)598-2825
rosfeld@hot.mail.com
NASEEM F. SAAB
CLIPPER SIG
D (703)860-5022
Fax (703)264-1345
nsaab@ix.netcom.com
RICH SCHINNELL
FIRST VICE PRESIDENT
D (301) 949-9292
E/W (301)949-9292
Fax (301)949-8847
schinnel@cpcug.org
RICK SHADDOCK
FoxPro (MICROSOFT) SIG
D(703)486-2222 or (202)829-4444
rick@cicorp.com
PAUL SHAPIRO
DIRECTOR OF VOLUNTEERS
D (301)770-7899
E/W 9am-9pm (301)770-9512
pauls@cpcug.org or pauls@capFoxPro.org
A. GREG SMITH
PAST PRESIDENT
gsmith@mail.thuntek.net
RANDY STEER
CAPITAL AREA VICE PRESIDENT
D (202)395-3164
E/W (202)745-5932
Fax (202)395-3165
r_steer@cpcug.org
PAT STINGLEY
JAVA SIG (Provisional 2/5/98)
D/E/W (301)587-7605
stingley@cpcug.org
RENE THIRION
COMMUNITY SERVICES DIRECTOR / MIX SYSOP
D/E/W (703)256-6764
rene@cpcug.org
ANDY THOMPSON
INVESTMENT SIG
D* (202)529-3330
E/W (301)270-6790
Fax (202)529-0911
pallotti01@aol.com
HARLEY WILBUR
INVESTMENT SIG
D (301)949-8131
hwilbur@cpcug.org
STEVE WORKINGS
ALPHA SIG
D/E/W (301)933-3832
Fax (301)933-4161
steve@workings.com
RUBEN WORRELL
PROJECT MANAGEMENT AND VISUAL BASIC SIG;
D(703)276-3000
E/W pager (202)490-0166
Fax (703)276-3017
worrellr@cpcug.org
C. PATRICK ZILLIACUS
OS/2 SIG
E/W (301)384-0972
Fax* (301)384-1740
patrick.zilliacus@mix.cpcug.org
From: Laurie Pollack [laurie@pimusa.com]
Sent: Friday, February 27, 1998 9:27 AM
To: rick@cicorp.com
Subject: MS FoxPro: Philadelphia
I saw your email address on the Washington area FoxPro interest group
page.
I actually am in the Philadelphia area, and was wondering if you know of
any
FoxPro interest groups in my area, or if not, of any FoxPro programmers
or
companies specializing in or using FoxPro, in my area.
I am an FoxPro programmer, and am interested in networking locally.
Sincerely yours,
Laurie Pollack
P.S. my home email address is: lioness1@prodigy.net
From: Rick@CICorp.com
Sent: Thursday, February 26, 1998 12:57 PM
To: AppAlt@AOL.com
Cc: C I Corporation
Subject: CPCUG FoxPro SIG meeting
Thank you for your interest in the Capital PC Users Group and the
FoxPro Special Interest Group (SIG).
The CPCUG FoxPro SIG is now meeting after the main meeting instead of separately. We are also considering merging with the Mid-Atlantic FoxPro Users Group.
Here is some information.
FoxPro Special Interest Group
Meets after main meeting of CPCUG
(See above)
The CPCUG FoxPro SIG presently meets for question & answer (& snacks) at the NIH caferia immediately after the main meeting. We also try to answer at least 10 questions as a group from the computer based multiple choice practice test for the Microsoft FoxPro Certified Professional Examination. We discuss why each answer is or is not correct, and always end up learning something new.
Mid-Atlantic FoxPro Users Group (MAAUG)
Meets 3rd Tuesday of each month at 7:00pm
National Association Social Workers
(next to Union Station Metro)
750 1st Street NE
Suite 700
Washington, DC
The Mid-Atlantic FoxPro User Group (MAAUG), has been in operation for about three years. Guest speakers include Luke Chung of FMS and Andrew Coupe of the Microsoft FoxPro development team. The CPCUG and MAAUG have a mix of members ranging from novices to a number of very experienced programmers.
Contact: Bruce Troutman 800-918-5670From: NANCY M LANDREVILLE [nancyml@juno.com]
Sent: Thursday, February 26, 1998 7:20 AM
To: rick@cicorp.com
Subject: New Site
Any requests about viewing the new site, handling the April meeting,
etc.?
I will be on travel starting Friday. Please RSVP EMail's. Thanks!
Nancy L.
_____________________________________________________________________
You don't need to buy Internet FoxPro to use free Internet e-mail.
Get completely free e-mail from Juno at http://www.juno.com
Or call Juno at (800) 654-JUNO [654-5866]
From: NANCY M LANDREVILLE [nancyml@juno.com]
Sent: Thursday, February 26, 1998 7:08 AM
To: Rick@CICorp.com
Subject: Re: FW: News from the Office
Twinbrook is walking distance from my home. I live on Twinbrook Pkwy.
Nancy L.
_____________________________________________________________________
You don't need to buy Internet FoxPro to use free Internet e-mail.
Get completely free e-mail from Juno at http://www.juno.com
Or call Juno at (800) 654-JUNO [654-5866]
From: Rick@CICorp.com
Sent: Wednesday, February 25, 1998 11:52 AM
To: gmclennan@smmg.com
Subject: RE: FoxPro meeting
No problem. I was also unable to attend. We'll get cranked up for March 17th.
Feel free to call on Nancy@CICorp.com or Nick@CICorp.com if you need a hand.
Thanks,
Rick
> -----Original Message-----
> From: gmclennan [mailto:gmclennan@smmg.com]
> Sent: February 25, 1998 9:57 AM
> To: Rick@CICorp.com
> Subject: Re: FoxPro meeting
>
> Unfortunately, after all that preparation and getting everyone involved
> all cranked up, I was not able to attend the meeting. I'm now scheduled
> to do it next month.
>
> --
> George L. McLennan
> Strategic Marketing & Management Group
> gmclennan@smmg.com
> 703-354-9839 (voice)
> 703-354-1694 (fax)
>
>
> From: gmclennan [gmclennan@smmg.com]
Sent: Wednesday, February 25, 1998 9:57 AM
To: Rick@CICorp.com
Subject: Re: FoxPro meeting
Unfortunately, after all that preparation and getting everyone involved
all cranked up, I was not able to attend the meeting. I'm now scheduled
to do it next month.
--
George L. McLennan
Strategic Marketing & Management Group
gmclennan@smmg.com
703-354-9839 (voice)
703-354-1694 (fax)
From: Rick@CICorp.com
Sent: Wednesday, February 25, 1998 7:31 AM
To: GMcLennan@SMMG.com
Subject: FoxPro meeting
How did it go? What was the response to MAAUG working more closely with CPCUG?
Thanks again for your help and initiative.
Rick
From: Rick@CICorp.com
Sent: Monday, February 23, 1998 4:33 PM
To: C I Corporation
Subject: FW: FoxPro developer
Nick is pretty busy, now but when does he want to start?
Please call him and get the information. Maybe Nancy or Alex could do it.
(But Nancy charges too much for her skills.) Is it something that Gerard could do from here? I better not take any more on myself. I have enough to do with Hill Group, WR, and Telegroup.
Thanks.
-----Original Message-----
From: Terry Kades [mailto:tkades@erols.com]
Sent: February 23, 1998 2:15 PM
To: rick@cicorp.com
Subject: RE: FoxPro developer
Rick
I am looking for a competent FoxPro developer for about a one month
project. Thereafter maintenance and customization on an ad hoc basis.
Please could you refer me to someone or vice versa.
Many thanks.
Terry Kades
Strategic Feasibilities, Inc.
(301) 983-6305
From: Les Squires [lsquires@wj.WJI.COM]
Sent: Wednesday, February 18, 1998 4:28 PM
To: Rick@CICorp.com
Subject: JE218: Sample JobsExpress
---------------------------------------------------------------
Member: Rick Shaddock, Crystal City Maryland USA
Subs ON/off: ACCE clip FOXP sqlw it vdb WJI
Jobs ON/off: acce actx c++ clip cobol cfusion cryst dbase delph
dmodel docmgt domino dware edp fox glob it java
notes novell nt oracle pb perl sqa sqls sqlw syba
unix vb vdb vconf warp webd webs y2k
---------------------------------------------------------------
Rick,
Since you're not currently enrolled in any of WWDN JobsExpresses,
it dawned on me the other day that it might be a good idea to
show you a sample of the announcements.
Feel free to forward a copy to some company you know that could
benefit from direct email FoxPro to the developer communities.
We're mailing 9,500 of these per day on the average and hope that
one of these announcements can help you one of these days--either
as an employer or as a job seeker.
Second. Please notice the new header. It provides a summary of
all your activity with wji.com. Subs ON/off indicates newsletters
and special interest groups. Jobs ON/off indicates current job
announcements. Upper case is ON; lower case is off.
Third. We're open again for additional jobs items you suggest
for inclusion on http://www.wji.com/wji/w1002779.html .
Les
----------------------- RealTimeJobs(tm) --------------------------
World Wide Developer Network (WWDN) Les Squires LSquires@wji.com
Woburn, Massachusetts (Boston) 781-935-1520
------------- http://www.wji.com/wji/w1002779.html ---------------
------------ SAMPLE
------------ Job Opportunity from World Wide Developer Network
------------ Posted from http://www.wji.com/wji/w9235.html
Contact: John Thornton
Email: JMTNORRELL@AOL.COM
Company: Norrell Services, Inc.
URL: http://www.
Address: 4401 Connecticut Ave. N.W.
Suite 604
City: Washington
State: DC
ZipCode: 20008
Country: USA
Phone: 202-686-0130
Fax: 202-363-2464
Telecommuting will not be considered.
'Virtual Contracting' will not be considered.
Job Description
-------------------------------------------------------------
Business Systems Analyst (No Coding) 5 Openings
The successful candidates will assist the project leader in
leading business systems analysts/programmers in the design,
development, implementation and maintenance of business
information systems. Analyzes only; does not code.
Contract and contract to hire positions available for
Wilkes-Barre, PA area.
Please submit your resume via fax or e-mail to:
Norrell Information Systems, Inc.
(703)917-1616
PCVISION@ATR.COM
---------------------------------------------------------------
IT@WJI.com //-Use this email ID for writing to WJI.
ITJOBSNL-NO //-Remove your ID from this jobs list.
ITJOBSNL-YES //-Reactivate yourself. Add friend to list.
Change Address //-Use OLD address to send NEW address.
Abuse@wji.com //-Report anything you consider to be abusive.
IT Professionals JobsExpress http://www.wji.com/wji/w1002779.html
A Service of Word Jenny, Inc. Boston USA 02/18/98 12:06:11EST
-----Original Message-----
From: Les Squires [mailto:lsquires@wj.WJI.COM]
Sent: February 10, 1998 1:47 AM
To: Rick@CICorp.com
Subject: AC198: FoxPro Email Newsletter
-----------------------------------------------------------------
Member: Rick Shaddock of Crystal City Maryland USA
Subs ON/off: ACCE clip FOXP sqlw it vdb wji
Jobs ON/off: acce actx c++ clip dbase delphi fox it java oracle
pb sqlserver sqlwind sybase vb vdb y2k
-----------------------------------------------------------------
Hi again, Rick!
Here's the catch for this month!
Les
-----------------------------------------------------------------------
-----------------------------------------------------------------------
FoxPro Email Newsletter January, 1998
A Service of the World Wide Developer Network Vol. 6 No. 1
http://www.wji.com/FoxPro/homepage.html ftp.wji.com/FoxPro
-----------------------------------------------------------------------
1. Editor's Note
2. FoxPro JobsExpress
3. Finding the Irish in your Database
4. Auditing the Records in an FoxPro 97 Table
5. Managing Your Reports with the Data-Driven Paradigm
6. Printing Reports as a Batch Process
7. Extracting the Network User ID using 16 Bit API Calls.
8. Showing System Objects And "Select *" Don't Mix In Replicas
9. Viewing Logged-on Users Through the .LDB
10. Handling Year 2000 Issues (Y2K)
11. Achieving Automation Goals using Database Documenters Code.
12. Repairing and Compacting with Jet API's
13. Optimizing the Speed of Report Runs.
14. Creating a Flexible User Interface Design with 'System' Tables
15. ODBCDirect Askew Without VB 5.0 Pro Installed
16. Setting Echo On/Off For Debugging
17. Printing Relations from Any FoxPro 97 Database
18. Internet Database in German FoxPro 97
19. FoxPro 8.0 Export Facilities.
20. Customizing Function Keys in FoxPro
21. Comment Block and UnComment Block Builders for FoxPro 97
22. Passing SQL Strings to FoxPro
23. LookIn on Outlook to Speed Up FoxPro and Office
24. From the Publisher
-----------------------------------------------------------------------
1. Editor's Note
-------------------------------------------------------------------
ReplyTo: Pat Tormey
URL: http://www.wji.com/FoxPro/w9161.html
Lots of good code this month. Thanks again to all of you who take
the time to enrich our FoxPro developer community!
2. FoxPro JobsExpress
-------------------------------------------------------------------
ReplyTo: Les Squires, Publisher
URL: http://www.wji.com/FoxPro/w7413.html
We've built a new email system to push job announcements immediately
to developers who want them. Please note that we are no longer
posting job announcements to web pages. Use one of the links below
to fire up the system! Let us know what you think!
Employers: http://www.wji.com/FoxPro/w7412.html
---------------------------------------------------------
Looking for full or part-time employees, consultants, tutors, or the
like? Submit your job announcement ad to us and we will email it to
our pool of FoxPro developers.
Developers: http://www.wji.com/FoxPro/w8495.html
---------------------------------------------------------
Sign up for FoxProJobsExpress! Receive an email of a job
announcement within 15 seconds of its being posted!
3. Finding the Irish in your Database
-------------------------------------------------------------------
ReplyTo: Pat Tormey
URL: http://www.wji.com/FoxPro/w9355.html
ftp://ftp.wji.com/FoxPro/fsifind.zip //-FSIFIND.ZIP (43,064)
FoxPro Version: 8.0 (But you could roll it back to version 2.0.)
Keywords: SQL, FINDIT, QUOTE
Problem: SQL engines can choke on a single quote, but they think
two single quotes are just one literal quote.
Solution: Use the file fsiFind.MDB. It converts O'Brein to O''Brien
and all single apostrophes to two apostrophes.
I got a little help writing this from the Microsoft Systems Journal
at http://www.microsoft.com/msj/
4. Auditing the Records in an FoxPro 97 Table
-------------------------------------------------------------------
ReplyTo: Pat Tormey
URL: http://www.wji.com/FoxPro/w9354.html
ftp://ftp.wji.com/FoxPro/fsiaudit.exe //-FSIAUDIT.EXE (190,416)
ftp://ftp.wji.com/FoxPro/fsivideo.exe //-FSIVIDEO.EXE (1,360,643)
Keywords: AUDIT TRAIL; CLASSES; NETWORK USER NAME
FoxPro Version: 97
The file fsiAudit.EXE contains an FoxPro 97 MDB (self extracting
PkZip) that demonstrates how to audit the records in an FoxPro
table.
Each record programmatically maintains its origin date/time,
date/time of the last edit, and the Network User ID of person logged
on at the time.
The sample code wraps the Network User Id API call in an FoxPro 97
Class, which can be used "as is" in VB 4-32 or VB 5.0. All source
code is included and it's free.
The file fsiVideo.EXE is an instructional overview for the code,
made with the MS Camcorder Movie. You will need a sound card to
fully appreciate it!
5. Managing Your Reports with the Data-Driven Paradigm
-------------------------------------------------------------------
ReplyTo: Pat Tormey
URL: http://www.wji.com/FoxPro/w9353.html
ftp://ftp.wji.com/FoxPro/ddreport.zip //-DDREPORT.ZIP (22,024)
Keywords: 'DATA DRIVEN' REPORTS
FoxPro Versions: All
This file Reports.MDB, is a complete 'Data Driven' example of an
easy way to manage all the reports in you application, developed in
FoxPro 2.0.
The idea is to store all reports in a table, let the user select the
reports they would like, and print them in a batch from a single
command button.
6. Printing Reports as a Batch Process
-------------------------------------------------------------------
ReplyTo: Pat Tormey
URL: http://www.wji.com/FoxPro/w9350.html
FoxPro Versions: All
Suppose you had a table with all available reports and you wanted to
let the user select several for printing.
All you'd have to do is let them toggle the 'PrintIt' field with a
simple form. You can even give the report a user friendly name so
they can recognize the report 'Mary's Monthly Report' instead of the
less kindly 'rptEmployeeLayoffsAntiPersonalDept'.
Then run this handy routine:
Sub ReportAll ()
'**************************************************************
' Author : Pat Tormey Tormey@foursquare.com
' Date : 7 April 1996
' Comments : Objective is to Select a report from Engine Listing
'***************************************************************
On Error GoTo ReportAll_ERR
Dim db As Database
Dim rs As Recordset
Dim i As Integer
Dim Count%
Dim x As Variant
Set db = DBengine.WorkSpaces(0).Databases(0)
Set rs = db.OpenRecordset("tblReports", DB_OPEN_SNAPSHOT)
'Count Up the Printit
Count% = 0
Do Until rs.EOF 'let's see how many reports are tagged to Print
If rs.PrintIt Then
Count% = Count% + 1
End If
rs.MoveNext
Loop
If Count% = 0 Then
Exit Sub
End If
'Set up the system Meter
x = SysCmd(SYSCMD_INITMETER, Str$(Count%) & " Reports", Count%)
rs.MoveFirst
Do Until rs.EOF
If rs.PrintIt Then 'Is It Tagged
x = PrintReport(rs.ReportName)
x = SysCmd(SYSCMD_UPDATEMETER, i)
End If
i = i + 1
rs.MoveNext
Loop
rs.Close
db.Close
x = SysCmd(SYSCMD_REMOVEMETER)
ReportAll_EXIT:
Exit Sub
ReportAll_ERR:
'Never Say Error .. especially if there is one
MsgBox "Note:" & Error$, 64, "Application Note"
Resume ReportAll_EXIT
End Sub
Function PrintReport (cReport)
'**********************************************************
' Author : Pat Tormey Tormey@foursquare.com
' Date : 7 April 1996
'**********************************************************
On Error GoTo PrintReport_ERR
DoCmd OpenReport cReport, A_NORMAL
PrintReport_EXIT:
Exit Function
PrintReport_ERR:
MsgBox "Note:" & Error$, 64, "Application Note: There is " & _
"nothing wrong. Go back to sleep"
Resume PrintReport_EXIT
End Function
7. Extracting the Network User ID using 16 Bit API Calls.
-------------------------------------------------------------------
ReplyTo: Pat Tormey
URL: http://www.wji.com/FoxPro/w9347.html
FoxPro Versions: All
This file extracts the Network User ID in FoxPro 2.0 using the 16
Bit API calls.
I must admit that I stole this idea from somebody, probably Dan
Appleman. I recommend his books 'Visual Basic Programmer's Guide to
the Windows API' for 16 bit calls and 'Visual Basic Programmers
Guide to the WIN 32 API' for 32 bit calls.
Option Compare Database 'Use database order for string comparisons
Option Explicit
' Uses WINDOW API to get the Net Users Name from the Local Area
' Network
' You can use this to see if the users should have write/delete
' privileges, or to secretly tag who did what last!
Declare Function wu_WNetGetUser% Lib "USER.EXE" Alias _
"WNetGetUser" (ByVal szUser$, lpnBufferSize%)
Function NetworkUserID () As String
'**********************************************************
' Author : Pat Tormey Tormey@foursquare.com
' Date : 7 April 1996
'**********************************************************
On Error GoTo NetworkUserID_ERR
Dim szUser As String * 255
Dim lpnBufferSize As Integer
Dim status As Integer
lpnBufferSize = 255
status = wu_WNetGetUser(szUser, lpnBufferSize)
If (status = 3) Then
NetworkUserID = "WNetGetUser Failed"
Else
'Return up to first Null.
NetworkUserID = Left$(szUser, InStr(szUser, Chr(0)) - 1)
End If
NetworkUserID_EXIT:
Exit Function
NetworkUserID_ERR:
MsgBox "Note:" & Error$, 64, "Application Note"
Resume NetworkUserID_EXIT
End Function
8. Showing System Objects And "Select *" Don't Mix In Replicas
-------------------------------------------------------------------
ReplyTo: Philip Hurzeler
URL: http://www.wji.com/FoxPro/w9341.html
FoxPro Versions: 97, possibly 95 (Try this in FoxPro 95, and
If any controls in your replicated FoxPro 97 database have row
sources that start with 'SELECT * FROM...', you can create bugs by
setting the 'Show System Objects' option on or off.
Setting the option on makes visible the system columns devoted to
replication, which can have a ripple effect. For instance, the
ordinal values of the Column property of list or combo boxes all get
bumped up by 3 unless the row source property calls out each column
by name.
One way you can prevent this problem is to avoid 'SELECT' in row
sources. Another way is to set the Application's 'Show System
Objects' option to False in your Autoexec macro or startup form's
Open event.
9. Viewing Logged-on Users Through the .LDB
-------------------------------------------------------------------
ReplyTo: Thom Allen
URL: http://www.wji.com/FoxPro/w9322.html
FoxPro Version: 2.0
For a very rudimentary view of who is logged in to a database, read
the .LDB file. Remember that the .LDB is not cleared when a user
logs out, so this is not 100% accurate, but at least you can get an
idea who is in or has been. I run the function from a Macro, and a
nice little pop-up box displays the user names in the .LDB file.
Have fun, and I would like any feed back. I also have a version
that allows for you to enter an .LDB file name anywhere on the
network and display the users in that database as well.
Function WhosInDB ()
Dim varPullID As String
Dim varUserID As String * 64
Dim Indx As Long
Dim varArray() As String
Dim varDisplay As String
Dim varLDBName As String
Dim dbName As Database
Dim varBackPath As Integer
Dim Max As Integer
Set dbName = CurrentDB()
varLDBName = Left(dbName.Name, Len(dbName.Name) - 4) & _
".LDB" ' Assign .LDB file name
On Error GoTo errFileOpenError
Open varLDBName For Random Shared As #1 Len = Len(varUserID)
' Open to read file.
Max = LOF(1) / Len(varUserID)
ReDim varArray(1, Max) ' Set up Array to hold user names
For Indx = 1 To Max ' Loop to cycle through entries.
On Error GoTo errGatheringUsers
Get #1, Indx, varUserID ' Read from file.
varPullID = Mid(varUserID, 33, 7) ' Pull User ID from 64 bites
varArray(1, Indx) = varPullID ' Assign User ID to Array
' Put User ID's in one variable
varDisplay = varDisplay & varArray(1, Indx) & Chr(13)
Next Indx
' Display users in MsgBox
MsgBox "These users are currently" & Chr(13) _
"logged into the database:" & _
Chr(13) & Chr(13) & varDisplay
Close #1 ' Close file
Exit Function
errNoLDBFile:
MsgBox "There is no LDB file to pull data from"
Exit Function
errFileOpenError:
MsgBox "There was an error opening the LDB file for read. " & _
"Please try again."
Exit Function
errGatheringUsers:
MsgBox "There was an error gathering the User names. " & _
"Please try again."
Exit Function
End Function
10. Handling Year 2000 Issues (Y2K)
-------------------------------------------------------------------
ReplyTo: Christopher R. Baker
URL: http://www.wji.com/FoxPro/w9268.html
ftp://ftp.wji.com/FoxPro/sagent.zip //-Download SAgent.ZIP (19,435)
Keywords: TIPS YEAR 2000
FoxPro Versions: 2.0 and Up
Editor's Note. SAgent.mdb comprises of source code and demo files
from Chris Baker's January, 1998 presentation to the FoxPro User
Group in Boston.
In the sample below, Chris creates a manageable technique to control
FoxPro intrinsic Y2K methods. For details of this and many other
handy routines download SAgent.mdb (FoxPro 2.0.)
Function saCentury (varDate As Variant) As Variant
On Error GoTo Err_saCentury
' Chris Baker cBaker@SpecialAgent.com
' Given a date with a two digit year, FoxPro 2.0
' ALWAYS ASSUMES THE YEAR IS 19xx
' Regardless of the system date.
'
' This routine compensates for 2-digit year ambiguity by creating
' 100 year window for valid dates. The window 'rolls over' at
' the year calculated by adding FUTURE_YEARS to the present system
' year.
'
' This system does not properly handle all four digit years that
' fall outside of the 100 year window.
'
Const FUTURE_YEARS = 10
Dim intDateYear As Integer 'Year part of date to be tested
Dim intFutureYear As Integer 'Future Year Cutoff for all dates
Dim intPastYear As Integer '
saCentury = varDate
If IsDate(varDate) Then
intDateYear = DatePart("yyyy", varDate)
intFutureYear = DatePart("yyyy", Now) + FUTURE_YEARS
intPastYear = intFutureYear - 100
If (intDateYear > intFutureYear) Then
saCentury = DateAdd("yyyy", -100, varDate)
ElseIf (intDateYear <= intPastYear) Then
saCentury = DateAdd("yyyy", 100, varDate)
End If
End If
Exit_saCentury:
Exit Function
Err_saCentury:
'!! saMsgErr Error$, Err, "zbasUtility.saCentury"
Resume Exit_saCentury
End Function
11. Achieving Automation Goals using Database Documenters Code.
-------------------------------------------------------------------
ReplyTo: Michael Kaplan
ReplyTo: Mark Higham
URL: http://www.wji.com/FoxPro/w9260.html
URL: http://www.wji.com/FoxPro/w9270.html
RE: Interrogating Macros Using Automation by Mike Swift.
I have an application which, using Automation and COM does the following
things:
o Starts an FoxPro session
o Loads a database from a pre-defined list of files
o Looks through each collection - Forms, Queries, Modules etc.
o Opens, design view, each item in these collections
o Prints the properties of the items (or components within the items.)
o For modules, the application interrogates individual lines of
source code (using the Lines property), and searches for certain
keywords.
Now, I want to do the same thing with Macros as I've done with
modules - using automation....
Kaplan replies:
Have you considered perhaps looking at the Database Documenter's
code in the unlocked wizards and trying to make that work for you?
There are no guarantees that this will work past FoxPro 97, but
you are in undocumented-land already as it is.
Higham replies:
You could try doing something with the Application.SaveAsText
procedure. This procedure is a hidden member of the 'FoxPro
Application Object'. Go to your 'Object Browser' and ensure that
'Show Hidden Members' is checked. The syntax is relatively
straightforward
Application.SaveAsText acMacro, "YOURMACROHERE", "FILENAMEHERE"
You still have to parse the text file.
12. Repairing and Compacting with Jet API's: A Question
-------------------------------------------------------------------
ReplyTo: Michael Kaplan
URL: http://www.wji.com/FoxPro/w9259.html
RE: Repairing and Compacting Databases Automatically by Holger Rohlfs.
Is there a possibility to repair and compact a database
automatically, AND finding out if an error occurred in FoxPro 2.0?
Currently I use the following code:
Code (and further details) Follow
There is no fix or anything here that you can do. The only reason
that FoxPro is able to warn you of errors is that they use Jet
directly, not DAO.
There is no way that anyone outside of Microsoft can do the Jet APIs
to compact or repair, and that is the only way you would be able to
do this.
13. Optimizing the Speed of Report Runs.
-------------------------------------------------------------------
ReplyTo: Dave Milidonis
URL: http://www.wji.com/FoxPro/w9263.html
FoxPro Version: 7.0
While working on a client project I stumbled on a neat trick for
optimizing the speed of report runs. Reports tend to run faster off
tables, rather than directly off queries, so create a "make table"
query for your report data. Once done, use an Event Procedure on
the report's On_Open event and insert the following code:
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenQuery "mkqryName",,acReadOnly
DoCmd.OpenReport "rptName", acViewPreview
End Sub
Now, run the report.
The query executes creating the table and bringing up the report.
All with the click of a mouse. I am sure any developer could add
more to the event procedure in order to further automate
processing. For example: Saving the Report to a file; sending the
report; etc.
14. Creating a Flexible User Interface Design with 'System' Tables
-------------------------------------------------------------------
ReplyTo: Robert S. Ruder
URL: http://www.wji.com/FoxPro/w9224.html
FoxPro Versions: 97
It has occurred to me that if you take the concept of databases to
a logical conclusion, then the whole process of designing a
database is built around sets of data which describe the nature
and process of the database. This is in fact what Microsoft
employs in their System Tables. What I decided to develop was a
database design which would allow me to add functionality to the
database by simply adding a record to a table which defined that
functionality.
What I have managed to build is a user interface which is designed
around an initial 'database map' form which allows the user to
choose a selection of database tasks based on a selected
category... in this case I use a department metaphor. However, no
matter which department the user selects, the second form you go
to is the same, but as it opens, I query my system table to find
out how to modify the form for the chosen department.
My system tables are as follows:
Table Name: Task Categories
Fields: TaskCategoryID AutoNumber
CategoryName Text
Table Name: Tasks
Fields: TaskID AutoNumber
TaskName Text
TaskCategory Number-Long Integer
TaskSubForm Text
When the user clicks on a map item, the following code is
executed:
Function DisplayTasks(Dept As Integer)
'The Dept value is passed in, i.e. =displayTasks(1)
Dim tempdb As Database, temprs As Recordset
Dim SQLst As String
Set tempdb = CurrentDb
SQLst = "Select * from TaskCategories where TaskCategoryID = " & _
Dept Set temprs = tempdb.OpenRecordset(SQLst)
DoCmd.OpenForm "Unit Form", acNormal
Forms![Unit Form].Caption = temprs![CategoryName] & "Tasks"
Forms![Unit Form]![Title] = temprs![CategoryName] & "Options"
Forms![Unit Form]![TaskCatID] = Dept
End Function
The control [TaskCatID] is an unbound Text Box which is the Link
Master for a subform control. It displays the appropriate tasks
for the department selected. The form [Unit Form] also contains
another unbound subform control. Because my Tasks system table
contains the field [TaskSubform], I have a location where I can
store the name of the appropriate subform for the defined task. In
the On Current event for the tasks subform, I execute the
following code:
Parent![SubDisplay].SourceObject = _
IIf(IsNull([TaskSubForm]),"sfrm_None", [TaskSubForm])
The result is that which ever record becomes current in the left
hand list of tasks, the appropriate subform is displayed in the
right hand subform control. As my list of tasks grows, all I must
do is create a subform which can perform the task, add the task to
the tasks table, categorize it, and add its subform name. The rest
is already in place.
I am currently working on expanding this concept to include
information about attached tables and data synchronization
information between two attached data sources. My code can simply
loop through a set of data and attach and synchronize the data
automatically. Overall, I'm pleased with the ability to create
dynamic designs by incorporating my own custom system tables.
15. ODBCDirect Askew Without VB 5.0 Pro Installed
-------------------------------------------------------------------
ReplyTo: Kevin Bruton
URL: http://www.wji.com/FoxPro/w9258.html
FoxPro Version: 97
ODBCDirect demonstrates unpredictible behaviour when VB 5.0
Professional is not installed on a machine. Sample Code :
Open RecordSet
Set rsProduct = gblcnnODBC.OpenRecordset(strSQL, dbOpenDynaset, _
0, dbOptimisticValue)
'Then later on issue a
With rsProduct
.Edit
rsProduct!Fields = Something
.Update
End With
Locking problem encountered:
Machine A updates the same record Machine B is viewing. Machine B
tries to update the record, If VB 5.0 is installed, the record
locking error is correctly triggered, if VB 5.0 is not installed,
no error is returned, but the record is not updated.
Error : 0 Assignment error.
With Recordset
.Edit
rsProduct!Fields = Something
.Update
End With
Machine A, tries to Update Record, receives error (VB 5.0 not
installed.) Machine B, tries to Update Record, suceeds (VB 5.0
installed.)
The code is identical on the two machines , the only differance is
when VB 0.5 Pro is installed. How can this be solved without
installing VB 5.0 Pro on each machine?
Installing the latest DAO (1.5) drivers did not solve this
problem.
16. Setting Echo On/Off For Debugging
-------------------------------------------------------------------
ReplyTo: Mark Higham
URL: http://www.wji.com/FoxPro/w9256.html
RE: Speeding Up Your Front End with the Echo Method by Paul Brower.
FoxPro Versions: 2.0 (?), 7.0, 8.0
Are you aware that the Echo Method is a great tool for speeding up
many processes. When used correctly, the echo method will stop the
screen from being updated/repainted.
Let's say that you have a Customer Screen (frmCustomer), and an
Invoice Screen (frmInvoice). You want to be able to allow users
to double click an invoice, and go directly to the customer. Here
is the Event you would use:
Code follows:
When debugging FoxPro 97 code that has the echo set to off, make
sure that you set the debug window to be on top. That way you can
always do CTRL+G and get to it.
In FoxPro 97, I use compiler constants to set echo. For example:
#If FDEBUG Then
'continue as normal
#Else
Application.Echo False
#End If
17. Printing Relations from Any FoxPro 97 Database
-------------------------------------------------------------------
ReplyTo: Mark Higham
URL: http://www.wji.com/FoxPro/w9153.html
FoxPro Versions: 8.0
Keywords: PRINT RELATIONS
ftp://ftp.wji.com/FoxPro/daorelat.exe //-DAORELAT.EXE (219,407)
I wrote this after downloading the Print Relations Wizard which
was patently no use.
This self extracting zip file allows you to print out relations
from any FoxPro 97 database, you can save each separate diagram.
Zoom in and out.
Generally it gives good relational diagrams which you can include
in any documentation.
18. Internet Database in German FoxPro 97
-------------------------------------------------------------------
ReplyTo: Karl-Heinz Kristen
URL: http://www.wji.com/FoxPro/w9155.html
URL: http://193.24.175.164/aspneu/VhsStart.htm //-Online example
ftp: ftp.wji.com/FoxPro/i_net.zip //-I-NET.ZIP (199,811)
Product Ver.: German FoxPro 2.0 and 8.0, German IIS and German NT
System: Windows NT / Windows 95 - InternetServer 3.0
Development: ASP (ActiveServerPages) from MS must be installed.
Database: FoxPro 2.0 or FoxPro 7.0
Language: VBScript / VisualBasic (2.0 - 5.0)
Important: Use the Permission-Configuration from the
InternetServer and check the Directorys of the InetPub
for Read/Write & Run.
Step by Step Prodcedure to run the DB on an Intranet.
1. Copy all Files to your InetPub-Root. I used the
E:\InetPub\wwwroot\aspneu as Directory
2. Goto the ControlPanel to invoke the ODBC-Manager for a new
System-DSN entry.
i. Check the System-DNS to make a new entry.
ii. Check the FoxPro-Driver (Version 2.0/7.0)
iii. Put in name of Database(ASP-File = aspneu), name it: aspneu
iv. Check the Select-Button from Database and choose the
Database-Directory. I used
E:\InetPub\wwwroot\aspneu\kurse2.mdb
3. Now run IIE or Netscape and call Homepage (VHSSTART.HTM)
For more Information on ASP. A very good Book about ASP is 'ASP:
Working with ActiveServerPages', available at Carry Book Support
at http://www.caryp.com/
19. FoxPro 8.0 Export Facilities.
-------------------------------------------------------------------
ReplyTo: Anders H. Madsen
URL: http://www.wji.com/FoxPro/w9170.html
FoxPro Versions: 8.0
ftp://ftp.wji.com/FoxPro/ac8exnl.zip //-AC8EXNL.ZIP (210,080)
Abstract
The attached database includes two top level procedures for
exporting which can be used instead of TransferText in certain
situations.
TransferText_nl
This procedure is similar to TransferText but independent of
fields. What this means is that TransferText_nl can be used with
tables and queries even if they have different fields than the
table or query, which were used when creating the import/export
specification. In other words, TransferText_nl works more like
TransferText in the good old FoxPro 2.0.
Limitations: Only works for exporting of delimited test files. Can
not export to ASCII format.
Export_nl
This is an export procedure which in some ways are more flexible
than TransferText and TransferText_nl. The procedure is especially
valuable when exporting cross tab queries. The most important
facilities are:
o Uses import export specifications but independent of fields
(like FoxPro 2.0)
o Also capable of using an other record separator than cariage
return line feed.
o Capable of sorting columns in complex ways.
o Can export two queries to the same file: Typically this is a
detail query and a totals query.
o Can convert null values to zero.
Limitations: Only works for exporting of delimited test files.
Cannot export to ASCII format.
References to similar pieces of work
http://www.wji.com/FoxPro/w6630.html -- Incompatibility Between
FoxPro 2.0 and FoxPro7.0/8.0 Text File Inport/Export by Shamil
Salakhetdinov.
Mr. Salakhetdinov has overcome the problem with FoxPro 8.0's
dependence of fields in an other way. His approach is to create a
specification and then use FoxPro's own TransferText. In short his
code has the following advantages:
o Can both import and export.
o Can handle both delimited and fixed width formats.
o Also works for ASCII format.
o Less code (= less memory footprint).
In short our approach has the following advantages:
o Several good facilities for handling cross tab queries (see the
comments in Export_nl below).
o Can put together the results of a detail query and a totals
query into one export file.
o Also capable of using an other record separator than cariage
return line feed.
o You do not have to change any code to use other delimiters etc.
20. Customizing Function Keys in FoxPro
-------------------------------------------------------------------
ReplyTo: Paul Brower
URL: http://www.wji.com/FoxPro/w9172.html
ftp://ftp.wji.com/FoxPro/fkeys.zip //-FKEYS.ZIP (29,301)
FoxPro Versions: 8.0
The file fKeys.mdb consists of 1 macro (AutoKeys), 1 form
(frmFKeys) and 1 module (mdlFkeys).
These items will allow developers to run code by pressing a
function key. The form frmFkeys allows users to customize what
function key will perform what action. The windows registry is
used to keep track of the function key assignments.
For an example, when you download this database, open up the form
frmFkeys, select "ScreenName" in the combo box, and click a
function key command button. (i.e. F-5). "ScreenName" will
appear in the text box under the command button. You must now
exit the database and re-open it. Now you can press function key
F-5 (or the one that you assigned), and if you have a form open, a
message box will appear with the form name. By looking at my
code, you should be able to see that it will now be very simple
for you to create new subroutines and be able to execute them by
pressing a function key.
I have a client that was using a system called TeleMagic, and they
were used to being able to print forms, etc, from the use of
function keys. They asked that I provide the same functionality
with function keys in the new FoxPro database, and this has worked
out very well.
Important Notes:
o You must add subroutine names to the combo box on the form
"frmFkeys." i.e. if you create a sub called "Calculate
Commission", you need to add "Calculate Commission" to the value
list in the combo box.
o I don't have any error code for these examples. You will need
to create your own.
o Additional key assignments could be made. For example if you
wanted to assign a subroutine to "Ctl-F5", you would need to add
^{F5} in the macro AutoKeys, and provide the necessary controls
and code on the form and module.
o The function key values are stored in the windows registry as
"txtF#". My code requires that a field prefixed with "txtF" be
present in order to write the registry information. Feel free
to change this.
o This code if free and available to all who dare to make their
development just a tad bit more user friendly, and cool.
21. Comment Block and UnComment Block Builders for FoxPro 97
-------------------------------------------------------------------
ReplyTo: Mehmet Acikgoz
URL: http://www.wji.com/FoxPro/w9154.html
ftp://ftp.wji.com/FoxPro/pdncbuil.zip //-PDNCBuil.ZIP (23,609)
Keywords: COMMENT BLOCK UNCOMMENT BLOCK BUILDERS FoxPro 97
FoxPro Versions: 8.0
In developing an FoxPro application, the developer might
frequently need commenting out or un-commenting block of codes in
module windows. These are quite tedious processes if the block has
several tens of code lines.
In complex modules, it might be useful to put some sort of signs
to mark commented blocks as showing "why they have been commented"
or in team work situations; "who commented out the block of code
lines and why". These marks can play some sort of vital role in
programming techniques.
To solve these issues, I present two separate builders in the same
file.
22. Passing SQL Strings to FoxPro
-------------------------------------------------------------------
ReplyTo: <>
URL: http://www.wji.com/FoxPro/w9223.html
FoxPro Versions: All.
When passing SQL strings, you have to be careful about embedded
apostrophes and quotation marks. For example, this SQL command
will fail for the name O'Brien:
strName = "O'Brien"
DoCmd.RunSQL _
"DELETE * FROM Customers WHERE Fullname = ' " & strName & " ';"
Because of the embedded apostrophe in O'Brien, the SQL string
won't be interpreted properly.
To solve this problem, I wrote a function called SafeSQLString
which surrounds the string in quotation marks. It also replaces
any embedded quotations marks with double quotations, which SQL
can handle. You use the function like this:
strName = "O'Brien"
DoCmd.RunSQL _
"DELETE * FROM Customers WHERE Fullname = " & _
SafeSQLString(strName) & ";"
Here's the function along with the Replace helper function:
Public Function SafeSQLString(Text As Variant) As Variant
On Error GoTo Err_SafeSQLString
Const QUOTE = """" ' That's 4 quotation marks in a row.
If IsNull(Text) Then
SafeSQLString = QUOTE & QUOTE
Else
SafeSQLString = QUOTE & _
Replace(Text, QUOTE, QUOTE & QUOTE) & QUOTE
End If
Exit_SafeSQLString:
Exit Function
Err_SafeSQLString:
SafeSQLString = ""
Resume Exit_SafeSQLString
End Function
Public Function Replace(ByVal varValue As Variant, _
ByVal strFind As String, _
ByVal strReplace As String) As Variant
Dim intLenFind As Integer
Dim intLenReplace As Integer
Dim intPos As Integer
If IsNull(varValue) Then
Replace = Null
Else
intLenFind = Len(strFind)
intLenReplace = Len(strReplace)
intPos = 1
Do
intPos = InStr(intPos, varValue, strFind, 0)
If intPos > 0 Then
varValue =
Left(varValue, intPos - 1) & strReplace & _
Mid(varValue, intPos + intLenFind)
intPos = intPos + intLenReplace
End If
Loop Until intPos = 0
End If
Replace = varValue
End Function
I intentionally left out the single quotation mark. Since I'm
surrounding the string with double quotes, the single quotes are
no longer needed. Here are some examples:
o "DELETE * FROM Customers WHERE Fullname = " &
SafeSQLString("O'Brien")
The SQL engine will see the above string as:
DELETE * FROM Customers WHERE Fullname = "O'Brien"
o "DELETE * FROM Customers WHERE Fullname = " &
SafeSQLString("O'Brien")
The SQL engine will see the above string as:
DELETE * FROM Customers WHERE Fullname = "O'Brien"
o strName = "An embedded " & """" & " double quote!"
' translates to: An embedded " double quote!
"DELETE * FROM Customers WHERE Fullname = " & _
SafeSQLString(strName)
The SQL engine will see the above string as:
DELETE * FROM Customers WHERE Fullname = "An embedded "" double quote!"
23. LookIn on Outlook to Speed Up FoxPro and Office
-------------------------------------------------------------------
ReplyTo: Bharat Ahluwalia
URL: http://www.wji.com/FoxPro/w9142.html
Product Versions: FoxPro 97, Office 97
If you have MS Outlook installed, your FoxPro 97 and other Office
97 applications can be speeded up by changing some of the default
values.
o Open Outlook
o Go to Options under the Tools menu.
o Click on the Journal tab.
o Uncheck all the boxes from the Also Record files from: listbox.
If an application is checked, everytime you open the application a
journal entry is created, thus slowing you down.
24. From the Publisher
-------------------------------------------------------------------
The FoxPro Email Newsletter is created and distributed compliments
of the World Wide Developer Network (WWDN) of Boston, Massachusetts,
USA. Come, get involved! This is the place where developers talk
to developers as peers.
Publisher:
Les Squires
Word Jenny, Inc.
400 West Cummings Park, Woburn (Boston), Massachusetts USA
781-935-1520
Editors/Content Specialists
Phil Denoncourt III
Pat Tormey
Brendan Donovan
Website: http://www.wji.com/FoxPro/homepage.html
Address Changes:
Send an email USING YOUR OLD ADDRESS.
Indicate New Address.
Adding Subscribers to Mailing List:
Send Email to FoxPro@WJI.Com.
Include String FoxPro-YES in Subject: or Body of Message.
Deleting Subscribers:
Send Email to FoxPro@WJI.Com.
Include String FoxPro-NO in Subject: or Body of Message.
Retrieving Back Issues and General Downloads:
FTP to ftp.wji.com.
Login as FTP.
Use your ID as your password.
cd FoxPro
Copy back issues and downloads as they become available.
(c) 1998 World Wide Developer Network (WWDN)
From: Les Squires [lsquires@wj.WJI.COM]
Sent: Tuesday, February 10, 1998 1:47 AM
To: Rick@CICorp.com
Subject: AC198: FoxPro Email Newsletter
-----------------------------------------------------------------
Member: Rick Shaddock of Crystal City Maryland USA
Subs ON/off: ACCE clip FOXP sqlw it vdb wji
Jobs ON/off: acce actx c++ clip dbase delphi fox it java oracle
pb sqlserver sqlwind sybase vb vdb y2k
-----------------------------------------------------------------
Hi again, Rick!
Here's the catch for this month!
Les
-----------------------------------------------------------------------
-----------------------------------------------------------------------
FoxPro Email Newsletter January, 1998
A Service of the World Wide Developer Network Vol. 6 No. 1
http://www.wji.com/FoxPro/homepage.html ftp.wji.com/FoxPro
-----------------------------------------------------------------------
1. Editor's Note
2. FoxPro JobsExpress
3. Finding the Irish in your Database
4. Auditing the Records in an FoxPro 97 Table
5. Managing Your Reports with the Data-Driven Paradigm
6. Printing Reports as a Batch Process
7. Extracting the Network User ID using 16 Bit API Calls.
8. Showing System Objects And "Select *" Don't Mix In Replicas
9. Viewing Logged-on Users Through the .LDB
10. Handling Year 2000 Issues (Y2K)
11. Achieving Automation Goals using Database Documenters Code.
12. Repairing and Compacting with Jet API's
13. Optimizing the Speed of Report Runs.
14. Creating a Flexible User Interface Design with 'System' Tables
15. ODBCDirect Askew Without VB 5.0 Pro Installed
16. Setting Echo On/Off For Debugging
17. Printing Relations from Any FoxPro 97 Database
18. Internet Database in German FoxPro 97
19. FoxPro 8.0 Export Facilities.
20. Customizing Function Keys in FoxPro
21. Comment Block and UnComment Block Builders for FoxPro 97
22. Passing SQL Strings to FoxPro
23. LookIn on Outlook to Speed Up FoxPro and Office
24. From the Publisher
-----------------------------------------------------------------------
1. Editor's Note
-------------------------------------------------------------------
ReplyTo: Pat Tormey
URL: http://www.wji.com/FoxPro/w9161.html
Lots of good code this month. Thanks again to all of you who take
the time to enrich our FoxPro developer community!
2. FoxPro JobsExpress
-------------------------------------------------------------------
ReplyTo: Les Squires, Publisher
URL: http://www.wji.com/FoxPro/w7413.html
We've built a new email system to push job announcements immediately
to developers who want them. Please note that we are no longer
posting job announcements to web pages. Use one of the links below
to fire up the system! Let us know what you think!
Employers: http://www.wji.com/FoxPro/w7412.html
---------------------------------------------------------
Looking for full or part-time employees, consultants, tutors, or the
like? Submit your job announcement ad to us and we will email it to
our pool of FoxPro developers.
Developers: http://www.wji.com/FoxPro/w8495.html
---------------------------------------------------------
Sign up for FoxProJobsExpress! Receive an email of a job
announcement within 15 seconds of its being posted!
3. Finding the Irish in your Database
-------------------------------------------------------------------
ReplyTo: Pat Tormey
URL: http://www.wji.com/FoxPro/w9355.html
ftp://ftp.wji.com/FoxPro/fsifind.zip //-FSIFIND.ZIP (43,064)
FoxPro Version: 8.0 (But you could roll it back to version 2.0.)
Keywords: SQL, FINDIT, QUOTE
Problem: SQL engines can choke on a single quote, but they think
two single quotes are just one literal quote.
Solution: Use the file fsiFind.MDB. It converts O'Brein to O''Brien
and all single apostrophes to two apostrophes.
I got a little help writing this from the Microsoft Systems Journal
at http://www.microsoft.com/msj/
4. Auditing the Records in an FoxPro 97 Table
-------------------------------------------------------------------
ReplyTo: Pat Tormey
URL: http://www.wji.com/FoxPro/w9354.html
ftp://ftp.wji.com/FoxPro/fsiaudit.exe //-FSIAUDIT.EXE (190,416)
ftp://ftp.wji.com/FoxPro/fsivideo.exe //-FSIVIDEO.EXE (1,360,643)
Keywords: AUDIT TRAIL; CLASSES; NETWORK USER NAME
FoxPro Version: 97
The file fsiAudit.EXE contains an FoxPro 97 MDB (self extracting
PkZip) that demonstrates how to audit the records in an FoxPro
table.
Each record programmatically maintains its origin date/time,
date/time of the last edit, and the Network User ID of person logged
on at the time.
The sample code wraps the Network User Id API call in an FoxPro 97
Class, which can be used "as is" in VB 4-32 or VB 5.0. All source
code is included and it's free.
The file fsiVideo.EXE is an instructional overview for the code,
made with the MS Camcorder Movie. You will need a sound card to
fully appreciate it!
5. Managing Your Reports with the Data-Driven Paradigm
-------------------------------------------------------------------
ReplyTo: Pat Tormey
URL: http://www.wji.com/FoxPro/w9353.html
ftp://ftp.wji.com/FoxPro/ddreport.zip //-DDREPORT.ZIP (22,024)
Keywords: 'DATA DRIVEN' REPORTS
FoxPro Versions: All
This file Reports.MDB, is a complete 'Data Driven' example of an
easy way to manage all the reports in you application, developed in
FoxPro 2.0.
The idea is to store all reports in a table, let the user select the
reports they would like, and print them in a batch from a single
command button.
6. Printing Reports as a Batch Process
-------------------------------------------------------------------
ReplyTo: Pat Tormey
URL: http://www.wji.com/FoxPro/w9350.html
FoxPro Versions: All
Suppose you had a table with all available reports and you wanted to
let the user select several for printing.
All you'd have to do is let them toggle the 'PrintIt' field with a
simple form. You can even give the report a user friendly name so
they can recognize the report 'Mary's Monthly Report' instead of the
less kindly 'rptEmployeeLayoffsAntiPersonalDept'.
Then run this handy routine:
Sub ReportAll ()
'**************************************************************
' Author : Pat Tormey Tormey@foursquare.com
' Date : 7 April 1996
' Comments : Objective is to Select a report from Engine Listing
'***************************************************************
On Error GoTo ReportAll_ERR
Dim db As Database
Dim rs As Recordset
Dim i As Integer
Dim Count%
Dim x As Variant
Set db = DBengine.WorkSpaces(0).Databases(0)
Set rs = db.OpenRecordset("tblReports", DB_OPEN_SNAPSHOT)
'Count Up the Printit
Count% = 0
Do Until rs.EOF 'let's see how many reports are tagged to Print
If rs.PrintIt Then
Count% = Count% + 1
End If
rs.MoveNext
Loop
If Count% = 0 Then
Exit Sub
End If
'Set up the system Meter
x = SysCmd(SYSCMD_INITMETER, Str$(Count%) & " Reports", Count%)
rs.MoveFirst
Do Until rs.EOF
If rs.PrintIt Then 'Is It Tagged
x = PrintReport(rs.ReportName)
x = SysCmd(SYSCMD_UPDATEMETER, i)
End If
i = i + 1
rs.MoveNext
Loop
rs.Close
db.Close
x = SysCmd(SYSCMD_REMOVEMETER)
ReportAll_EXIT:
Exit Sub
ReportAll_ERR:
'Never Say Error .. especially if there is one
MsgBox "Note:" & Error$, 64, "Application Note"
Resume ReportAll_EXIT
End Sub
Function PrintReport (cReport)
'**********************************************************
' Author : Pat Tormey Tormey@foursquare.com
' Date : 7 April 1996
'**********************************************************
On Error GoTo PrintReport_ERR
DoCmd OpenReport cReport, A_NORMAL
PrintReport_EXIT:
Exit Function
PrintReport_ERR:
MsgBox "Note:" & Error$, 64, "Application Note: There is " & _
"nothing wrong. Go back to sleep"
Resume PrintReport_EXIT
End Function
7. Extracting the Network User ID using 16 Bit API Calls.
-------------------------------------------------------------------
ReplyTo: Pat Tormey
URL: http://www.wji.com/FoxPro/w9347.html
FoxPro Versions: All
This file extracts the Network User ID in FoxPro 2.0 using the 16
Bit API calls.
I must admit that I stole this idea from somebody, probably Dan
Appleman. I recommend his books 'Visual Basic Programmer's Guide to
the Windows API' for 16 bit calls and 'Visual Basic Programmers
Guide to the WIN 32 API' for 32 bit calls.
Option Compare Database 'Use database order for string comparisons
Option Explicit
' Uses WINDOW API to get the Net Users Name from the Local Area
' Network
' You can use this to see if the users should have write/delete
' privileges, or to secretly tag who did what last!
Declare Function wu_WNetGetUser% Lib "USER.EXE" Alias _
"WNetGetUser" (ByVal szUser$, lpnBufferSize%)
Function NetworkUserID () As String
'**********************************************************
' Author : Pat Tormey Tormey@foursquare.com
' Date : 7 April 1996
'**********************************************************
On Error GoTo NetworkUserID_ERR
Dim szUser As String * 255
Dim lpnBufferSize As Integer
Dim status As Integer
lpnBufferSize = 255
status = wu_WNetGetUser(szUser, lpnBufferSize)
If (status = 3) Then
NetworkUserID = "WNetGetUser Failed"
Else
'Return up to first Null.
NetworkUserID = Left$(szUser, InStr(szUser, Chr(0)) - 1)
End If
NetworkUserID_EXIT:
Exit Function
NetworkUserID_ERR:
MsgBox "Note:" & Error$, 64, "Application Note"
Resume NetworkUserID_EXIT
End Function
8. Showing System Objects And "Select *" Don't Mix In Replicas
-------------------------------------------------------------------
ReplyTo: Philip Hurzeler
URL: http://www.wji.com/FoxPro/w9341.html
FoxPro Versions: 97, possibly 95 (Try this in FoxPro 95, and
If any controls in your replicated FoxPro 97 database have row
sources that start with 'SELECT * FROM...', you can create bugs by
setting the 'Show System Objects' option on or off.
Setting the option on makes visible the system columns devoted to
replication, which can have a ripple effect. For instance, the
ordinal values of the Column property of list or combo boxes all get
bumped up by 3 unless the row source property calls out each column
by name.
One way you can prevent this problem is to avoid 'SELECT' in row
sources. Another way is to set the Application's 'Show System
Objects' option to False in your Autoexec macro or startup form's
Open event.
9. Viewing Logged-on Users Through the .LDB
-------------------------------------------------------------------
ReplyTo: Thom Allen
URL: http://www.wji.com/FoxPro/w9322.html
FoxPro Version: 2.0
For a very rudimentary view of who is logged in to a database, read
the .LDB file. Remember that the .LDB is not cleared when a user
logs out, so this is not 100% accurate, but at least you can get an
idea who is in or has been. I run the function from a Macro, and a
nice little pop-up box displays the user names in the .LDB file.
Have fun, and I would like any feed back. I also have a version
that allows for you to enter an .LDB file name anywhere on the
network and display the users in that database as well.
Function WhosInDB ()
Dim varPullID As String
Dim varUserID As String * 64
Dim Indx As Long
Dim varArray() As String
Dim varDisplay As String
Dim varLDBName As String
Dim dbName As Database
Dim varBackPath As Integer
Dim Max As Integer
Set dbName = CurrentDB()
varLDBName = Left(dbName.Name, Len(dbName.Name) - 4) & _
".LDB" ' Assign .LDB file name
On Error GoTo errFileOpenError
Open varLDBName For Random Shared As #1 Len = Len(varUserID)
' Open to read file.
Max = LOF(1) / Len(varUserID)
ReDim varArray(1, Max) ' Set up Array to hold user names
For Indx = 1 To Max ' Loop to cycle through entries.
On Error GoTo errGatheringUsers
Get #1, Indx, varUserID ' Read from file.
varPullID = Mid(varUserID, 33, 7) ' Pull User ID from 64 bites
varArray(1, Indx) = varPullID ' Assign User ID to Array
' Put User ID's in one variable
varDisplay = varDisplay & varArray(1, Indx) & Chr(13)
Next Indx
' Display users in MsgBox
MsgBox "These users are currently" & Chr(13) _
"logged into the database:" & _
Chr(13) & Chr(13) & varDisplay
Close #1 ' Close file
Exit Function
errNoLDBFile:
MsgBox "There is no LDB file to pull data from"
Exit Function
errFileOpenError:
MsgBox "There was an error opening the LDB file for read. " & _
"Please try again."
Exit Function
errGatheringUsers:
MsgBox "There was an error gathering the User names. " & _
"Please try again."
Exit Function
End Function
10. Handling Year 2000 Issues (Y2K)
-------------------------------------------------------------------
ReplyTo: Christopher R. Baker
URL: http://www.wji.com/FoxPro/w9268.html
ftp://ftp.wji.com/FoxPro/sagent.zip //-Download SAgent.ZIP (19,435)
Keywords: TIPS YEAR 2000
FoxPro Versions: 2.0 and Up
Editor's Note. SAgent.mdb comprises of source code and demo files
from Chris Baker's January, 1998 presentation to the FoxPro User
Group in Boston.
In the sample below, Chris creates a manageable technique to control
FoxPro intrinsic Y2K methods. For details of this and many other
handy routines download SAgent.mdb (FoxPro 2.0.)
Function saCentury (varDate As Variant) As Variant
On Error GoTo Err_saCentury
' Chris Baker cBaker@SpecialAgent.com
' Given a date with a two digit year, FoxPro 2.0
' ALWAYS ASSUMES THE YEAR IS 19xx
' Regardless of the system date.
'
' This routine compensates for 2-digit year ambiguity by creating
' 100 year window for valid dates. The window 'rolls over' at
' the year calculated by adding FUTURE_YEARS to the present system
' year.
'
' This system does not properly handle all four digit years that
' fall outside of the 100 year window.
'
Const FUTURE_YEARS = 10
Dim intDateYear As Integer 'Year part of date to be tested
Dim intFutureYear As Integer 'Future Year Cutoff for all dates
Dim intPastYear As Integer '
saCentury = varDate
If IsDate(varDate) Then
intDateYear = DatePart("yyyy", varDate)
intFutureYear = DatePart("yyyy", Now) + FUTURE_YEARS
intPastYear = intFutureYear - 100
If (intDateYear > intFutureYear) Then
saCentury = DateAdd("yyyy", -100, varDate)
ElseIf (intDateYear <= intPastYear) Then
saCentury = DateAdd("yyyy", 100, varDate)
End If
End If
Exit_saCentury:
Exit Function
Err_saCentury:
'!! saMsgErr Error$, Err, "zbasUtility.saCentury"
Resume Exit_saCentury
End Function
11. Achieving Automation Goals using Database Documenters Code.
-------------------------------------------------------------------
ReplyTo: Michael Kaplan
ReplyTo: Mark Higham
URL: http://www.wji.com/FoxPro/w9260.html
URL: http://www.wji.com/FoxPro/w9270.html
RE: Interrogating Macros Using Automation by Mike Swift.
I have an application which, using Automation and COM does the following
things:
o Starts an FoxPro session
o Loads a database from a pre-defined list of files
o Looks through each collection - Forms, Queries, Modules etc.
o Opens, design view, each item in these collections
o Prints the properties of the items (or components within the items.)
o For modules, the application interrogates individual lines of
source code (using the Lines property), and searches for certain
keywords.
Now, I want to do the same thing with Macros as I've done with
modules - using automation....
Kaplan replies:
Have you considered perhaps looking at the Database Documenter's
code in the unlocked wizards and trying to make that work for you?
There are no guarantees that this will work past FoxPro 97, but
you are in undocumented-land already as it is.
Higham replies:
You could try doing something with the Application.SaveAsText
procedure. This procedure is a hidden member of the 'FoxPro
Application Object'. Go to your 'Object Browser' and ensure that
'Show Hidden Members' is checked. The syntax is relatively
straightforward
Application.SaveAsText acMacro, "YOURMACROHERE", "FILENAMEHERE"
You still have to parse the text file.
12. Repairing and Compacting with Jet API's: A Question
-------------------------------------------------------------------
ReplyTo: Michael Kaplan
URL: http://www.wji.com/FoxPro/w9259.html
RE: Repairing and Compacting Databases Automatically by Holger Rohlfs.
Is there a possibility to repair and compact a database
automatically, AND finding out if an error occurred in FoxPro 2.0?
Currently I use the following code:
Code (and further details) Follow
There is no fix or anything here that you can do. The only reason
that FoxPro is able to warn you of errors is that they use Jet
directly, not DAO.
There is no way that anyone outside of Microsoft can do the Jet APIs
to compact or repair, and that is the only way you would be able to
do this.
13. Optimizing the Speed of Report Runs.
-------------------------------------------------------------------
ReplyTo: Dave Milidonis
URL: http://www.wji.com/FoxPro/w9263.html
FoxPro Version: 7.0
While working on a client project I stumbled on a neat trick for
optimizing the speed of report runs. Reports tend to run faster off
tables, rather than directly off queries, so create a "make table"
query for your report data. Once done, use an Event Procedure on
the report's On_Open event and insert the following code:
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenQuery "mkqryName",,acReadOnly
DoCmd.OpenReport "rptName", acViewPreview
End Sub
Now, run the report.
The query executes creating the table and bringing up the report.
All with the click of a mouse. I am sure any developer could add
more to the event procedure in order to further automate
processing. For example: Saving the Report to a file; sending the
report; etc.
14. Creating a Flexible User Interface Design with 'System' Tables
-------------------------------------------------------------------
ReplyTo: Robert S. Ruder
URL: http://www.wji.com/FoxPro/w9224.html
FoxPro Versions: 97
It has occurred to me that if you take the concept of databases to
a logical conclusion, then the whole process of designing a
database is built around sets of data which describe the nature
and process of the database. This is in fact what Microsoft
employs in their System Tables. What I decided to develop was a
database design which would allow me to add functionality to the
database by simply adding a record to a table which defined that
functionality.
What I have managed to build is a user interface which is designed
around an initial 'database map' form which allows the user to
choose a selection of database tasks based on a selected
category... in this case I use a department metaphor. However, no
matter which department the user selects, the second form you go
to is the same, but as it opens, I query my system table to find
out how to modify the form for the chosen department.
My system tables are as follows:
Table Name: Task Categories
Fields: TaskCategoryID AutoNumber
CategoryName Text
Table Name: Tasks
Fields: TaskID AutoNumber
TaskName Text
TaskCategory Number-Long Integer
TaskSubForm Text
When the user clicks on a map item, the following code is
executed:
Function DisplayTasks(Dept As Integer)
'The Dept value is passed in, i.e. =displayTasks(1)
Dim tempdb As Database, temprs As Recordset
Dim SQLst As String
Set tempdb = CurrentDb
SQLst = "Select * from TaskCategories where TaskCategoryID = " & _
Dept Set temprs = tempdb.OpenRecordset(SQLst)
DoCmd.OpenForm "Unit Form", acNormal
Forms![Unit Form].Caption = temprs![CategoryName] & "Tasks"
Forms![Unit Form]![Title] = temprs![CategoryName] & "Options"
Forms![Unit Form]![TaskCatID] = Dept
End Function
The control [TaskCatID] is an unbound Text Box which is the Link
Master for a subform control. It displays the appropriate tasks
for the department selected. The form [Unit Form] also contains
another unbound subform control. Because my Tasks system table
contains the field [TaskSubform], I have a location where I can
store the name of the appropriate subform for the defined task. In
the On Current event for the tasks subform, I execute the
following code:
Parent![SubDisplay].SourceObject = _
IIf(IsNull([TaskSubForm]),"sfrm_None", [TaskSubForm])
The result is that which ever record becomes current in the left
hand list of tasks, the appropriate subform is displayed in the
right hand subform control. As my list of tasks grows, all I must
do is create a subform which can perform the task, add the task to
the tasks table, categorize it, and add its subform name. The rest
is already in place.
I am currently working on expanding this concept to include
information about attached tables and data synchronization
information between two attached data sources. My code can simply
loop through a set of data and attach and synchronize the data
automatically. Overall, I'm pleased with the ability to create
dynamic designs by incorporating my own custom system tables.
15. ODBCDirect Askew Without VB 5.0 Pro Installed
-------------------------------------------------------------------
ReplyTo: Kevin Bruton
URL: http://www.wji.com/FoxPro/w9258.html
FoxPro Version: 97
ODBCDirect demonstrates unpredictible behaviour when VB 5.0
Professional is not installed on a machine. Sample Code :
Open RecordSet
Set rsProduct = gblcnnODBC.OpenRecordset(strSQL, dbOpenDynaset, _
0, dbOptimisticValue)
'Then later on issue a
With rsProduct
.Edit
rsProduct!Fields = Something
.Update
End With
Locking problem encountered:
Machine A updates the same record Machine B is viewing. Machine B
tries to update the record, If VB 5.0 is installed, the record
locking error is correctly triggered, if VB 5.0 is not installed,
no error is returned, but the record is not updated.
Error : 0 Assignment error.
With Recordset
.Edit
rsProduct!Fields = Something
.Update
End With
Machine A, tries to Update Record, receives error (VB 5.0 not
installed.) Machine B, tries to Update Record, suceeds (VB 5.0
installed.)
The code is identical on the two machines , the only differance is
when VB 0.5 Pro is installed. How can this be solved without
installing VB 5.0 Pro on each machine?
Installing the latest DAO (1.5) drivers did not solve this
problem.
16. Setting Echo On/Off For Debugging
-------------------------------------------------------------------
ReplyTo: Mark Higham
URL: http://www.wji.com/FoxPro/w9256.html
RE: Speeding Up Your Front End with the Echo Method by Paul Brower.
FoxPro Versions: 2.0 (?), 7.0, 8.0
Are you aware that the Echo Method is a great tool for speeding up
many processes. When used correctly, the echo method will stop the
screen from being updated/repainted.
Let's say that you have a Customer Screen (frmCustomer), and an
Invoice Screen (frmInvoice). You want to be able to allow users
to double click an invoice, and go directly to the customer. Here
is the Event you would use:
Code follows:
When debugging FoxPro 97 code that has the echo set to off, make
sure that you set the debug window to be on top. That way you can
always do CTRL+G and get to it.
In FoxPro 97, I use compiler constants to set echo. For example:
#If FDEBUG Then
'continue as normal
#Else
Application.Echo False
#End If
17. Printing Relations from Any FoxPro 97 Database
-------------------------------------------------------------------
ReplyTo: Mark Higham
URL: http://www.wji.com/FoxPro/w9153.html
FoxPro Versions: 8.0
Keywords: PRINT RELATIONS
ftp://ftp.wji.com/FoxPro/daorelat.exe //-DAORELAT.EXE (219,407)
I wrote this after downloading the Print Relations Wizard which
was patently no use.
This self extracting zip file allows you to print out relations
from any FoxPro 97 database, you can save each separate diagram.
Zoom in and out.
Generally it gives good relational diagrams which you can include
in any documentation.
18. Internet Database in German FoxPro 97
-------------------------------------------------------------------
ReplyTo: Karl-Heinz Kristen
URL: http://www.wji.com/FoxPro/w9155.html
URL: http://193.24.175.164/aspneu/VhsStart.htm //-Online example
ftp: ftp.wji.com/FoxPro/i_net.zip //-I-NET.ZIP (199,811)
Product Ver.: German FoxPro 2.0 and 8.0, German IIS and German NT
System: Windows NT / Windows 95 - InternetServer 3.0
Development: ASP (ActiveServerPages) from MS must be installed.
Database: FoxPro 2.0 or FoxPro 7.0
Language: VBScript / VisualBasic (2.0 - 5.0)
Important: Use the Permission-Configuration from the
InternetServer and check the Directorys of the InetPub
for Read/Write & Run.
Step by Step Prodcedure to run the DB on an Intranet.
1. Copy all Files to your InetPub-Root. I used the
E:\InetPub\wwwroot\aspneu as Directory
2. Goto the ControlPanel to invoke the ODBC-Manager for a new
System-DSN entry.
i. Check the System-DNS to make a new entry.
ii. Check the FoxPro-Driver (Version 2.0/7.0)
iii. Put in name of Database(ASP-File = aspneu), name it: aspneu
iv. Check the Select-Button from Database and choose the
Database-Directory. I used
E:\InetPub\wwwroot\aspneu\kurse2.mdb
3. Now run IIE or Netscape and call Homepage (VHSSTART.HTM)
For more Information on ASP. A very good Book about ASP is 'ASP:
Working with ActiveServerPages', available at Carry Book Support
at http://www.caryp.com/
19. FoxPro 8.0 Export Facilities.
-------------------------------------------------------------------
ReplyTo: Anders H. Madsen
URL: http://www.wji.com/FoxPro/w9170.html
FoxPro Versions: 8.0
ftp://ftp.wji.com/FoxPro/ac8exnl.zip //-AC8EXNL.ZIP (210,080)
Abstract
The attached database includes two top level procedures for
exporting which can be used instead of TransferText in certain
situations.
TransferText_nl
This procedure is similar to TransferText but independent of
fields. What this means is that TransferText_nl can be used with
tables and queries even if they have different fields than the
table or query, which were used when creating the import/export
specification. In other words, TransferText_nl works more like
TransferText in the good old FoxPro 2.0.
Limitations: Only works for exporting of delimited test files. Can
not export to ASCII format.
Export_nl
This is an export procedure which in some ways are more flexible
than TransferText and TransferText_nl. The procedure is especially
valuable when exporting cross tab queries. The most important
facilities are:
o Uses import export specifications but independent of fields
(like FoxPro 2.0)
o Also capable of using an other record separator than cariage
return line feed.
o Capable of sorting columns in complex ways.
o Can export two queries to the same file: Typically this is a
detail query and a totals query.
o Can convert null values to zero.
Limitations: Only works for exporting of delimited test files.
Cannot export to ASCII format.
References to similar pieces of work
http://www.wji.com/FoxPro/w6630.html -- Incompatibility Between
FoxPro 2.0 and FoxPro7.0/8.0 Text File Inport/Export by Shamil
Salakhetdinov.
Mr. Salakhetdinov has overcome the problem with FoxPro 8.0's
dependence of fields in an other way. His approach is to create a
specification and then use FoxPro's own TransferText. In short his
code has the following advantages:
o Can both import and export.
o Can handle both delimited and fixed width formats.
o Also works for ASCII format.
o Less code (= less memory footprint).
In short our approach has the following advantages:
o Several good facilities for handling cross tab queries (see the
comments in Export_nl below).
o Can put together the results of a detail query and a totals
query into one export file.
o Also capable of using an other record separator than cariage
return line feed.
o You do not have to change any code to use other delimiters etc.
20. Customizing Function Keys in FoxPro
-------------------------------------------------------------------
ReplyTo: Paul Brower
URL: http://www.wji.com/FoxPro/w9172.html
ftp://ftp.wji.com/FoxPro/fkeys.zip //-FKEYS.ZIP (29,301)
FoxPro Versions: 8.0
The file fKeys.mdb consists of 1 macro (AutoKeys), 1 form
(frmFKeys) and 1 module (mdlFkeys).
These items will allow developers to run code by pressing a
function key. The form frmFkeys allows users to customize what
function key will perform what action. The windows registry is
used to keep track of the function key assignments.
For an example, when you download this database, open up the form
frmFkeys, select "ScreenName" in the combo box, and click a
function key command button. (i.e. F-5). "ScreenName" will
appear in the text box under the command button. You must now
exit the database and re-open it. Now you can press function key
F-5 (or the one that you assigned), and if you have a form open, a
message box will appear with the form name. By looking at my
code, you should be able to see that it will now be very simple
for you to create new subroutines and be able to execute them by
pressing a function key.
I have a client that was using a system called TeleMagic, and they
were used to being able to print forms, etc, from the use of
function keys. They asked that I provide the same functionality
with function keys in the new FoxPro database, and this has worked
out very well.
Important Notes:
o You must add subroutine names to the combo box on the form
"frmFkeys." i.e. if you create a sub called "Calculate
Commission", you need to add "Calculate Commission" to the value
list in the combo box.
o I don't have any error code for these examples. You will need
to create your own.
o Additional key assignments could be made. For example if you
wanted to assign a subroutine to "Ctl-F5", you would need to add
^{F5} in the macro AutoKeys, and provide the necessary controls
and code on the form and module.
o The function key values are stored in the windows registry as
"txtF#". My code requires that a field prefixed with "txtF" be
present in order to write the registry information. Feel free
to change this.
o This code if free and available to all who dare to make their
development just a tad bit more user friendly, and cool.
21. Comment Block and UnComment Block Builders for FoxPro 97
-------------------------------------------------------------------
ReplyTo: Mehmet Acikgoz
URL: http://www.wji.com/FoxPro/w9154.html
ftp://ftp.wji.com/FoxPro/pdncbuil.zip //-PDNCBuil.ZIP (23,609)
Keywords: COMMENT BLOCK UNCOMMENT BLOCK BUILDERS FoxPro 97
FoxPro Versions: 8.0
In developing an FoxPro application, the developer might
frequently need commenting out or un-commenting block of codes in
module windows. These are quite tedious processes if the block has
several tens of code lines.
In complex modules, it might be useful to put some sort of signs
to mark commented blocks as showing "why they have been commented"
or in team work situations; "who commented out the block of code
lines and why". These marks can play some sort of vital role in
programming techniques.
To solve these issues, I present two separate builders in the same
file.
22. Passing SQL Strings to FoxPro
-------------------------------------------------------------------
ReplyTo: <>
URL: http://www.wji.com/FoxPro/w9223.html
FoxPro Versions: All.