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"

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.

Journal Entries in FoxPro

The Journal Entry window inputs transactions and posts these transactions to the General Ledger. Although multiple users are allowed to input transactions, only the GL Bookkeeper can post transactions.