Skip to main content

Trigger Visual Impact: Transform MS Form Buttons with This VBScript Hack

 
Creating a responsive button interface in MS Access has never been easier. With a simple VBScript function, you can automatically highlight the clicked button, reset the rest, and update a label—all in real time. It adds a polished user experience by giving immediate visual feedback and guiding the user's next step. Think of it as turning your form into an interactive dashboard with just a few lines of code.

🔘 What Does SetBt() Do in MS Access?

This function is designed to highlight the active button in a form-based interface and update a few related controls based on which button is clicked.

🧠 Step-by-Step Breakdown:

  • Detect which button was clicked The function captures the name of the active control (the button the user just clicked) and extracts its last digit to figure out which button number it is—like B1, B2, ..., B7.

  • Safety checks It first checks if the user actually clicked something (i.e., ActiveControl isn't empty) and that the extracted number is between 1 and 7.

  • Reset all buttons' appearance It loops through all 7 buttons named B1 to B7 and sets their background color to match a neutral color box called Box0. This effectively removes the highlight from all buttons.

  • Highlight the clicked button It sets the background color of the active button to match Box1, which serves as a highlight color.

  • Set focus and caption update Then it moves the focus to a corresponding control named P1 to P7, and updates a label (LB0) to display the caption of the button that was clicked.

✅ Key Takeaways

  • Dynamic Highlighting: Only the button that was clicked gets visually highlighted, improving user experience.

  • Clean State Management: All buttons are reset to a neutral background before highlighting the selected one.

  • Smart Focus Handling: After interaction, focus shifts to a related control (P1–P7) to streamline the user's next action.

  • Real-Time Feedback: The label (LB0) updates instantly to reflect the selected button’s caption, providing clarity.

  • Error-Resistant Logic: Built-in checks prevent errors if no control is active or if the button name doesn’t follow expected rules.













Private Function SetBt()                        
    
    Dim Ax As Integer  ' Use Integer instead of Single for indexing
    Dim i As Integer
    
        ' Ensure ActiveControl is not null before proceeding
    If Not Me.ActiveControl Is Nothing Then
        Ax = Val(Right(Me.ActiveControl.Name, 1)) ' Use .Name instead of direct reference
        
                ' Ensure Ax is within valid bounds
         If  Ax >= 1 And Ax <= 7 Then
               ' Loop through buttons and reset their BackColor
             For i = 1 To 7
                  Me.Controls("B" & i).BackColor = Me.Box0.BackColor
             Next
            
            ' Update ActiveControl properties safely
            Me.ActiveControl.BackColor = Me.Box1.BackColor
            Me.Controls("P" & Ax).SetFocus
            Me.LB0.Caption = Me.Controls("B" & Ax).Caption            
            
        End If
    End If                      
                        
End Function

============================ APPROVAL BUTTONS ========================== 

Private Sub Command270_Click()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim htmlFile As Integer
    Dim htmlPath As String
    Dim rowHtml As String

    Set db = CurrentDb
    Set rs = db.OpenRecordset("qry_main")

    htmlPath = "C:\Users\TSAD6\OneDrive\Documents\01 INTERNAL TRANSFER TRACKER" & "\dashboard.html"
    htmlFile = FreeFile

    Open htmlPath For Output As htmlFile

    Print #htmlFile, "<!DOCTYPE html>"
    Print #htmlFile, "<html lang='en'><head><meta charset='UTF-8'>"
    Print #htmlFile, "<title>Internal Transfers</title>"
    Print #htmlFile, "<style>"
    Print #htmlFile, "body{font-family:Arial;padding:20px;background:#f4f4f4}"
    Print #htmlFile, "table{width:100%;border-collapse:collapse;background:#fff}"
    Print #htmlFile, "th,td{padding:10px;border:1px solid #ccc;text-align:left}"
    Print #htmlFile, "th{background:#007bff;color:white}"
    Print #htmlFile, "button{padding:5px 10px;background:#28a745;color:#fff;border:none;border-radius:4px;cursor:pointer}"
    Print #htmlFile, "button:hover{background:#218838}"
    Print #htmlFile, "</style></head><body>"

    Print #htmlFile, "<h2>Internal Transfer Dashboard</h2>"
    Print #htmlFile, "<table>"
    
    Print #htmlFile, "<thead><tr>" & _
                 "<th>ID</th><th>BN</th><th>EmpNamee</th><th>ReqType</th><th>FromDate</th><th>ToDate</th><th>Pending</th>" & _
                 "<th>Date Approved</th><th>Approved By</th><th>Action</th></tr></thead><tbody>"
        
    Do While Not rs.EOF
      rowHtml = "<tr>" & _
        "<td>" & rs!ID & "</td>" & _
        "<td>" & rs![BN] & "</td>" & _
        "<td>" & rs![EmpName] & "</td>" & _
        "<td>" & rs![ReqType] & "</td>" & _
        "<td>" & rs![FromDate] & "</td>" & _
        "<td>" & rs![ToDate] & "</td>" & _
        "<td>" & rs![Pending] & "</td>" & _
        "<td></td>" & _
        "<td></td>" & _
        "<td><button onclick=""approveRow(this)"">Approve</button></td>" & _
        "</tr>"


        Print #htmlFile, rowHtml
        rs.MoveNext
    Loop

    Print #htmlFile, "</tbody></table>"
    Print #htmlFile, "<script>"
    Print #htmlFile, "function approveRow(btn) {"
    Print #htmlFile, "  var row = btn.parentNode.parentNode;"
    Print #htmlFile, "  row.cells[5].innerText = 'Approved';"
    Print #htmlFile, "  row.cells[6].innerText = 'Approved by HR';"
    Print #htmlFile, "  btn.disabled = true;"
    Print #htmlFile, "  btn.innerText = 'Approved';"
    Print #htmlFile, "  btn.style.backgroundColor = '#6c757d';"
    Print #htmlFile, "}"
    
    Print #htmlFile, "function approveRow(btn) {"
    Print #htmlFile, "  var row = btn.parentNode.parentNode;"
    Print #htmlFile, "  var now = new Date().toLocaleString();"
    Print #htmlFile, "  var username = '" & Environ$("USERNAME") & "';"  ' ? Inject Windows username
    Print #htmlFile, "  row.cells[5].innerText = 'Approved';"
    Print #htmlFile, "  row.cells[6].innerText = 'Approved by HR';"
    Print #htmlFile, "  row.cells[7].innerText = now;"           ' Date Approved
    Print #htmlFile, "  row.cells[8].innerText = username;"      ' Approved By
    Print #htmlFile, "  btn.disabled = true;"
    Print #htmlFile, "  btn.innerText = 'Approved';"
    Print #htmlFile, "  btn.style.backgroundColor = '#6c757d';"
    Print #htmlFile, "}"
    
    Print #htmlFile, "</script>"
    Print #htmlFile, "</body></html>"

    Close htmlFile
    rs.Close
    Set rs = Nothing
    Set db = Nothing

    MsgBox "Dashboard created at: " & htmlPath, vbInformation
    
End Sub

============================ DASHBOARD LINK ========================== 

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>Department File Download Dashboard</title>
  <style>
    body {
      font-family: Arial, sans-serif;
      background-color: #f7f9fb;
      padding: 40px;
    }

    h2 {
      text-align: center;
      color: #333;
    }

    table {
      margin: 0 auto;
      width: 80%;
      border-collapse: collapse;
      background-color: #fff;
      box-shadow: 0 2px 6px rgba(0,0,0,0.1);
    }

    th, td {
      padding: 14px 18px;
      text-align: left;
      border-bottom: 1px solid #ddd;
    }

    th {
      background-color: #4CAF50;
      color: white;
    }

    a.download-link {
      color: #1a73e8;
      text-decoration: none;
      font-weight: bold;
    }

    a.download-link:hover {
      text-decoration: underline;
    }
  </style>
</head>
<body>

  <h2>Department File Download Dashboard</h2>

  <table>
    <thead>
      <tr>
        <th>Department Name</th>
        <th>Download Link</th>
      </tr>
    </thead>
    <tbody>
      <tr>
        <td>Finance</td>
        <td><a class="download-link" href="files/finance_report.pdf" download>Download Finance Report</a></td>
      </tr>
      <tr>
        <td>HR</td>
        <td><a class="download-link" href="files/hr_policy.pdf" download>Download HR Policy</a></td>
      </tr>
      <tr>
        <td>IT</td>
        <td><a class="download-link" href="files/it_guide.pdf" download>Download IT Guide</a></td>
      </tr>
    </tbody>
  </table>

</body>
</html>

=================== DASHBOARD WITH SEND BUTTON ======================= 


Private Sub Command274_Click()
 
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim htmlFile As Integer
    Dim htmlPath As String
    Dim rowHtml As String

    Set db = CurrentDb
    Set rs = db.OpenRecordset("qry_main")

    htmlPath = "C:\Users\TSAD6\OneDrive\Documents\01 INTERNAL TRANSFER TRACKER\dashboard.html"
    htmlFile = FreeFile

    Open htmlPath For Output As htmlFile

    Print #htmlFile, "<!DOCTYPE html>"
    Print #htmlFile, "<html lang='en'><head><meta charset='UTF-8'>"
    Print #htmlFile, "<title>Internal Transfers</title>"
    Print #htmlFile, "<style>"
    Print #htmlFile, "body{font-family:Arial;padding:20px;background:#f4f4f4}"
    Print #htmlFile, "table{width:100%;border-collapse:collapse;background:#fff}"
    Print #htmlFile, "th,td{padding:10px;border:1px solid #ccc;text-align:left}"
    Print #htmlFile, "th{background:#007bff;color:white}"
    Print #htmlFile, "button{padding:5px 10px;background:#28a745;color:#fff;border:none;border-radius:4px;cursor:pointer}"
    Print #htmlFile, "button:hover{background:#218838}"
    Print #htmlFile, "#sendBtn{margin-top:20px;background:#ffc107;color:#000}"
    Print #htmlFile, "#sendBtn:hover{background:#e0a800}"
    Print #htmlFile, "</style></head><body>"

    Print #htmlFile, "<h2>Internal Transfer Dashboard</h2>"
    Print #htmlFile, "<table>"
    Print #htmlFile, "<thead><tr>" & _
                 "<th>ID</th><th>BN</th><th>EmpNamee</th><th>ReqType</th><th>FromDate</th><th>ToDate</th><th>Status</th><th>Pending</th>" & _
                 "<th>Date Approved</th><th>Approved By</th><th>Action</th></tr></thead><tbody>"

    Do While Not rs.EOF
        rowHtml = "<tr>" & _
            "<td>" & rs!ID & "</td>" & _
            "<td>" & rs![BN] & "</td>" & _
            "<td>" & rs![EmpName] & "</td>" & _
            "<td>" & rs![ReqType] & "</td>" & _
            "<td>" & rs![FromDate] & "</td>" & _
            "<td>" & rs![ToDate] & "</td>" & _
            "<td>" & rs![Status] & "</td>" & _
            "<td>" & rs![Pending] & "</td>" & _
            "<td></td>" & _
            "<td></td>" & _
            "<td><button onclick=""approveRow(this)"">Approve</button></td>" & _
            "</tr>"

        Print #htmlFile, rowHtml
        rs.MoveNext
    Loop

    Print #htmlFile, "</tbody></table>"
    Print #htmlFile, "<button id='sendBtn' onclick='sendApproved()'>Send Approvals</button>"

    Print #htmlFile, "<script>"
    Print #htmlFile, "function approveRow(btn) {"
    Print #htmlFile, "  var row = btn.parentNode.parentNode;"
    Print #htmlFile, "  var now = new Date().toLocaleString();"
    Print #htmlFile, "  var username = '" & Environ$("USERNAME") & "';"
    Print #htmlFile, "  row.cells[6].innerText = 'Approved';"
    Print #htmlFile, "  row.cells[7].innerText = 'HR';"
    Print #htmlFile, "  row.cells[8].innerText = now;"
    Print #htmlFile, "  row.cells[9].innerText = username;"
    Print #htmlFile, "  btn.disabled = true;"
    Print #htmlFile, "  btn.innerText = 'Approved';"
    Print #htmlFile, "  btn.style.backgroundColor = '#6c757d';"
    Print #htmlFile, "}"

    Print #htmlFile, "function sendApproved() {"
    Print #htmlFile, "  var rows = document.querySelectorAll('tbody tr');"
    Print #htmlFile, "  var approved = [];"
    Print #htmlFile, "  rows.forEach(function(row) {"
    Print #htmlFile, "    if (row.cells[6].innerText === 'Approved') {"
    Print #htmlFile, "      approved.push(row.cells[0].innerText);"
    Print #htmlFile, "    }"
    Print #htmlFile, "  });"
    Print #htmlFile, "  alert('Sending the following approvals: ' + approved.join(', '));"
    Print #htmlFile, "  alert('Copy the following Approved IDs and paste into Access:\n\n' + approved.join(', '));"
    Print #htmlFile, "}"
    Print #htmlFile, "</script></body></html>"

    Close htmlFile
    rs.Close
    Set rs = Nothing
    Set db = Nothing

    MsgBox "Dashboard created at: " & htmlPath, vbInformation

 

End Sub
















Comments

Post a Comment

Popular posts from this blog

Fixing Slow Internet at Work—Here’s the Step-by-Step That Finally Worked

The Day the Internet Slowed Down—and How I Fixed It Fast It was a regular Thursday morning—until my phone buzzed. The general manager was on the line, clearly frustrated. She couldn’t log into SAP. “The internet’s crawling,” she said. “Can you do something about it?” Challenge accepted. Without wasting a minute, I grabbed my laptop and sprang into action. I knew that identifying the real problem quickly was key. My first move? Run a speed test—fast, simple, and revealing. Here’s how I did it, step by step: Step 1 Visit Speedtest.net 🌐 to quickly check your internet speed! 🚀 Step 2 Open the Command Prompt (CMD) 💻 and perform a quick ping test to your ISP 🌐 and other IP addresses to check for any network delays ⏳ or issues (e.g., 4.2.2.2). Step 3 Check the router's LED indicators 💡 for any unusual blinking patterns 🔄 that may indicate connectivity issues 🌐. Step 4 Check the back of the router 🔌 to confirm that all essential lights 💡 are on and functioning correctly. Step 5 T...
2 comments
Read more

Grounded by Loss, Lifted by Grit: A True Story of Reinvention Abroad (Saudi Arabia)

🌅 My Last Day, A Life-Changing Goodbye It was a hectic Thursday—June 24, 2021—my final day at work. As I handed over my responsibilities to my Filipino replacement and prepared to close a chapter of my life, I received a message that shattered my world. My sister reached out via Facebook: our father had passed away. 😢 I was paralyzed with grief. I turned to a close Filipino friend and shared the news; word spread quickly, and the room softened in sympathy. The Chief Executive and General Manager both offered their heartfelt condolences before departing. 🤝✨ When I arrived back at my rented room in Al-Khobar, the tears came rushing. I wept uncontrollably—my father’s memory flooding my heart. ❤️ ✅ My Final Settlement and Exit Visa Despite the emotional turmoil, the exit process went smoothly. I received my final settlement 💰 and exit visa ✈️, followed by a confirmed flight scheduled for July 25, 2021. The closure I expected was now within reach—until new obstacles emerged. 🛂 Urgent V...
Post a Comment
Read more

Step-by-Step Guide to Renewing Your Company’s Civil Defence License in KSA—Fast and Hassle-Free

Civil Defence is a regulatory body tasked by the government to protect lives and properties regionally and internationally. As a company, it is compulsory to get a Civil Defense License before it can operate a business and has to undergo compliance to the Fire Fighting System, Safety and Security - You may refer to step by step guide on how to get around this procedure.   Step 1 Secured the below certificates and other documents to be submitted to Salamah Online, among these are as follows: 1. Facility Contract 2. CR 3. Certificate of Insurance            a.  Comprehensive          b.  Workmen            c.  Employers          d.  Property All Risks 4. Municipal & Professional License 5. CCTV Certification from Police 6. Enjaz / Police Clearance 7. Annual Maintenance Contract—Firefighting Equipment 6. Salama Code Step 2   Share a...
Post a Comment
Read more