Updating Access Query HELP!

Mar 27, 2007

Hello, I'm stuck on what seems to be a somewhat simple problem. I have an access db and have an admin area to allow for edits/deletions for the faq's entries. I was able to add in queries to sort the massive amount of data (3000 entries), but when I try to edit or delete one of them, I get the error message that the Database or object is read-only. I am somewhat new to coding, and if anyone could look over the below code and make any recommendations, it would be greatly appreciated.

FAQ_EDITDELETE.ASP CODE
<!--#include file="../globalvariables/backglobalvariablez.asp"-->
<%
Dim ID, responsewrite
ID = Request.Querystring("ID")
If ID <> "" Then
Set objCon = Server.CreateObject("ADODB.Connection")
objCon.Open connectionstring
Set objRS = Server.CreateObject("ADODB.Recordset")
strQuery = "Delete from Dilemmas where ID = " & ID
objRS.Open strQuery, objCon
responsewrite="<font color = green>FAQ Deleted Successfully</font>"
Set objRS = Nothing
Set objCon = Nothing
end if
%>

<font class="headertext">FAQ Edit / Delete</font><br><br><br>
<center><%=responsewrite%><P></center>

<%
Set objCon = Server.CreateObject("ADODB.Connection")
objCon.Open connectionstring
Set objRS = Server.CreateObject("ADODB.Recordset")
strQuery = "SELECT * from Dilemmas order by subject asc"
objRS.Open strQuery, objCon
IF objRS.EOF Then
Response.Write("<tr><td colspan = 3>No Faqs</td></tr>")
Else
Do While not objRS.EOF
x = x + 1
%>

<table border = 0 bgcolor = DDDDDD cellpadding = 7 cellspacing = 0 width = 530>
<tr bgcolor = CCCCCC>
<td align = left><b><%=objRS("subject")%></b></td>
<td align = right><input class = "btn" type = button onclick = "top.location.href='faq_edit.asp?id=<%=objRS("ID")%>'" value = "Edit" class = "btn"> <input class = "btn" type = button onclick = "fndelete('<%=objRS("ID")%>');" value = "Delete" class = "btn"></td>
</tr>
<tr>
<td colspan = 2><b>Question:</b><br><%=replace(objRS("question"),vbcrlf,"<BR>")%></td>
</tr>
<tr>
<td colspan = 2><b>Answer:</b><br><%=replace(objRS("answer"),vbcrlf,"<BR>")%></td>
</tr>
</table>
<br>
<%
objRS.MoveNext
Loop
End If
objRS.Close
Set objRS = Nothing
Set objCon = Nothing
%>


<!--#include file="../globalvariables/backglobalvariablez2.asp"-->

FAQ_EDIT.ASP CODE
<!--#include file="../globalvariables/backglobalvariablez.asp"-->

<%
Dim ID
ID = Request.Form("ID")

If ID <> "" Then

Set objCon = Server.CreateObject("ADODB.Connection")
objCon.Open connectionstring
Set objRSAddComments = Server.CreateObject("ADODB.Recordset")
strQuery = "SELECT * FROM Dilemmas where ID = " & ID & " "
objRSAddComments.CursorType = 2
objRSAddComments.LockType = 3
objRSAddComments.Open strQuery, objCon
objRSAddComments.Fields("subject")=strformat(request.form("subject"))
objRSAddComments.Fields("question")=strformat(request.form("question"))
objRSAddComments.Fields("answer")=strformat(request.form("answer"))
objRSAddComments.Update
objRSAddComments.Close
response.redirect("faq_editdelete.asp")
end if

ID = Request.Querystring("ID")
Set objCon = Server.CreateObject("ADODB.Connection")
objCon.Open connectionstring
Set objRS = Server.CreateObject("ADODB.Recordset")
strQuery = "SELECT * from Dilemmas where ID = " & ID
objRS.Open strQuery, objCon
IF objRS.EOF Then
Else
dim subject, question, answer
subject = objRS("subject")
question = objRS("question")
answer = objRS("answer")
End If
objRS.Close
Set objRS = Nothing
Set objCon = Nothing
%>



<font class="headertext">Edit an FAQ</font><br><br><br>
<table cellpadding = 10 width = 450 bgcolor = DDDDDD>
<tr valign = top>
<td>
<form name = workform action = "faq_edit.asp" method = "post">

<b>Subject:</b><br>
<input type = text value = "<%=subject%>" name = "subject" size =83 >
</td>
</tr>
<tr>
<td><b>Question:</b><br>
<textarea name = "question" cols = 60 rows = 7><%=question%></textarea>
</td>
</tr>
<tr>
<td><b>Answer:</b><br>
<textarea name = "answer" cols = 60 rows = 7><%=answer%></textarea>
</td>
</tr>
<tr>
<td align = center>
<input type = hidden value = "<%=Request.Querystring("ID")%>" name = "ID">
<input class = "btn" type = button value = "Update" onclick = "if(document.workform.subject.value=='' || document.workform.question.value=='' || document.workform.answer.value==''){alert('Please fill out all fields')}else{document.workform.submit();}">
<input class = "btn" type = reset value = "Clear Form">
</td>
</tr>
</table>
</center>
<!--#include file="../globalvariables/backglobalvariablez2.asp"-->

Thank You!

View Replies


ADVERTISEMENT

Updating Date In A Query & Data Access Page

Feb 1, 2007

I am trying to do an auto time stamp in a data access page without having to go to new record. What I am doing is farming out parts of a record to different departments, and I want to time stamp each time each department performs their query, but I want to be able to display the data for the department, and store the timestamp in the original table.

Everything is stored under the table [Appointments] and different departments enter certain things. For instance, Reception gives the customer a ticket # which they enter into the database based on the query for the customer appointment. On the data-access page, I want them to be able to see their check in time. I am able to do this with an expression, but I can't store it in the field. Here's what I have:

Expr1:Now() (this is in the query)

In the data access page I display Expr1 with no problem. How do I get the field [Reception time stamp] to equal Expr1? When I try to do it in the query, it treats it as another expression and doesn't store it in the field (Reception time stamp = Expr1)

Is there a way to assign a field the value of an expression in queries or data access pages?

In other words, for the field I have named "Reception Time Stamp" is their anyway to plug in the Now() value when I do my query? Not search for Now() but have it show Now() when I use the query in a data access page without having to create new record. I know how to do it when you time stamp it with new record, but how can I do it with a query that pulls up an existing record that more info is being updated on it.

Table Demographics:
Account Number (Primary)
Name
Address
SSN
DOB

Table Appointments:
Appointment Number (Primary Autonumber)
Appointment Date
Appointment Time
Account Number (Relationship tied to Demographics)
Reception Ticket #
Reception Timestamp
Cashier Amount Collected
Cashier Timestamp

Relationship is One to Many (One Demographic to Many Appointments)

Everybody will be entering data on a data access page using internet explorer, which makes this more tricky, as it would be easy to do using forms. So here's how it works. Appointments enters customer information, appt date, appt time, and account number.

Reception pulls up by date and name(queries) the appointment, and then enters a ticket number (Like taking a number to be waited upon) After they enter this and write this ticket number, I want an auto-time stamp to be saved under the appointment table for reception time stamp.

Then, when their number gets called, the cashier pulls up the patient by ticket number(queries) and collects money from the customer. After they write the amount they collected, I need an auto-time stamp.

The reason I need to auto time stamp is so I can calculate time difference from the customer walking in the door and going out the door. The main reason this has proven difficult is because I am doing all data entry on data access pages, and everybody will be using internet explorer. Another reason is because almost all info is being saved under appointments, and each department only enters a couple of things towards completing the appointment record.

View 5 Replies View Related

Query: Access Macros, Autodeleting Rows And Updating Table Names

Oct 11, 2007

I'm a bit in over my head. Unfamiliar with Access macros, I need to write a bunch of them for work, and soon. Unixen I can deal with, largely undocumented convoluted Access macros are something else....

My current problem is: I have a table. The first column has a value in it for almost every row. However, there are six other columns after it. I need to write a macro to automatically delete all of the rows that don't have data in the last six columns. Microsoft Help is, as always, of zero use. The FindRecord feature allows me to use expressions to search, but of course, the help fails to tell me what syntax Access uses.

Oh, and it asks me to select a table from the drop-down list. Can I use wildcards here? Is there a way to get it to automatically open the newest table, or will we have to change the macro accordingly each month?

Most tutorials/guides I'm Googling rather brilliantly repeat the same things the help does, AKA, are useless. (Why do they bother writing them if you're not giving new information...?)

I'd ask about the other various access questions I have, but I can pick them up as I go, this is the most pressing question.

View 2 Replies View Related

Updating Objects In Another Access DB

Sep 25, 2004

I am doing bug and enhancement development for an existing Access Application.

Periodically (after testing) I need to take my changed objects and export them to the production database.

My problems are:
1. Is there any easy way to tell which objects have been changed (is there any flag I can set at the beginning of a development cycle and then check to see which objects have been changed)

2. When exporting these objects they don't replace the existing objects in the Production Database they add a new object with the number "1" appended. If there was a form called "fCustomer Input" I end up with a new form called "fCustomer Input1"

This problem must come up with other development efforts.

Help

Thanks

View 2 Replies View Related

Trouble Updating Access Db Through FP

Jan 13, 2005

I am trying to setup a webpage for people to update
access 2000 db using FP2000 and DRW. I do not want
update all fields in a record though - a couple of them
are static. I have been unable to do this. I am using
code lifted from another page and DB that does
exactly the same thing for the update. I either get the
error data type mismatch, or I should provide default
values for all form related fields, or no records get
updated.
Is there anyone who can point me inthe right direction?
Thanks,
Tim

View 2 Replies View Related

Updating A Table In Access Using VB

Jul 6, 2005

I have a form that updates a table. It's got some convaluted logic (I didn't write it I swear ). I am trying to add some VB code so that when the form closes, the table (TableA) opens, all records are selected, and any commas are replaced by periods. The table should then be saved and close.

I know how to open, select records, save, and close, but I can figure out how to get it to do the find and replace. Any takers?

Thanks,

B

View 7 Replies View Related

Updating Access Tables

Apr 16, 2006

Is it possible to update Access tables through a macro of some sort.

Thing is:
I would like to collect a membership list in a table.
I will add new members on my database but i would like to update another database to have the same data as me. Especially for this particular table.

I will have a look around (i never used access before) but if there is a tip on where to go and find such a feature would be much appreciated.

Thankyou

View 1 Replies View Related

Updating MS Access On Web Server

Dec 20, 2007

Hi, Using MS Access on a Server on the web with main programming in .asp
I have done all the hard work to have a userid logon with password and stop anyone getting in. I have now set up a change password facility and that again is working great on checking userid, old password, new and repeat new password.
I cannot get the update part working to update the new password. It is in the way I have specified the "call adorecordset.open" as that is the line I get the error as incompatible parameters.

Basic Code is :


set adoconnection = server.createobject("adodb.connection")
set adorecordset = server.createobject("adodb.recordset")
connectionstring = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=" & server.mappath(databaseid) & "" & databaseid & "user.mdb"

call adoconnection.open(connectionstring)
adorecordset.cursorlocation = aduseclient

sqlstring = "SELECT * from [tblOwnerLog] WHERE [UserId] = '"&username&"' AND [Password] = '"&oldpw&"';"

call adorecordset.open(sqlstring, adoconnection,adopenkeyset,adlockoptimistic)

I have tried many ways, by altering my SQLString etc, but seem to get all tied up.

I want to update adorecordset("password") with the new password.

Any help would be much appreciated.

Thanks

Jim

View 4 Replies View Related

Updating Existing MS Access Solution

Mar 7, 2006

I have searched the forum but have failed to find the answer to my problem. I have a front end ms access 2000 solution that I distribute to user PC's with an MDE back end data base on a server.
I now need to release a new version that includes changes to forms, queries and tables.
However there is data in the original mde data base I need to retain. Is there an easy method to migrate that data to the new data base. I have changed some relationships but this should not affect data integrity - most change is related to adding new fieldsto existing tables or new tables (no previous data).
If I create a new empty mde will I be able to import old data into it from previous mde?:confused:

View 2 Replies View Related

Advice On Updating Someone Else's Access Database!

Mar 15, 2007

Hi, im currently working on a database which is for someone else. Whe it is handed over to them, they will no doubt want some changes done every so oftern eg new reports, changes to forms etc.

What is the best way to do this? The database holds a lot of data, so I dont think its feasible for them to send it to me via email everytime they need something changed!

Ive noticed a few posts here about splitting the database into a front/back end. If this was done, would they just have to send me the front end; this would probably be a smaller file ye?

If this is going to be the best way round the problem; how easy is it to split a database which is basically already made?

Thank!!!

View 3 Replies View Related

Advice On Updating Someone Else's Access Database!

Mar 15, 2007

Hi, im currently working on a database which is for someone else. Whe it is handed over to them, they will no doubt want some changes done every so oftern eg new reports, changes to forms etc.

What is the best way to do this? The database holds a lot of data, so I dont think its feasible for them to send it to me via email everytime they need something changed!

Ive noticed a few posts here about splitting the database into a front/back end. If this was done, would they just have to send me the front end; this would probably be a smaller file ye?

If this is going to be the best way round the problem; how easy is it to split a database which is basically already made?

Thank!!!

View 3 Replies View Related

Access File Became Very Large During Updating

Mar 16, 2007

I am writing a vba procedure to updating some records in another Access database.

rsAccess.Open "SELECT * FROM AI_Table",conAccess, adOpenForwardOnly, adLockPessimistic

rsAccess!OCRExist = "Exist"
rsAccess.Update

it has about 3 millions of records in that AI_Table. In the procedure, I perform some calculation and put the result into a TEXT(50) field in the AI_TABLE. As it was updating the records, I could see the size of the Access database file (the one contained AI_Table) grew very quickly, almost 1 MB/sec. I am pretty sure I am not adding that much data. If I stop the procedure and packed the database, it shrunk a lot.

I am just wondering if there is anything wrong with the way I am locking or updating the records.

Thanks,
pggsB

View 2 Replies View Related

Updating Access Tables From Excel

Mar 26, 2008

Hi, can someone please help with this?

I have a table for storing details of share prices relating to specific certificate numbers, so only the £ value and the value date changes when we update (done manually at present).

The updates for different companies are done at different times, hence I cannot just delete and import new data, it needs to be an update to a value from an excel sheet (the excell sheet is downloaded from the web provider in question).

I had thought of using "get external data" to create a new or ad to a new table, then an update query to update the main table from the new one, but again cannot seem to get it to work on the specific certificate numbers.

As you can see I have little knowledge on code etc, and have so far only used macros to automate the application we use, can anyone please help???

Thanks in advance!

Steve

View 2 Replies View Related

Noob In Access Needs Help With Date Updating

Aug 4, 2005

For my own database which i created in Access i have something called a mutation date. so when i changed something i typed the date of that day.
Now i want that to go automaticly. How do i do that???
It would be great if someone could help me with that. Once there's something changed in the record i need to make the date of that record changed.
Hope u can Help me
SilverBlood

What doesn't kill u makes u stronger

View 6 Replies View Related

Updating Yes/No Fields In MS Access Database

Feb 9, 2005

Hi there,

I have a number of Yes/No fields in my database (their default value is False), and I am looking to update these via one of my webpages via INSERT INTO... statement.

What I have is a checkbox on the page, with checked value as true, and then before the values are written to the database, for the unchecked boxes, it converts all empty check box values (ie the ones which have been left unticked) to false, so the INSERT INTO statement will either write true or false for each checkbox, like this... true,true,false,true

The thing is, on executing the code, it add all the other details fine, and hits no errors, but NONE of the checkboxes get updated... these ALL remain unticked in the database, so ignoring any true values which are written to it. Please can someone help, I would be sooo thankful ).

View 1 Replies View Related

Updating Access Database Table

Feb 21, 2005

Hi

Beginner at ASP VBscript. Hi I'm trying to update a Access database through a form using ASP VBScript. Kind of lost. I've looked at some sites and they all have complex ways of doing it. CAn anyone help with a simple little example. say a table with firstname and second name.


Any help would be great!

View 1 Replies View Related

Forms :: Update Form / Query Without Updating Underlying Tables To Query

Jul 23, 2015

I have a form which will be used as the basis to print a label.

It is bound to a query and when I open the form I pass over a 'where' condition to return 1 record. I then use the query to produce a report/label.

What I want to do is to update the form/query without updating the underlying tables to the query.

View 14 Replies View Related

Forms :: Excel Graph In Access Not Updating

May 7, 2014

I'm trying to have a linked Excel chart in Access form. What I've done so far is create a chart in Excel and Paste Special>>Linked into Access.

I also have code inside Excel that will update chart data, it works fine.

Then I have code in Access that calls the code in Excel to update the data.

The data gets updated fine and the chart in Excel gets updated but the chart in Access only gets updated if I close and open the form again.

Here is the code that will update the Excel Data

Public Sub Import_VRSS_Graph_Data(strDayType As String, strTimeBand As String, strEntrance As String, Ws As Worksheet)
' Create a connection object.
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection
' Provide the connection string.
Dim strConn As String

[Code] ....

View 2 Replies View Related

Access 2010 / Linked Tables Not Updating?

Jan 4, 2012

I have an access 2010 Database connected to a MySQL database through a ODBC 5.1 connector.

when I run the linked table manager, I get the message "Linked tables updated successfully" but the tables residing on the server do not update.

View 1 Replies View Related

Updating Information Via Email In Access Database

Sep 14, 2012

I would like to know if there is a way to update information in my Access database via e-mail.

I tried to do this, but when I collect from the e-mail, it creates a new entry in the database.

View 1 Replies View Related

Live Updating Access Records From Websites

Nov 8, 2011

I've set up a product database in Access for my small business. One of its functions is to track the prices of competing products online.I'm trying to get Access to automatically update these prices en-mass, yanking the data off the websites in question without me having to manually enter hundreds of prices each week.

I have a unique record for each product instance as it appears online. For example, there are three unique records for a 4oz hand sanitizer I sell (one for each of my competitors that sells it). Each record has the price, quantity, and unique URL on which the product appears.I'm not great with HTML and don't really have any programming expertise aside from some pretty simple VBASIC.

View 1 Replies View Related

Updating Fields In Access Table Using Data From Excel

Dec 6, 2005

Hello,

Been wondering how I can update fields in my Access database table using data that lies in an excel spreadsheet.
They have a common row ie say account number and other common fields that need to be updated.

thanks

View 1 Replies View Related

Forms :: Updating Fields Via Code Not Updating Table

Dec 16, 2014

I have a form that has combo boxes and text fields (as well as sub forms). There is also a button linked to some code that says'

Private Sub cmdQuote_Click()
'Creates quote date and prints quote
Me.QuoteDate = Now()
Me.cbAgentID.Requery
DoCmd.OpenReport "Quote", acViewPreview, , "BookingID = " & Me.BookingID
End Sub

When the button is pressed the QuoteDate field (it is bound) should be be populated, but unfortunately it is not. I have played with refresh and requery but cannot derive a solution.

View 1 Replies View Related

Problem While Updating Table Data Using Forms In MS Access 2003

Feb 1, 2005

hi

i am getting stuck while updating the data in the database table using a command button in the MS Access2003 forms. when i click the command button in the form, a message "Run time 2185: you cant refer to a property or method for a control unless the control has a focus". the code is as follows.

rivate Sub Command10_Click()
Dim query As String

query = "select RESOURCEINFO from tbl_control where CONTROLNAME='" + Combo4.Text + "'"

If (cn.State <> 1) Then
cn.Open "dsn=ABC", "", ""
End If
rs.Open query, cn, adOpenKeyset, adLockOptimistic
RESOURCEINFO.SetFocus
rs.Fields(0) = RESOURCEINFO
rs.Update

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
On Error GoTo Err_Command10_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click

End Sub

i am a beginner. any help would be greatly appreciated.

View 2 Replies View Related

Updating Oracle Backend Tables From MS Access Forms(dynamically)??

Feb 13, 2007

Hello,

My requirement is this...

I have to use MS Access as front end with ODBC connection to Oracle 9i

DB.

The application(forms) should be able to update, delete ,insert records into oracle tables(backend).

i have a main form,which has some unique id's and other info about the ids and the subform shows several matching id's for that unique id in main form. the user who uses this application should be able to
1) search for the unique id in the main form such that the subform displays all its matches
2) they should be able to select anyone match and say approve(there can only be one match), then that particular record should be updated in the table.IF I USE A CHECK BOX AND IF THEY CLICK ON ONE RECORD AS MATCH,HOW
DO I TAKE THAT RECORD SAY THE ID , NAME ADDRESS AND ALL DETAILS AND UPDATE THE TABLE???
similarly when they select some other record i should give option of deleting other irrelevant matches in the backend table.

the main form and the subform uses the same table as source.updates are to another table, i should also have to put entry into audit table about which record was deleted and which one inserted..

How should i do this?? i am new to MS access .VBA, any help would be highly appreciated!

Thanks so much!

View 1 Replies View Related

Updating Multiple Access Records From Imported Excel Spreadsheet?

Mar 1, 2012

Access Database 2010 is used to capture progress on accounts. We are able to perform remedies on multiple accounts in the field and would like to update the records in Access all at once (by batch) rather than one-by-one.

I would like to export specific records from Access into Excel, make the updates to the records in Excel, then import the changes back into Access. I am looking for the updated Excel spreadsheet to overwrite the existing data in Access for that particular record.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved