Visual Basic

Option Explicit

Public strword, intplace, idlast
Dim temp As Long
Dim currID As String

Private Sub cmdAdd_Click()

    Data1.Recordset.AddNew

    Dim temp As Long

    date.Text = DateValue(Now)

    Dim m

    m = Month(Now)
    monthnam.Text = MonthName(m, False)
    weekd.Text = WeekdayName(Weekday(Now))
    time.Text = TimeValue(Now)
    starttime = Now
    orig.Text = ""
    Text1.Text = ""
    copname.Text = ""
    contactno.Text = ""
    contemail.Text = ""
    qrytype.Text = ""
    prodcode.Text = ""
    mel.Text = ""
    vendcode.Text = ""
    batchlot.Text = ""
    saleval.Text = ""
    qry.Text = ""
    answer.Text = ""
    sourcecontact.Text = ""
    status.Text = ""
    cmdAdd.Visible = False
    cmdUpdate.Enabled = True
    
End Sub

Private Sub cmdRefresh_Click()

End Sub

Private Sub cmdUpdate_Click()
       cmdUpdate.Enabled = False
    cmdAdd.Visible = True
    cmdAdd.Enabled = True

    If status.Text = "Closed" Then

        endtime = Now

    End If

    Data1.Recordset.Update
  
Dim conn As New ADODB.Connection
Dim mydb As Database
Dim qd As QueryDef
     
    Dim CONNECT_STRING As String

    CONNECT_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=\\10.192.16.21\global\uktech\db\uktech.mdb"
                
    Dim RS As Recordset

    Set RS = New Recordset
        
    'Generate a SQL
    Dim strSQL

    strSQL = "INSERT INTO main ([ID],[Date], [Month], Weekday, [Time], Originator, [First & Last Name], [Company name], [Contact No], [Contact Email], [Type of query], [Product code], MEL, [Vendor code], [Batch/Lot/Serial], [Sale value (GBP)], QUERY, ANSWER, STATUS, REPID, ContactSource, starttime, endtime, totaltime, Methcontact )"
    strSQL = strSQL & "SELECT [ID],[Date], [Month], [Weekday], [Time], [Originator], [First & Last Name], [Company name], [Contact No], [Contact Email], [Type of query], [Product code], [MEL], [Vendor code], [Batch/Lot/Serial], [Sale value (GBP)], [QUERY], [ANSWER], [STATUS], [REPID], [ContactSource], [starttime], [endtime], [totaltime], [Methcontact]"
    strSQL = strSQL & "FROM tempmain;"

    With RS
        .Open strSQL, CONNECT_STRING
                
    End With
    Set RS = NothingCall idReturn
    Call idReturn
    Call deleteTemptable

   
    
End Sub

Private Sub Command1_Click()

    Dim strSQL1 As String

    Dim key     As String

    key = Text2.Text

    'This specifies the SQL to be executed
    strSQL1 = "SELECT DISTINCT * "
    strSQL1 = strSQL1 & "FROM [qa]where QUERY LIKE '%" & key & "%'"
    'This specifies that the type of the ADODC is for SQL
    Adodc1.CommandType = adCmdText
    'This assigns the SQL result to the ADODC
    Adodc1.RecordSource = strSQL1
    Adodc1.Refresh

    'Adodc1.Recordset.Requery
    'The result from the SQL will then be used
    'to populate the datagrid
    Set DataGrid1.DataSource = Adodc1
    DataGrid1.Columns(0).Width = 7300
    DataGrid1.Columns(1).Width = 7200
    DataGrid1.AllowUpdate = False

End Sub

Private Sub Command2_Click()

    Adodc1.Recordset.AddNew
    Adodc1.Recordset(0) = qry.Text
    Adodc1.Recordset(1) = answer.Text
    Adodc1.Recordset.Update

End Sub

Private Sub Command3_Click()
    
    Form3.Show
    Unload Me
End Sub

Private Sub Form_Click()
Call idReturn
End Sub

Private Sub Form_Load()
Call idReturn
    cmdAdd_Click
    
    
    cmdAdd.Enabled = False
   
    DataGrid1.AllowUpdate = False

    DataGrid1.Columns(0).Width = 7300
    DataGrid1.Columns(0).Caption = "QUERY"

    DataGrid1.Columns(1).Width = 7300
    DataGrid1.Columns(1).Caption = "ANSWER"

End Sub

Private Sub Form_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Call idReturn

End Sub

Private Sub Option1_Click()

    methcontact.Text = "Call"

End Sub

Private Sub Option2_Click()

    methcontact.Text = "Email"

End Sub

Private Sub orig_Click()

    Combo1.Visible = True
    orig.Visible = False

End Sub

Private Sub combo1_Click()

    orig.Text = Combo1.Text
    Combo1.Visible = False
    orig.Visible = True

End Sub

Private Sub qry_GotFocus()

    strword = ""
    intplace = 0

End Sub

Private Sub qrytype_Click()

    Combo2.Visible = True
    qrytype.Visible = False

End Sub

Private Sub Combo2_click()

    qrytype.Text = Combo2.Text
    Combo2.Visible = False
    qrytype.Visible = True

End Sub

Private Sub sourcecontact_Click()

    Combo4.Visible = True
    sourcecontact.Visible = False

End Sub

Private Sub status_Click()

    Combo3.Visible = True
    status.Visible = False

    If status.Text = "Closed" Then

        endtime = Now

    End If

End Sub

Private Sub combo3_Click()

    status.Text = Combo3.Text
    Combo3.Visible = False
    status.Visible = True

End Sub

Private Sub combo4_Click()

    sourcecontact.Text = Combo4.Text

    If Combo4.Text = "Others" Then
        sourcecontact.Text = ""
        MsgBox "Please Specify The Customer's Source Of Contact Information in this Text Box", , "Contact Source Details"
        sourcecontact.SetFocus
    End If

    Combo4.Visible = False
    sourcecontact.Visible = True

End Sub

Public Sub idReturn()
    
    Dim CONNECT_STRING As String

    CONNECT_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=\\10.192.16.21\global\uktech\db\uktech.mdb"
                
    Dim RS As Recordset

    Set RS = New Recordset
        
    'Generate a SQL
    Dim strSQL As String

    strSQL = "select count(*) from main "

    With RS
        .Open strSQL, CONNECT_STRING
            
    End With

    temp = RS(0)
    id.Text = temp + 1
    RS.Requery
    RS.Close
    Set RS = Nothing
      
End Sub



Private Sub deleteTemptable()
 
    Dim conn As New ADODB.Connection
Dim mydb As Database
Dim qd As QueryDef
        
      With conn 'this reliably opens a connection
         .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=\\10.192.16.21\global\uktech\db\uktech.mdb"
          .Open
    End With
        
      Set mydb = OpenDatabase("\\10.192.16.21\global\uktech\db\uktech.mdb")
        
    Set qd = mydb.QueryDefs("deletetemp")
        Call qd.Execute

End Sub