Addnew throwing error 3027

In one sub I am trying to add a record to to "Abstracts" table. Records in 
the abstracts table are related to the States and counties tables. So I open 
a recordset with joins. I have tried several times after checking everything 
out and cannot find any reason why the recordset should be read only. I have 
also checked for a lock file (as suggested in another post).

The code:

Private Sub cmdSave_Click()
Dim strSQL As String
Dim rs As DAO.Recordset
Dim db As DAO.Database

    If blnCheckFields Then
        'save new abstract
        Set db = CurrentDb()
        strSQL = "SELECT Abstracts.StateID As AStateID, Abstracts.CountyID 
AS ACountyID, Abstracts.FileNo, " & _
                "Abstracts.OriginalGrantee, Abstracts.Patentee, " & _
                "Abstracts.PatentDate, Abstracts.PatentNumber, 
Abstracts.PatentVol, Abstracts.Certificate, Abstracts.Section, " & _
                "Abstracts.Acreage, Abstracts.AbstractName, 
Abstracts.AbstractNo, County.CountyID, States.StateID" & _
                " FROM States INNER JOIN (County INNER JOIN Abstracts ON 
County.CountyID = Abstracts.CountyID) " & _
                "ON (Abstracts.StateID = States.StateID) AND (States.StateID 
= County.StateID) " & _
                "WHERE (((County.CountyID)=" & cboCounty & ") AND 
((States.StateID)=" & cboState & "));"

        Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
        rs.FindFirst "AbstractNo" = txtAbstractNo
        If rs.NoMatch Then
            rs.AddNew
        Else
            rs.Edit
        End If
        With Me
            rs!ACountyID = .cboCounty
            rs!AStateID = cboState
            rs!FileNo = .txtFileNo
            rs!OriginalGrantee = .txtGrantee
            rs!Patentee = .txtPatentee
            rs!Patentdate = .txtDate
            rs!PatentNumber = .txtPatentNo
            rs!PatentVol = .txtVol
            rs!Certificate = .txtCert
            rs!Section = .txtSection
            rs!Acreage = .txtAcreage
            rs.Update
        End With
        ClearFields
        rs.Close
        db.Close
        Set rs = Nothing
        Set db = Nothing
    Else
        'cancelout and leave routine
        MsgBox "Missing Required Data", vbOKOnly
    End If
    
End Sub

This routine is found in the form "frmAbstracts" and there is nothing else 
open or running at the time.  I have done it this way since the beginning of 
time. Can't figure out what is going on. 

Access 2007, Windows XP Professional, Dell Vostro 1510

Thanks for your help.

Scott
0
Utf
2/11/2010 7:47:01 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

1 Replies
1125 Views

Similar Articles

[PageSpeed] 9

I would avoid the join with State and County, just try it out without the
table join.

strSQL = "SELECT StateID,CountyID 
,FileNo, " & _
"OriginalGrantee, Patentee, " & _
"PatentDate, PatentNumber, PatentVol, Certificate, Section, " & _
"Acreage, AbstractName, AbstractNo " & _
" FROM Abstracts " & _
" WHERE CountyID=" & cboCounty & " AND StateID)=" & cboState & "));"
........
With Me
           rs!CountyID = .cboCounty
           rs!StateID = cboState
           rs!FileNo = .txtFileNo
           rs!OriginalGrantee = .txtGrantee
           rs!Patentee = .txtPatentee
           rs!Patentdate = .txtDate
           rs!PatentNumber = .txtPatentNo
           rs!PatentVol = .txtVol
           rs!Certificate = .txtCert
           rs!Section = .txtSection
           rs!Acreage = .txtAcreage
           rs.Update
       End With
.....
Don't forget about required fields.

ScottInTexas wrote:
snip......

-- 
Please Rate the posting if helps you.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1

0
AccessVandal
2/12/2010 1:17:45 AM
Reply:

Similar Artilces:

Trial Balance Report Error
I am trying to print a detailed trial balance out of Financial. I am getting the following error message: Error in equation ****ILLEGAL SYNTAX*** found]: Any idea on how to fix this? Thanks Angie Did this error just start? Have there been any recent customizations to the report? Try logging out of GP and back in and see if that resolves the issue. If there haven't been any customizations, it's likely a communications error. Frank Hamelly MCP-GP, MCT East Coast Dynamics www.eastcoast-dynamics.com I have this same issue with one user, unfortunately my direct manager. He ...

Syntax Error
Hi Everyone I got these code from "Allexpert.com" that I will modify for my use if I can,but it's giving me a "Syntax Error" at the "IF" statement. Playing around I always get a error at the "IF" or the "For" statement Can anyone help me Sub ChangeColor() Dim LastRow_1 As Integer Dim LastCol_1 As Integer Dim Data_1 As Range Dim LastRow_2 As Integer Dim LastCol_2 As Integer Dim Data_2 As Range Dim Sh_1 As Worksheet Dim Sh_2 As Worksheet Dim X As Long Dim Y As Long Dim C_1 As Range Dim C_2 As Range Set Sh_1 = Active...

divide by zero error
hi get a #DIV/0! error when divide back by 0 but need to show that if the value in the invoice column is greater than 0 but quoted value is 0 then it gives 100% hit rate i.e. work was not quoted but an order was received retrospectively. Total Quoted = V2 Total Invoiced = W2 Hit Ratio = X2 currently have very basic formula of w2/v2 which works until below condition is met. v2 (quoted) is 0 and w2 (inoviced) has a figure and is returning #DIV/ 0! error currently. any help appreciated. =IF(ISERROR(W2/V2),0,W2/V2) This will return 0 if there is a divide by zero error edvwvw Potsy wrote:...

Error: Memory could not be read
I am running a Multithreaded VC application and some times I am getting the error in my application when it runs on the production server. "The instruction ********** referenced memory at "***********". The memory could not be read. Click OK to terminate the program. Click on CANCEL to debug the program. But when I test the same on other machines I am unable replicate . As it is production server I am unable to Debug in that system. Both systems have same configurations (windows XP professional, 2002 version, service pack1) Can someone let me know what are all the general ...

Error code 1603
Just recently, I downloaded version 7.1 for my Wireless Mouse Explorer v.2 and tried to install it. It does not ending in an error code 1603. I investigated that error code and found no helpful explanation under Microsoft's support except for the extraordinary helpful hint that this error is by design ...HuuH? What a BS. I followed all instruction by removing Intellipoint, did a Clean up afterwards etc. Still no success. I had to install the old version 6,2 and even this caused some problems. At one time I had a MS techie from India on the phone and he saw what happened and...

error (0x800CCCF6)
can u help me to configuration on error above please thnaks guys ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=f2073175-a70e-4493-ab06-6a4879a8982a&dg=microsoft.public.outlook Marcio Lupusella <...

Outlook 2003 send/receive error message
Get This operation failed. Object can not be found! when attempting to send email or when I click on Send/Receive buttom. See if http://support.microsoft.com/default.aspx?scid=kb;en-us;312354 helps. -- Jeff Stephenson Outlook Development This posting is provided "AS IS" with no warranties, and confers no rights "Frustrated" <anonymous@discussions.microsoft.com> wrote in message news:00fa01c3dd29$cae21e40$a101280a@phx.gbl... > Get This operation failed. Object can not be found! when > attempting to send email or when I click on Send/Receive > buttom. &...

Who Can explan external error?
I have mentioned that many users faced the ( External-Error,No such object on server). But till now, seems nobody can explain this problem. Nobody can give details. 5555555 Actually, I dont think it's a complex problem. Microsoft may only need to tell us where to maintenance the Domain-User ID in CRM. explain this issue more -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "goldenshine" <goldenshine@discussions.microsoft.com> wrote in message news:CCC3BD25-A209-47A8-8298-EE093D003496@microsoft.com... > I have mentioned that many users faced the ( ...

CRM upgrade (1.2->3.0) error message
sHi all, I am trying to upgrade my 1.2 install to 3. I run the compatibitly test which all went through fine. the error appears during the system requiements stage. The error is against the MS SQL Server REporting Services, and the error states that SRS cannot use the website (website name) because it has an identifier that the SRS setup cannot handle. Then tells me to change to 1. Now, I have checked and the website identifier is 691827, i can change this via metabse editor to 1, however other aspects of the install fail - so i am presuming that CRM attaches to the instance ID, ...

Cut or Copy Error
Hello and thanks ahead for looking at my problem. I am running MS Pub 2000 on XP Home edition with all updates for both installed. I'm trying to update some pages that I have updated before but not for 6 months or so but nevertheless I have done it many time for years. Whenever I try to copy or cut, with the menu or by Control C or x, I get a -->> Microsoft� Publisher 2000 Version 6.0 has encountered a problem and needs to close. We are sorry for the inconvenience.<<-- It doesn't matter where on the form I try to copy the results are the same. I have 17 forms that look ali...

Unexpected error has occured
Hello This has been happening to a user on every Excel worksheet she opens. This pops up "Unexpected error has occured - AutoRecover has been disabled for the session of excel" She has been recently upgraded to Office 2007 SP2 running on XP SP3. I checked online "google" and it says to change the place where the autorecovery is saved. I did that but it is still happening. Please let me know what I am doing wrong. Thanks This KB might help http://support.microsoft.com/kb/289273/en-us best wishes -- Bernard Liengme http://people.stfx.ca/bliengme...

division by zero error
I have data where there is a division by zero error because nothing is entered, but that will change when the data is entered. Is there a way I can suppress the chart from putting 0's in its place so that there is a gap in the chart when there is a division by zero error? Try replacing the division formula by something along these lines: =IF(A1="",NA(),B1/A1) The chart engine ignores N/A best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "Tom" <Tom@discussions.microsoft.com> wrote in message news:570F8C5...

Error assign Account to user
Hi, I have a problem when I'm trying to assign a account to a new user. The system are just saying: unexpected Error Anyone there have same issue? -- Best regards, Anders Jeppesen MCP, MCTS, MPV CRM Gruppen ApS ...

COMPILE ERROR
When I open a Document in Either EXCEL or WORD I get a message, COMPILE ERROR IN HIDDEN MODULE AUTOEXEC, or AUTOEXEC NEW. I get a similar message when I close the document. File works OK but how can I get rid of this. MS say its something to do with the Norton Antivirus I have installed. Never had problems before just recently after I had to reinstall Win 2000, and Office 2000 Thanks PHIL ...

ESM error ID c1030af2 "Internal Server Error"
Hi all, We are in progress of upgrading from our 2000 domain and Exchange to 2003. So far we have deployed a w2003 server, upgraded to a DC and transfered the FSMO roles from the old W2K DC. The W2K DC has been demoted to a member server this server hosts Exchange 2000. Exchange 2003 has been installed on the new 2003 DC in the same site as the E2K. We now come to mgrate Exchange 2000 from the old DC to the new DC with Exchange 2003. As described in Q307917 Removing the first Excahnge 2000 Server from the site, we attempt to connect to the Public folders using ESM to replicate and g...

Error
I get an error when I do anything on a form, the error = "A problem occurred while (database name) was communicating with the OLE server or ActiveX control” I don’t have any ActiveX or OLE server!, this comes up if I write any code or a macro, and then I can’t do anything I have to make a new form and make sure I don’t write code Have a look at this Knowledge Base article and see if it will solve your problem: http://support.microsoft.com/kb/907337/en-us -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Jone" <Jon...

Error #800401d0 When Opening Other Programs While ESM is Open
Hi - When I have Exchange Systme Manager open on my workstation and I try to open other programs like Word, Outlook, other non-Microsoft programs, I get the following error: OpenClipboard Failed ID no: 800401d0 Exchange System Manager Does anyone know what is causing this and how to correct it. Michael ...

Canadian Payroll Error After upgrading to GP9 from GP8
Hi: I have upgraded Great Plains 8 to Dynamics 9 some days ago. Everything is working fine but I am getting error of failed accessing SQL Data in Canadian Payroll windows. I cannot see Employer data. I can see Employee master data. I can create and delete any employee too. I am wondering what will happen when I will run the Payroll Run on this Friday. I will definitly get this error again on some other windows too. I ran the drop and create SQL Stored procedure through File >> Maintenance >> SQL. I have given the Grants too. This error and problem is with 'sa' user ...

error when trying to restore Money 01 backup after hard drive crash
I recently had to replace my hard drive. I installed Money (04 Deluxe) and tried to restore my Money back up that was on an external hard drive. I got this error: "Please insert next backup disk. (The disk containing file "".)" Probably the most useless error message ever since a) i'm pretty sure this is the entire backup and b) it doesn't tell me what file it's talking about. I realized that my previous Money file might have been from Money 2001 Standard so I uninstalled Money 04, installed Money 01, and tried to restore the file again. Same error message. ...

Outlook synchronization error #2
Good evening, we are using Outlook 2003 and MS exchange 2003 with all the patches and updates. We are having this reoccurring problem with a number of users. So far we have tried the following: rebuilt the profiles, the database as well as the OST with no luck. The message mentioned under "Synchronizing local changes in folder 'Conflicts' appears actually in the inbox! The link provided with the error takes you to the Microsoft support site and there is no solution there. We will be very grateful if anyone has any suggestions on how to fix this. Here is the error: 20:14:15 ...

Offline synchronization error
We just upgraded to 2.0. We upgraded our OFFLINE database to 2.0 also. After I run a Z report on the networked computers, the following happens: The synchronoization window is open and shows folders floating from the left to the right and the window says: "Synchronizing table: Alias", it stays on this for a couple of minutes and the following error message pops up: Run-time error '-2147217900 (80040e14)': OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Login timeout expired I've seen others have had this problem, an...

No Current Record error
I have a form with a subform on it. When I open the form with the acFormAdd option (to add a new record) I get the error "No Current Record" on the following: Me.RecordsetClone.MoveLast which is in the load event of a subform on the form. The statements Me.RecordsetClone.MoveLast Me.RecordsetClone.MoveFirst are in my subform so the "Item n of n Items" label box will show the correct number of items. How can I avoid this error message? Robert When you open a form in add mode, it does not load any records. Consequently, the instruction to move to the ...

Get an error when opening Public Folders
When trying to open public folders in Exchange System manager. the token supplied to the function is invalid. id no. 80090308. Do I need a MAPI client on the server like Outlook? I always thought it was a big no no to put Outlook on the same box as Exchange is installed. Thanks On Sun, 27 Mar 2005 16:35:02 -0800, "John McCoy" <JohnMcCoy@discussions.microsoft.com> wrote: >When trying to open public folders in Exchange System manager. the token >supplied to the function is invalid. id no. 80090308. http://support.microsoft.com/?id=324345 > >Do I need a MAPI cl...

Error Message ???
Where can I check for an explanation of a particular error message ? example... Error 10055 : No buffer space is available.... Is there a help file for this where I can search the error number and it will tell me the solution or cause of this error ? thanks Greg, Depends on the error message and when and where it happens. I use Google, Microsoft.com, CustomerSource Knowledgebase for starters. This particular error seems to be a Windows TCP/IP Sockets error, too many connections to a machine. I've seen this when you attempt to connect more than 5 machines to XP Home or 10 mach...

Problem with .addnew
I have a form which allows a user to add a new category to table [CPlan Type]. After update I then want to be able to add an additional record of that category to a table called [CPlan Outcome] for each staff member. I'm using the following code (Item holds the # of Staff members with their ID#s in StaffIDArray() Set RS = db.OpenRecordset("Cplan Outcome", dbOpenDynaset) For Numb = 0 To Item With RS .AddNew !StaffID = StaffIDArray(Numb) !Category = NewCategory !Outcome = "" !U...