View Full Version : Urgent..."Please HELP with updating a access database

01-11-2005, 03:26 PM
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.

<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")
' if connected OK call sub 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

strHTML = strHTML & "</table>"

thediv.innerHTML = strHTML

end sub

sub deleteUser(id)

SQL_query = "DELETE * FROM NIV WHERE ID = " & id

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 &"')"

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

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

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
end if
// SQL_query = "UPDATE NIV SET Send='"& txtTitle.value &"'WHERE ID=" & id
// conn.Execute(SQL_query)
// getdata

end sub

01-11-2005, 04:01 PM
I have been playing about with this since my last post.

I'm I way off with this?

sub sendAppt(id)

SQL_query = "UPDATE NIV SET Sent="" FROM NIV WHERE ID = " & id

end sub

01-11-2005, 04:31 PM
Got my answer.

sub sendAppt(id)
SQL_query = "UPDATE NIV SET Sent=True WHERE ID = " & id

01-18-2005, 01:26 PM
not gunna let ne1 else help then? :(

01-18-2005, 06:47 PM
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.