Access

VBA modify Multivalued Field

2015-03-03T11:15:00.000-08:00

Private Sub Criteria_Tracker_ID_Exit(Cancel As Integer)

Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim childRS As Recordset
Dim var As Variant
Dim CriteriaName As String
Dim strRange1 As String
Dim strRange2 As String

CriteriaName = ""

Set db = CurrentDb
Set rst1 = db.OpenRecordset("SF Data and Technology Priorities")
strRange1 = "SELECT [Criteria Tracker ID] FROM [SF Data and Technology Priorities] WHERE [ID] = " & Me.[ID].Value
' "Me" identify current selected record on the form.
Set rs1 = db.OpenRecordset(strRange1)

' Multivalued field is actually a recordset. Here "Criteria Tracker ID" is multivalued field
Set childRS = rs1![Criteria Tracker ID].Value

If childRS.RecordCount = 0 Then
Me.[Criteria Name].Value = ""
Me.[Criteria Name].SetFocus
Exit Sub
End If

childRS.MoveFirst

' Loop through the records in the child recordset.
Do Until childRS.EOF
strRange2 = "SELECT [Sector] FROM [SF Criteria Tracker] WHERE [ID] = " & childRS.Fields(0).Value
Set rs2 = db.OpenRecordset(strRange2)
CriteriaName = CriteriaName & rs2![Sector].Value & "; "
rs2.Close
childRS.MoveNext
Loop

rs1.Close
Me.[Criteria Name].Value = CriteriaName
Me.[Criteria Name].SetFocus
' [Criteria Name] can be written as Criteria_Name, we can use _ to replace space in the field name
End Sub

Form VBA Sort or Order by 2 or more fields

2015-01-09T14:00:00.001-08:00

Me.OrderBy = Me.cboSort1 & " DESC, " & Me.cboSort2 & " ASC"
Me.OrderByOn = True

Add VBA code to a control (button, combobox etc.) on from

2015-01-06T11:19:00.001-08:00

1. Go to design or layout view of the form
2. Right click the Control and select "Properties" ==> "Event" tab
3. Go to "On click", select "...", on popup box select "Code Builder" (Alternatively, select "[Event Procedure]", then click "...")
4.Change Control name if necessary by going to "Other" tab ==> "Name"

Click a button on a form to export a table

2015-01-06T11:13:00.002-08:00

1. Go to design or layout view of the form
2. Right click the command button and select "Properties" ==> "Event" tab
3. Go to "On click", select "...", on popup box select "Macro Builder"
4. Under "Action Catalog" on the right (click "Action Catalog" under "Design" tab on the top if it is shown", select "Data Import/Export"==> "ExportWithFormatting"

Create Dropdown list in Form (only one value can be selected)

2015-01-06T10:53:00.003-08:00

1. Go to Design or Layout view of the form
2. Right click the field, select "Properties"
3. Go to "Data" tab
4. Change "Row Source" Type to "Value List"
5. Enter list of choices under "Row Source", e.g. "Yes";"No", or click "..." to enter

You may want to add below for data validation to prevent accidentally direct data inputs to the linked table (not necessary)
1. Go to the Design view of the table linked to the form.
2. Select Field ==> go to "General" tab at the bottom.
3. Go to "Validation Rule" and enter list of choices, e.g. "Yes";"No"

Create dropdown list with multiple selection check boxes in form

2015-01-06T10:53:00.000-08:00

1. Go to the Design view of the table linked to the form.
2. Select Field ==> go to "Lookup" tab at the bottom.
3. Change "Row source Type" to "Value List"
4. Go to "Row Source" and enter list of choices,
e.g. "ABCP";"ABS";"All Asset Classes";"CB";"CMBS";"RMBS";"SME";"SC";"LOC"

Apply Conditonal Formatting to Form

2015-01-06T10:45:00.000-08:00

Go to Design or Layout View, click on the column/field, a "Form Design Tool" section appears on the top menu bar, go to format ==> Conditional Formatting

example rules: ([Status]="Backlog" Or [Status]="Completed") And [Active]="Yes"

VBA apply multiple filters

2015-01-06T10:36:00.003-08:00

Private Sub cmdGenerate_Click()
Dim PriorityFilter As String
Dim AssetClassFilter As String
Dim ImpactedRegionFilter As String
Dim ImpactTypeFilter As String
Dim StatusFilter As String
Dim TotalFilter As String

If Me.cboPriority <> "All" Then
TotalFilter = "[Priority] = '" & Me.cboPriority & "'"
Else
TotalFilter = "([Priority] Is Null Or [Priority] Like '*')"
End If

If Me.cboAssetClass <> "All" Then
TotalFilter = TotalFilter & " And ([Asset Class] = 'All Asset Classes' Or [Asset Class] Like '*" & Me.cboAssetClass & "*')"
Else
TotalFilter = TotalFilter & " And ([Asset Class] Is Null Or [Asset Class] Like '*')"
End If

If Me.cboImpactedRegion <> "All" Then
TotalFilter = TotalFilter & " And ([Impacted Region] = 'All Regions' Or [Impacted Region] Like '*" & Me.cboImpactedRegion & "*')"
Else
TotalFilter = TotalFilter & " And ([Impacted Region] Is Null Or [Impacted Region] Like '*')"
End If

If Me.cboImpactType <> "All" Then
TotalFilter = TotalFilter & " And [Impact Type] = '" & Me.cboImpactType & "'"
Else
TotalFilter = TotalFilter & " And ([Impact Type] Is Null Or [Impact Type] Like '*')"
End If

If Me.cboStatus <> "All" Then
TotalFilter = TotalFilter & " And [Status] = '" & Me.cboStatus & "'"
Else
TotalFilter = TotalFilter & " And ([Status] Is Null Or [Status] Like '*')"
End If

DoCmd.ApplyFilter , TotalFilter

Forms![SF Data and Technology Priorities].OrderBy = Me.cboSort
Forms![SF Data and Technology Priorities].OrderByOn = True

End Sub

Delete dataset from database using VBA

2012-12-10T13:49:00.003-08:00

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim DatasetID As Integer
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\CovBonds\CB.mdb"
Dim strRange As String
strRange = "SELECT * FROM dbo_tblCoveredBonds Where [cboExistingCoveredBondProgram] = '" & cboExistingCoveredBondProgram & "' AND [cboCoveredBondType] = '" & cboCoveredBondType & "' AND [cboCoveredBondSubType] = '" & cboCoveredBondSubType & "' AND [cboReportingDate] = #" & VBA.Format(cboReportingDate, "mm/dd/yyyy") & "#;"
rst.Open strRange, _
cnn, adOpenDynamic, adLockOptimistic
If rst.RecordCount <> 0 Then
DatasetID = rst![ProgramID].Value
rst.Delete
Else
MsgBox "This dataset is not in database."
Exit Sub
End If
Set rst = Nothing

VBA Select MAX dataset from database

2012-09-20T10:55:00.003-07:00

Sub test()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim oCm As New ADODB.Command
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\CovBonds\CB.mdb"
Dim strRange As String

strRange = "SELECT MAX([ProgramID]) FROM dbo_tblCoveredBonds;"
rst.Open strRange, _
cnn, adOpenStatic
rst.MoveFirst
Sheets("Misc").Range("R2").Value = rst.RecordCount
Sheets("Misc").Range("Q2").Value = rst.Fields(0).Value

End Sub

Delete All Tables or One Table

2012-07-31T13:39:00.003-07:00

Sub DeleteFromAllTables()
On Error GoTo Err_DeleteFromAllTables
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef

Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
dbCurr.Execute "DELETE * FROM [" & tdfCurr.Name & "]", dbFailOnError
End If
Next tdfCurr

End_DeleteFromAllTables:
Set dbCurr = Nothing
Exit Sub

Err_DeleteFromAllTables:
MsgBox Err.Number & ": " & Err.Description
Resume End_DeleteFromAllTables

End Sub

'delete one table data code: DoCmd.RunSQL "DELETE Table1.* FROM Table1;"

Open Excel File within the Same Folder

2012-07-31T13:37:00.002-07:00

Dim objXL As Object
Dim objXLWB As Object

Dim MyBook As String
MyBook = CurrentProject.Path & "\YouExcelWorkBook.xlsx"
Set objXL = CreateObject("Excel.Application")
'if excel is opened, Set objXL = GetObject(, "Excel.Application"), when excel not opened or opened (workbooks won't show), objXL = CreateObject("Excel.Application")
Set objXLWB = objXL.workbooks.Open(MyBook)

Close All Tables VBA Code

2012-07-31T13:33:00.001-07:00

Dim TableName As AccessObject
With CurrentData
For Each TableName In .AllTables
If TableName.IsLoaded Then
DoCmd.Close acTable, TableName.Name, acSaveYes
End If
Next TableName
End With

Access Number Data Type Field Size

2012-07-26T12:35:00.000-07:00

Byte — Use for integers that range from 0 to 255. Storage requirement is 1 byte.
Integer — Use for integers that range from -32,768 to 32,767. Storage requirement is 2 bytes.
Long Integer — Use for integers that range from -2,147,483,648 to 2,147,483,647. Storage requirement is 4 bytes.
Single Use for numeric floating point values that range from -3.4 x 10E38 to 3.4 x 10E38 and up to seven significant digits. Storage requirement is 4 bytes.
Double Use for numeric floating point values that range from -1.797 x 10E308 to 1.797 x 10E308 and up to fifteen significant digits. Storage requirement is 8 bytes.
Replication ID Use for storing a globally unique identifier required for replication. Storage requirement is 16 bytes. Note that replication is not supported using the .accdb file format.
Decimal Use for numeric values that range from -9.999... x 10E27 to 9.999... x 10E27. Storage requirement is 12 bytes.

For best performance, always specify the smallest sufficient Field Size.

(Quoted from Microsoft.com)