 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / March 2005Opening ADO recordset problems
I am using the following code to attenot ti open a recordset and add a new record to it. I'm using Access2003 and the databases are split ( backend with data is in different directory front end with all forms etc) Is this a proper way to open a recordset with a split database? I,m new to ADO. Every time I run this code (it's behind Onclick event of command button) I receive an error about Function is not available in expressions in table-level validation expression. There are no validations rules or text in the fields of this table. Any thoughts or maybe a place to look for answers. I'm at my wits end. Thanks Dim rs2 As New ADODB.Recordset rs2.Open "tbl1", CurrentProject.Connection, adOpenDynamic, adLockOptimistic If rs2.EOF Then rs2.AddNew rs2![Field1] = Forms!frm1!Field1 End if You are missing the rs.update method. Dim rs2 As New ADODB.Recordset rs2.Open "tbl1", CurrentProject.Connection, adOpenDynamic, adLockOptimistic If rs2.EOF Then rs2.AddNew rs2![Field1] = Forms!frm1!Field1 rs2.Update End if rs.close set rs = nothing - RAoul > I am using the following code to attenot ti open a recordset and add a new > record to it. I'm using Access2003 and the databases are split ( backend [quoted text clipped - 15 lines] > rs2![Field1] = Forms!frm1!Field1 > End if Thanks for the response. I neglected to put the rs.Update in the code I posted. It is there though. I left it out because within the code I Dimmed a variable called "Line" and updated its value by one for every line of code. In the error section of the Sub I display a message box with the value of the line and the error message. This way I can find the find the last value of the line and know the code failed on the next line. In my case it is failing on the "If Not rs2.EOF" and "rs2.AddNew" lines. This is why I thought the recordset was not set correctly but I am at a loss. Thanks > You are missing the rs.update method. > [quoted text clipped - 36 lines] >> rs2![Field1] = Forms!frm1!Field1 >> End if Gibson, Is this your code as it is in the module? Among other things, you close 'rs' rather than 'rs2'. Also, this will run through the statements between 'If' ... 'End If' only if the table is empty. You should also be able to do the following, since you are only adding one new record: rs2.AddNew "Field1", Forms!frm1!Field1 Better might be: rs2.AddNew "Field1", Me!Field1.value The 'Me' is an alias for the current form. I prefer not to depend on default properties which is why I added the '.value' but that may be a matter of taste. Good Luck! > Thanks for the response. I neglected to put the rs.Update in the code I > posted. It is there though. I left it out because within the code I Dimmed [quoted text clipped - 46 lines] > >> rs2![Field1] = Forms!frm1!Field1 > >> End if Once again, I apologize for the sloppy posting. I think I've been up to long working on this. Following is the actual code I am attempting to use. I try to open the recordset, move to the first record and then find a record where Field1 matches the variable strRef. If I get to the end of the recordset and don't find it then I add a new record, if I find it then I edit the record. Thanks so much for your input. Dim rs2 As New ADODB.Recordset rs2.Open "tbl1", CurrentProject.Connection, adOpenDynamic, adLockOptimistic rs2.MoveFirst rs2.Find "[Fileld1]= '" & strRef & "'" If rs2.EOF Then rs2.AddNew rs2![Field1] = strRef rs2.Update Else rs2.Edit 'I edit the record' rs2.Update End if rs2.close set rs2 = nothing > Gibson, > [quoted text clipped - 71 lines] >> >> rs2![Field1] = Forms!frm1!Field1 >> >> End if the error is not caused by the add but at the edit ado recordsets don't support the edit method edit is automated by changing a value for the current record. I also clean the code a bit to make it a bit more performant. - Raoul your code should become: Dim rs2 As New ADODB.Recordset rs2.Open "SELECT Field1 FROM tbl1 WHERE Field1='" & strRef & "'", CurrentProject.Connection, adOpenDynamic, adLockOptimistic If rs2.EOF Then rs2.AddNew rs2![Field1] = strRef Else ' rs2.Edit is not used with ADO 'I edit the record' rs2![Field1] = strRef End if rs2.update rs2.close set rs2 = nothing > Once again, I apologize for the sloppy posting. I think I've been up to long > working on this. [quoted text clipped - 98 lines] > >> >> rs2![Field1] = Forms!frm1!Field1 > >> >> End if |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.