After Check event for list box

[mba_110]

Hi everyone,

I am facing some problem with delimiters in my below sql

Private Sub chkBank_Cash_Click()
If Me.chkBank_Cash = True Then
Me.EntryList.RowSource = "SELECT tblEntries.EntryNo, tblEntryTypes.EntryType, tblEntryTypes.Bank, tblEntryTypes.Cash, tblEntryTypes.Customer, tblEntryTypes.Supplier, tblEntryTypes.Employee, tblEntryTypes.Owners"
FROM tblEntryTypes INNER JOIN tblEntries ON tblEntryTypes.EntryType = tblEntries.EntryType
WHERE (((tblEntryTypes.Bank) Like "*") And ((tblEntryTypes.Cash) Like "*") And ((tblEntryTypes.Customer) = False) And ((tblEntryTypes.Supplier) = False) And ((tblEntryTypes.Employee) = False) And ((tblEntryTypes.Owners) = False)) ";"
End If

This will filter the Entrylist to be appear with Bank&Cash transactions only.

 

[JHB]

Try the below:

  Me.EntryList.RowSource = "SELECT tblEntries.EntryNo, tblEntryTypes.EntryType, tblEntryTypes.Bank, tblEntryTypes.Cash, tblEntryTypes.Customer, tblEntryTypes.Supplier, tblEntryTypes.Employee, tblEntryTypes.Owners " _
  & "FROM tblEntryTypes INNER JOIN tblEntries ON tblEntryTypes.EntryType = tblEntries.EntryType " _
  & "WHERE (((tblEntryTypes.Bank) Like '*') And ((tblEntryTypes.Cash) Like '*') And ((tblEntryTypes.Customer) = False) And ((tblEntryTypes.Supplier) = False) And ((tblEntryTypes.Employee) = False) And ((tblEntryTypes.Owners) = False));"

 

[mba_110]

Thanks for your help

However i want list box to appear only three columns EntryNo,EntryDate,EntryType nothing else.

Me.EntryList.RowSource = "SELECT tblEntries.EntryNo, tblEntries.EntryDate, tblEntryTypes.EntryType, tblEntryTypes.Bank, tblEntryTypes.Cash, tblEntryTypes.Customer, tblEntryTypes.Supplier, tblEntryTypes.Employee, tblEntryTypes.Owners" _
& "FROM tblEntryTypes INNER JOIN tblEntries ON tblEntryTypes.EntryType = tblEntries.EntryType" _
& "WHERE (((tblEntryTypes.Bank) Like " * ") AND ((tblEntryTypes.Cash) Like " * ") AND ((tblEntryTypes.Customer)=False) AND ((tblEntryTypes.Supplier)=False) AND ((tblEntryTypes.Employee)=False) AND ((tblEntryTypes.Owners)=False));"

 

[June7]

Use ColumnWidths property to hide columns. Set the columns you don't want to see to 0".

Or don't include other fields in the SELECT clause. They can still be in the WHERE.

Why include field in WHERE if you are just using wildcard as parameter?

 

[mba_110]

When i am entering it manually in list box row source its working fine, what is the issue with after check event.


If Me.chkBank_Cash = True Then
Me.EntryList.RowSource = "SELECT tblEntries.EntryNo,tblEntries.EntryDate, tblEntryTypes.EntryType, tblEntryTypes.Cash, tblEntryTypes.Bank" _
& "FROM tblEntryTypes INNER JOIN tblEntries ON tblEntryTypes.EntryType = tblEntries.EntryType" _
& "WHERE (((tblEntryTypes.Cash)=True)) OR (((tblEntryTypes.Bank)=True));"
End If

First it shows type mismatch, and after that its not showing any error nothing list box is blank, even after i check.

 

[Gasman]

You do not have any spaces between each of your statements to start with

 

[mba_110]

Thank you gasman (plain English and programmatically) both

 

[mba_110]

A Similar query with sql on load event with calculated fields.

Private Sub Form_Load()
me.ListBox.RowSource = " SELECT tblTransactions.AccountNo, tblAccounts.AccountName, tblAccountsHeader.HeadingName, tblGroups.GroupName, tblAccounts.Currency, Sum(tblTransactions.CurDebit) AS SumOfCurDebit, Sum(tblTransactions.CurCredit) AS SumOfCurCredit, Sum(IIf([GroupName]="Asset",[CurDebit]-[CurCredit],IIf([GroupName]="Liability",[CurCredit]-[CurDebit],IIf([GroupName]= "Equity",[CurCredit]-[CurDebit],IIf([GroupName]="Income",[CurCredit]-[CurDebit],IIf([GroupName]="Expense",[CurDebit]-[CurCredit],0)))))) AS CurBalance" _
& " FROM tblGroups INNER JOIN ((tblAccountsHeader INNER JOIN tblAccounts ON tblAccountsHeader.AccHeaderID = tblAccounts.AccHeaderID) INNER JOIN tblTransactions ON tblAccounts.AccountNo = tblTransactions.AccountNo) ON tblGroups.GroupID = tblAccountsHeader.GroupID" _
& " GROUP BY tblTransactions.AccountNo, tblAccounts.AccountName, tblAccountsHeader.HeadingName, tblGroups.GroupName, tblAccounts.Currency;"
End Sub

it is giving syntax error, is it due to sum with aggregation function?

 

[Gasman]

Paste the code into the QBE SQL windows and see what it flags up.?

Personally I create anything like that in the SQL window first (to make sure it is correct), THEN amend for code.

In fact if you search this site there is a SQL2VBA DB that will do all the hard work for you.

 

[mba_110]

I big thanks to the developer and a great circulation by my friend isladogs and your guidance for search SQL2VBA.

i have got the very handy tool which i attached here that works with 2007 and above versions also.

it was originally posted by isladogs https://access-programmers.co.uk/forums/showthread.php?t=293372 on this forum.

Really its save lot of my time and queries modifications.

 

[isladogs]

Thanks

As it says in that link, it is an extended version of an application originally created by the great Allen Browne many years ago.

You can also find it on my website: http://www.mendipdatasystems.co.uk/sql-to-vba-back-again/4594398120

You'll gradually find yourself needing it less & less and then not at all as you become more experienced.
In a way it proves its worth when it becomes redundant