Different ways of doing things in VBA - DMU



There is often a choice of ways to do what you want in Access and VBA.

This table aims to bring together some of the different ways that you can use to achieve your aim, so that you can choose what to use.

| |Embedded SQL (action queries) |Recordsets |Built-in Functions |Other |

| |Use DoCmd.RunSQL |dbsDBName = CurrentDB | |For queries, create via Query Design Window or |

| |Or |Set rstData = dbsMyDB.OpenRecordset(…) | |SQL Window then use Wizard Run Query button |

| |dbsMyDB.Execute. |….process the data… | |code for DoCmd.RunQuery |

| | |rstData.Close | | |

| | | | | |

|Create a table |To create a new, empty, table use SQL: | | |To create a table with data from an existing |

| |“CREATE TABLE tblName (col1 type, col2 | | |table, use a make-table query. |

| |type…);” | | | |

| | | | |Or… |

| | | | |To create an exact copy of a table, can also |

| | | | |use DoCmd.CopyObject. |

| | | | | |

|Insert rows in a table |“INSERT INTO tblName |rstData.AddNew | |Append query. |

| |VALUES (data1, data2…);” |rstData!Coln = | | |

| | |rstData.Update | | |

| | | | | |

| | | | | |

| | | | | |

|Update rows in a table |UPDATE tblName |rstData.Edit | |Update query. |

| |SET coln = |rstData!Coln = | | |

| |{WHERE …};” |rstData.Update | | |

| | | | | |

| | | | | |

|Delete rows from a |DELETE * FROM tblName |rstData.Delete | |Delete query. |

|table |{WHERE …};” |rstData!Coln = | | |

| | |rstData.Update | | |

| | | | | |

| | | | | |

|Delete a table |“DROP TABLE tblName;” | | |doCmd.DeleteObject actable “tblName” |

| |For list box based on a table: | | |For list box based on a value list: |

|Add rows to a list box |Row Source Type property = Table/Query, | | |Row Source Type property = Value List, |

| |Row Source property = table/query name | | |Row Source property = value1;value2;value3… |

| | | | | |

| |Use an INSERT… SQL statement to add the | | |Then: |

| |row to a table, | | |Use lstOutput.AddItem NewItem |

| |followed by | | | |

| |lstX.requery | | | |

| |to requery the list box and show the new | | | |

| |row. | | | |

| | | | |Or… |

| | | | |Use the RowSource Property: |

| | | | |lstX.RowSource = lstX.Rowsource & ";" & NewItem|

| | | | |for reverse order, do |

| | | | |lstX.RowSource = |

| | | | |NewItem & ";" & lstX.Rowsource |

| | | | |Similar to above, but use RemoveItem method: |

|Remove rows from a list|Similar to above, but use DELETE… SQL | | |lstX.RemoveItem rowNo |

|box |followed by | | |where rowNo = 0 for the 1st (possibly header) |

| |lstX.requery | | |row, 1 for 2nd row, etc. |

| | | | |If user has selected a row, then lstX.ListIndex|

| | | | |= rowNo, else = -1 |

| | | | |lstX.ColumnHeads property = True if there is a |

| | | | |header row, else False. |

| | | | |lstX.ListCount property holds the number of |

| | | | |rows (incl. header) |

| |For list box based on a table: | | |For list box based on a value list: |

|Clear a list box |Use a DELETE… SQL statement to remove all| | |lstX.RowSource = “” |

| |rows from the table. | | |This will remove any header row! |

| |Then lstX.requery | | | |

| |Use only if you don’t mind losing the | | | |

| |data! | | | |

| | | | |Or… |

| | | | |lstX.RemoveItem rowNo |

| | | | |in a loop until no more rows left |

| | | | | |

|Show a field from a | | |= DLookup("colName", "tblName", "[{criteria}") |The simplest method for related tables is |

|table on a form | | |Use this if there should only be one match. |normally to use a multi-table query in the |

| | | | |first place. |

| | | | | |

| | | | |Or… |

| | | | |Use a single-value list box, based on a query. |

| | | | | |

|Calculate totals of | |rstData.RecordCount will give a count of |Use Domain Aggregate functions, e.g.: | |

|table values, | |records. |DCount (“ColName”, “tblName”, {criteria}) | |

|counts of records, etc | |(need to do MoveLast to get the full count) | | |

| | | | | |

|Check for record(s) in | |rstData.Index = “PrimaryKey” | | |

|a table | |rstData.Seek “=”, | | |

| | |If rstData.NoMatch then … | | |

| | | |DLookup("colName", "tblName", {criteria}) | |

| | | |Use this if there should only be one match. | |

| | |Or… | | |

| | |Set rstData = dbsMyDB.OpenRecordset (SELECT…| | |

| | |WHERE.) | | |

| | |IF rstData.RecordCount = 0 then … | | |

| | | | |For a look-up table: |

|Find record(s) in a | |Set rstData = dbsMyDB.OpenRecordset (SELECT…|DLookup("colName", "tblName", {criteria}) |Store values in array(s) at start. |

|table | |{WHERE} ...) |Use this if there should only be one match. |Then loop through the arrays to find the |

| | |Code a loop to look through the data if >1 | |required record. |

| | |record read. | | |

| | | | |DoCmd.ApplyFilter, {criteria} |

|Filter records on a | | | | |

|form | | | | |

| | | | | |

| | | | | |

| | | | |Or… |

| | | | |DoCmd.OpenForm frmName, viewType, qryName, |

| | | | |{criteria}) |

| | | | |Or… |

| | | | |Wizard Combo box linked to field |

| | | | | |

|Remove form filter | | | |Me!FilterOn = False |

|(base form on query to | | | | |

|get original sort | | | | |

|order) | | | | |

| | | | |Or… |

| | | | |DoCmd.ShowAllRecords |

| | | |IsNull to check that something has been entered in a| |

|Validate parameters on | | |form field (Variant). | |

|form or input box | | | | |

| | | |=”” to check that something has been entered in an | |

| | | |InputBox (Text). | |

| | | | | |

| | | |isNumeric to check that value entered is a number |For form field, set field format property to |

| | | | |suitable number. |

| | | |IsDate to check that value entered is a date | |

| | | | |For form field, set the field Format property |

| | | |Then copy the value to a variable with an |to Date. |

| | | |appropriate datatype. | |

| | | | |Then copy the value to a variable with an |

| | | | |appropriate datatype. |

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download