Simple Stuff

[EscapeUK]

I want to stream line my Access DB app. How do I replace my quieries with code. I know simple SQL but not how to stick it in VBA.

Also when I have obtained result from the SQL how do i place the records found in my reports.

A simple example would be Getting a set of results from two tables.

Any help would be great [sig][/sig]

 

[Robertd]

Hi ExcapeUK,

well to bring an SQL statement into code the general scheme is to use a string type variable

to do this the best method is to define a var to accept the SQL statement

dim mySQL as string
an example an SQL statement to update a cheque account

' add cheque details
mySQL = "INSERT INTO BankAcctCheques"
mySQL = mySQL & " ( AcctID, ChqNr, Amount )"
mySQL = mySQL & " SELECT "
mySQL = mySQL & Me.SelBankAcctID
mySQL = mySQL & " AS AcctID,"
mySQL = mySQL & BankRef
mySQL = mySQL & " AS ChqNr,"
mySQL = mySQL & Me.TransAmnt + Me.TransGST
mySQL = mySQL & " AS Amount;"

here the SQL statement uses some control values (which are checked before the query is run!) this way the user can edit the ontrol values as required then apply the transaction (in reality this is only one of a few queries used for the transaction)

to actually run the query use the docmd method
' hides run sql prompt and others
DoCmd.SetWarnings (False)
DoCmd.RunSQL mySQL 'update BankAccountCheques table
' turn back on or check them
DoCmd.SetWarnings (True)

you can call "real" queries using the same command as long as the target refers to an existing query
like if i had a query called "dosomething" then

dim stDocName as string
stDocName = "dosomething"
DoCmd.OpenQuery stDocName

would call the existing query, you can base recordsets etc using this ;-)

HTH
Robert
[sig][/sig]