PDA

Click to See Complete Forum and Search --> : Adding values in Access

jjr0319
11-08-2005, 02:34 PM
i created a web page that users can go into and add workcounts to an access database based upon the type of work they did.
here's an example:
<tr>
<td ALIGN=LEFT>
<SELECT name="WORKTYPE">

<OPTION selected value="">Please select

<OPTION value="Catalogging">Catalogging

<OPTION value="Data Entry">Data Entry
</SELECT>
</td>
</tr>

<tr>
<td ALIGN=RIGHT VALIGN=TOP class="text">Workcount:</td>

<td ALIGN=LEFT><input name="WORKCOUNT" size=10 maxlength="10"></td>
</tr>

when the user selects a WORKTYPE, and enters a WORKCOUNT, the database gets updated with their input. If catalogging or any other WORKTYPE is chosen twice, however, instead of adding the value of WORKCOUNT to the existing catalogging record in the database, it adds a new record completely. I know I would have to use the UPDATE statement in SQL, but I'm not sure how to set it up so that it first checks the database to see if any other records contain "catalogging" or the selected WORKTYPE.

here's my ASP code so far:
<html>
<head>
<title>IDOC Workcount</title>
</head>

<body class="tablewhite">

<%putWT=request.form("WORKTYPE")
putWC=request.form("WORKCOUNT")



set conn=Server.CreateObject("ADODB.Connection")
conn.ConnectionString="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=d:\Inetpub\wwwroot\subs\IDOC\IDOC.mdb;"
conn.Open
set rs=Server.CreateObject("ADODB.Recordset")


sql="INSERT INTO [WORK_COUNT] ([WORKTYPE], [WORKCOUNT]) VALUES ('" & putWT & "', '" & putWC & "')"

conn.execute sql
%>

<h2>Your workcount has been added to the IDOC database</h2>

<P>Click on your browser's <I>Back</I> button to return to the workcount input page or . . .
</body>
</html>

anyone know how to set this up? thanks in advance.
buntine
11-08-2005, 08:06 PM
Just attempt to select the record. If it exists, then update the work count field. Otherwise, insert the record.

'Execute the following SQL query up here:
"SELECT WorkType FROM YourTableName WHERE WorkType = '" & putWT & "';"

...

If (rsWorkType.EOF) Then
'| Record does not exist, create it.
Else
'| Record exists, update it.
End If

...

Regards.
jjr0319
11-09-2005, 07:48 AM
thanks for the reply. this is what i tried, but still can't get it to work:


sql="SELECT WORKTYPE FROM WORK_COUNT WHERE WORKTYPE = '" & putWT & "';"

If (rsWORKTYPE.EOF) Then

INSERT INTO [WORK_COUNT] ([WORKTYPE], [WORKCOUNT]) VALUES ('" & putWT & "', '" & putWC & "')

Else

UPDATE WORK_COUNT SET WORKCOUNT = (rsWORKCOUNT.EOF) + ('" & putWC & "') WHERE WORKTYPE = ('"& putWT & "')

i think my syntax for adding the current workcount number in the database to the new workcount inputted by the user is incorrect. how do i write the update statement for this?

thanks again.