Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Jul 2002
    Location
    UK
    Posts
    302
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Urgent..."Please HELP with updating a access database

    Hi all. Can anyone help?

    I'm working on a nice little HTA project. It reads and writes and deletes data to an access db. However, one the felids i have is "Sent" field, I use to tell me when an appt has been sent out and it only shows the outstanding appts on the display page. I use a the yes/no Data Type in access.

    What I can't work out is the code for updating the db to send the appt off. I.e you would click a hyperlink and it would update the db to send it off.

    Here is my code, if someone can help.

    Code:
    <script language="vbscript">
    
    Dim conn 'GLOBAL doing this here so that all functions can use it
    
    sub dotheconnection
    
    	Set conn = CreateObject("ADODB.Connection")
    
    
    	conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=niv.mdb; User Id=; Password="
    
    	If conn.errors.count <> 0 Then 
    	
    		alert("problem connecting to the database")
    	else
    		' if connected OK call sub getdata
    		getdata
    
    	end if
    end sub
    
    
    sub getdata
    
    	SQL_query = "SELECT * FROM NIV WHERE Sent = False ORDER BY Date_of_Appt"
    	Set rsData = conn.Execute(SQL_query)
    
    strHTML = strHTML & "<table cellspacing='0' cellpadding='4' border='1'><tr><td bgcolor='#000000'>&nbsp;</td><td bgcolor='#000000'><font color='#FFFFFF'><b>Title</b></font></td><td bgcolor='#000000'><font color='#FFFFFF'><b>FName</b></font></td><td bgcolor='#000000'><font color='#FFFFFF'><b>LName</b></font></td><td bgcolor='#000000'><font color='#FFFFFF'><b><nobr>Date of Appt</b></font></td><td bgcolor='#000000'><font color='#FFFFFF'><b><nobr>Time of Appt</b></font></td><td bgcolor='#000000'><font color='#FFFFFF'><b>Location</b></font></td><td bgcolor='#000000'><font color='#FFFFFF'><b>Email</b></font></td><td bgcolor='#000000'><font color='#FFFFFF'><b>Notes</b></font></td><td bgcolor='#000000'>&nbsp;</td></tr>"
    //strHTML = strlHTML &"" & rsData("Fname") & ""
    Do Until rsData.EOF = True
    strHTML = strHTML & "<tr><td onclick='deleteUser("& rsData("ID") &")' language='vbscript' style='cursor:hand; color:red'>del</td><td>" & rsData("Title") & "</td> <td>" & rsData("Fname") & " </td> <td>" & rsData("Lname") & " </td> <td>" & rsData("Date_of_Appt") & " </td><td>" & rsData("Time_of_Appt") & " </td><td>" & rsData("Location") & " </td><td><a href=mailto:" & rsData("Email") & "?subject=" & rsData("Fname") &"&nbsp;"& rsData("Lname") & "> "& rsData("Email") & "</a></td><td>" & rsData("Notes") & " </td><td onclick='editUser("& rsData("ID") &")' language='vbscript' style='cursor:hand; color:red'>Edit</td><td onclick='sendAppt("& rsData("ID") &")' language='vbscript' style='cursor:hand; color:red'>Send</td></tr>"
    		rsData.moveNext ' go to next record
    	Loop
    
    	strHTML = strHTML & "</table>"
    
    	thediv.innerHTML = strHTML
    
    end sub
    
    
    sub deleteUser(id)
    
    	SQL_query = "DELETE * FROM NIV WHERE ID = " & id
    	conn.Execute(SQL_query)
    	getdata
    
    end sub
    
    
    sub addUser
    
    	SQL_query = "INSERT INTO NIV (Title,Fname,Lname,Date_of_Appt,Time_of_Appt,Location,Email,Notes) VALUES ('"& txtTitle.value &"','"& txtFname.value &"','"& txtLname.value &"','"& txtDate.value &"','"& txtTime.value &"','"& txtLocation.value &"','"& txtEmail.value &"','"& txtNotes.value &"')"
    	conn.Execute(SQL_query)
    	getdata
    
    end sub
    
    sub editUser(id)
    
    	SQL_query = "SELECT * FROM NIV WHERE ID=" & id
    	Set rsData=conn.Execute(SQL_query)
    	txtTitle.value = rsData("Title")
    	txtFname.value = rsData("Fname")
    	txtLname.value = rsData("Lname")
    	txtDate.value = rsData("Date_of_Appt")
    	txtTime.value = rsData("Time_of_Appt")
    	txtLocation.value = rsData("Location")
    	txtEmail.value = rsData("Email")
    	txtNotes.value = rsData("Notes")
    	txtID.value = rsData("ID")
        btnUpdate.disabled = false
    	getdata
    
    end sub
    
    sub updateUser
    
    	SQL_query = "UPDATE NIV SET Title='"& txtTitle.value &"', Fname='"& txtFname.value &"', Lname='"& txtLname.value &"', Date_of_Appt='"& txtDate.value &"'WHERE ID= " & txtID.value 
    	conn.Execute(SQL_query)
    	getdata
    	
    end sub
    
    
    
    sub sendAppt
    
    SQL_query = "UPDATE * FROM NIV WHERE ID = " & id
    
    if strAction = "sent" then
        rsData.Fields("Sent") = True
        //rsData.Fields("Date_Sent") = Now
        rsData.Update
    end if
    	// SQL_query = "UPDATE NIV SET Send='"& txtTitle.value &"'WHERE ID=" & id
    	// conn.Execute(SQL_query)
    	// getdata
    	
    end sub
    Kind regards,
    Mike Hughes

  • #2
    Regular Coder
    Join Date
    Jul 2002
    Location
    UK
    Posts
    302
    Thanks
    16
    Thanked 0 Times in 0 Posts
    I have been playing about with this since my last post.

    I'm I way off with this?

    Code:
    sub sendAppt(id)
    
    SQL_query = "UPDATE NIV SET Sent="" FROM NIV WHERE ID = " & id
    conn.Execute(SQL_query)
    getdata
    	
    end sub
    Kind regards,
    Mike Hughes

  • #3
    Regular Coder
    Join Date
    Jul 2002
    Location
    UK
    Posts
    302
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Got my answer.

    Code:
    sub sendAppt(id)
    	SQL_query = "UPDATE NIV SET Sent=True WHERE ID = " & id
    	conn.Execute(SQL_query)
    	getdata
    Kind regards,
    Mike Hughes

  • #4
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    not gunna let ne1 else help then?

  • #5
    Regular Coder
    Join Date
    Jul 2002
    Location
    UK
    Posts
    302
    Thanks
    16
    Thanked 0 Times in 0 Posts
    the pressure was well on, I had to come up with solution fast. It's amazing what you can do when the stress factor increases.
    Kind regards,
    Mike Hughes


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •