Adding a new record to the recordset of a form using ADO (adp file) "NoClue" <paul_rgill[ at ]hotmail.com> 29.09.2006 14:42:17 I get the following error when adding a new record to the underlying recordset of my form: data added database won't displayed form because doesn't satisfy criteria underlying record source. The reason is because i am selecting the record set based on a specific ID, which is an autonumber in my sqlserver database. When i create a new record the error above appears because the new record has a new ID which does not fit the original recordset source. Does anyone know away around this? e.g. modifying the recordset source dynamically Thanks in advance.

Re: Adding a new record to the recordset of a form using ADO (adp file) "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 29.09.2006 15:19:57 Not sure what you mean by adding a new record: are you using a bound form, an ADODB recordset or manipulating directly the database with an INSERT statement? Usually, adding a ResyncCommand, setting the UniqueTable property and make sure that all the stored procedures have the SET NOCOUNT ON option set at their beginning should give ride of this problem. Take a look with the SQL-Server profiler to make sure that you are really knowing what's happening on the SQL-Server when this error message occurs. -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF "NoClue" <paul_rgill[ at ]hotmail.com> wrote in message news:1159540937.538068.310500[ at ]m7g2000cwm.googlegroups.com... [Quoted Text] >I get the following error when adding a new record to the underlying > recordset of my form: > > data added database won't displayed form because doesn't satisfy > criteria underlying record source. > > The reason is because i am selecting the record set based on a specific > ID, which is an autonumber in my sqlserver database. When i create a > new record the error above appears because the new record has a new ID > which does not fit the original recordset source. > > Does anyone know away around this? > > e.g. modifying the recordset source dynamically > > Thanks in advance. >

Re: Adding a new record to the recordset of a form using ADO (adp file) "NoClue" <paul_rgill[ at ]hotmail.com> 29.09.2006 15:42:14 In this instance i am setting the forms recordsource using a "select..." statement and specifying a specific ID in the where clause. When i use the form to create a new record (DoCmd.GoToRecord , , A_NEWREC) and then saving the record using (DoCmd.RunCommand acCmdSaveRecord0 i get the error, this is because i am using a new ID which of course does not fit the original select statements criteria. Would this be corrected if i used a ADODB recordset for the forms recordsource. Sylvain Lafontaine (fill the blanks, no spam please) wrote: [Quoted Text] > Not sure what you mean by adding a new record: are you using a bound form, > an ADODB recordset or manipulating directly the database with an INSERT > statement? > > Usually, adding a ResyncCommand, setting the UniqueTable property and make > sure that all the stored procedures have the SET NOCOUNT ON option set at > their beginning should give ride of this problem. > > Take a look with the SQL-Server profiler to make sure that you are really > knowing what's happening on the SQL-Server when this error message occurs. > > -- > Sylvain Lafontaine, ing. > MVP - Technologies Virtual-PC > E-mail: http://cerbermail.com/?QugbLEWINF > > > "NoClue" <paul_rgill[ at ]hotmail.com> wrote in message > news:1159540937.538068.310500[ at ]m7g2000cwm.googlegroups.com... > >I get the following error when adding a new record to the underlying > > recordset of my form: > > > > data added database won't displayed form because doesn't satisfy > > criteria underlying record source. > > > > The reason is because i am selecting the record set based on a specific > > ID, which is an autonumber in my sqlserver database. When i create a > > new record the error above appears because the new record has a new ID > > which does not fit the original recordset source. > > > > Does anyone know away around this? > > > > e.g. modifying the recordset source dynamically > > > > Thanks in advance. > >

Re: Adding a new record to the recordset of a form using ADO (adp file) "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 29.09.2006 16:32:03 Oh, you are using a sql string like "Select * From MyTable Where IdTable = 1". You're right, this is a bad idea. In your case, the solution is simply to replace this Select statement with a Stored Procedure and set the RecordSource to an EXEC statement (or use the InputParameters property) that will call the SP with its arguments: CREATE PROCEDURE dbo.qf_Routes ( [ at ]IdRoute int ) As Select IdRoute, NomRoute From dbo.Routes Where IdRoute = [ at ]IdRoute Order by NomRoute GO And for the RecordSource: EXEC qf_Routes 1 -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF "NoClue" <paul_rgill[ at ]hotmail.com> wrote in message news:1159544534.294586.307950[ at ]m73g2000cwd.googlegroups.com... [Quoted Text] > In this instance i am setting the forms recordsource using a > "select..." statement and specifying a specific ID in the where clause. > > When i use the form to create a new record (DoCmd.GoToRecord , , > A_NEWREC) and then saving the record using (DoCmd.RunCommand > acCmdSaveRecord0 i get the error, this is because i am using a new ID > which of course does not fit the original select statements criteria. > > Would this be corrected if i used a ADODB recordset for the forms > recordsource. > > Sylvain Lafontaine (fill the blanks, no spam please) wrote: >> Not sure what you mean by adding a new record: are you using a bound >> form, >> an ADODB recordset or manipulating directly the database with an INSERT >> statement? >> >> Usually, adding a ResyncCommand, setting the UniqueTable property and >> make >> sure that all the stored procedures have the SET NOCOUNT ON option set at >> their beginning should give ride of this problem. >> >> Take a look with the SQL-Server profiler to make sure that you are really >> knowing what's happening on the SQL-Server when this error message >> occurs. >> >> -- >> Sylvain Lafontaine, ing. >> MVP - Technologies Virtual-PC >> E-mail: http://cerbermail.com/?QugbLEWINF >> >> >> "NoClue" <paul_rgill[ at ]hotmail.com> wrote in message >> news:1159540937.538068.310500[ at ]m7g2000cwm.googlegroups.com... >> >I get the following error when adding a new record to the underlying >> > recordset of my form: >> > >> > data added database won't displayed form because doesn't satisfy >> > criteria underlying record source. >> > >> > The reason is because i am selecting the record set based on a specific >> > ID, which is an autonumber in my sqlserver database. When i create a >> > new record the error above appears because the new record has a new ID >> > which does not fit the original recordset source. >> > >> > Does anyone know away around this? >> > >> > e.g. modifying the recordset source dynamically >> > >> > Thanks in advance. >> > >