Client Side Storage Access And WebSQL Database Part3

In this post I am showing how you can make your webform more user friendly.

Sub GenerateContainmentInspectionsHTML()
    Dim intRandomDB As Integer
    Dim intSeed As Integer
    Dim strDB As String
    
    'this is seed value for the RND function's random number generator.
    Randomize
    
    'Int ((upperbound - lowerbound + 1) * Rnd + lowerbound)
    intRandomDB = Int((2500 - 1 + 1) * Rnd(2) + 10)
    Debug.Print intRandomDB
    strDB = "inspdb" & intRandomDB
    
    strPath = CurrentProject.Path & "\containment_inspections.htm"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    strSQL = "SELECT * FROM qrySecondaryContainmenttodrain"
    
    Set rst = CurrentDb.OpenRecordset(strSQL)
    rst.MoveFirst
    
    Dim objFile As Object
    Set objFile = fso.CreateTextFile(strPath, True, True)
    objFile.write " <!DOCTYPE HTML>" & vbCrLf & vbCrLf

    objFile.write "<html>" & vbCrLf
       objFile.write "<head>" & vbCrLf
       
    '=====================================================================================
    'create a WebSQL database for the Access DB containment inpections
    '=====================================================================================
        objFile.write "<script src=""https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js""></script>" & vbCrLf
          objFile.write "<script type = ""text/javascript"">" & vbCrLf
             'objFile.write "var db = openDatabase('inspdb31', '1.0', 'Test DB', 50 * 1024 * 1024);" & vbCrLf '50mb
             objFile.write "var db = openDatabase('" & strDB & "', '1.0', 'Test DB', 50 * 1024 * 1024);" & vbCrLf '50mb
             
            objFile.write "var querystring = window.location.search.substring(1);" & vbCrLf
            objFile.write "var uid1 =querystring.split('=');" & vbCrLf
            objFile.write "containment_id=uid1[1];" & vbCrLf & vbCrLf
                
             objFile.write "db.transaction(function (tx) {" & vbCrLf
             
                'YOU CANNOT USE THE WORD "GROUP", BECAUSE IT IS A KEYWORD FOR THE SQL LANGUAGE!
                objFile.write "tx.executeSql('CREATE TABLE INSPECTIONS (id INTEGER PRIMARY KEY autoincrement, InventoryID INTEGER,DrainerID INTEGER,ResponsibleDrainer,DateDrained,TimeDrained,Group1)');" & vbCrLf
                
                Do Until rst.EOF
                    objFile.write "tx.executeSql('INSERT INTO INSPECTIONS (InventoryID,DrainerID,ResponsibleDrainer,DateDrained,TimeDrained,Group1) VALUES (""" & rst.Fields("InventoryID") & """,""" & _
                        rst.Fields("DrainerID") & """,""" & _
                        rst.Fields("Responsible Drainer") & """,""" & _
                        rst.Fields("DateDrained") & """,""" & _
                        rst.Fields("TimeDrained") & """,""" & _
                        rst.Fields("Group") & """)');" & vbCrLf
                    rst.MoveNext
                Loop
             
                
             objFile.write "});" & vbCrLf & vbCrLf
            
           objFile.write "displayAll();" & vbCrLf & vbCrLf
           
        
    '=====================================================================================
    'now display the contents of the previous section in an html table
    '=====================================================================================
    
            objFile.write "function displayAll()" & vbCrLf
            objFile.write "{" & vbCrLf
                                
                objFile.write "db.transaction(function(tx)" & vbCrLf
                objFile.write "{" & vbCrLf
                    objFile.write "tx.executeSql('SELECT * FROM INSPECTIONS',[],function (tx,results)" & vbCrLf
                    objFile.write "{" & vbCrLf
                        objFile.write "var n = results.rows.length;" & vbCrLf
                        objFile.write "var s = ""<table cellpadding='2' cellspacing ='2' border='1'>"";" & vbCrLf

                        objFile.write "s += '<tr><th>ID</th><th>InventoryID</th><th>DrainerID</th>';" & vbCrLf
                        objFile.write "s += '<th>ResponsibleDrainer</th><th>DateDrained</th>';" & vbCrLf
                        objFile.write "s += '<th>TimeDrained</th><th>Group</th></tr>';" & vbCrLf
                        
                        objFile.write "for(var i=0; i < n ; i++  )" & vbCrLf
                        objFile.write "{" & vbCrLf
                            objFile.write "var containment=results.rows.item(i);" & vbCrLf

                            objFile.write "s+= '<tr>';" & vbCrLf
                            objFile.write "s+= '<td>'+ containment.InventoryID +'</td>';" & vbCrLf
                            objFile.write "s+= '<td>'+ containment.InventoryID +'</td>';" & vbCrLf
                            objFile.write "s+= '<td>'+ containment.DrainerID +'</td>';" & vbCrLf
                            objFile.write "s+= '<td>'+ containment.ResponsibleDrainer +'</td>';" & vbCrLf
                            objFile.write "s+= '<td>'+ containment.DateDrained +'</td>';" & vbCrLf
                            objFile.write "s+= '<td>'+ containment.TimeDrained +'</td>';" & vbCrLf
                            objFile.write "s+= '<td>'+ containment.Group1 +'</td>';" & vbCrLf
                            
                            objFile.write "s+= ""<td><a href='#' onclick=edit("" + containment.InventoryID + "")>Edit</a> </td>"";" & vbCrLf
                            objFile.write "s+= '</tr>';" & vbCrLf
                        objFile.write "}" & vbCrLf

                        objFile.write "s += '</table>';" & vbCrLf

                        objFile.write "document.querySelector('#result').innerHTML =  s ;" & vbCrLf
                    objFile.write "});" & vbCrLf
                objFile.write "});" & vbCrLf
            objFile.write "}" & vbCrLf & vbCrLf
            
          
    '=====================================================================================
    'now display the contents of the previous section in an html table
    '=====================================================================================
    
            objFile.write "function edit(id)" & vbCrLf
            objFile.write "{" & vbCrLf
                objFile.write "db.transaction(function(tx)" & vbCrLf
                objFile.write "{" & vbCrLf
                    objFile.write "tx.executeSql('SELECT * FROM INSPECTIONS WHERE InventoryID = ?',[id],function (tx,results)" & vbCrLf
                    objFile.write "{" & vbCrLf
                        objFile.write "var containments = results.rows.item(0);" & vbCrLf & vbCrLf
                        
                        objFile.write "document.getElementById('inv_id').value = containments.InventoryID ;" & vbCrLf
                        objFile.write "document.getElementById('inventory_id').value = containments.InventoryID ;" & vbCrLf
                        objFile.write "document.getElementById('drainer_id').value = containments.DrainerID ;" & vbCrLf
                        objFile.write "document.getElementById('resp_drainer').value = containments.ResponsibleDrainer ;" & vbCrLf
                        objFile.write "document.getElementById('date_drained').value = containments.DateDrained ;" & vbCrLf
                        objFile.write "document.getElementById('time_drained').value = containments.TimeDrained ;" & vbCrLf
                        objFile.write "document.getElementById('group1').value = containments.Group1 ;" & vbCrLf & vbCrLf

                        
                        objFile.write "//alert('added new entry');" & vbCrLf
                    objFile.write "});" & vbCrLf
                objFile.write "});" & vbCrLf
            objFile.write "}" & vbCrLf & vbCrLf

            objFile.write "function save()" & vbCrLf
            objFile.write "{" & vbCrLf & vbCrLf

                objFile.write "db.transaction(function(tx)" & vbCrLf
                objFile.write "{" & vbCrLf & vbCrLf
                
                    objFile.write "var id=document.getElementById('inv_id').value;" & vbCrLf
                    objFile.write "var DateDrained=document.getElementById('date_drained').value;" & vbCrLf & vbCrLf
                    objFile.write "var TimeDrained=document.getElementById('time_drained').value;" & vbCrLf & vbCrLf

                    objFile.write "tx.executeSql('UPDATE INSPECTIONS SET DateDrained=?,TimeDrained=? WHERE InventoryID=?',[DateDrained,TimeDrained,id],displayAll());" & vbCrLf & vbCrLf
                    
                objFile.write "});" & vbCrLf & vbCrLf
                
                'add the current date and time
                 
                    objFile.write "var d = new Date();" & vbCrLf
                    objFile.write "var t = new Date();" & vbCrLf
                                       
                    objFile.write "d=d.toLocaleDateString(); // -> '2/1/2013'""" & vbCrLf
                    objFile.write "t=t.toLocaleTimeString(); // -> '7:38:05 AM'""" & vbCrLf
                    
                    objFile.write "document.getElementById('date_drained').value = d;" & vbCrLf
                    objFile.write "document.getElementById('time_drained').value = t;" & vbCrLf
                 
                objFile.write "alert('saved entry');" & vbCrLf
                
            objFile.write "}" & vbCrLf
                
        '=====================================================================================
          objFile.write "</script>" & vbCrLf & vbCrLf

    
    '=====================================================================================
       objFile.write "</head>" & vbCrLf & vbCrLf
      
       objFile.write "<body>" & vbCrLf
          
           objFile.write "<div id='result'></div>" & vbCrLf
                        
           objFile.write " <!--the fieldset element groups related form elements and puts a box around them-->"
            objFile.write "<fieldset>"
                objFile.write "<legend>Add Work</legend>"
                objFile.write "<form>"
                    objFile.write "<table cellpadding='2' cellspacing='2'>"
                        objFile.write "<tr>"
                            objFile.write "<td>Id</td>"
                            objFile.write "<td>&nbsp;</td>"
                        objFile.write "</tr>"
                        objFile.write "<tr>"
                            objFile.write "<td>Inventory ID</td>"
                            objFile.write "<td><input type='text' id='inventory_id'></td>"
                        objFile.write "</tr>"
                        
                        objFile.write "<tr>"
                            objFile.write "<td>Drainer ID</td>"
                            objFile.write "<td><input type='text' id='drainer_id'></td>"
                        objFile.write "</tr>"
                        
                        objFile.write "<tr>"
                            objFile.write "<td>Responsible Drainer</td>"
                            objFile.write "<td><input type='text' id='resp_drainer'></td>"
                        objFile.write "</tr>"
                        
                        objFile.write "<tr>"
                            objFile.write "<td>Date Drained</td>"
                            objFile.write "<td><input type='text' id='date_drained'></td>"
                        objFile.write "</tr>"
                        
                        objFile.write "<tr>"
                            objFile.write "<td>Time Drained</td>"
                            objFile.write "<td><input type='text' id='time_drained'></td>"
                        objFile.write "</tr>"
                                                                                               
                        objFile.write "<tr>"
                            objFile.write "<td>Group</td>"
                             objFile.write "<td><input type='text' id='group1'></td>"
                        objFile.write "</tr>"
                        
                        objFile.write "<tr>"
                            objFile.write "<td>&nbsp;</td>"
                            objFile.write "<td>"
                                objFile.write "<input type='button' value='Save' onclick='save()'>"
                                objFile.write "<input type='hidden' name='inv_id'  id='inv_id' value=''>"
                            objFile.write "</td>"
                        objFile.write "</tr>"
                    objFile.write "</table>"
                objFile.write "</form>"
            objFile.write "</fieldset>"
          
          
          
       objFile.write "</body>" & vbCrLf
    objFile.write "</html>"

    
    
     MsgBox "Complete"
 
    
End Sub

 

Here is what the end result looks like so far

client_side_storage_websql

Now I need to enhance the userform and add the url params, stay tuned, and let me know if you have any questions.


Comments are closed.