Visual Basic for Application (VBA)

Visit MS Access as FrontEnd to see the VBA in Action

The movie_production_companies database integrates with Microsoft Access to streamline and facilitate the data entry process. This integration leverages Visual Basic for Applications (VBA), a powerful programming language that allows for the creation of user-friendly forms and automation of database interactions. Here's how I used VBA to enhance data entry and user interaction:

Custom Forms for User Input

Design: Custom forms are designed in Microsoft Access tailored to the specific data entry requirements of the database. These forms provide an intuitive graphical user interface, making it easier for users to input data correctly.
Validation: VBA scripts were also utilized to validate user input on the forms. This ensures that the data entered meets the predefined criteria and maintains the integrity of the data being inserted into the database.

The utilization of VBA in conjunction with Microsoft Access forms provides a robust solution for data entry into the movie_production_companies database. It not only enhances the user experience but also reinforces data integrity, streamlines processes, and upholds the security measures in place. As a result, data management becomes more efficient and reliable, enabling users to focus on the quality of their work without being hindered by complex database interactions.

Employee Form VBAs

This VBA statement checks the value selected in DivisionCombo. If "Crew" is selected, it prepares an SQL query to fetch names from the role table. If "Staff" is selected, it changes the query to fetch names from the department table. This functionality allows the RolesCombo combo box to display roles relevant to the selected division. Additionally, it controls the visibility of a subform (staff_salary_form) based on the selected division. The staff_salary subform is visible only if "Staff" is selected in the DivisionCombo.

Private Sub DivisionCombo_AfterUpdate()

Dim sql As String

If Me.DivisionCombo.Value = "Crew" Then

sql = "SELECT name FROM role"

ElseIf Me.DivisionCombo.Value = "Staff" Then

sql = "SELECT name FROM department"

End If

Me.RolesCombo.RowSource = sql

Me.RolesCombo.Requery

' Check the selected value in the combo box

If Me.DivisionCombo.Value = "Staff" Then

' Enable (make visible) the Staff Salary subform

Me.staff_salary_form.Visible = True

Else

' Disable (make invisible) the Staff Salary subform

Me.staff_salary_form.Visible = False

End If

End Sub

This VBA code snippet is designed to dynamically update an employee's gender image based on the gender selection. It consists of two event procedures and a subroutine that together ensure the gender-specific image is displayed whenever the form is loaded or the gender field is updated.

Private Sub Form_Current()

UpdateGenderImage

End Sub

Private Sub gender_AfterUpdate()

UpdateGenderImage

End Sub

Private Sub UpdateGenderImage()

Dim imagePath As String

' Update the path to reflect the provided location

imagePath = "C:\Users\HP\OneDrive\Project Files\Movie Companies db dev\"

' Update the case statements to use the new image names

Select Case Me.gender.Value

Case "M"

imagePath = imagePath & "malesilo.png"

Case "F"

imagePath = imagePath & "femalesilo.jpg"

Case Else

imagePath = "" ' Clear the image or set to a default image if gender is not specified

End Select

' Check if the file exists before attempting to set the picture property

If Len(imagePath) > 0 And Dir(imagePath) <> "" Then

Me.imgEmployeeGender.Picture = imagePath

Else

Me.imgEmployeeGender.Picture = "" ' Clear the image if file not found

End If

End Sub

Company Form VBAs

city_ctrl

This subroutine city_ctrl() checks if the combo box Combo840 has a selected value (city) that is not null or empty. If a valid selection exists, it updates three text boxes (country_code, city_junctionbox, regbodyname) with values from the combo box's columns. These values correspond to related data like the country code, city ID, and registration body name, linking the combo box selection to relevant detailed information. If the combo box is clear or an invalid selection is made, it resets the text boxes to empty, ensuring no outdated or incorrect data is displayed as users navigate through records or make selections.

Private Sub city_ctrl()

If Not IsNull(Me.Combo840.Value) And Not (Me.Combo840.Column(0) = "") Then

' Update the text boxes with the corresponding values

Me.country_code.Value = Me.Combo840.Column(2)

Me.city_junctionbox.Value = Me.Combo840.Column(3)

Me.regbodyname.Value = Me.Combo840.Column(4)

Else

' Clear the text boxes if no city is selected or if Combo840 is not populated

Me.country_code.Value = ""

Me.city_junctionbox.Value = ""

Me.regbodyname.Value = ""

End If

End Sub

Private Sub Combo840_AfterUpdate()

city_ctrl

End Sub

Private Sub Form_Current()

city_ctrl

End Sub

UI Form VBA

The VBA procedure Combo214_AfterUpdate() is designed for the UI form and executes when the user updates the combo box Combo214 . It connects to the current database to run an SQL query that joins multiple Access queries to fetch data related to the selected company name in the combo box. The procedure populates various form text boxes with the retrieved data, including company details, financial statistics, and employee counts. If no data is found for the selected company, the form fields display "N/A," and a message box alerts the user. The recordset is closed and cleared at the end of the procedure to clean up resources.

Private Sub Combo214_AfterUpdate()

Dim db As DAO.Database

Dim rs As DAO.Recordset

Dim query As String

Set db = CurrentDb

query = "SELECT root.*, root_ui.crewcount, root_ui.staffcount, root_ux.* " & _

"FROM ((root " & _

"INNER JOIN root_ui ON root.company.name = root_ui.company.name) " & _

"INNER JOIN root_ux ON root.company.name = root_ux.company.name) " & _

"WHERE root.company.name = '" & Me.Combo214 & "'"

Set rs = db.OpenRecordset(query, dbOpenDynaset)

If Not rs.EOF Then

Me.companyaddress.Caption = rs!address

Me.zipcode.Caption = rs!zip_code

Me.citycountry.Caption = rs!cityname & ", " & rs!countryname

Me.kindoforg.Caption = rs!kindoforgname

Me.regbody.Caption = rs!registrationbodyname

Me.regdate.Caption = rs!registration_date

Me.asset.Caption = Format(rs!total_asset, "Standard")

Me.liability.Caption = Format(rs!total_liability, "Standard")

Me.employeecount.Caption = rs!employeecount

' Including root_ui columns

Me.crewcount.Caption = rs!crewcount

Me.staffcount.Caption = rs!staffcount

' Including the root_ux columns

Me.shareholdercount.Caption = rs!shareholdercount

Me.filmcount.Caption = rs!filmcount

Me.grantcount.Caption = rs!grantcount

Me.approved.Caption = rs!approvedgrants

Me.pending.Caption = rs!pendinggrants

Me.denied.Caption = rs!deniedgrants

Else

' Handling case where no data is found

Me.companyaddress.Caption = "N/A"

Me.zipcode.Caption = "N/A"

Me.citycountry.Caption = "N/A"

Me.kindoforg.Caption = "N/A"

Me.regbody.Caption = "N/A"

Me.regdate.Caption = "N/A"

Me.asset.Caption = "N/A"

Me.liability.Caption = "N/A"

Me.employeecount.Caption = "N/A"

' You can also opt to display a message box

MsgBox "No data found for the selected company.", vbInformation, "No Data"

End If

rs.Close

Set rs = Nothing

End Sub