Filter Combobox based on Another Combobox

How to synchronize two combo boxes in a Microsoft Access Form.


Watch the video and/or follow the step by step instructions below (this will take about 15 minutes of your time but you will get what you came for!)






Requirements

• This example uses MS Access 2007 but it should also work in previous versions.
  
• You will need six objects in your database:




• One form: frmMain
  
• Two combo boxes: The Master combo box: cboProjects and The Slave combo box: cboProjectTasks
  
• One table: tblProjectsAndTasks
  
• Two queries: The Master query: qryProjects and The Slave query: qryProjectTasks

STEP 1: Create the Form:

• Create a new form and name it frmMain.
  
• Save the form.

STEP 2: Create the combo boxes:

• Create the Master combo box.
  
• Name it: cboProjects
  
• Create the Slave combo box.
  
• Name it: cboProjectTasks
  
• Save the form.

STEP 3: Create the data table:

• Create a table and name it: tblProjectsAndTasks
  
• Add these FieldNames (and DataTypes):




• TaskID (Autonumber)
  
• ProjectName (Text)
  
• TaskName (Text)


• Populate the table.




• In this example the projects are: Alpha, Delta and Gamma.
  
• And each project has multiple tasks, for example:




• Project Alpha Task 1
  
• Project Alpha Task 2
  
• Project Gamma Task 3

STEP 4: Create the master query:

• The Master Query should be named: qryProjects
  
• This query will select unique project names from tblProjectsAndTasks.
  
• It will be the data source for the master combo box.
  
• The SQL code for this query is: SELECT DISTINCT ProjectName FROM tblProjectsAndTasks;
  
• Alternatively, you could use the following SQL code: SELECT ProjectName FROM tblProjectsAndTasks GROUP BY ProjectName;
  
• Either way, the output should be:




• Project Alpha
  
• Project Delta
  
• Project Gamma

STEP 5: Create the Slave Query:

• Create a new query and name it: qryProjectTasks
  
• This query will show tasks related to the project that is selected in cboProjects.
  
• The SQL code for this query is: SELECT DISTINCT ProjectName, TaskName FROM tblProjectsAndTasks WHERE ProjectName =[forms]![frmMain].[cboProjects].[value];
  
• At this point, running this query will not return any results because the WHERE clause is looking for the project selected in cboProjects, which is not data bound yet!

STEP 6: Data Binding the Master Combo Box:

• Give cboProjects these properties:




• Row Source: qryProjects
  
• Row Source Type: Table/Query
  
• Column Count: 1
  
• Bound Column: 1

STEP 7: Data Binding the Slave Combo Box:

• Give cboProjectTasks these properties:




• Row Source: qryProjectTasks
  
• Row Source Type: Table/Query
  
• Column Count: 2
  
• Column Width: 0";1"
  
• Bound Column: 2

STEP 8: Refreshing the Slave Combo Box:

• The slave combo box needs to be refreshed after each change in the master combo box. The VBA procedure below will refresh the slave combo box.
  
• This procedure should be tied to the On Change event of the master combo box. Private Sub cboProjects_Change() Me.cboProjectTasks.Requery End Sub

STEP 9: Clearing the Slave Combo Box:

• Refreshing the combo box does not clear the current task selected.

Modify the VBA procedure to clear the combo box:

Private Sub cboProjects_Change()

Me.cboProjectTasks.Requery

Me.cboProjectTasks = ""

End Sub


STEP 10: ENJOY!

SQL APPEND TABLE vs. CREATE TABLE

--TO CREATE A NEW TABLE:

SELECT *
INTO newTableName
FROM sourceTableName



--TO APPEND DATA TO AN EXISTING TABLE:

INSERT INTO existingTableName
SELECT *
FROM sourceTableName

Check for Numeric Values in a String using VBA

I needed to check that the values entered into a textbox called StoreID in an Access Database where numerical and composed of 3 digits. Here is one solution: on the AfterUpdate event of the textbox, put this code:

Private Sub StoreID_AfterUpdate()
Dim intLen, intChar, i As Integer
Dim strTextBox As String

intLen = Len(StoreID) 'Len is a function that counts the amount of characters in a string
If intLen <> 3 Then ' if the length is not 3, give message box
MsgBox "The field 'Store ID' requires 3 digits from 001 to 900"
Else ' if length is 3 then assign variables and start loop for checking numerical values
intLen = Len(Me!StoreID)
'Me! always refers to the active form
strTextBox = Me!StoreID
For i = 1 To 3 'For 1 to 3
intChar = Mid(strTextBox, i, 1) 'Mid function takes middle values (expression,start value, length of selection)
If IsNumeric(intChar) = False Then
MsgBox " ' " & intChar & " ' is not a number, please enter numeric values only from 001 to 900"
Exit Sub
Else
End If
Next
End If
End Sub

Call an Access Database Procedure from another Access Database

Here is an Access VBA example of how to call an external Access Database VBA procedure from within a different access database.

Sub ForeignProcedure()

Dim appAccess As Access.Application

' Create instance of Access Application object.
Set appAccess = CreateObject("Access.Application")

' Open MyDatabaseName.mdb database in Microsoft Access window.
appAccess.OpenCurrentDatabase “C:\MyDatabaseName", False
'False so as not to open in exclusive mode
' Run Sub procedure.
appAccess.Run "ForeignProcedureName"
appAccess.Quit
Set appAccess = Nothing

MsgBox "Done!"

End Sub

Call an Access Database VBA Procedure from Excel

In order to call an Access vba procedure from an Excel spreadsheet, the first thing you need in Excel is a Reference for the Microsoft Access Object Library, as seen in the picture below ...




To see this screen, from within Excel, go to VBA (press ALT + F11), then select Tools from the top menu and then References

Public Sub ProcedureInAccess()

‘this VBA code is run in Excel
Dim acApp As Object
Dim db As Object
Set acApp = CreateObject("Access.Application")
acApp.OpenCurrentDatabase ("C:\MyDatabaseName.mdb")
Set db = CurrentDb
acApp.Run "NameOfProcedureInAccess"
acApp.Quit
Set acApp = Nothing

End Sub

Delete Rows from Access Database Table Using VBA and SQL

Using the DoCmd property you can delete all rows in an Access Database table using Access VBA and SQL

Sub DeleteRows()

'Deletes all rows from tblName in an Access Database

DoCmd.RunSQL "DELETE tblName.* FROM tblName;"

End Sub

Access Database Programmer Forum

I recently came across a great resource for Access Database programming. It is a forum where you can post questions and receive knowledgeable answers from the forum's members. http://www.utteraccess.com/

Transaction Log for Microsoft Access Database

Here is one of the most useful pieces of code I have every come accross the Internet for programming an access database. This code will track all changes made by a user, meaning it will keep track of what data was changed, who changed it and when it was changed. This functionality is usually called a transaction log or a change log. I have copied the code directly from this page:http://www.tek-tips.com/faqs.cfm?fid=291 . The code was written by Michael Red a regular contributor at Tek-tips, which is a wonderful website that I have used many times for finding answers to many programming questions.

I have learned a few things not explained in his original code:

1) Put this code in a Module.
2) Call the basLogTrans function from the BeforeUpdate event of any form that you want to track changes.
3) Pass 3 values to the basLogTrans function: FormName, Name of Field with Unique ID, and the Unique ID.
In my implementation, I use Me as the form name, "CustomerID" as the Unique ID field, and CustomerID to pass the actual customer id value. So here is how I would call the basLogTrans function:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Call basLogTrans(Me, "CustomerId", CustomerId)

End Sub

4) Mike says the code will work only on a form formated as single form, but I see it also works on a datasheet form.
5) He alos says that Access Security needs to be enabled, but I'm not sure it's true. When I used his original code for CurrentUser(), I got back "Admin" as the user that was making changes. But what I really want to know is the NT User ID of whoever is making the changes, therefore, I use Environ("Username"). If you have problems with the Environ function, please see here how to enable the Environ Function properly.
6) The rest of the instructions are pretty clear, but just to be safe, remember to add a reference for DAO, bind the form to a table, but who's to say this won't work on a Select Query? I don't know, why don't you tell me later? Make 2 tables, one called tblHist and the other tblHistMemo. First create tblHist, with the fields indicated in code comments in the function called basAddHist, then make a copy of it, name it tblHistMemo and change the OldVal and NewVal fields to datatype Memo.

That's it, it should work easily.



OK, HERE IS THE ORIGINAL VBA CODE:
All this needs to be placed in a Module, and called from the BeforeUpdate event of a form.


Public Function basLogTrans(Frm As Form, MyKeyName As Variant, MyKey As Variant) As Boolean

'Michael Red 8/1/2002 Transaction log for Ms. Access BOUND forms.

'The following three functions and table definitions are
'(should be) sufficient to establish a 'poor mans' transaction
'log for Ms. Access bound forms.

'As noted in basAddHist, you will need to create the tables
'for History (general fields) and Memo History (history of
'JUST memo fields).

'Also, note that these functions are designed to work in
'conjunction with Ms. Access Bound forms where the view is
'set to single form. Further, the history will only capture
'the user if the db App is "secured".

'In the following, "MyKey" refers to the Record ID (Unique value)
'for the single record which the form is 'tied' to.

Dim MyDb As DAO.Database
Dim MyCtrl As Control
Dim MyMsg As String
Dim Hist As String


'In the Statement block below, basFlgValidRec is a routine which
'includes validation of all fields necessary to 'define' a record
'is valid. Commented out here, as I do not know what validation
'rules need to be checked/enforced.

' If (Not basFlgValidRec) Then
' basLogTrans = False 'Let User know of FAILURE!!
' Exit Sub
' End If


For Each MyCtrl In Frm.Controls
If (basActiveCtrl(MyCtrl)) Then 'Consider replacing w/ test for "ControlSource"
If ((MyCtrl.Value <> MyCtrl.OldValue) _
Or (IsNull(MyCtrl) And Not IsNull(MyCtrl.OldValue)) _
Or (Not IsNull(MyCtrl) And IsNull(MyCtrl.OldValue))) Then
If (MyCtrl.ControlType = dbMemo) Then
Hist = "tblHistMemo"
Else
Hist = "tblHist"
End If
Call basAddHist(Hist, Frm.Name, MyKey.Name, MyCtrl)
End If
End If
Next MyCtrl

basLogTrans = True 'Let User know sucess

End Function
Public Function basActiveCtrl(Ctl As Control) As Boolean
Select Case Ctl.ControlType

Case Is = acTextBox
basActiveCtrl = True

Case Is = acLabel
Case Is = acRectangle
Case Is = acLine
Case Is = acImage
Case Is = acCommandButton
Case Is = acOptionButton
Case Is = acCheckBox
basActiveCtrl = True
Case Is = acOptionGroup
Case Is = acBoundObjectFrame
Case Is = acListBox
basActiveCtrl = True
Case Is = acComboBox
basActiveCtrl = True
Case Is = acSubform
Case Is = acObjectFrame
Case Is = acPageBreak
Case Is = acPage
Case Is = acCustomControl
Case Is = acToggleButton
Case Is = acTabCtl

End Select
End Function
Public Function basAddHist(Hist As String, Frm As String, MyKeyName As String, MyCtrl As Control)

'tblHist
'FrmName Text 80 Name of the form where change Occured
'FldName Text 80 Field Name of the changed value
'dtChg Date/Time 8 Date/Time of Change (MACHINE value!!)
'OldVal Text 255 Field Value BEFORE change
'NewVal Text 255 Field Value after change
'UserId Text 50 User who Made Change
'MyKey Variant ?? KeyField as Indentified by Caller
'MyKeyName Text 80 'Key Field Contents

'tblHistMemo is the same structure except the "type" for the fields
'OldContents and NewContents are Memo (and therfore the length is "??")

Dim dbs As DAO.Database
Dim tblHistTable As DAO.Recordset

Set dbs = CurrentDb
Set tblHistTable = dbs.OpenRecordset(Hist, dbOpenDynaset)

With tblHistTable
.AddNew
!MyKey = Forms(Frm).Controls(MyKeyName)
!MyKeyName = MyKeyName
!frmName = Frm
!FldName = MyCtrl.ControlSource
!dtChg = Now()
' !UserId = Environ("Username") 'Orlando's Way
!UserId =CurrentUser() 'Mike's way, example of current user would be Admin
!OldVal = MyCtrl.OldValue
!NewVal = MyCtrl
.Update
End With

End Function

First and Last Access Function not supported in SQL Server

In a Microsoft Access database, in order to return the first or last record in the result set returned by a query, you can use the First and Last Functions. The syntax is simple: First(expr) or Last(expr). If you try to use these functions in SQL Server, however, they will not work. You will get a message similar to this:

Server: Msg 195, Level 15, State 10, Line 2
'First' is not a recognized function name.

In order to repeat the desired outcome in SQL Server, simply use the MIN and MAX function.

ACCESS SQL VERSION:
SELECT
ContractorID, FIRST(Contractor)
FROM
tblContractor
WHERE
ContractorID = "ABC6783"
GROUP BY
ContractorID

(T-SQL) SQL SERVER VERSION:
SELECT
ContractorID, MIN(Contractor)
FROM
tblContractor
WHERE
ContractorID = 'ABC6783'
GROUP BY
ContractorID

Loop in VBA Easy Example

There are different versions of looping in VBA. Here is an example of the Do While version.

Sub letsLoop()
Dim intCounter As Integer

intCounter = 1 'Set the counter to 1

Do While intCounter <3'Loop while value of intCounter less then 3
MsgBox intCounter 'show message box with value of intCounter
intCounter = intCounter + 1 ' increase value of intCounter by 1
Loop 'loop again

End Sub