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"

THISFORM.Text15.Value = THISFORM.Text13.Value + THISFORM.Text10.Value

THISFORM.Text18.Value = THISFORM.Text14.Value + THISFORM.Text11.Value

THISFORM.Text25.Value = THISFORM.Text23.Value + THISFORM.Text10.Value - THISFORM.Text11.Value

THISFORM.Text26.Value = THISFORM.Text24.Value + THISFORM.Text10.Value - THISFORM.Text11.Value

CASE THISFORM.Text33.VALUE = "2"

THISFORM.Text22.Value = "LIABILITY"

THISFORM.Text15.Value = THISFORM.Text13.Value + THISFORM.Text10.Value

THISFORM.Text18.Value = THISFORM.Text14.Value + THISFORM.Text11.Value

THISFORM.Text25.Value = THISFORM.Text23.Value - THISFORM.Text10.Value + THISFORM.Text11.Value

THISFORM.Text26.Value = THISFORM.Text24.Value - THISFORM.Text10.Value + THISFORM.Text11.Value

CASE THISFORM.Text33.VALUE = "3"

THISFORM.Text22.Value = "CAPITAL"

THISFORM.Text15.Value = THISFORM.Text13.Value + THISFORM.Text10.Value

THISFORM.Text18.Value = THISFORM.Text14.Value + THISFORM.Text11.Value

THISFORM.Text25.Value = THISFORM.Text23.Value - THISFORM.Text10.Value + THISFORM.Text11.Value

THISFORM.Text26.Value = THISFORM.Text24.Value - THISFORM.Text10.Value + THISFORM.Text11.Value

CASE THISFORM.Text33.VALUE = "4"

THISFORM.Text22.Value = "OPN"

THISFORM.Text15.Value = THISFORM.Text13.Value + THISFORM.Text10.Value

THISFORM.Text18.Value = THISFORM.Text14.Value + THISFORM.Text11.Value

THISFORM.Text25.Value = THISFORM.Text23.Value + THISFORM.Text10.Value - THISFORM.Text11.Value

THISFORM.Text26.Value = THISFORM.Text24.Value + THISFORM.Text10.Value - THISFORM.Text11.Value

CASE THISFORM.Text33.VALUE = "5"

THISFORM.Text22.Value = "INCOME"

THISFORM.Text15.Value = THISFORM.Text13.Value + THISFORM.Text10.Value

THISFORM.Text18.Value = THISFORM.Text14.Value + THISFORM.Text11.Value

THISFORM.Text25.Value = THISFORM.Text23.Value - THISFORM.Text10.Value + THISFORM.Text11.Value

THISFORM.Text26.Value = THISFORM.Text24.Value - THISFORM.Text10.Value + THISFORM.Text11.Value

CASE THISFORM.Text33.VALUE = "6"

THISFORM.Text22.Value = "EXPENSE"

THISFORM.Text15.Value = THISFORM.Text13.Value + THISFORM.Text10.Value

THISFORM.Text18.Value = THISFORM.Text14.Value + THISFORM.Text11.Value

THISFORM.Text25.Value = THISFORM.Text23.Value + THISFORM.Text10.Value - THISFORM.Text11.Value

THISFORM.Text26.Value = THISFORM.Text24.Value + THISFORM.Text10.Value - THISFORM.Text11.Value

ENDCASE

IF THISFORM.Text59.Value = "FIRST"

THISFORM.Text61.Value = VAL(THISFORM.Text13.Value - THISFORM.Text14.Value)

ELSE

THISFORM.Text61.Value = VAL(THISFORM.Text59.Value + THISFORM.Text13.Value - THISFORM.Text14.Value)

ENDIF

SELECT SviewAccountList

LOCATE FOR (IdNum) = (THISFORM.Text34.Value)

IF FOUND()

REPLACE TotalDebit WITH THISFORM.TEXT15.Value

REPLACE TotalCredit WITH THISFORM.TEXT18.Value

REPLACE EndingBalance WITH THISFORM.TEXT26.Value

REPLACE TotalAmount WITH THISFORM.TEXT61.Value

REPLACE TagSked WITH CMONTH(DATE())

REPLACE LDate WITH DATE()

REPLACE NoOfSl WITH "NEXT"

REPLACE Lines WITH "POST"

=TABLEUPDATE(.T.)

ELSE

=MESSAGEBOX("Error 001: IdNum not found.", "Information")

THISFORM.ErrorAdd()

ENDIF

SELECT SviewAccountRecord

APPEND BLANK

REPLACE RecAccountCode WITH THISFORM.TEXT41.Value

REPLACE RecType WITH THISFORM.TEXT50.Value

REPLACE RecAccountLevel WITH THISFORM.TEXT47.Value

REPLACE RecBeginBsl WITH THISFORM.TEXT23.Value

REPLACE RecTotalDebit WITH THISFORM.TEXT15.Value

REPLACE RecTotalCredit WITH THISFORM.TEXT18.Value

REPLACE RecEndingBal WITH THISFORM.TEXT26.Value

REPLACE RecBalance WITH THISFORM.TEXT26.Value

REPLACE RecDate WITH THISFORM.TEXT51.Value

REPLACE RecBranchName WITH pBranchName

=TABLEUPDATE(.T.)

SELECT SviewJournalEntry

LOCATE FOR IdNum = (THISFORM.Text7.Value)

IF FOUND()

REPLACE PostBy WITH pCurrUser

REPLACE PostDate WITH DATETIME()

REPLACE PostStatus WITH "POST"

REPLACE Balance WITH THISFORM.TEXT26.Value

=TABLEUPDATE(.T.)

ELSE

=MESSAGEBOX("Error 004: Account Code not found.", "Information")

THISFORM.ErrorAdd()

ENDIF

THISFORM.MainProcess()

THISFORM.CodeProcess()

ELSE

=MESSAGEBOX("Error 003: Account Code not found.", "Information")

THISFORM.ErrorAdd()

ENDIF

ENDIF

SELECT SviewJournalEntry

LOCATE FOR IdNum = (THISFORM.Text7.Value)

IF FOUND()

REPLACE PostBy WITH pCurrUser

REPLACE PostDate WITH DATETIME()

REPLACE PostStatus WITH "POST"

*REPLACE Balance WITH THISFORM.TEXT26.Value

*REPLACE Balance WITH THISFORM.Text61.Value

=TABLEUPDATE(.T.)

ELSE

=MESSAGEBOX("Error 004: Account Code not found.", "Information")

THISFORM.ErrorAdd()

ENDIF

ENDSCAN

ELSE

=MESSAGEBOX("Not balance.", "Information")

ENDIF

*=MESSAGEBOX("Journal Entry processed.", "Confirmation")

ELSE

=MESSAGEBOX("Journal Entry already processed.", "Information")

ENDIF

SELECT SviewJvOneHeader

LOCATE FOR IdNum = (THISFORM.Text5.Value)

IF FOUND()

REPLACE PostBy WITH pCurrUser

REPLACE PostDate WITH DATE()

REPLACE PostStatus WITH "POST"

=TABLEUPDATE(.T.)

ELSE

=MESSAGEBOX("Error 002: IdNum not found.", "Information")

THISFORM.ErrorAdd()

ENDIF

ENDSCAN

=MESSAGEBOX("Journal Entry processed.", "Information")

THISFORM.Grid2.REFRESH()

THISFORM.ActivityPost()

THISFORM.Text5.Value = ""

THISFORM.cmdPost.Enabled = .F.

Change Password Code

Login requirement needs changing password for security reason. Here is a sample change of password for FoxPro.

IF ALLTRIM(THISFORM.Text4.Value) == ALLTRIM(THISFORM.Text4.Value)

REPLACE SviewAccess.Password WITH ALLTRIM(THISFORM.Text4.Value)
*WAIT WINDOW "Password has been changed." NOWAIT
=MESSAGEBOX("Password has been changed. This window will exit.", "Information")
THISFORM.Release()
ELSE
WAIT WINDOW "Confirm password does not match." NOWAIT
ENDIF

Branch Setting

PUBLIC pBranchName

LOCATE FOR ALLTRIM(BranchName) = ALLTRIM(THISFORM.Combo1.Value)

pBranchName = ALLTRIM(BranchName)

= MESSAGEBOX("You select "+ALLTRIM(pBranchName)+" Office?", "Confirmation")

THISFORM.Release()

Release Block User Code

In any login code, aside from status login, logout, I also made use of block. A user has a status of block if his username and password dis not match thrice in a row. This will help in the security of accessing the program.

PUBLIC pBlockUser

IF !EMPTY(THISFORM.Text2.Value)

IF THISFORM.Text4.Value = "BLOCK"

pBlockUser = THISFORM.Text3.Value

lAnswer = MESSAGEBOX("Do you want to release "+ALLTRIM(pBlockUser)+"?", 4, "Confirmation")

IF lAnswer = 6

SELECT SviewAccess

LOCATE FOR IdNum = THISFORM.Text2.Value

IF FOUND()

REPLACE AccessStatus WITH "LOGOUT"

=MESSAGEBOX("Successfully release "+ALLTRIM(pBlockUser)+" .", "Confirmation")

THISFORM.Grid1.REFRESH

ENDIF

ENDIF

ELSE

=MESSAGEBOX("Select a Block User.", "Confirmation")

ENDIF

ELSE

=MESSAGEBOX("Select User.", "Confirmation")

ENDIF

Balance Code

This code will check if textbox 1 is equal to textbox 2. If the two quantities is not equal a messagebox 'Not balance.' will appear but continue printing if balance at all.

IF !EMPTY(THISFORM.Text3.Value)

IF (THISFORM.Text1.Value) = (THISFORM.Text2.Value)

REPORT FORM "C:\GenLed\Reports\ReportJournalEntry" TO PRINTER PROMPT NODIALOG PREVIEW

THISFORM.Refresh()

ELSE

=MESSAGEBOX("Not balance.", "Confirmation")

ENDIF

ELSE

=MESSAGEBOX("Choose Journal Entry.", "Confirmation")

ENDIF

Counter Code

PUBLIC pEndBegin, pidnum
SELECT SviewAccountList
lBranches = pBranchName
=REQUERY("SviewAccountList")
GO TOP
lCounter = RECCOUNT()
SCAN
pEndBegin = SviewAccountList.EndingBalance
pIdNum = SviewAccountList.IdNum
SELECT SviewAccountList
LOCATE FOR IdNum = pIdNum
IF FOUND()
REPLACE BeginBal WITH pEndBegin
REPLACE tagsked WITH CMONTH(DATE())
ENDIF
ENDSCAN

Changing Image or Picture of a Button

You can definitely change the picture of your bitton upon clicking, try these codes and play with the images. But first you have to prepare the image in JPEG.

IF THIS.Picture = "c:\images\edit.jpg"
THIS.Picture = "c:\images\save.jpg"
THISFORM.cmdClose.Picture = "c:\images\cancel.jpg"
ELSE
IF THISFORM.Combo1.Value = 'PESO'
lAnswer = MESSAGEBOX("Save changes?", 4, "Confirmation")
IF lAnswer = 6
THISFORM.cmdClose.Picture = "c:\images\close.jpg"
THISFORM.cmdEdit.Picture = "c:\images\edit.jpg"
ENDIF
ELSE THISFORM.COMBO1.Value = 'DOLLAR'
lAnswer = MESSAGEBOX("Save changes?", 4, "Confirmation")
IF lAnswer = 6
THISFORM.cmdClose.Picture = "c:\images\close.jpg"
THISFORM.cmdEdit.Picture = "c:\images\edit.jpg"
ENDIF
ENDIF
ENDIF

Delete Data in a Table Code

Deleting data from the local table.

SET EXCLUSIVE ON
USE "C:\EMPLOYEE\DATA\EmployeeList"
DELETE ALL
PACK

Journal Entries on Journal Form

Upon clicking the grid a new form will appear where you can add information. It will also retain information from previous form such as Journal Number, Reference Documents and Particular.

PUBLIC pJournalNo, pJeNum, pRefDocu, pPart
IF !EMPTY(THISFORM.Text5.VALUE)
IF !EMPTY(THISFORM.Text7.VALUE)
THISFORM.Cs_Branches()
pJournalNo = THISFORM.Text5.VALUE
pJeNum = INT(THISFORM.Text7.VALUE)
pRefDocu = THISFORM.Text8.VALUE
pPart = THISFORM.Text1.VALUE
DO FORM "C:\GenLed\Forms\JournalForm"
ELSE
pJournalNo = THISFORM.Text5.VALUE
pRefDocu = THISFORM.Text8.VALUE
pPart = THISFORM.Text1.VALUE
pJeNum = ""
DO FORM "C:\GenLed\Forms\JournalForm"
ENDIF
ELSE
=MESSAGEBOX("Select a Journal Number", "Confirmation")
ENDIF
THISFORM.Grid1.Refresh

Activity Log Code

This is the first time I will be using Activity Log on my program. Where the User or Admin can view the activity of a certain user such as adding or editing. You can also add certain specific information such as amount and account chart of journal entries.

SELECT SviewActivityLog
APPEND BLANK
REPLACE UserName WITH pCurrUser
REPLACE ActivityDate WITH DATE()
REPLACE ActivityLog WITH "Add Journal Entries in "+ALLTRIM(pBranchName)+".."

SELECT SviewActivityLog
APPEND BLANK
REPLACE UserName WITH pCurrUser
REPLACE ActivityDate WITH DATE()
REPLACE ActivityLog WITH "Edit Journal Entries in "+ALLTRIM(pBranchName)+".."

Public

Recently I learned how to use PUBLIC in getting info or data from another form to another form. And it was very usable for me since my program has a login and branch name requirements.

On Login Form
PUBLIC pBranchName
pBranchName = ALLTRIM(THISFORM.Combo1.Value)

On Branch Setting Form
PUBLIC pBranchName
THISFORM.Label18.Caption = pBranchName

On Journal Entry Form
PUBLIC pBranchName
LOCATE FOR ALLTRIM(BranchName) = ALLTRIM(THISFORM.Combo1.Value)
pBranchName = ALLTRIM(BranchName)
= MESSAGEBOX("You select "+ALLTRIM(pBranchName)+" Office?", "Confirmation")
THISFORM.Release()

Cancel Close Code

If you want your button's picture to change when your Close/Cancel button also changed.

IF THIS.Picture = "c:\SalesMarket\Images\close.jpg"

THISFORM.Release()

ELSE

THIS.Picture = "c:\SalesMarket\Images\close.jpg"

THISFORM.ReadOnlyTrue()

THISFORM.Combo1.Enabled = .F.

THISFORM.cmdPrint.Enabled = .T.

THISFORM.cmdEdit.Enabled = .T.

THISFORM.cmdEdit.Picture = "c:\SalesMarket\Images\edit.jpg"

ENDIF

Opening a PDF File

You can open a PDF file in VFP b using below codes. Just specify the location of your PDF file on cFileName

DECLARE INTEGER ShellExecute IN shell32.dll ;
INTEGER hndWin, ;
STRING cAction, ;
STRING cFileName, ;
STRING cParams, ;
STRING cDir, ;
INTEGER nShowWin
cFileName = "c:\GenLed\Images\CC.pdf"
cAction = "open"
ShellExecute(0,cAction,cFileName,"","",1)

Disable Code

How to disable command button, text box, combo box, grid and pageframe:

Thisform.Command1.Enabled = .F.

Thisform.Text1.Enabled = .F.

Thisform.Combo1.Enabled = .F.

Thisform.Grid1Enabled = .F.

Thisform.Pageframe1.Enabled = .F.

FoxPro Graph

To those who are neophyte in VFP Language might have a hard time researching and building a graph in a form and report. I also had a hard time making a graph when I first encountered a VFP Project with a graph. But once you learned, it is easy for the next project to bulid a graph in form and report.

You need the following requirements before continuing to the graph:

Table

OleGraph

Grid

SELECT * FROM crewquestion INTO CURSOR MYCURSOR

TAB = CHR(09)

CRLF = CHR(13) + CHR(10)

SELECT MYCURSOR

NEWDATA = TAB + "Recruitment & Selection" + TAB + "Documentation" + TAB ;

+ "In House Training" + TAB + "Accounting Activity" + TAB + "Working Gears" + ;

CRLF

SCAN

NEWDATA = NEWDATA + TAB + STR(question1) + TAB + STR(question2) + TAB ;

+ STR(question3) + TAB + STR(question4) + TAB + STR(question5) + ;

CRLF

ENDSCAN

SELECT crewquestion

APPEND GENERAL crewques1.Olegraph DATA NEWDATA

SELECT crewquestion

THISFORM.REFRESH

SELECT * FROM crewquestion INTO CURSOR MYCURSOR

TAB = CHR(09)

CRLF = CHR(13) + CHR(10)

SELECT MYCURSOR

NEWDATA = TAB + "Padala Sytem" + TAB + "Company Facilities" + TAB + "Attitude of Staff" + TAB ;

+ "P&I Medical Assistance" + CRLF

SCAN

NEWDATA = NEWDATA + TAB + STR(question6a) + TAB + STR(question6b) + TAB + STR(question6c) + TAB ;

+ STR(question6d) + CRLF

ENDSCAN

SELECT crewquestion

APPEND GENERAL crewques2.Olegraph DATA NEWDATA

SELECT crewquestion

THISFORM.REFRESH

Back Color

Changing the back color of an Edit Box. Same applies to Text Box, Combo Box or Grid.

THISFORM.TEXT1.BackColor = RGB(255,255,255)
THISFORM.EDIT1.BackColor = RGB(255,255,255)
THISFORM.EDIT2.BackColor = RGB(255,255,255)
THISFORM.EDIT3.BackColor = RGB(255,255,255)
THISFORM.EDIT4.BackColor = RGB(255,255,255)
THISFORM.EDIT5.BackColor = RGB(255,255,255)
THISFORM.EDIT6.BackColor = RGB(255,255,255)
THISFORM.EDIT7.BackColor = RGB(255,255,255)
THISFORM.EDIT8.BackColor = RGB(255,255,255)

Clearpage Code

THISFORM.Text1.VALUE = ""

THISFORM.Command1.VALUE = ""

THISFORM.Pageframe1.VALUE = ""

THISFORM.Grid1.VALUE = ""

THISFORM.Combo1.VALUE = ""

Read Only Code

Thisform.Text1.ReadOnly = .T.

Thisform.Command1.ReadOnly = .T.

Thisform.Grid1.ReadOnly = .T.

Thisform.Pageframe1.ReadOnly = .T.

Thisform.Combo1.ReadOnly = .T.

Set Focus Code

You can use this code if you want to focus on a certain text. I usually use this after enabling the textboxes.

Thisform.Text1.Setfocus()

If Else EndIf Code

You can utilize this code if you want your Command box caption to be change.

IF This.Caption = "Edit"

This.Caption = "Save"

Thisform.cmdClose.Caption= "Cancel"

ELSE

This.Caption = "Edit"

Thisform.cmdClose.Caption = "Close"

ENDIF