ACCESS FEED: RE: [MS_AccessPros] VBA incorp SQL with number values

Kamis, 07 Maret 2013

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@yahoogroups.com] On Behalf Of connollyadrian
Sent: Thursday, March 07, 2013 4:37 AM
To: MS_Access_Professionals@yahoogroups.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]