Microsoft Visual Foxpro: 2011

Delete Code

This is delete code where you can delete a line or specific data from the table one at a time.

SELECT TableName

LOCATE FOR ALLTRIM(TableName.Allottee) = ALLTRIM(THISFORM.Text1.Value)

IF FOUND()

IF ALLTRIM(TableName.Allottee) = ALLTRIM(THISFORM.Text1.Value)

SELECT TableName

LOCATE FOR Allottee = ALLTRIM(THISFORM.Text1.Value)

SELECT TableName

USE

USE Allotments EXCLU

DELETE

PACK

=TABLEUPDATE(.T.)

ENDIF

Here is a sample of my tag code or tagging an amount .

lAnswer = MESSAGEBOX("Do you want to tag Journal Entry?", 4, "Confirmation")
IF lAnswer = 6
SELECT SviewJournalUpdate
LOCATE FOR IdNum = THISFORM.Text2.Value
IF FOUND()
REPLACE lDate WITH pJeDate
REPLACE RefDocu WITH pRefDocu
REPLACE JvNumber WITH THISFORM.TEXT10.Value
REPLACE LibUpdate WITH THISFORM.TEXT15.Value
THISFORM.Grid1.REFRESH
ENDIF
THISFORM.ActivityTag()
THISFORM.Release()
ENDIF

General Ledger FoxPro Folder

Whenever I make a FoxPro project folder I always make a folder for the Attach, Classlib, Data, Forms, Images, Menus, Progs and Reports. This folder making will help me later on to separate the files that will be created.

Form Init Code

Here is a sample of my code in Init of a Form.

SET DELETED ON
SET TALK OFF
PUBLIC pCurrUser
THISFORM.Text9.VALUE = DATE()
THISFORM.BeginBalance()
THISFORM.PettyDisable()
THISFORM.Grid1.Enabled = .F.

DO CASE
CASE ALLTRIM(pCurrUser) = "Admin"
THISFORM.Grid1.Enabled = .T.
THISFORM.cmdApprove.Enabled = .T.
CASE ALLTRIM(pCurrUser) = "User"
THISFORM.Grid1.Enabled = .T.
THISFORM.cmdApprove.Enabled = .T.
ENDCASE

Trial Balance Report

Trial Balance Report of Microsoft Visual FoxPro

Income Statement Report

Income Statement Report of Microsoft Visual FoxPro

Check Voucher Report

Check Voucher Report of Microsoft Visual FoxPro

Number to Words FoxPro Code

Here is a sample of a FoxPro code on how to change the numbers you typed into words or number to words functions.

PARAMETER numAmt

PRIVATE numAmt, chrAmt, cDNums, wordAmt, cDvar

*Covert amount to string, add leading zeros
numAmt = VAL(THISFORM.Text1.Value)
chrAmt=RIGHT('000000000'+LTRIM(STR(numAmt,12,2)),12)

*Initialize literal string
Dol1 = 'ONE'
Dol2 = 'TWO'
Dol3 = 'THREE'
Dol4 = 'FOUR'
Dol5 = 'FIVE'
Dol6 = 'SIX'
Dol7 = 'SEVEN'
Dol8 = 'EIGHT'
Dol9 = 'NINE'
Dol10 = 'TEN'
Dol11 = 'ELEVEN'
Dol12 = 'TWELVE'
Dol13 = 'THIRTEEN'
Dol14 = 'FOURTEEN'
Dol15 = 'FIFTEEN'
Dol16 = 'SIXTEEN'
Dol17 = 'SEVENTEEN'
Dol18 = 'EIGHTEEN'
Dol19 = 'NINETEEN'
Dol20 = 'TWENTY'
Dol30 = 'THIRTY'
Dol40 = 'FORTY'
Dol50 = 'FIFTY'
Dol60 = 'SIXTY'
Dol70 = 'SEVENTY'
Dol80 = 'EIGHTY'
Dol90 = 'NINETY'
wordAmt=''
IsHundred = .F.
checkMillion =.T.
FOR Counter = 1 TO 3

* First time through the For loop to check for millions
* Second time through the FOR loop to check for thousands
* Third time through the FOR loop to check for hundreds, tens and ones
DO CASE
CASE Counter = 1
cDNums = SUBSTR(chrAmt,1,3)
CASE Counter = 2
cDNums = SUBSTR(chrAmt,4,3)
CASE Counter = 3
cDnums = SUBSTR(chrAmt,7,3)
ENDCASE

* Check hundreds
IF LEFT(cDNums, 1) > '0'
cDvar = 'Dol'+LEFT(cDNums,1)
wordAmt = wordAmt + EVAL(cDvar)+SPACE(1)+'HUNDRED'+SPACE(1)
IsHundred = .T.
IF Counter = 2
CheckMillion = .T.
ENDIF
ENDIF

* Check tens and ones
Dtens = VAL(SUBSTR(cDNums,2,2))
IF Dtens > 0
IF Dtens > 20
cDvar = 'Dol'+SUBSTR(cDNums,2,1)+'0'
wordAmt = wordAmt + EVAL(cDvar)
IF SUBSTR(cDNums,3,1) > '0'
cDvar = 'Dol'+SUBSTR(cDNums,3,1)
wordAmt = wordAmt + '-'+ EVAL(cDvar) + SPACE(1)
ELSE
wordAmt = wordAmt + SPACE(1)
ENDIF
ELSE
cvar = 'Dol'+LTRIM(STR(Dtens))
wordAmt = wordAmt + EVAL(cDvar) + SPACE(1)
ENDIF
IsHundred = .F.
IF Counter = 2
CheckMillion = .T.
ENDIF
ENDIF

* Add in Million, if needed
IF numAmt > 999999.99 .AND. Counter = 1
wordAmt = wordAmt + SPACE(1)+'MILLION'+SPACE(1)
CheckMillion = .F.
ENDIF

* Add in Thousand, if needed
IF CheckMillion
IF numAmt > 999.99 .AND. Counter = 2
IF Dtens > 0
wordAmt = wordAmt + SPACE(1)+'THOUSAND'+SPACE(1)
ENDIF
IF IsHundred
wordAmt = wordAmt + SPACE(1)+'THOUSAND'+SPACE(1)
ENDIF
ENDIF
ENDIF
ENDFOR

* Construct the complete dollar amount in words
wordAmt = IIF(numAmt<1, 'ONLY'+SPACE(1), '** ' + wordAmt + ' **') &&+SPACE(1)) + RIGHT(chrAmt,2)+'/100 ' + IIF(numAmt>1,'S','')
RETURN wordAmt

Menu Designer in FoxPro

Menu Designer on Other of Project Manager allows you you build and edit menu or menus for your FoxPro System. On Prompt textbox, assign a name designated then on Result will appear Command, Pad Name, Submenu or Procedure. You can make as many list as you want. Then a textbox will appear on Options depends on what you had chosen on Result.

Subsidiary Ledger

Subsidiary Ledger account in the Chart of Accounts from the Account field and scroll and click the desired branch on the Branch field. Indicate in the Start Date and End Date the desired transaction period to be displayed for the chosen account. Ok button allows the user to refresh screen and current entries for a particular period, account and Branch will be displayed in the transaction field.

Remote View Designer in FoxPro

The Remote View Designer allows you to edit the Fields, Join, Filter, Order By, Group By, Update Criteria and Miscellaneous in your view. As a sample I have the AccountChange table from SQL Database to be access.

SQL Connections in FoxPro

SQL Database in FoxPro is possible using the Connections feature of this application. As you can see I have three different database from SQL Server. The InfoConnString, MoneyConnString and SQLConnString. Now I am able to access the tables from those database. But of course, I need to provide a connection string to access.

Grid Back Color Code

This FoxPro code will help you identify which list on the grid has different data. My sample is that all Pending Journal Entry will be in orange backgound otherwise will be in blue background. Now I will know which entries to be posted!

THISFORM.Grid2.SetAll("DynamicBackColor", "(IIF(PostStatus = 'PENDING', RGB(244,185,96), RGB(157,206,255)))")

Journal Entries Posting FoxPro Code

In line with the General Ledger window, here is the post or posting code after transaction entries. This will transfer the account entries to be posted and be viewed on Trial Balance. Also the FoxPro code is complicated due to locating the desired account name then add or subtract depends on the debit or credit on different account type.

PUBLIC tDebitTotal, tCreditTotal

THISFORM.MonthProcess()

SELECT SviewJvOneHeader

StartDate = THISFORM.Text51.Value

EndDate = THISFORM.Text51.Value

lNameBranch = pBranchName

=REQUERY("SviewJvOneHeader")

GO TOP

SCAN

THISFORM.Text5.Value = SviewJvOneHeader.IdNum

IF SviewJvHeader.PostStatus = "PENDING"

IF (THISFORM.Text16.Value) = (THISFORM.Text17.Value)

SELECT SviewJournalEntry

lJvNumber = THISFORM.Text5.Value

=REQUERY("SviewJournalEntry")

GO TOP

lCounter = RECCOUNT()

SCAN

THISFORM.Text7.Value = SviewJournalEntry.IdNum

THISFORM.Text9.Value = SviewJournalEntry.AccountCode

THISFORM.Text10.Value = SviewJournalEntry.Debit

THISFORM.Text11.Value = SviewJournalEntry.Credit

THISFORM.Text12.Value = SviewJournalEntry.BranchName

SELECT SviewAccountPosting

lBranchName = pBranchName+"%"

lAccountCode = THISFORM.Text9.VALUE

=REQUERY("SviewAccountPosting")

LOCATE FOR ALLTRIM(AccountCode) = ALLTRIM(THISFORM.Text9.Value)

IF FOUND()

THISFORM.Text41.VALUE = Code1

THISFORM.Text48.VALUE = Code2

THISFORM.Text55.VALUE = Code3

SELECT SviewAccountPosting

lBranchName = pBranchName+"%"

lAccountCode = THISFORM.Text41.VALUE

=REQUERY("SviewAccountPosting")

LOCATE FOR ALLTRIM(AccountCode) = ALLTRIM(THISFORM.Text41.Value)

IF FOUND()

THISFORM.Text13.Value = TotalDebit

THISFORM.Text14.Value = TotalCredit

THISFORM.Text23.VALUE = BeginBal

THISFORM.Text24.VALUE = EndingBalance

THISFORM.Text34.VALUE = IdNum

THISFORM.Text47.VALUE = AccountLevel

THISFORM.Text49.VALUE = TagSked

THISFORM.Text50.VALUE = Type

THISFORM.Text59.VALUE = NoOfSl

THISFORM.Text59.VALUE = TotalAmount

THISFORM.MonthlyProcess()

THISFORM.Text33.VALUE = LEFT(TRANS(THISFORM.Text41.Value),1)

DO CASE

CASE THISFORM.Text33.VALUE = "1"

THISFORM.Text22.Value = "ASSET"