Error 80040e14

Really stuck on this update. Code that changes is in SET.

Using Access 2000.

Anyone know why this code works:

cmdUpdate.CommandText = "UPDATE DateAvailable SET EnquiryID = 123 WHERE BookingDate >= #" + Replace(DoDateTime(varFromDate,1,2057), "'", "''") + "# and BookingDate <= #" + Replace(DoDateTime(varToDate,1,2057), "'", "''") + "#"


But the code below gives me the following error:

"You cannot add or change a record because a related record is required in table 'CottageReserve' error '80040e14'"

I have also experienced the following error

"Operation must use an updateable query 80004005" error. I know its not permissions because the code above works:

cmdUpdate.CommandText = "UPDATE DateAvailable SET EnquiryID = (SELECT MAX(EnquiryID) FROM CottageReserve) WHERE BookingDate >= #" + Replace(DoDateTime(varFromDate,1,2057), "'", "''") + "# and BookingDate <= #" + Replace(DoDateTime(varToDate,1,2057), "'", "''") + "#"

The join type I have is "one-to-many". The DateAvailable table doesn't have a PrimaryKey assigned to it (although I did try it with a PrimaryKey, but got the same results).

For info, I also have a Recordset on the same page which gets the MAX ID:

rsGetID.Source = "SELECT * FROM CottageReserve WHERE EnquiryID = (SELECT MAX(EnquiryID) FROM CottageReserve)"

Currently, the field EnquiryID in the DateAvailable table is empty. When a user completes the relevant form a record is created in the CottageReserve table and then the EnquiryID should be put into the corresponding fields in the DateAvailable table (EnquiryID field).

Regards

Paul K

Could you post you DB with tthe two tables and the query. I don't see anything wrong with the statement (but the message has to do with the logic of updating you tables and not about the SQL statement)

S_

Dear S,

Many thanks for your kind reply.

I have uploaded 3 files in the attached ZIP file (2 asp pages (with HTML stripped out) and 1 mdb file.

The problem part is in the AvailabilityThanks.asp page, starting at Line 102.

I've been beating my head against a brick wall for weeks on this. I'm sure the code is correct but I'm just not going about it in a logical way!!

Regards

Paul

Dear S,

Sorry, forgot to mention, there is one record in the CottageReserve table. I manually typed in the EnquiryID into the DateAvailable table. The EnquiryID field in the DateAvailable table is the one I'm having trouble writing to.

Regards

Paul

Have look at this over the past two days, and I can't determine why access won't let you do this. It wosk, in SQL Server, must be a limitation in Access,

Work around:

Create a form with a text box where you capture the latest ID into it.

Us that text box as the holder of you variable for us in you update statement

UPDATE DateAvailable SET DateAvailable.EnquiryID = [forms]![form1]![Text1]
WHERE (((DateAvailable.BookingDate)>=#1/1/2004# And (DateAvailable.BookingDate)<=#1/30/2004#));



S-

Hi S,

Can't thank you enough for the help you have provided.

I tried what you said, but with no luck. I have a form (getID) on the page with a text field (ID) that holds the last record ID. I tried the following 2 versions and got the error messages as indicated. Does the form (getID) need any kind of action on it?


Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

for this code:

"UPDATE DateAvailable SET DateAvailable.EnquiryID = [forms]![getID]![ID] WHERE BookingDate >= #" + Replace(DoDateTime(varFromDate,1,2057), "'", "''") + "# and BookingDate <= #" + Replace(DoDateTime(varToDate,1,2057), "'", "''") + "#"

and...

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

for this code:


if(Request.Form("ID") <> "") then varID = Request.Form("ID")


"UPDATE DateAvailable SET DateAvailable.EnquiryID = " + Replace(varID, "'", "''") + " WHERE BookingDate >= #" + Replace(DoDateTime(varFromDate,1,2057), "'", "''") + "# and BookingDate <= #" + Replace(DoDateTime(varToDate,1,2057), "'", "''") + "#"


Regards

Paul

I would force the entry of the ID and get rid of the replace, for ID, in the SQL statement (other wise you will you into other DB problems)

S-

Hi S

Once again, thanks for your comments.

Getting a bit beyond my limitations of SQL now, regarding forcing entry of the ID. Could you show me how to write that in the UPDATE statement?

Regards

Paul

WHen I say force entry, I mean that before you execute the update, you check the textbox on the form and look to make sure there is a valid ID entered there. Then just reference the textbox in you SQl statement (like you are doing).

S-


I dont know ASP (so I don't know what the replace .... command does), But I would just populate a variable and clean up the SQL statement to include just the final value (it will be easier to trouble shoot). Also if you variable is declared as a date, you don't need the # symbols.





S-

Hi S

Yes, I'm with you now.

The problem lies in the fact that I need to get the last ID, stuff this into the SQL UPDATE statement and then execute the UPDATE, and herein lies the problem. All this needs to be achieved when the page loads, not on a form submission.

When the user enters their details on the previous page, this is inserted into the database and they are redirected to a confirmation page, with EnquiryID, their personal details etc. When the confirmation page is loaded, then the last EnquiryID needs to be put into the SQL UPDATE statement and then executed. The logical thing to do was as I did in the first post of this thread, but as you've seen, this doesn't work in Access.

Looks like this is now more of an ASP issue, so perhaps I should now pose the question to the ASP experts.

Thanks for all your help and advice S. Much appreciated.

Regards

Paul

Hi S

Just to let you know I've finally managed to get the thing working. I think it was more trial, error and luck than anything else.

I had a recordset on the page that retreived the last EnquiryID. Even though the SELECT(MAX) etc code didn't work, stuffing the value from the recordset did, code below:


<%
cmdUpdate__varID = (rsGetID.Fields.Item("EnquiryID").Value)

set cmdUpdate = Server.CreateObject("ADODB.Command")
cmdUpdate.ActiveConnection = MM_pkson_STRING
cmdUpdate.CommandText = "UPDATE DateAvailable SET DateAvailable.EnquiryID = " + Replace(cmdUpdate__varID, "'", "''") + " WHERE DateAvailable.BookingDate >= #" + Replace(DoDateTime(varFromDate,1,2057), "'", "''") + "# and DateAvailable.BookingDate <= #" + Replace(DoDateTime(varToDate,1,2057), "'", "''") + "#"
cmdUpdate.CommandTimeout = 0
cmdUpdate.Prepared = true
cmdUpdate.Execute()
set cmdUpdate = Nothing
%>

Once again, thank you for all your help.

Regards

Paul