To John
At first I have chart of accounts table called assistance_chart showing
_______________________________________________________
Code Description Dr/Cr BAS
=================================================
1085 Bank Dr -
5012 Income Cr GST
Now the Frame8 is a option frame with a group of toggle buttons used as a
selection of choices based upon a certain code selections based upon the
criteria set.
Then this is displayed in the list box. As shown below
cid:image005.jpg@01CE1BE1.78517940
The idea is if I select the current assets then the code sown should be 2001
to 2999 as if I select livestock the using the case select the SQL code
should be
Select Code, Description, Dr/Cr, BAS;
From assistance_Code table;
Between 1010 and 1020 and Between 5020 and 5030 and Between 6080.01 and
6080.09
and yes the code is from another table that I have uses text not values
except the following as I getting disparate for a solution
' Modify this line to include the path to Northwind
' on your computer.
' Set dbs = OpenDatabase("NewAdministration.mdb")
' List the name and contact of every customer
' who placed an order in the second quarter of
' 1995.
Set rst = dbs.OpenRecordset("SELECT Assistance_chart.Code," _
& " Assistance_chart.Description" _
& " FROM Assistance_chart" _
& " WHERE Code" _
& " IN (SELECT Code FROM code" _
& " WHERE code Between #1001#" _
& " And #1010#);")
' Populate the Recordset.
rst.MoveLast
' Call EnumFields to print the contents of the
' Recordset. Pass the Recordset object and desired
' field width.
' EnumFields rst, 25
'dbs.Close
Regards
Mr Adrian Connolly
B.Bus(accounting), FIPA,ASA, NTAA
Logo_Bar
Web Site: www.connollysaccounting.com.au
<http://www.connollysaccounting.com.au/>
E-Mail: cas@connollysaccounting.com.au
Phone: [03] 58 621 224
Fax: [03] 58 621 247
DISCLAIMER:
The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited.
If you received this in error, please contact the sender and delete the
material from any computer.
Regards
Mr Adrian Connolly
B.Bus(accounting), FIPA,ASA, NTAA
Logo_Bar
Web Site: <http://www.connollysaccounting.com.au/>
www.connollysaccounting.com.au
E-Mail: <mailto:cas@connollysaccounting.com.au>
cas@connollysaccounting.com.au
Phone: [03] 58 621 224
Fax: [03] 58 621 247
DISCLAIMER:
The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited.
If you received this in error, please contact the sender and delete the
material from any computer.
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Thursday, 7 March 2013 7:44 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] VBA incorp SQL with number values
Adrian-
Your code has several examples clearly copied from elsewhere, but they don't
give a clue about what it is you actually want to happen. What is in Frame8
that triggers this change? Is there some sort of category the user selects
in that control? And do you want that selection to ultimately filter what
appears in ListBox1?
Please describe your business problem in terms like: "When the user selects
value X in Frame8, the listbox should show accounts in the range D to E."
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of
connollyadrian
Sent: Thursday, March 07, 2013 4:37 AM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: [MS_AccessPros] VBA incorp SQL with number values
i have a table with accounting chart of accounts which is the tables key id
what i need is the list to change when i press current assets or what ever
and the codes with the discriptions are displayed in the list box
Private Sub Frame8_AfterUpdate()
Dim SQLText, textbox
'Dim dbs As Database
Dim rst As Recordset
Select Case Frame8
Case 1
SQLText = "SELECT Assistance_chart.Code, Assistance_chart.Description " _
& "FROM Assistance_chart ORDER BY Assistance_chart.Code; "
Case 2
' Modify this line to include the path to Northwind
' on your computer.
' Set dbs = OpenDatabase("NewAdministration.mdb")
' List the name and contact of every catergory
' who placed in order by value code
Set rst = dbs.OpenRecordset("SELECT Assistance_chart.Code," _
& " Assistance_chart.Description" _
& " FROM Assistance_chart" _
& " WHERE Code" _
& " IN (SELECT Code FROM code" _
& " WHERE code Between #1001#" _
& " And #1010#);")
' Populate the Recordset.
rst.MoveLast
' Call EnumFields to print the contents of the
' Recordset. Pass the Recordset object and desired
' field width.
' EnumFields rst, 25
'dbs.Close
' SQLText = "SELECT Assistance_chart.Code, Assistance_chart.Elite,
Assistance_chart.Description " _
& "Assistance_chart.AccountType, Assistance_chart.TAG " _
& "FROM Assistance_chart WHERE Assistance_chart.Code Between 1001 And 1998;
"
'SQLText = "SELECT Assistance_chart.[Code], Assistance_chart.[Description]"
_
& "Assistance_chart.[AccountType]FROM Assistance_chart WHERE
(((Assistance_chart.[Code]) Between 1001 And 1010));"
' SQLText = "SELECT sheet2.Code, sheet2.Description, sheet2.Type,
sheet2.GST, sheet2.Group, sheet2.Section " _
& "FROM sheet2 WHERE (((sheet2.Group) like '1. Manufacturing' ));"
' textbox = "Manufacturing 1 - 50"
End Select
Me!ListBox1.RowSource = strSQL 'Text
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (4) |
Tidak ada komentar:
Posting Komentar